CODEWITHSUNDEEP
sql-serverdelphicursorvarying
NizamUlMulk
NizamUlMulk

Reputation: 386

How get an SQL output cursor into a Delphi Data Component?

I have a stored procedure with this signature:

CREATE PROCEDURE SI_Inteligence(@dt datetime, @actions varchar(6), @FullData cursor varying out)

This procedure returns an open cursor.

What kind of component do I need to trap it and iterate over it record by record? It's only a parameter from stored procedure!

procedure DoIt;
var sp: TADOStoredProc;
    x: TADODataSet; //?
begin
  sp               := TADOStoredProc.Create(Self);
  sp.Connection    := myConnection; //TADOConnection Component
  sp.ProcedureName := 'SI_Inteligence';
  sp.Parameters.ParamByName('@dt').Value      := date;
  sp.Parameters.ParamByName('@actions').Value := 'something';
  sp.ExecProc;//? Open doesn't return anything
  x := TADODataSet.Create(Self);
  //How load the cursor??
  x.Assign(sp.Parameters.ParamByName('@FullData') as TADODataSet); //crash

end;

Now I need loop that cursor. How can I do that?

Upvotes: 1

Views: 1066

Answers (1)

RRUZ
RRUZ

Reputation: 136431

The CURSOR params are returned as recordsets, So you can iterate over the data using the TADOStoredProc methods related to the TDataSet class like Eof, Next, FieldByName and so on.

Try this code .

  ADOStoredProc1.ExecProc;
  while not ADOStoredProc1.Eof do
  begin
    //do something 
    //ADOStoredProc1.FieldByName('Foo').Value;    
    ADOStoredProc1.Next;
  end;

if the stored procedure return more than a cursor, you can iterate over the recordsets using the NextRecordset method as is described on this post.

Upvotes: 4

Related Questions