Bug: TZQuery.Bookmark does not work

[mmvisual]

Hello,

I have using a TDBGrid and use the Multiselect feature (dgMultiSelect).

To do any on all records, I have a For loop an want select ever record:
TZQuery.Bookmark := TDBGrid.SelectedRows.Items;

The mistake: The query does not go to the correct data record. The query remains always on the same record.

Then I have make no changes in my code an switch to ZEOS V6.6.6, then my code work fine.

(Lazarus V0.9.29, Windows XP)

Thanks for Help, Markus.

[mdaems]

Markus,

What zeoslib version are you using? SVN testing, trunk , official 7.0 release,snapshot, ...

Bookmark code changed a lot, so please try again with last snapshot if you used an older version.

It may also be possible you didn't use 'correct' code before which worked correctly in zeoslib 6.6 'by accident', because the zeoslib Bookmark implementation allowed it? Is TDBGrid.SelectedRows.Items really a Bookmark type?

Mark

[mmvisual]

I have load the V7.0.0alpha vom 2009-12-30 from Sourceforge.
From where can I load a newer version?

I think the code must be correct, this is my code who is working with V6.6.6:

For i := grdInhalt.SelectedRows.Count DownTo 0 Do
Begin
If i < grdInhalt.SelectedRows.Count Then
qInhalt.Bookmark := grdInhalt.SelectedRows.Items;
q.SQL.Text := 'UPDATE bauteillager';
q.SQL.Add('SET LagerNr=' + MakeSQLString(sFach) + ',');
q.SQL.Add('Kiste_ID=' + IntToStr(iIDK));
q.SQL.Add('WHERE ID = ' + IntToStr(qInhaltID.AsInteger));
q.ExecSQL;
End;

It manipulate the current row and all selected rows.

[mmvisual]

OK, I have found the svn version and install it. But with then I cannot start my exe, a new problem is comming:

I have in my application pictures in the database. This pictures have maximal 64000 bytes size and are in PNG format. When I want load it then I use a stream. Bute the data in the stream are corrupt.

The code works with Zeos V6.6.6 fine:

qFoto.Close;
qFoto.SQL.Text := 'SELECT * FROM foto WHERE ID = ' + IntToStr(iID);
qFoto.Open;
If qFotoBild.IsNull Then
Begin
img.Picture.Clear;
qFoto.Close;
Exit;
End;
st := TMemoryStream.Create;
TGraphicField(qFotoBild).SaveToStream(st);
st.Position := 0;
If st.Size > 0 Then
img.Picture.LoadFromStream(st)
Else img.Picture.Clear;
st.Free;

The error comes on the line "img.Picture.LoadFromStream(st)"

[mdaems]

Grmbl,

Probably some encoding/decoding issue between database and blob field again.
What databse are you using, so whe know at which driver we should focus?

And, just to remain on topic, can you test the bookmark issue by disabling this blob loading?

Mark

[mmvisual]

I use the databases with my application:

- sqlite-3 (Dll V3.7.3)
- mysql-5
- postgresql-8 (with Database Engine V9)

In Future my Application want use firebird-2.1 (V2.5), too. (Firebird does not work with V6.6.6. But I have include the extra code and it have work with V7.0Alpha)

This test, I have done with SQLite.

I make a test application for testing the bookmark.

[mmvisual]

OK, I have test it. The bookmark with the current SVN Version 845 is working mostly correct!

It work correct with:
- SQLite
- MySQL
- PostgreSQL

It does not work with:
- Firebird

[mdaems]

Ooops...

On your last answer concerning the bookmarks, I must say that's impossible. Bookmarks are fully implemented on TDataset level (ie. in the component layer of zeoslib). And this means this code is 100% database independant.
But is IS possible when you bookmark and requery your resultset, the record your bookmarked is in an other position, but that depends on the resultset sorting order. Bookmarks do only remember the row dataset object you were on, not the database record behind it.
Please provide proof that I'm wrong using the zeoslib source code.

