mORMot Open Source

jvillasantegomez

Member

From: Ciudad Habana. Cuba.

Registered: 2013-10-21

Posts: 54

Problem with cursors and Step

Hello all

I'm having a problem when trying to Step from a cursor. Here's some code

function TMyClass.GetCursor: ISQLDBRows;
var
  query: RawUTF8;
  stmt : ISQLDBStatement;
  cursor: ISQLDBRows;
begin
  query := 'BEGIN SOME_PACKAGE.SOME_METHOD(P_cursor => ?); END;';
  stmt := Props.NewThreadSafeStatementPrepared(query, false);
  stmt.BindCursor(1);
  stmt.ExecutePrepared;
  cursor := stmt.BoundCursor(1);
  Result := cursor;
end;

// here's the calling code
cursor :=  TMyClass.GetCursor;
while cursor.step do begin
  writeln(cursor['column1'], ' ', cursor['column2']);
end;

The problem here is that, even when cursor.FetchAllAsJSON(true); returns ok with all the data on the database, when I try to step a cursor with code it returns fast, meaning that it doesn't write any line on the console. The problem seems to be on unit SynDBOracle on function TSQLDBOracleStatement.Step(SeekFirst: boolean): boolean;

function TSQLDBOracleStatement.Step(SeekFirst: boolean): boolean;
var sav, status: integer;
begin
  if not Assigned(fStatement) then
    raise ESQLDBOracle.CreateFmt('%s.Execute should be called before Step',[fStatementClassName]);
  result := false;
  if (fCurrentRow<0) or (fRowCount=0) then  // <<---- here's the problem
    exit; // no data available at all
  sav := fCurrentRow;
  fCurrentRow := -1;
  if fColumnCount=0 then
    exit; // no row available at all (e.g. for SQL UPDATE) -> return false

when I call Step on a cursor, fCurrentRow is always -1 so the function exists, but I know that the cursor is not empty 'cause when I call cursor.fetchAllAsJson it returns all the data.

Any help??

Best Regards!

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,659

Website

Re: Problem with cursors and Step

If you take a look at function TSQLDBStatement.FetchAllToJSON():

    // write rows data
    while Step do begin
      ColumnsToJSON(W,DoNotFletchBlobs);
      W.Add(',');
      inc(result);
    end;

So it uses "while Step" just like you.

What I suppose is that your code is not correct.
In fact, you are releasing the main statement (stmt : ISQLDBStatement) BEFORE fetching the cursor data.
So there is no data any more in the "cursor: ISQLDBRows" variable since the associated statement has been set to "stmt := nil" when TMyClass.GetCursor returned.

jvillasantegomez

Member

From: Ciudad Habana. Cuba.

Registered: 2013-10-21

Posts: 54

Re: Problem with cursors and Step

Hello

I'm really sorry, it works ok.

The problem is that I'm not working anymore with Mormot directly, I made the server and gave it to coworkers to finish it and they where who tell me about this issue, but the real issue was that the database wasn't returning any data, so, the cursor, rightly, was returning early. I tried it this morning on my own computer and the code works well. I have to try things out before posting here and not trust blindly in my coworkers.

Best regards and long live Mormot...

Last edited by jvillasantegomez (2014-02-06 14:47:35)

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,659

Website

Re: Problem with cursors and Step

No problem.