Problems updating Blob field

[PhillHS]

Hi,

I'm trying to update a Blob field in one of my tables from a small word doc file that is stored in a TStream.

I'm using Delphi 7, Zeos 6.6.5, and MySQL 5.0.x

My update code is as follows :

Code: Select all

PROCEDURE TChemDataModule.SaveDatasheet(InChemicalIDNo  : Cardinal;
                                        Datasheet       : TStream;
                                        FileName        : STRING);

VAR     Blob    : TStream;

BEGIN;
  WITH ZDatasheetQuery DO
  BEGIN;
    IF (NOT LocateDatasheet(InChemicalIDNo)) THEN
    BEGIN;
      Append;
      FieldByName(DBFChemDataChemID).AsInteger:=InChemicalIDNo;
    END;

    FieldByName(DBFChemDataFilename).AsString:=FileName;
    Blob:=CreateBlobStream(FieldByName(DBFChemDataDatasheet),bmWrite);
    TRY
      Datasheet.Seek(soFromBeginning,0);
      Debug.Log('Blob.Size=%d, Datasheet.Size=%d',[Blob.Size,Datasheet.Size]);
      Blob.CopyFrom(Datasheet,0);//Datasheet.Size);
      Debug.Log('Blob.Size=%d',[Blob.Size]);

      post;
      ApplyUpdates;
    FINALLY
      Blob.Free;
    END;
  END;
END;

My logging statements produce the following output :

Code: Select all

[127.0.0.1] [/:0] Blob.Size=0, Datasheet.Size=48640
[127.0.0.1] [/:0] Blob.Size=48640
[127.0.0.1] [/:0] SQLLog:2009-11-16 12:09:35 cat: Execute, proto: mysql-5, msg: INSERT INTO TestChemicalDB.datasheets (chemical_id,datasheet,filename) VALUES (23,NULL,'industrial methylated spirit.doc')

Which seems to sugest to me that the blob data is never being sent to the MySQL server, and the Blob field is remaining null.

Does anyone know how I can get this working ?

Cheers.

Phill.

[PhillHS]

Trying another way of doing the update :

Code: Select all

PROCEDURE TChemDataModule.SaveDatasheet(InChemicalIDNo  : Cardinal;
                                        Datasheet       : TStream;
                                        FileName        : STRING);

VAR     Blob    : TStream;

BEGIN;
  WITH ZDatasheetQuery DO
  BEGIN;
    Active:=FALSE;

    SQL.Text:=Format('replace into %s values(:%s,:%s,:%s)',
                     [DbTableChemDatasheet.TableName,
                      DBFChemDataChemID,DBFChemDataDatasheet,
                      DBFChemDataFilename]);

    ParamByName(DBFChemDataChemID).AsInteger:=InChemicalIDNo;
    ParamByName(DBFChemDataDatasheet).LoadFromStream(Datasheet, ftBlob);
    ParamByName(DBFChemDataFilename).AsString:=FileName;
    ExecSQL;
  END;
END;

If I run the above code I get an EIdPackageSizeTooBig reported in IdStackWindows, which is an Indy unit. This only seems to happen if the blob is bigger than about 36 kilobytes.

Cheers.

Phill.

[PhillHS]

Ok, I have got it working using the second version, the exception was being caused by my logging code which was being called from an TZSQLMonitor.

Cheers.

Phill.