Recent

Author Topic: MS SQL Server Stored Procedure Return Values  (Read 6046 times)

Steve

  • New Member
  • *
  • Posts: 11
MS SQL Server Stored Procedure Return Values
« on: May 26, 2016, 06:22:29 pm »
Hi,

I am using Lazarus 1.6 FPC 3.0.0  for x86_64-win64-win32/win64. I need to be able to execute a stored procedure in SQL Server and then read the return integer value, all output parameter values and the records returned by the stored procedure. I am using TODBCConnection with TSQLQuery. 

The below code illustrates the scenario where the stored procedure named sp_Test takes one output varchar parameter, one input varchar parameter and returns an integer.

Code: Pascal  [Select][+][-]
  1. with query.SqlQuery do
  2.     begin
  3.       DataBase:= connSet.Connection;
  4.       PacketRecords:= -1;
  5.       UsePrimaryKeyAsKey:= false;
  6.  
  7.       SQL.Clear;
  8.       SQL.Text:= 'EXEC :Return = Stocktake.dbo.sp_Test :TestOut OUT, :TestIn';
  9.       Params.BeginUpdate;
  10.       Params.CreateParam(TFieldType.ftInteger, 'Return', TParamType.ptResult);
  11.       Params.CreateParam(TFieldType.ftString, 'TestOut', TParamType.ptOutput);
  12.       Params.CreateParam(TFieldType.ftString, 'TestIn', TParamType.ptInput);
  13.       Params.ParamByName('TestIn').Value:= 'TEST IN';
  14.       Params.EndUpdate;
  15.  
  16.       ReadOnly:= true;
  17.       Open;
  18.  
  19.       ShowMessage('TestIn: ' + Params.ParamByName('TestIn').AsString);
  20.       ShowMessage('TestOut: ' + Params.ParamByName('TestOut').AsString);
  21.       ShowMessage('Return: ' + Params.ParamByName('Return').AsString);
  22.     end;      
  23.  

The stored procedure executes correctly using the value of the input parameter and I am getting the records returned okay which can be loaded into a DBGrid.

What I am not getting, however, is a returned value for the output parameter or a value for the return integer as set by the stored procedure. I am trying to do this by reading the query parameter values after opening the records, but perhaps that is not the right way to do it? I know the parameters are going into SQL Server because I can read the values back through the records returned. Note that in the actual requirement I need to be able to read the output parameter values and the returned integer separately from the records returned because there will not always be  records returned and I need to take action based on the specific return integer and output values first.

Any assistance would be greatly appreciated...

Thanks
« Last Edit: May 26, 2016, 06:26:02 pm by Steve »

LacaK

  • Hero Member
  • *****
  • Posts: 691
Re: MS SQL Server Stored Procedure Return Values
« Reply #1 on: May 29, 2016, 06:11:03 pm »
I am afraid, that support for output parameters is not implemented ATM.
I can look how ODBC handles them and if there is way how to add support to sqlDB ...

Steve

  • New Member
  • *
  • Posts: 11
Re: MS SQL Server Stored Procedure Return Values
« Reply #2 on: May 30, 2016, 04:11:14 am »
Okay, thanks for letting me know.

As this is only on Windows I can get this functionality using ADO. I was just hoping to use TODBC because of the DBGrid binding, and quite frankly the speed.

Appreciate the response.

LacaK

  • Hero Member
  • *****
  • Posts: 691
Re: MS SQL Server Stored Procedure Return Values
« Reply #3 on: May 30, 2016, 10:58:51 am »
I have looked at ODBC specification and it seems to me, that it will be problematic add support for output parameters.

From ODBC documentation:
"SQL Server stored procedures can have integer return codes and output parameters.
The return codes and output parameters are sent in the last packet from the server and are not available to the application until SQLMoreResults returns SQL_NO_DATA.
If an error is returned from a stored procedure, call SQLMoreResults to advance to the next result until SQL_NO_DATA is returned."

As far as stored procedure can return multiple resultset (in your case at least one is returned) we must first fetch all rows from all resultsets before we can read OUTPUT parameters.
It is problematic as far as structure of local dataset is determined by structure of first resultset returned from server. If next resultset will have different structure we can not alter existing local dataset structure on the fly.

I can imagine only situation, where stored procedure does not return resultset, then probably we can read OUTPUT parameters just after executing stored procedure, because we are not required fetch rows in this case ...

Btw how do you use ADO with FreePascal ?

Thaddy

  • Hero Member
  • *****
  • Posts: 14214
  • Probably until I exterminate Putin.
Re: MS SQL Server Stored Procedure Return Values
« Reply #4 on: May 30, 2016, 12:17:58 pm »
Btw how do you use ADO with FreePascal ?

IIRC ADO has a full set of COM/DCOM interfaces. It is even fully COM based in the first place.
Under Windows import the type library. Note I may be partially wrong/not current because I stopped using ADO more than 10 years ago.
« Last Edit: May 30, 2016, 12:19:56 pm by Thaddy »
Specialize a type, not a var.

Steve

  • New Member
  • *
  • Posts: 11
Re: MS SQL Server Stored Procedure Return Values
« Reply #5 on: May 30, 2016, 02:23:38 pm »
Yes I did find the same scenario with ADO where the output parameters and return value are only accessible after closing the recordset returned by the stored procedure. I am getting around that by using ADO to return a variant array from the recordset and closing the recordset before I read the return values.

To use ADO I just added ComObj and Variants in the uses list then created the required ADO objects with the CreateOleObject() method. You don't get the benefit of intellisense auto-completion but it seems to work well.

Code: Pascal  [Select][+][-]
  1. uses
  2.   ComObj, Variants;
  3.  
  4. implementation
  5.  
  6. procedure CreateADOObjects();
  7. var
  8.   db: variant;
  9. begin
  10.   db:= CreateOleObject('ADODB.Connection');
  11.   // etc...
  12. end;
  13.  
  14.  

Thaddy

  • Hero Member
  • *****
  • Posts: 14214
  • Probably until I exterminate Putin.
Re: MS SQL Server Stored Procedure Return Values
« Reply #6 on: May 30, 2016, 02:53:13 pm »
Note you sketch a late bound scenario. It is perfectly possible to do early bound com (with its inherent speed increase and error handling) with ADO. (iirc)
Specialize a type, not a var.

Steve

  • New Member
  • *
  • Posts: 11
Re: MS SQL Server Stored Procedure Return Values
« Reply #7 on: May 31, 2016, 04:22:01 am »
I am new to this so always willing to learn how to do something better. How do you use early binding with COM?
I do find the error handling with late binding to be a bit cumbersome.

Steve

  • New Member
  • *
  • Posts: 11
Re: MS SQL Server Stored Procedure Return Values
« Reply #8 on: June 04, 2016, 06:27:50 am »
Okay, I found the following article that explains how to use early binding with Windows ActiveX so I will give that a go with ADODB.

http://wiki.lazarus.freepascal.org/LazActiveX

Thanks for all the responses.


 

TinyPortal © 2005-2018