Isn't there a Step(TRUE) needed in TSQLDBStatement.FetchAllToJSON()?

Bascy · 2012-08-15 06:52:53

While trying to code a native NexusDB driver something struck me in the base code of TSQLDBStatement.
The FetchAllToJSON method starts the data-processing loop with a call to Step. If I would follow this in my NexusDB implementation (or rather if i did not override it ;-)) then the first record would not be processed, as this is the current record when entering the FetchAllToJSON method.

I would expect the data processing loop to look something like this

while Step(result=0) do 
begin
      ColumnsToJSON(W);
      W.Add(',');
      inc(result);
end;

ab · 2012-08-15 07:14:05

As you stated, Step(TRUE) is needed only to rewind the cursor to the first position.

The statement implementation shall position the cursor at the first position before calling Step(FALSE) for the first time.
See how OleDB / ODBC / Oracle / SQLite3 clients are implemented.

This follows the implementation pattern of both SQLite3 and ODBC, which are a good reference of KISS design IMHO.

var Customer: Variant;
begin
with Props.Execute(
'select * from Sales.Customer where AccountNumber like ?',
['AW000001%'],@Customer) do
while Step do
assert(Copy(Customer.AccountNumber,1,8)='AW000001');
end;

Writing Step(result=0) + inc(result) in the loop would make it unecessary overheaded.

The FetchAllToJSON() method is to be called just after the statement being prepared.
It is by design that the current row is processed, until all rows are consumed.
In all cases, since it will be called just after the statement preparation, all rows will be exported.
This is how the ORM part of the framework, sample code and tools (including SynDBExplorer) uses it.
So I think there is no need to change the method.

Bascy · 2012-08-15 07:45:47

I'm still not convinced ;-)

What will be the result of the following code:

var Customer: Variant;
begin
  with Props.Execute( 'select * from Sales.Customer where AccountNumber like ?', ['AW000001%'],@Customer) do
      assert(Copy(Customer.AccountNumber,1,8)='AW000001');
end;

Or in a question: Isn't the first record the active record after running Props.Execute() ?

ab · 2012-08-15 08:00:18

The "Step" method is not labeled "Next".
It does not go to the next row, but perform a step within all rows.

The code you propose is incorrect, and will fail execution at least with most implementations, depending on the underlying client library.
The correct code is:

var Customer: Variant;
begin
  with Props.Execute( 'select * from Sales.Customer where AccountNumber like ?', ['AW000001%'],@Customer) do
    if Step then
      assert(Copy(Customer.AccountNumber,1,8)='AW000001') else
      assert(false,'No data returned');
end;

That is, Step is to be called every time you want to access the content.
- Execute will create, prepare and launch the statement, it will return a statement instance even if no data will be returned: statement has to be valid;
- Step will fetch one row of data into the results buffers, and allow to guess if there is (still) some pending data.

This is the exact same implementation pattern than with SQLite3. See http://www.sqlite.org/c3ref/step.html
In fact, our SynDB classes were modeled after it, since we found out the pattern to be very efficient and match our ORM needs.
This is one of the reasons why it has better performance results than any other TDataSet-based unit.
For instance, see also the column data types handled: SQLite3 types, plus the Currency type. This is enough for most use in high-level languages like Delphi. What is the purpose of having dedicated types for boolean, bytes, smallints, widestring, fixed-length string? This is a "recall" of the 90th computer time, when DB layers were mapping directly the underlying engine. No modern DB engine has a field-aligned on disk layout these days. Hardware we work on is very diverse from the one used in DBase time, whereas the APIs are still inheriting from this good old time.
Just compare how we implemented ODBC, Oracle or OleDB in comparison with other Delphi DB libraries (Zeos, UniDac, and even RemObjects).

Bascy · 2012-08-15 08:22:06

Ok, your first line had me convinced LOL: "Step" is not "Next"

But what confused me was the word "Next"in the first line in the documentation of the Step() method:

    {/ Access the next or first row of data from the SQL Statement result
     - return true on success, with data ready to be retrieved by Column*()
     - return false if no more row is available (e.g. if the SQL statement
      is not a SELECT but an UPDATE or INSERT command)
     - if SeekFirst is TRUE, will put the cursor on the first row of results
     - should raise an Exception on any error }
    function Step(SeekFirst: boolean=false): boolean; virtual; abstract;

Last edited by Bascy (2012-08-15 08:36:46)

ab · 2012-08-15 08:43:37

You are right: I've just rephrased it, to avoid confusion.

See http://synopse.info/fossil/info/05c5fe1469

Thanks for the feedback.

mORMot Open Source

#1 2012-08-15 06:52:53

Isn't there a Step(TRUE) needed in TSQLDBStatement.FetchAllToJSON()?

#2 2012-08-15 07:14:05

Re: Isn't there a Step(TRUE) needed in TSQLDBStatement.FetchAllToJSON()?

#3 2012-08-15 07:45:47

Re: Isn't there a Step(TRUE) needed in TSQLDBStatement.FetchAllToJSON()?

#4 2012-08-15 08:00:18

Re: Isn't there a Step(TRUE) needed in TSQLDBStatement.FetchAllToJSON()?

#5 2012-08-15 08:22:06

Re: Isn't there a Step(TRUE) needed in TSQLDBStatement.FetchAllToJSON()?

#6 2012-08-15 08:43:37

Re: Isn't there a Step(TRUE) needed in TSQLDBStatement.FetchAllToJSON()?

Board footer