Stored procedure output parameters

alpinistbg · 2015-01-13 08:21:04

Hi,
Does anybody knows how a value of an output parameter can be obtained? TSQLDBConnectionProperties.Execute() takes its Params as array of const. Thanks!

ab · 2015-01-13 09:40:59

Just use the statement level syntax, in which you will have the ParamToVariant() method after execution.

alpinistbg · 2015-01-13 12:16:05

Hi ab,

The following code:

CREATE procedure [dbo].[dbclone_genkey]
  @out_key bigint output,
  @in_increment bigint = 1
as
begin
  ...
  set @out_key = @key * 10000 + @sid;
end

  Props := TODBCConnectionProperties.Create('','DRIVER=SQL Server Native Client 10.0;'+...);

  ...

  Stmt := Props.NewThreadSafeStatementPrepared('Exec dbclone_genkey ?,?', false, true);
  Stmt.Bind(1, 666, paramInOut);
  Stmt.Bind(2, I, paramIn);
  Stmt.ExecutePrepared;
  Stmt.ParamToVariant(1, V);
  Result := Int64(V);

Returns always 666, which is the value from Stmt.Bind(). The procedure call is performed and the result is quite different than 666.
What I'm missing?

ab · 2015-01-13 12:36:09

Perhaps SQL_C_SBIGINT is not handled by the provider.
Try to find out what may be wrong in TODBCStatement.ExecutePrepared.
Or change your procedure to define an input+output out_key parameter.

You may try with SynOleDB.pas unit instead of ODBC.

alpinistbg · 2015-01-13 18:17:08

ab wrote:

Perhaps SQL_C_SBIGINT is not handled by the provider.
Try to find out what may be wrong in TODBCStatement.ExecutePrepared.

Have tried with the latest ODBC driver 11 for SQL server. I can't find what's wrong in TODBCStatement.ExecutePrepared. It seems to me that params were bound successfully, excluding some tricks with SQL_C_CHAR/SQL_C_SBIGINT, which I do not understand completely.

ab wrote:

Or change your procedure to define an input+output out_key parameter.

In the T-SQL params are input or output, the latter is actually input/output.

ab wrote:

You may try with SynOleDB.pas unit instead of ODBC.

Tried with TOleDBMSSQLConnectionProperties, also with TSQLDBZEOSConnectionProperties - same results .

Moreover - putting a 'select @out_key' at the end of the procedure does not bring a resultset back (?!). From the three props only ZEOS detects the resultset with Stmt.Step().
But ZEOS redirects to some TZDBLibPreparedStatementEmulated which substitutes the ? parameters textually. No way of getting output parameter values IMHO.

Regards,

Last edited by alpinistbg (2015-01-13 18:18:20)

EgonHugeist · 2015-01-13 19:17:05

@alpinistbg

Accordingly Zeos:
Therefor the IZCallableStatment API is made. There is a spezial CAPI for StoredProcedures (using the TDS-Protocol) only. But this isn't supported my mORMot yet.

Propose you use ADO driver or since Z7.3 the new OleDB Driver with a syntax like "ado[mssql]" as protocol-name.
Both are implmented to fetch Multiple-ResultSets.

Some providers do return multiple Results as RowSets:
1. RowSet: UpdateCount/RowCount
2. RowSet: A ResultSet, part of stored procedure or if more Selects are called one:
3. RowSet: Another user fetched Row
....
X. Last are the Out-Params.

This behavior is provider dependend some do support multiple RS (eg. MySQL, MSSQL, Sybase) others don't. Most providers do execute everything but return only last result...

You can use the IZStatement.GetMoreResults: Boolean; to check if another RowSet is available. If so use IZStatement.GetResultSet to retrieve the data.

alpinistbg · 2015-01-14 07:53:30

@EgonHugeist

Thank for the info! Actually my troubles are not with the Zeos, just tried it to see is there a different behavior than ODBC in my particular case. I am pretty aware of MSSQL multiple resultsets feature (or trouble?) since I've used it with the raw ODBC API and juggled with Fetch/MoreResults/NumResultCols.

@ab

