TSQLDBStatement.FetchAllToJSON Missing first row of data ?

畅雨 · 2014-05-26 13:49:08

Test code :

  ...
  VQuery:TQuery;
begin   
   VQuery:=TQuery.create(con);
   with VQuery do  
   begin   
      SQL.Clear;
      SQL.Add('select * from table');
      Open;
      Result:=PreparedSQLDBStatement.FetchAllAsJSON(aExpanded);
  end;
  ...

There are 5 row of data in table , but only returns 4.

In SynDB 5440 line

  function TSQLDBStatement.FetchAllToJSON(JSON: TStream; Expanded: boolean;
  DoNotFletchBlobs: Boolean=false): PtrInt;
   ...  
            <-----------------  CurrentRow=1 ?
  // write rows data  
  while Step do begin
      ColumnsToJSON(W,DoNotFletchBlobs);
      W.Add(',');
      inc(result);
    end;
   ...

If I change the following:

    
 // write rows data
    //////////////////////////  畅雨 add begin 2014.05.26
    if CurrentRow=1 then
     begin
      ColumnsToJSON(W,DoNotFletchBlobs);
      W.Add(',');
      inc(result);
     end; 
    /////////////////////////// 畅雨 and end  2014.05.26
    while Step do begin
      ColumnsToJSON(W,DoNotFletchBlobs);
      W.Add(',');
      inc(result);
    end;

THen Return 5 ,why?

Last edited by 畅雨 (2014-05-26 13:49:48)

ab · 2014-05-26 15:05:25

This is by design.

You are calling PreparedSQLDBStatement which is used INTERNALLY by the TQuery wrapper.
You should NOT use it as you do.
The first row is already in the buffers, until you call TQuery.Next.

The correct way of using TQuery is to use it... like a TQuery!

That is:

VQuery.Open;
VQuery.First; // move to the first record
while not VQuery.EOF do begin
   // do something with the current record
   ...
   // move to the next record
   VQuery.Next;
end;

Or use directly the SynDB classes, which is always preferred.
TQuery is an emulation class to be used with existing legacy code.

畅雨 · 2014-05-27 01:09:32

o～～，thanks!

But I need a query of Parameter based on the name,and return json. How I'm doing is the right ?

Last edited by 畅雨 (2014-05-27 01:20:40)

ab · 2014-05-27 05:52:56

I've added RewindToFirst optional parameter to TSQLDBStatement.FetchAllAsJSON() and FetchAllToJSON() methods.
It could be used e.g. for TQuery.FetchAllAsJSON, in your case.

See http://synopse.info/fossil/info/56c52be18f

畅雨 · 2014-05-27 06:09:45

Thank you very much!

畅雨 · 2014-05-27 06:14:33

have a suggestion:
Can Change the Connection property of TQuery to read and write?
property Connection: TSQLDBConnection read fConnection write fConnection;

ab · 2014-05-27 06:24:49

It would be a bad idea, since it may uncouple the TSQLDBConnection and its TSQLDBStatement.
Once a TQuery is initiated, it is tied to a TSQLDBStatement, and its associated TSQLDBConnection.

Why do you want this?

畅雨 · 2014-05-27 06:37:27

I use the Tquery with Pooling, and the connection is different,I want get query object form pool and set conncetion

ab · 2014-05-27 07:04:28

So you have to recreate the TQuery each time.

TQuery is a one-time class.
Just like the standard VCL's TQuery.

mORMot Open Source

#1 2014-05-26 13:49:08

TSQLDBStatement.FetchAllToJSON Missing first row of data ?

#2 2014-05-26 15:05:25

Re: TSQLDBStatement.FetchAllToJSON Missing first row of data ?

#3 2014-05-27 01:09:32

Re: TSQLDBStatement.FetchAllToJSON Missing first row of data ?

#4 2014-05-27 05:52:56

Re: TSQLDBStatement.FetchAllToJSON Missing first row of data ?

#5 2014-05-27 06:09:45

Re: TSQLDBStatement.FetchAllToJSON Missing first row of data ?

#6 2014-05-27 06:14:33

Re: TSQLDBStatement.FetchAllToJSON Missing first row of data ?

#7 2014-05-27 06:24:49

Re: TSQLDBStatement.FetchAllToJSON Missing first row of data ?

#8 2014-05-27 06:37:27

Re: TSQLDBStatement.FetchAllToJSON Missing first row of data ?

#9 2014-05-27 07:04:28

Re: TSQLDBStatement.FetchAllToJSON Missing first row of data ?

Board footer