Concerning the BLOB data:
Is the data written and read by the same zeoslib version or are you trying to read data written with an older zeoslib version? This makes an iportant difference for SQLite, because the storage algorithm has changed recently for that database.
See SVN revision 792:

Code: Select all

Index: D:/Data/Delphi/ZEOSTesting/src/plain/ZPlainSqLiteDriver.pas
===================================================================
--- D:/Data/Delphi/ZEOSTesting/src/plain/ZPlainSqLiteDriver.pas	(revision 791)
+++ D:/Data/Delphi/ZEOSTesting/src/plain/ZPlainSqLiteDriver.pas	(revision 792)
@@ -57,7 +57,7 @@
 
 {$I ZPlain.inc}
 
-uses ZClasses, ZCompatibility, ZPlainDriver, SysUtils;
+uses ZClasses, ZCompatibility, ZPlainDriver, SysUtils, classes;
 
 const
   WINDOWS_DLL_LOCATION = 'sqlite.dll';
@@ -175,6 +175,11 @@
   Tsqlite_close = procedure(db: Psqlite); cdecl;
   Tsqlite_column_count = function(db: Psqlite): Integer; cdecl;
   Tsqlite_column_bytes = function(db: Psqlite; iCol: Integer): PAnsiChar; cdecl;
+
+  //  NEW : FST  100214
+  Tsqlite_column_rawbytes = function(db: Psqlite; iCol: Integer): integer; cdecl;
+  Tsqlite_column_blob = function(db:PSqlite;iCol:integer):PAnsiChar; cdecl;
+
   Tsqlite_column_name = function(db: Psqlite; iCol: Integer): PAnsiChar; cdecl;
   Tsqlite_column_decltype = function(db: Psqlite; iCol: Integer): PAnsiChar; cdecl;
   Tsqlite_exec = function(db: Psqlite; const sql: PAnsiChar;
@@ -261,6 +266,11 @@
   sqlite_close: Tsqlite_close;
   sqlite_column_count: Tsqlite_column_count;
   sqlite_column_bytes: Tsqlite_column_bytes;
+
+//  NEW : FST  100214
+  sqlite_column_rawbytes: Tsqlite_column_rawbytes;
+  sqlite_column_blob:TSqlite_column_blob;
+
   sqlite_column_name: Tsqlite_column_name;
   sqlite_column_decltype: Tsqlite_column_decltype;
   sqlite_exec: Tsqlite_exec;
@@ -372,6 +382,9 @@
       nKey: Integer; var pErrcode: Integer; var pzErrmsg: PAnsiChar): Psqlite;
     function ReKey(db: Psqlite; const pKey: Pointer; nKey: Integer): Integer;
     function Key(db: Psqlite; const pKey: Pointer; nKey: Integer): Integer;
+
+//  NEW : FST  100214
+    function getBlob(pVm: Psqlite_vm; columnID: integer): TMemoryStream;
   end;
 
   {** Implements a base driver for SQLite}
@@ -447,6 +460,9 @@
       nKey: Integer; var pErrcode: Integer; var pzErrmsg: PAnsiChar): Psqlite;
     function ReKey(db: Psqlite; const pKey: Pointer; nKey: Integer): Integer;
     function Key(db: Psqlite; const pKey: Pointer; nKey: Integer): Integer;
+
+//  NEW : FST  100214
+    function getBlob(pVm: Psqlite_vm; columnID: integer): TMemoryStream;
   end;
 
   {** Implements a driver for SQLite 3 }
@@ -736,6 +752,18 @@
   Result := SQLite_API.sqlite_set_result_string(func, arg, len, nil);
 end;
 
+
+//  NEW : FST  100214
+function TZSQLiteBaseDriver.getBlob(pVm:Psqlite_vm;columnID:integer):TMemoryStream;
+var P : Pointer;
+    len : integer;
+begin
+   result := TMemoryStream.Create;
+   P := SQLite_API.sqlite_column_blob(pVm,columnID-1);
+   len := SQLite_API.sqlite_column_rawbytes(pVm,ColumnID-1);
+   result.WriteBuffer(P^,len);
+end;
+
 function TZSQLiteBaseDriver.Step(pVm: Psqlite_vm; var pN: Integer;
   var pazValue, pazColName: PPAnsiChar): Integer;
 var
