[SOLVED] How to retrieve stored procedure resultset using ADO

serdarulutas · 2016-03-16

I've been searching on internet and trying. My stored procedure seems like running I cannot catch the result set. It returns -1 records. Normally, it should retrieve 1 record. I appreciate your help.

I use 'Microsoft ActiveX Data Objects 2.8 Library'

CREATE(Conn2); //'Microsoft ActiveX Data Objects 2.8 Library'.Connection
Conn2.ConnectionString := 'PROVIDER=SQLOLEDB;Data Source=%1;Initial Catalog=%2;Trusted_Connection=Yes;' //(or the alternative below)
//Conn2.ConnectionString := 'Provider=SQLNCLI10;Server=%1;Database=%2;Trusted_Connection=yes';
Conn2.Open();

CREATE(ADOCommand2); //'Microsoft ActiveX Data Objects 2.8 Library'.Command
ADOCommand2.ActiveConnection := Conn2;
ADOCommand2.CommandText := 'MyStoredProcedure';
ADOCommand2.CommandType := 4;
ADOCommand2.CommandTimeout := 0;
ADOCommand2.Execute; //This takes time

CREATE(RecSet2); //'Microsoft ActiveX Data Objects 2.8 Library'.Recordset
RecSet2.ActiveConnection := Conn2;
RecSet2.Open(AdoCommand2);
RecSet2.movefirst;

MESSAGE('Done: (%1)',RecSet2.RecordCount);

It always show RecordCount -1. It is -1 when I execute it as "RecSet2 = ADOCommand2.Execute;" as well

Thanks

FYI: The SP must return a result set. It is not necessarily 1 records only. I need to loop through on records

serdarulutas · 2016-03-16

Thanks for the reply. It is resolved with this:

CREATE(Conn2);
Conn2.ConnectionString := ConnString;
Conn2.Open();

CREATE(RecSet2);
RecSet2.ActiveConnection := Conn2;
RecSet2.Open('EXEC MyStoredProcedure',Conn2,1,3);

RecSet2.MoveFirst;

RockWithNAV · 2016-03-16

Hey serdarulutas ,

Check out this link.

https://rockwithnav.wordpress.com/tag/call-stored-procedure-from-nav-automation/

After ADOCommand.Execute; you need to write code like this.

IF ISCLEAR(ADORecSet) THEN
CREATE(ADORecSet,FALSE,TRUE);

ADORecSet.ActiveConnection := VarActiveConnection;
ADORecSet.Open(ADOCommand);

WHILE NOT ADORecSet.EOF DO BEGIN
TextFile := FORMAT(ADORecSet.Fields.Item('File').Value);
ADORecSet.MoveNext;
END;

Message('%1',File );

File is something which your SP is returning.

serdarulutas · 2016-03-16

Thanks for the reply. It is resolved with this:

CREATE(Conn2);
Conn2.ConnectionString := ConnString;
Conn2.Open();

CREATE(RecSet2);
RecSet2.ActiveConnection := Conn2;
RecSet2.Open('EXEC MyStoredProcedure',Conn2,1,3);

RecSet2.MoveFirst;

RockWithNAV · 2016-03-16

Great

[SOLVED] How to retrieve stored procedure resultset using ADO

Best Answer

Answers

Categories