mORMot Open Source

and4sh

Member

Registered: 2016-06-23

Posts: 5

Output Parameter

Hi,after upgrate framework (2013-> actual) my problem is that no matter how long text back from sql, value will have length of value while binding. I try with NewThreadSafeStatementPrepared and same situation.

var
value: Variant;
stmt: TSQLDBStatement;

stmt := queryBuilder.NewStatement;
stmt.BindTextS(1, value, paramOut);
stmt.ExecutePrepared;
stmt.ParamToVariant(1, value);

eg

value := 'qq';
stmt.BindTextS(1, value, paramOut);
stmt.ParamToVariant(1, value);
//max value length = 2
////////////////////////////////////////////

value := DupeString(' ', 100);
stmt.BindTextS(1, value, paramOut);
stmt.ParamToVariant(1, value);
//max value length = 100

Any advice what i doing wrong?

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,661

Website

Re: Output Parameter

Which back-end DB?
Which connection properties?
Some code to reproduce?

and4sh

Member

Registered: 2016-06-23

Posts: 5

Re: Output Parameter

Hi, db is Microsoft SQL Server 2008 R2 (SP2)

sample function

create procedure test(@param1 nvarchar(100) output) as
begin
	set @param1 = '1234567890'	
end;

delphi

function TForm3.paramTest: string;
var stmt: ISQLDBStatement;
    val: Variant;
    vConnProp: TOleDbConnectionProperties;
    con: TSQLDBConnection;
    res: string;
begin
    vConnProp := TOleDBMSSQLConnectionProperties.Create('db', 'dbname, 'usr', 'pass');
    val:= '';  //result will be 0 char
    val:= ' ';  //result will be 1 char.. and so on

    con := vConnProp.ThreadSafeConnection;
    con.Connect;
    stmt := con.NewStatementPrepared('exec test ? output ', false);

    stmt.BindTextS(1, val, paramOut);
    stmt.ExecutePrepared;
    stmt.ParamToVariant(1, val);
    result:= val
end;

Last edited by and4sh (2016-06-27 09:38:16)

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,661

Website

Re: Output Parameter

Try another class than plain TOleDBMSSQLConnectionProperties.
Use the one matching your exact MS SQL revision.

and4sh

Member

Registered: 2016-06-23

Posts: 5

Re: Output Parameter

I tried to change TOleDBMSSQLConnectionProperties to TOleDBMSSQL2008ConnectionProperties (and all others MSSQLCon.. for test). Unfortunately, the same problem

and4sh

Member

Registered: 2016-06-23

Posts: 5

Re: Output Parameter

Any idea what i can try else?

and4sh

Member

Registered: 2016-06-23

Posts: 5

Re: Output Parameter

Is something happening in topic? Shall i make ticket?

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,661

Website

Re: Output Parameter

We don't use at all stored procedure here, so we are not able to reproduce the issue...

Anyone using MSSQL stored procedures?

EMartin

Member

From: Buenos Aires - Argentina

Registered: 2013-01-09

Posts: 337

Re: Output Parameter

I used MSSQL stored procedures but with TODBCConnectionProperties as Microsoft has recommended.

---

Esteban

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,661

Website

Re: Output Parameter

Yes, ODBC may be definitively the way to go.
See http://blog.synopse.info/post/2012/02/2 … enjoy-ODBC!

danielkuettner

Member

From: Germany

Registered: 2014-08-06

Posts: 357

Re: Output Parameter

Or enjoy Zeos! We use also sp's and sometimes without errors.

dc

Member

Registered: 2012-11-16

Posts: 46

Re: Output Parameter

Or... maybe it's a bug/missing feature of handling output params correctly in mORMot SynOleDb? I've encountered something very similar in post from 2013-03-16 18:59:40

dc

dc

Member

Registered: 2012-11-16

Posts: 46

Re: Output Parameter

OK, I've made some tests with different ways of handling output parameters in mORMot and it looks like mORMot used to work better in 2013 :(

First of all here is my test procedure:

create procedure test_output_params @param1 varchar(30) OUTPUT, @param2 int OUTPUT, @param3 varchar(30) OUTPUT
as
begin
  SET NOCOUNT ON
  set @param1 = 'echo: '+@param1
  set @param2 = 100 + @param2
  set @param3 = 'test param3'
end

It accepts 3 parameters @p1, @p2 and @p3. Values of @p1 and @p2 are changed depending on input, while value of parameter@p3 is only set inside procedure.

You can test it with SQL statement like this one:

declare
  @p1 varchar(30),
  @p3 varchar(30),
  @p2 int;

set @p1 = 'test'
set @p2 = 100

exec test_output_params @p1 OUTPUT, @p2 OUTPUT, @p3 OUTPUT

select @p1 as p1, @p2 as p2, @p3 as p3

Query above returns:

p1                                        p2             p3
------------------------------ ----------- ------------------------------
echo: test                             200            test param3

Ok, now back to mORMot. Here is complete test code:

program stored_proc_output_params;

{$APPTYPE CONSOLE}
{$R *.res}

uses
  SysUtils, SynDB, SynOleDB;

var
  stmt: TSQLDBStatement;
  param1, param3: string;
  param2: integer;
  outParam: Variant;
  vConnProp: TOleDbConnectionProperties;
  conn: TSQLDBConnection;

begin
  vConnProp := TOleDBMSSQLConnectionProperties.Create('localhost', 'master', '', '');
  conn := vConnProp.ThreadSafeConnection;
  conn.Connect;
  stmt := conn.NewStatement;
  stmt.Prepare('exec test_output_params ? output, ? output, ? output', true);

  param1:= 'test 123';
  param2 := 123;

  stmt.BindTextS(1, param1, paramOut);
  stmt.Bind(2, param2, paramOut);
  stmt.BindTextS(3, ' ', paramOut);
  stmt.ExecutePrepared;

  Writeln('expected stored procedure output:');
  Writeln(format('    param1: "echo: %s"', [param1]));
  Writeln(format('    param2: %d', [param2 + 100]));
  Writeln('    param3: "test param3"');
  Writeln('actual stored procedure output:');

  stmt.ParamToVariant(1, outParam, false);
  param1 := outParam;
  stmt.ParamToVariant(2, outParam, false);
  param2 := outParam;
  stmt.ParamToVariant(3, outParam, false);
  param3 := outParam;

  Writeln(format('    param1: "%s"', [param1]));
  Writeln(format('    param2: %d', [param2]));
  Writeln(format('    param3: "%s"', [param3]));
  stmt.Free;

  Writeln('press [ENTER] to exit');
  conn.Free;
  Readln;
end.

As in SQL example expected output would be:

expected stored procedure output:
    param1: "echo: test 123"
    param2: 223
    param3: "test param3"

instead in current version of mORMot we get:

actual stored procedure output:
    param1: "test 123"
    param2: 123
    param3: "t"

It looks like param3 here is the problem and4sh wrote earlier. If you bind ' ' (space) you only get first letter as output. In this example it's only "t" instead of "test param3". If you bind a longer string you get longer result.

In old version of mORMmot (1f0ccd0924 from 2013) there were same problems for param1 and param2 but param3 used to work as expected. Here is an output from old version:

expected stored procedure output:
    param1: "echo: test 123"
    param2: 223
    param3: "test param3"
actual stored procedure output:
    param1: "test 123"
    param2: 123
    param3: "test param3"

Uff... hope it clarifies this topic a bit. Sorry for making it that long.

dc