@@ -762,9 +790,9 @@
     begin
       if Result = SQLITE_ROW then
       begin
-        val  := SQLite_API.sqlite_column_bytes(pVm, i);
         cname:= SQLite_API.sqlite_column_name(pVm, i);
         ctype:= SQLite_API.sqlite_column_decltype(pVm, i);
+        val  := SQLite_API.sqlite_column_bytes(pVm, i);
         pazValue0^ := val;
         inc(pazValue0);
       end
@@ -813,6 +841,11 @@
   @SQLite_API.sqlite_close                  := GetAddress('sqlite3_close');
   @SQLite_API.sqlite_column_count           := GetAddress('sqlite3_column_count');
   @SQLite_API.sqlite_column_bytes           := GetAddress('sqlite3_column_text');
+
+//  NEW : FST  100214
+  @SQLite_API.sqlite_column_rawbytes        := GetAddress('sqlite3_column_bytes');
+  @SQLite_API.sqlite_Column_blob            := GetAddress('sqlite3_column_blob');
+
   @SQLite_API.sqlite_column_name            := GetAddress('sqlite3_column_name');
   @SQLite_API.sqlite_column_decltype        := GetAddress('sqlite3_column_decltype');
   @SQLite_API.sqlite_exec                   := GetAddress('sqlite3_exec');
Index: D:/Data/Delphi/ZEOSTesting/src/dbc/ZDbcSqLiteResultSet.pas
===================================================================
--- D:/Data/Delphi/ZEOSTesting/src/dbc/ZDbcSqLiteResultSet.pas	(revision 791)
+++ D:/Data/Delphi/ZEOSTesting/src/dbc/ZDbcSqLiteResultSet.pas	(revision 792)
@@ -724,8 +724,11 @@
       if TZAbstractResultSetMetadata(Metadata).GetColumnType(ColumnIndex)
         <> stBinaryStream then
         Stream := TStringStream.Create(GetString(ColumnIndex))
-      else
-        Stream := TStringStream.Create(DecodeString(GetString(ColumnIndex)));
+      else begin
+//  NEW : FST  100214
+          Stream := FPlaindriver.getblob(FStmtHandle,columnIndex);
+  //        Stream := TStringStream.Create(DecodeString(GetString(ColumnIndex)));
+      end;
       Result := TZAbstractBlob.CreateWithStream(Stream)
     end
     else
Index: D:/Data/Delphi/ZEOSTesting/src/dbc/ZDbcSqLiteUtils.pas
===================================================================
--- D:/Data/Delphi/ZEOSTesting/src/dbc/ZDbcSqLiteUtils.pas	(revision 791)
+++ D:/Data/Delphi/ZEOSTesting/src/dbc/ZDbcSqLiteUtils.pas	(revision 792)
@@ -237,6 +237,43 @@
   end;
 end;
 
