mORMot Open Source

c5soft

Member

Registered: 2018-04-06

Posts: 3

How to get paramOut values of one stored Procedure which returns rows

Hi ab,
    when i try to use SynOleDB  work with MS SQL Database, i found ISQLDBStatement return from Props.NewThreadSafeStatementPrepared(...ExpectResults is True) calling ParamToVariant return a wrong output value when calling a stored Procedure which return records. how to solve this problem?

this is the SQL Profiler output:

declare @p5 money
set @p5=$0.0000
declare @p6 nvarchar(4000)
set @p6=N''
exec sp_executesql N'EXEC PR_SalaryQuery @P1,@P2,@P3 OUTPUT,@P4 OUTPUT',N'@P1 nvarchar(14),@P2 nvarchar(5),@P3 money OUTPUT,@P4 nvarchar(4000) OUTPUT',N'201706',N'89059',@p5 output,@p6 output
select @p5, @p6

this generated two dataset, one for exec sp_executesql ..., anothor for select @p5, @p6.
i knew when ExpectResults is false, ParamToVariant works. but i have to face so many stored procedure return rows and output params.

with regards.
xander xiao

c5soft

Member

Registered: 2018-04-06

Posts: 3

Re: How to get paramOut values of one stored Procedure which returns rows

it seems synOleDB only support  getting single recordset, as  source code at line 1996-2003 shows:

        if not OleDBConnection.OleDBProperties.fSupportsOnlyIRowset then begin
          // use IMultipleResults for 'insert into table1 values (...); select ... from table2 where ...'
          res := fCommand.Execute(nil,IID_IMultipleResults,fDBParams,@fUpdateCount,@mr);
          if res=E_NOINTERFACE then
            OleDBConnection.OleDBProperties.fSupportsOnlyIRowset := true else begin
            repeat
              res := mr.GetResult(nil,0,IID_IRowset,@fUpdateCount,@RowSet);
            until Assigned(RowSet) or (res <> S_OK);
          end;
        end;

if we assign aSQL with multiple select commands like "select ... from table1 where ...;select ... from table2 where ..." , is where a mechanisim  that we can access multiple rowset ?

Last edited by c5soft (2018-04-09 07:03:14)

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,659

Website

Re: How to get paramOut values of one stored Procedure which returns rows

It executes only the first statement supplied, by definition.
In fact, "select ... from table1 where ...;select ... from table2 where ..." needs several executes, and manual split.
Just call Execute() several times.

And by design, it allows only a single result set by now.

Use Zeos/ZDBC if you want to have more detailed access to the DB.

c5soft

Member

Registered: 2018-04-06

Posts: 3

Re: How to get paramOut values of one stored Procedure which returns rows

Thanks you very much, ab.
It is very common with MSSQL stored procedure like following code:

CREATE PROCEDURE Procname1
@p1 varchar(100), @p2 varchar(100) output
as
...
select @p2=anyfield from ... where ...   --Assign Value to output param
...
select ... from ... where ... like @p1       --finally return one recordset

is there a way to implement this function in SynOleDB ?

mdbs99

Member

From: Rio de Janeiro, Brazil

Registered: 2018-01-20

Posts: 139

Website

Re: How to get paramOut values of one stored Procedure which returns rows

I believe you can use `SET NOCOUNT ON` before all these instructions.

Last edited by mdbs99 (2018-07-02 19:04:21)