TODBCStatement.BindColumns() calls SQLNumResultCols/SQLDescribeCol before the statement actually executed, results fetched, etc. Due to the frivolous T-SQL procedure semantics the metadata is unavailable at that point, SQLNumResultCols returns 0. Thus consuming of the dataset is not possible and the next statement will crash => only SP's with input parameters and no resultsets can be called.

Forgetting to note that MARS_Connection=yes into the connection string, then:

TODBCStatement - TODBCLib error: [HY000] [Microsoft][ODBC Driver 11 for SQL Server]Connection is busy with results for another command (0).

If the user logged have no permission to read the DB metadata, the CreateMissingTables() attempts to create the external table and fails (the user probably don't have permission to modify it, either).

Despite I haven't tried much of mORMot+ODBC+MSSQL functionality, but IMHO the combination is suitable only for simple select/insert/update statements.

The simple thing I wanted was just to reuse my good old key generation procedure :(

Last edited by alpinistbg (2015-01-14 07:55:52)

ab · 2015-01-14 08:00:01

@alpinistbg
Do you see any modification to TODBCStatement.ExecutePrepared() to fix this problem?

alpinistbg · 2015-01-14 11:27:14

Proposed changes:

in TODBCStatement.Prepare() removed BindColumns() call because it works with the very first result set which may be an insert/update/delete, thus NumResultCols=0
in TODBCStatement.ExecutePrepared() called BindColumns() just after the execution to bind to the actual result sets
in TODBCStatement.BindColumns() result sets scanned until one found with NumResultCols>0
in TODBCStatement.Step() if SQL_NO_DATA then the rest of the result sets skipped with MoreResults()

The last one (4.) is needed, because the output parameters are transferred after all resultsets were consumed. Finally, I can get my @out_key value

It is a bit weird, but it turns out that if you want to receive the value of an output parameters, you should say that you're expecting results and then to call Step() until it returns false.

Here is the diff:

Index: SynDBODBC.pas
==================================================================
--- SynDBODBC.pas
+++ SynDBODBC.pas
@@ -0,0 +225,1 @@
+    function MoreResults: boolean;
@@ -0,0 +872,2 @@
+    MoreResults: function(StatementHandle: SqlHStmt): SqlReturn;
+    {$ifdef MSWINDOWS} stdcall {$else} cdecl {$endif};
@@ -961,1 +964,1 @@
-  ODBC_ENTRIES: array[0..62] of PChar =
+  ODBC_ENTRIES: array[0..63] of PChar =
@@ -0,0 +977,1 @@
+     'SQLMoreResults',
@@ -0,0 +1194,1 @@
+    status: SqlReturn;
@@ -1194,1 +1199,5 @@
-    Check(self,NumResultCols(fStatement,nCols),SQL_HANDLE_STMT,fStatement);
+    repeat
+      Check(self,NumResultCols(fStatement,nCols),SQL_HANDLE_STMT,fStatement);
+      if (nCols = 0) and not Self.MoreResults then
+          Break; // no more
+    until nCols > 0;
@@ -0,0 +1555,2 @@
+    if fExpectResults then
+      BindColumns;
@@ -0,0 +1596,11 @@
+
+function TODBCStatement.MoreResults: Boolean;
+var R: SqlReturn;
+begin
+  R := ODBC.MoreResults(fStatement);
+  case R of
+    SQL_NO_DATA: Result := False; // no more results
+    SQL_SUCCESS, SQL_SUCCESS_WITH_INFO: Result := True; // got next
+    else ODBC.Check(self, R, SQL_HANDLE_STMT, fStatement); // error
+  end;
+end;
@@ -1600,2 +0,0 @@
-    if fExpectResults then
-      BindColumns;
@@ -1627,1 +1647,2 @@
-    SQL_NO_DATA:
+    SQL_NO_DATA: begin
+      while Self.MoreResults do {consume all result sets};
@@ -0,0 +1650,1 @@
+    end;

ab · 2015-01-14 14:33:14

I tried the patch...
But it breaks the statement re-use.
For instance, the sample 15 is not working any more.

alpinistbg · 2015-01-14 15:20:27

ab wrote:

I tried the patch...
But it breaks the statement re-use.
For instance, the sample 15 is not working any more.

Can you be a bit more specific? Which one of the defines I must enable to reveal the issue?

I do not have broad experience with different DBMS systems, but as long as the MSSQL is concerned, you can't just "rewind" the statement and use it again. There is simply no such call as SQLLessResults. Of course, different modifications can be made in order to stop regressions.

ab · 2015-01-14 15:35:03

Once a statement is prepared, you can delete its cursor, and re-use the same statement, without parsing again the SQL, just by calling Reset + Bind*() + Execute again.
It increases execution speed a lot.
This is what our ORM does.

To test it, just enable USELOCALMSSQLEXPRESS or USELOCALDBMSSQLEXPRESS, depending on your MS SQL server version.
Or USELOCALPOSTGRESQL if you have a local PostgreSQL installation.

Sounds like if the following lines I added are faulty:

function TODBCStatement.Step(SeekFirst: boolean): boolean;
..
    SQL_NO_DATA:
      repeat until not Self.MoreResults;

... it works with the other modifications, if I left the original code untouched:

....
    SQL_NO_DATA:
      exit;

alpinistbg · 2015-01-14 16:17:33

Meanwhile, I have enabled ODBCSQLITEFIREBIRD and the Sample 15 crashed at TODBCStatement.BindColumns:

if (fColumnCount>0) or (fColData<>nil) then
    raise EODBCException.CreateUTF8('%.BindColumns twice',[self]);

And without the:

function TODBCStatement.Step(SeekFirst: boolean): boolean;
..
    SQL_NO_DATA:
      repeat until not Self.MoreResults;

The MSSQL ODBC Driver will not transfer it's output parameter values.

ab · 2015-01-14 17:16:47

Check the latest version from http://synopse.info/fossil/timeline

But I was not able to fix

function TODBCStatement.Step(SeekFirst: boolean): boolean;
..
    SQL_NO_DATA:
      repeat until not Self.MoreResults;

So I left the plain "exit" yet.

Now the sample 15 works as expected, but I guess your output parameter values won't be transferred.
We need further investigation.

EgonHugeist · 2015-01-14 17:34:26

Offtopic:

Arnaud that's one of the reason for the design/model of Zeos. The Results and Statements are different. Zeos can reuse the Parameters as a Prepared stmt. For some drivers like ADO,MSSQL,SYBASE,MYSQL i can NOT reuse the IZResultSets. Initial issue is i don't know for sure if GetMoreResults(ZEOS) can retrieve another ResultSet or not(except i'm starting a huge SQL-parsing which leads to pain and performance loss-> shouldn't be task of Zeos from my POV). For drivers like SQLite,FireBird,PostgreSQL and Oracle i was able to reuse the IZResultSets (Something similar like your SeekFirst option, which avoids rebuilding the column-informations, buffer-allocations etc.).

Oftenly i was thinking about changing and simplifiying the design of Zeos(IZStatement+IZResultSet in one Interface) to something similar like your TSQLRequest model, but exactly these (and some other Zeos internal automations like the IZCachedResolver) cases have been stopping my ideas...

So let's see how you resolve this case... I was a bit surpriced why this issue never did popup here. I'll stay tuned!

Some notes:

The behavior i wrote previously is also setting-dependend.
Getting the UpdateCound/RowCount as first resultset is optional. Have to study my MSSQL book again.. Back hole in my head now. Can't remember the option yet. Would this be interesting?

Last edited by EgonHugeist (2015-01-14 17:43:18)

alpinistbg · 2015-01-14 18:16:34

ab wrote:

Check the latest version from http://synopse.info/fossil/timeline
But I was not able to fix
function TODBCStatement.Step(SeekFirst: boolean): boolean;
..
    SQL_NO_DATA:
      repeat until not Self.MoreResults;
So I left the plain "exit" yet.
Now the sample 15 works as expected, but I guess your output parameter values won't be transferred.
We need further investigation.

You forgot to apply that piece:

Index: SynDBODBC.pas
==================================================================
--- SynDBODBC.pas
+++ SynDBODBC.pas
@@ -1200,1 +1200,5 @@
-    Check(self,NumResultCols(fStatement,nCols),SQL_HANDLE_STMT,fStatement);
+    repeat
+      Check(self,NumResultCols(fStatement,nCols),SQL_HANDLE_STMT,fStatement);
+      if (nCols = 0) and not Self.MoreResults then
+        Break; // no more
+    until nCols > 0;

It will not harm the sample 15 (checked!) but will allow the transfer of the output parameter when the procedure have no resultset to return, i.e. with plain exit at TODBCStatement.Step and above patch the stored procedure can have output parameter(s) or resultset, but not both. I think it is a bit of improvement.

Last edited by alpinistbg (2015-01-14 18:21:25)

ab · 2015-01-14 19:37:01

Sadly, I already tested all your code (including the missing patch), and it was failing with MS SQL 2012 ODBC provider.
The SQlite3 and FireBird ODBC providers did work, but MS SQL 2012 ODBC provider failed with an error about the cursor in inconsistent state.

alpinistbg · 2015-01-15 12:31:56

That's weird! I've tested it with 'ODBC Driver 11 for SQL Server', which I believe is the last MSSQL ODBC provider, but my local server is MSSQL 2008 R2. The Sample 15 is OK. It must be something from the server itself.

alpinistbg · 2015-01-15 13:53:17

EgonHugeist wrote:

The behavior i wrote previously is also setting-dependend.
Getting the UpdateCound/RowCount as first resultset is optional. Have to study my MSSQL book again.. Back hole in my head now. Can't remember the option yet.

I believe you're talking about SET NOCOUNT {ON|OFF}. Yes, if you put it on the first line of the SP and you don't have any SELECT then you'll get the output values without the need of calling SQLMoreResults(). Thank you for reminding me that! It solves the case with my key generator SP.

EgonHugeist wrote:

Would this be interesting?

If you mean the issue with both resultset+output params, then IMHO it's not worth the efforts. Considering the peculiarities of each provider is too much work and that is just for doing right SP calls - something that is not encouraged for the framework.

So, maybe I am the sole man who tries to call MSSQL SP through mORMot, but my problem is somewhat private ...

Edit:

@ab
Unfortunately the problem relapses in simple INSERT statement generated from TSQLRest.Add(). When there is an AFTER INSERT trigger then the method crashes with "Cursor in inconsistent state" message. The trigger body have INSERT in some side table, which adds an additional RowCount result, I suggest.

Last edited by alpinistbg (2015-01-15 16:07:30)

EgonHugeist · 2015-01-15 21:09:25

alpinistbg wrote:

I believe you're talking about SET NOCOUNT {ON|OFF}. Yes, if you put it on the first line of the SP and you don't have any SELECT then you'll get the output values without the need of calling SQLMoreResults(). Thank you for reminding me that! It solves the case with my key generator SP.

Glad, you got it! Even if it's not a full solution for SP's @all.... It simply helps you for "simple" SP, but the issue with multiple ResultSets is still pending, i think.

mORMot Open Source

#1 2015-01-13 08:21:04

Stored procedure output parameters

#2 2015-01-13 09:40:59

Re: Stored procedure output parameters

#3 2015-01-13 12:16:05

Re: Stored procedure output parameters

#4 2015-01-13 12:36:09

Re: Stored procedure output parameters

#5 2015-01-13 18:17:08

Re: Stored procedure output parameters

#6 2015-01-13 19:17:05

Re: Stored procedure output parameters

#7 2015-01-14 07:53:30

Re: Stored procedure output parameters

#8 2015-01-14 08:00:01

Re: Stored procedure output parameters

#9 2015-01-14 11:27:14

Re: Stored procedure output parameters

#10 2015-01-14 14:33:14

Re: Stored procedure output parameters

#11 2015-01-14 15:20:27

Re: Stored procedure output parameters

#12 2015-01-14 15:35:03

Re: Stored procedure output parameters

#13 2015-01-14 16:17:33

Re: Stored procedure output parameters

#14 2015-01-14 17:16:47

Re: Stored procedure output parameters

#15 2015-01-14 17:34:26

Re: Stored procedure output parameters

#16 2015-01-14 18:16:34

Re: Stored procedure output parameters

#17 2015-01-14 19:37:01

Re: Stored procedure output parameters

#18 2015-01-15 12:31:56

Re: Stored procedure output parameters

#19 2015-01-15 13:53:17

Re: Stored procedure output parameters

#20 2015-01-15 21:09:25

Re: Stored procedure output parameters

Board footer