+
+function NewEncodeString(Value: ansistring): ansistring;
+var
+  I: Integer;
+  SrcLength, DestLength: Integer;
+  SrcBuffer, DestBuffer: PAnsiChar;
+  IH : integer;
+begin
+
+  SrcLength := Length(Value);
+  SrcBuffer := PAnsiChar(Value);
+  DestLength := 2+ 2*SrcLength;  // Hex-value double
+
+  SrcBuffer := PAnsiChar(Value);
+  result := '';
+  for I := 1 to SrcLength do
+  begin
+    IH := ord(SrcBuffer^);
+    result := result + IntToHex(IH,2);
+    Inc(SrcBuffer,1);
+  end;
+  result := 'x'+QuotedStr(result);
+end;
+
+
+function NewDecodeString(Value:ansistring):ansistring;
+var iH, i : integer;
+    srcbuffer, destbuffer : PAnsichar;
+begin
+  value := copy(value,3,length(value)-4);
+  value := AnsiLowercase(value);
+  i := length(value) div 2;
+  srcbuffer := PAnsiChar(value);
+  setlength(result,i);
+  HexToBin(PAnsiChar(srcbuffer),PAnsiChar(result),i);
+end;
+
 {**
   Converts an string into escape PostgreSQL format.
   @param Value a regular string.
@@ -248,6 +285,9 @@
   SrcLength, DestLength: Integer;
   SrcBuffer, DestBuffer: PAnsiChar;
 begin
+
+  result := NewEncodeString(Value);
+exit;
   SrcLength := Length(Value);
   SrcBuffer := PAnsiChar(Value);
   DestLength := 2;
@@ -309,6 +349,11 @@
   {$IFDEF DELPHI12_UP} 
   value := utf8decode(value);
   {$ENDIF}
+  if pos('x''',value)= 1 then
+    result := NewDecodeString(value)
+  else result := value;
+  exit;
+
   SrcLength := Length(Value);
   SrcBuffer := PAnsiChar(Value);
   SetLength(Result, SrcLength);
Index: D:/Data/Delphi/ZEOSTesting/documentation/articles/changes/changes.xml
===================================================================
--- D:/Data/Delphi/ZEOSTesting/documentation/articles/changes/changes.xml	(revision 791)
+++ D:/Data/Delphi/ZEOSTesting/documentation/articles/changes/changes.xml	(revision 792)
@@ -55,6 +55,9 @@
       <title>Features added</title>
       <itemizedlist spacing="compact">
         <listitem>
+          <simpara>[SQLite] Blobdata is now stored as original bytes, without conversion</simpara>
+        </listitem>
+        <listitem>
           <simpara>[POSTGRES] Added new Postgres 8.3 Enum support (<ulink url="http://zeos.firmos.at/viewtopic.php?t=2625">http://zeos.firmos.at/viewtopic.php?t=2625</ulink>)</simpara>
         </listitem>
      </itemizedlist>

I agree this may cause some trouble to you as it involves a database conversion (or unload/load using different zeslib versions)
I don't know exactly what the changes does, but if I understood correctly it means zeoslib now stores blobs exaclty so other tools can also read the blob. And before the blob was encoded in some form.
WARNING : this interpretation may prove wrong.

Mark

[mmvisual]

I have test again with bookmarks, but with firebird does it not work. Here is my code:

Code: Select all

procedure TfrmKisteTausch.btnBauteilInFachClick(Sender: TObject);
Var q: TZQuery;
  sqq: TZSequence;
  tn: TTreeNode;
  i, iB, iL: Integer;
  Procedure CreateSequence(Var sq: TZSequence; Var q: TZQuery; Const sGen: String);
  Begin
    sq := TZSequence.Create(Self);
    sq.Connection := q.Connection;
    sq.SequenceName := sGen;
    q.Sequence := sq;
    q.SequenceField := 'ID';
  end;
begin
  If qBauteil.EOF And qBauteil.BOF Then Exit;
  If qBauteilBID.AsInteger = 0 Then Exit;
  If Not Assigned(tvKiste.Selected) Then Exit;
  ErrMessagesAdd('TfrmKisteTausch.btnBauteilInFachClick');
  tn := tvKiste.Selected;
  If Assigned(tn.Parent) Then
    tn := tn.Parent;
  q := TZQuery.Create(Self);
  q.Connection := frmMain.DB;
  sqq := Nil;
  If iSQLTyp = 2 Then // Firebird
    CreateSequence(sqq, q, 'BAUTEILLAGER_ID_GEN');
  iB := qBauteilBID.AsInteger;
  iL := qBauteilLID.AsInteger;
  For i := grdBauteil.SelectedRows.Count DownTo 0 Do
  Begin
    If i < grdBauteil.SelectedRows.Count Then
    Begin
      qBauteil.Bookmark := grdBauteil.SelectedRows.Items[i];
      If (iB = qBauteilBID.AsInteger) And (iL = qBauteilLID.AsInteger) Then
        Continue;
    end;
    q.SQL.Text := 'SELECT * FROM bauteillager WHERE Bauteil_ID = ' + IntToStr(qBauteilBID.AsInteger);
    q.Open;
    q.BeforePost := @frmMain.qryBeforePost;
    If q.BOF And q.EOF Then // Keine Lagerzuordnung gefunden, erzeuge eine
    Begin
      q.Append;
      q.FieldByName('Bauteil_ID').AsInteger := qBauteilBID.AsInteger;
      q.FieldByName('Kiste_ID').AsInteger := Integer(tn.Data);
      If Assigned(tvKiste.Selected.Parent) Then
        q.FieldByName('LagerNr').AsString := tvKiste.Selected.Text;
      q.Post;
    end Else Begin
      If q.Locate('ID', qBauteilLID.AsInteger, []) Then  // Bauteil bereits als Lager/Gehäuse angelegt
      Begin
        q.Edit;
        q.FieldByName('Kiste_ID').AsInteger := Integer(tn.Data);
        If Assigned(tvKiste.Selected.Parent) Then
          q.FieldByName('LagerNr').AsString := tvKiste.Selected.Text;
        q.Post;
      End Else Begin // Doch keine Lagerzuordnung gefunden, erzeuge eine
        q.Append;
        q.FieldByName('Bauteil_ID').AsInteger := qBauteilBID.AsInteger;
        q.FieldByName('Kiste_ID').AsInteger := Integer(tn.Data);
        If Assigned(tvKiste.Selected.Parent) Then
          q.FieldByName('LagerNr').AsString := tvKiste.Selected.Text;
        q.Post;
      End;
    End;
  end;
  q.Free;
  If Assigned(sqq) Then
    sqq.Free;
  grdBauteil.SelectedRows.Clear;
  OpenBauteil;
  tvKisteChange(Nil, Nil);
  ErrMessagesRemove();
end;

With the blob graphic data, the databases work correctly (only read tested):
- MySQL
- Postgresql
- Firebird

With SQLite ist a problem. I cannot showing the Images and it make a error.
How can I convert the SQLite Blob-Fields into the new Zeos format?
I think the new raw data format for SQlite is better.

Markus

[mdaems]

Markus,
What is the strange behaviour of the bookmarks with firebird? Are all bookmarks identical? Or are they different (the way it should be) but doesn't he jump to another record?

Concerning the BLOBs, I'm afraid I do not have a good solution right away. I never knew how the BLOBs were encoded so I don't know how they must be restored either.
Maybe the best solution is we re-add the old encoding/decoding mechanism and switch based on a TZQuery propertis entry. Like Q.Properties.Append('OldBlobEncoding=true') Not sure if that is possible. But if it is you can make a convertor with 2 queries of which the reader uses this setting.
Other ideas:
- Write an unloader with zeoslib 7.0 alpha and a loader with the new version. A generic command line tool downloading the BLOB in a file named <primarykey>.blob accepting as parameter a 2 column select statement would be ideal. Then the binaries could be published for use by all zeoslib users with this problem.
- Check if the old decoding method is still available in zeoslib and look if you can reuse it for a small conversion program.

Hope you can find a slution.

Mark

[mmvisual]

Bookmarks:
I have a application who can use this 4 databases. And I have in all 4 databeses the same data stored. If you want, I can send you my firebird and SQLite database an the source code, then you can see/debug it self. (write me a mail, but a lot of codelines )

For blob, I think the first solution is the best. Then I can programing a database update into my EXE. This month have more then 600 peoples download my freeware and I think they all cannot make self a conversion.
(It was published on a good visited webpage)

[wwywt]

I got the same problem
blob field write by a zlib old version and delphi2007
now i want to read this data with zlib7 and delphi2010
how can i do ?

[mmvisual]

I have not find out the mistake. I use the old release, but I use Lazarus.