Postgres CreateBlobStream Issue

[gmb]

Hi there,

Recently upgraded from v 6.1.5 to 6.6.2 (mainly for the fixes of memory problems) and I'm generally happy. But now walked into an issue with BLOBs;

We use Delphi 6 with Postgres 8 with code that looks like this:

Code: Select all

  Stream2 := TMemoryStream.Create;
  Stream2.LoadFromFile(sFile);
  ZTable1.Open;
  ZTable1.First;
  ZTable1.Append;
  ZTable1.FieldByName('att_name').AsString := sFile;
  Stream1:=ZTable1.CreateBlobStream(zTable1.FieldByName('att_file'), bmWrite );
  stream1.Seek(0,soFromBeginning);
  fstream := TFileStream.Create(sFile, fmShareDenyNone);
  Stream1.CopyFrom( fStream, fStream.size);
  stream1.free;
  fstream.free;
  ZTable1.Post;

I captured the generated SQL with ZSQLMonitor:


INSERT INTO mytable (mail_id,att_name,att_file) VALUES (11,'C:/image.jpg',ÿØÿà

which obviously gives a SQL error: syntax error at end of input at
character ....

This used to work with zeos 6.1.5

I tried a couple of different things: using TZquery instead of TZTable, etc, but to no avail....

Anything to do with db encoding maybe?
Hope somebody can help me out.

[gmb]

After doing some research I see it may have something to do with null-character (#0) and binary strings.

Using v 6.1.5 components, the same delphi-code (as displayed above) generates the following SQL:

INSERT INTO email.mailmaster (mail_id,att_file,att_name) VALUES (13,'BMæ\134000\134000\134000\1.....' );

Note the use of \134000 to cater for #0 character

Seems this is handled differently in version 6.6.2.
Could this be possible? Or did I miss something obvious?

Thanks for any help

[gmb]

No worries - solved the problem by using a different protocol.

After tracing the source code in both versions and comparing the different units I came to the conclusion:

In unit ZPlainPostgreSqlDriver.pas:

Code: Select all

function TZPostgreSQL7PlainDriver.DecodeBYTEA(value: string): string;
begin
 result:=value;
end;
....
function TZPostgreSQL8PlainDriver.DecodeBYTEA(value: string): string;
var decoded:pchar;
    len:Longword;
begin
  decoded:=ZPlainPostgreSql8.PQUnescapeBytea(pansichar(value),@len);
  SetLength(result,len);
  if (len > 0) then Move(decoded^,result[1],len);
  ZPlainPostgreSql8.PQFreemem(decoded);
end;

These functions gives back a different results depending on protocol;
For protocol postgresql-7, the function does not decode anything, which could be the cause of my problem(?)

After switching to protocol postgresql-8 (and the consequent scramble for DLL's) the problem was solved.