mORMot Open Source

igors233

Member

Registered: 2012-09-10

Posts: 234

SynDBZeos, Firebird and Step

I'm connecting to legacy FB database using ZeosLib (ver 7.3, but same is with current 7.2), I'm using something like this:

  var
    qry: ISQLDBStatement;
  begin
      qry := MainSrv.Props.MainConnection.NewStatementPrepared('SELECT * FROM MY_TABLE WHERE SOME_FIELD = ?');
      qry.BindTextS(1, SomeValue);

      qry.ExecutePrepared;

      while qry.Step(False) do
      begin
        // some processing
      end;

      while qry.Step(True) do
      begin
        // some additional processing
      end;
  end;

Problem is that second qry.Step fails because underlying TZInterbase6PreparedStatement is in rtForwardOnly mode (ResultSetType := rtForwardOnly), so trying to go to first record will raise exception.
If ResultSetType is rtScrollInsensitive, this would work since statement would use TZCachedResultSet to handle data access.

Question is how to reuse prepared statement results, traversing it several times?

mpv

Member

From: Ukraine

Registered: 2012-03-24

Posts: 1,544

Website

Re: SynDBZeos, Firebird and Step

Try to avoid bi-directional cursors. It kill the database engine.  In your case it is better to fetch all data to JSON table

var 
 buf: RawUTF8;
 inMemoryTable: TSQLTableJSON;
  
 qry.ExecutePreparedAndFetchAllAsJSON(false, buf);
 inMemoryTable :=  TSQLTableJSON.Create('', buf);
 for i := 0 to inMemoryTable.RowCount - 1 do 
  write(inMemoryTable.getU(i, 1));

 for i := inMemoryTable.RowCount - 1 downto 0 do 
  write(inMemoryTable.getU(i, 1)); 

igors233

Member

Registered: 2012-09-10

Posts: 234

Re: SynDBZeos, Firebird and Step

Try to avoid bi-directional cursors. It kill the database engine.  In your case it is better to fetch all data to JSON table

var 
 buf: RawUTF8;
 inMemoryTable: TSQLTableJSON;
  
 qry.ExecutePreparedAndFetchAllAsJSON(false, buf);
 inMemoryTable :=  TSQLTableJSON.Create('', buf);
 for i := 0 to inMemoryTable.RowCount - 1 do 
  write(inMemoryTable.getU(i, 1));

 for i := inMemoryTable.RowCount - 1 downto 0 do 
  write(inMemoryTable.getU(i, 1)); 

Thanks, this is not bi-directional cursor. Zeos, fetches all data at once from database in cached result set and with it gives bi-directional support.
Also I'm working with small data, like 10 at most and they are always fetched fully but sometimes you need to go through same result set again. Using local table is OK, but I think it complicates things a bit.
Resuing one statement worked fine a year ago (previous ZeosLib version) since then it had different default options (scrollable vs unidirectional).
I think it would be good to extend SynZeosDB to include access to underlying zeos class from which you could control this option and as well manually start Transaction. For example right now if you're using HardCommit transactions in FB (recommended option), it's impossible to issue direct DML (insert, update, delete) from mORMot prepared statement if you're dealing with blob fields.