Posts by c5soft

c5soft · mORMot 1

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 ?

c5soft · mORMot 1

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 ?

c5soft · mORMot 1

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

mORMot Open Source

#1 Re: mORMot 1 » How to get paramOut values of one stored Procedure which returns rows » 2018-04-09 09:43:41

#2 Re: mORMot 1 » How to get paramOut values of one stored Procedure which returns rows » 2018-04-09 07:00:54

#3 mORMot 1 » How to get paramOut values of one stored Procedure which returns rows » 2018-04-09 03:52:47

Board footer