mORMot Open Source

p.s.t.

Member

Registered: 2012-12-20

Posts: 9

Transactions and stored procedures in TOleDBConnectionProperties

I faced two problems with TOleDBConnectionProperties :
1. Transaction:
When I open transaction in some procedure, method InTransaction returns false - but I think it shout be true.
Please look at following source code:

class procedure TDocDao.SaveDocument(documentNo: string; documentDate: TDateTime);
var
  query: rawUtf8;
  dbStatement: ISQLDBStatement;
  lastInsertedId: integer;
  i: integer;
begin
  try
    Props.MainConnection.StartTransaction;
    query := 'insert into tst_document(documentNo, documentDate) values (?,?)';
    dbStatement := Props.NewThreadSafeStatementPrepared(query, false);
    dbStatement.BindVariant(1, 'DOC/1234', false);
    dbStatement.BindDateTime(2, Now);
    dbStatement.ExecutePrepared;
    with Props.Execute('SELECT @@IDENTITY as ''Identity''', []) do
      while Step do
      begin
        lastInsertedId := ColumnVariant(0);
      end;
    for i := 1 to 3 do
    begin
      SaveDocumentItem(lastInsertedId, 'Assort ' + IntToStr(i), i);
    end;

    Props.MainConnection.Commit; //here I get error because transaction was commited in SaveDocumentItem procedure
  except
    on e: Exception do
      Props.MainConnection.Rollback;
  end;
end;


class procedure TDocDao.SaveDocumentItem(documentId: integer; name: string; quantity: currency);
var
  isOuterTrans: boolean;
  dbStatement : ISQLDBStatement;
  query: rawUtf8;
begin
try
  query :=  'insert into tst_documentItem(documentId, name, quantity) values (?,?,?)';
  dbStatement := Props.NewThreadSafeStatementPrepared(query, false);
  isOuterTrans := Props.MainConnection.InTransaction;
  //I get here FALSE despite the fact that transaction is already opened (it was opened in procedure SaveDocument)
  if not isOuterTrans then
    Props.MainConnection.StartTransaction;
  dbStatement.Bind(1, documentId);
  dbStatement.BindVariant(2, name, false);
  dbStatement.BindVariant(3, quantity, false);
  dbStatement.ExecutePrepared;
  if not isOuterTrans then
    Props.MainConnection.Commit;
except
  on e:Exception do
  begin
    if not isOuterTrans then
      Props.MainConnection.Rollback;
    raise Exception.Create('Save DocumentItem Error ');
  end;
end;
end;

Am I doing something wrong?
My another question is if  there is a way to use named transaction with TOleDBConnectionProperties ?

2. Fetch rows from stored procedure witch uses temprary table
When I want to fech rows from stored procedure whitch uses temporary table it's nessesary to set SET NOCOUNT ON, otherwise it is impossible to fetch rows (function step returns False ). When I have "normal" stored procedure without any temporary tables or computations SET NOCOUNT ON isn't reqired. Is it correct?

I inculde my entire source code.

program mormotTransactionsTest;

{$APPTYPE CONSOLE}

{$R *.res}
//SQL Script
 {
      CREATE TABLE TST_Document
      (
        documentId bigint not null identity(1,1) primary key,
        documentDate datetime,
        documentNo varchar(15)
      )

      GO
      CREATE TABLE TST_DocumentItem
      (
        documentItemId bigint not null identity(1,1) primary key,
        documentId bigint,
        name nvarchar(200),
        quantity numeric(12,3)
      )
      GO

      CREATE PROCEDURE TST_SP_DOCUMENTS
       AS
       BEGIN
		    SET NOCOUNT ON;
		    declare
		      @CURRENDOCTS table(
			      id_document	int
		      )
		    insert into @CURRENDOCTS select documentId from tst_document;
		    select * from @CURRENDOCTS;
       END
}


uses
  System.SysUtils,
  System.Variants,
  System.Classes,
  SynCommons,
  SynDB,
  SynOleDB,
  mORMot;

Type
  TdocDao = class
    class procedure SaveDocument(documentNo: string; documentDate: TDateTime) ;
    class procedure SaveDocumentItem(documentId: integer; name: string; quantity: currency);
  end;



var
  Props: TOleDBConnectionProperties;
  Model: TSQLModel;


class procedure TDocDao.SaveDocument(documentNo: string; documentDate: TDateTime);
var
  query: rawUtf8;
  dbStatement: ISQLDBStatement;
  lastInsertedId: integer;
  i: integer;
begin
  try
    Props.MainConnection.StartTransaction;
    query := 'insert into tst_document(documentNo, documentDate) values (?,?)';
    dbStatement := Props.NewThreadSafeStatementPrepared(query, false);
    dbStatement.BindVariant(1, 'DOC/1234', false);
    dbStatement.BindDateTime(2, Now);
    dbStatement.ExecutePrepared;
    with Props.Execute('SELECT @@IDENTITY as ''Identity''', []) do
      while Step do
      begin
        lastInsertedId := ColumnVariant(0);
      end;
    for i := 1 to 3 do
    begin
      SaveDocumentItem(lastInsertedId, 'Assort ' + IntToStr(i), i);
    end;

    Props.MainConnection.Commit;//here I get error because transaction was commited in SaveDocumentItem procedure
  except
    on e: Exception do
      Props.MainConnection.Rollback;
  end;
end;


class procedure TDocDao.SaveDocumentItem(documentId: integer; name: string; quantity: currency);
var
  isOuterTrans: boolean;
  dbStatement : ISQLDBStatement;
  query: rawUtf8;
begin
try
  query :=  'insert into tst_documentItem(documentId, name, quantity) values (?,?,?)';
  dbStatement := Props.NewThreadSafeStatementPrepared(query, false);
  isOuterTrans := Props.MainConnection.InTransaction;
  //I get here FALSE despite the fact that transaction is already opened (it was opened in procedure SaveDocument)
  if not isOuterTrans then
    Props.MainConnection.StartTransaction;
  dbStatement.Bind(1, documentId);
  dbStatement.BindVariant(2, name, false);
  dbStatement.BindVariant(3, quantity, false);
  dbStatement.ExecutePrepared;
  if not isOuterTrans then
    Props.MainConnection.Commit;
except
  on e:Exception do
  begin
    if not isOuterTrans then
      Props.MainConnection.Rollback;
    raise Exception.Create('Save DocumentItem Error ');
  end;
end;
end;






procedure InitializeConnection;
begin
  Props := TOleDBMSSQLConnectionProperties.Create('localhost', 'MyDb', 'sa', 'sa');
end;




procedure TestRetrieveFromSP;
var
  stmt: ISQLDBStatement;
begin
  Writeln('Start TestRetrieveFromSP');
  stmt := Props.NewThreadSafeStatementPrepared('exec TST_SP_DOCUMENTS', true);
  stmt.ExecutePrepared;
  //WITHOUT SET NOCOUNT ON step returns false and no row is fetched
  while stmt.Step do
    Writeln('fetch row...');
  Writeln('End TestRetrieveFromSP');
end;




procedure TestTransaction;
begin
  TdocDao.SaveDocument('DOC/1234', Now);
end;



begin
    with TSQLLog.Family do begin
    Level := LOG_VERBOSE;
    HighResolutionTimeStamp := true;
    AutoFlushTimeOut := 0;
    ArchiveAfterDays := 1; // archive after one day
  end;

  InitializeConnection;
  TestTransaction;
  TestRetrieveFromSP;
  Readln;

end.

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,660

Website

Re: Transactions and stored procedures in TOleDBConnectionProperties

Transactions are connection-specific.
So I guess the MainConnection is not the same instance than the one used to run the SQL statements.
Could you try to use ThreadSafeConnection instead of MainConnection?

About MS SQL stored procedures, we didn't use it by now.
I don't know exactly what is wrong here, and why step returns false in this case.

p.s.t.

Member

Registered: 2012-12-20

Posts: 9

Re: Transactions and stored procedures in TOleDBConnectionProperties

I've tried ThreadSafeConnection and I've got the same error.
I include my entire source code 

program mormotTransactionsTest;

{$APPTYPE CONSOLE}

{$R *.res}
//SQL Script
 {
      CREATE TABLE TST_Document
      (
        documentId bigint not null identity(1,1) primary key,
        documentDate datetime,
        documentNo varchar(15)
      )

      GO
      CREATE TABLE TST_DocumentItem
      (
        documentItemId bigint not null identity(1,1) primary key,
        documentId bigint,
        name nvarchar(200),
        quantity numeric(12,3)
      )
      GO

      CREATE PROCEDURE TST_SP_DOCUMENTS
       AS
       BEGIN
		    SET NOCOUNT ON;
		    declare
		      @CURRENDOCTS table(
			      id_document	int
		      )
		    insert into @CURRENDOCTS select documentId from tst_document;
		    select * from @CURRENDOCTS;
       END
}


uses
  System.SysUtils,
  System.Variants,
  System.Classes,
  SynCommons,
  SynDB,
  SynOleDB,
  mORMot;

Type
  TdocDao = class
    class procedure SaveDocument(documentNo: string; documentDate: TDateTime) ;
    class procedure SaveDocumentItem(documentId: integer; name: string; quantity: currency);
  end;



var
  Props: TOleDBConnectionProperties;
  Model: TSQLModel;


class procedure TDocDao.SaveDocument(documentNo: string; documentDate: TDateTime);
var
  query: rawUtf8;
  dbStatement: ISQLDBStatement;
  lastInsertedId: integer;
  i: integer;
begin
  try
    Props.ThreadSafeConnection.StartTransaction;
    query := 'insert into tst_document(documentNo, documentDate) values (?,?)';
    dbStatement := Props.NewThreadSafeStatementPrepared(query, false);
    dbStatement.BindVariant(1, 'DOC/1234', false);
    dbStatement.BindDateTime(2, Now);
    dbStatement.ExecutePrepared;
    with Props.Execute('SELECT @@IDENTITY as ''Identity''', []) do
      while Step do
      begin
        lastInsertedId := ColumnVariant(0);
      end;
    for i := 1 to 3 do
    begin
      SaveDocumentItem(lastInsertedId, 'Assort ' + IntToStr(i), i);
    end;

    Props.ThreadSafeConnection.Commit;
  except
    on e: Exception do
      Props.ThreadSafeConnection.Rollback;
  end;
end;


class procedure TDocDao.SaveDocumentItem(documentId: integer; name: string; quantity: currency);
var
  isOuterTrans: boolean;
  dbStatement : ISQLDBStatement;
  query: rawUtf8;
begin
try
  query :=  'insert into tst_documentItem(documentId, name, quantity) values (?,?,?)';
  dbStatement := Props.NewThreadSafeStatementPrepared(query, false);
  isOuterTrans := Props.ThreadSafeConnection.InTransaction;
  //I get here FALSE despite the fact that transaction is already opened (it was opened in procedure SaveDocument)
  if not isOuterTrans then
    Props.ThreadSafeConnection.StartTransaction;
  dbStatement.Bind(1, documentId);
  dbStatement.BindVariant(2, name, false);
  dbStatement.BindVariant(3, quantity, false);
  dbStatement.ExecutePrepared;
  if not isOuterTrans then
    Props.ThreadSafeConnection.Commit;
except
  on e:Exception do
  begin
    if not isOuterTrans then
      Props.ThreadSafeConnection.Rollback;
    raise Exception.Create('Save DocumentItem Error ');
  end;
end;
end;






procedure InitializeConnection;
begin
  Props := TOleDBMSSQLConnectionProperties.Create('localhost', 'McspalPosNew', 'sp', 'sp');
end;




procedure TestRetrieveFromSP;
var
  stmt: ISQLDBStatement;
begin
  Writeln('Start TestRetrieveFromSP');
  stmt := Props.NewThreadSafeStatementPrepared('exec TST_SP_DOCUMENTS', true);
  stmt.ExecutePrepared;
  //WITHOUT SET NOCOUNT ON step returns false and no row is fetched
  while stmt.Step do
    Writeln('fetch row...');
  Writeln('End TestRetrieveFromSP');
end;




procedure TestTransaction;
begin
  TdocDao.SaveDocument('DOC/1234', Now);
end;



begin
    with TSQLLog.Family do begin
    Level := LOG_VERBOSE;
    HighResolutionTimeStamp := true;
    AutoFlushTimeOut := 0;
    ArchiveAfterDays := 1; // archive after one day
  end;

  InitializeConnection;
  TestTransaction;
  //TestRetrieveFromSP;
  Readln;

end.

dc

Member

Registered: 2012-11-16

Posts: 46

Re: Transactions and stored procedures in TOleDBConnectionProperties

Hi,

I did some tests and it looks like there really is a problem with mORMot transactions in MS SQL Server.

As far as stored procedures are considered there is a problem too, however it's not the temporary table problem.
Thing is that SQL Server sends (as a part of result set) messages with number of rows affected by each statement done in stored procedure. This is default behavior, and can be changed with "SET NOCOUNT ON" directive. In other words, if your stored procedure does some other operations than just using select statements (and it usually does much more) it will not show resultset in mORMot (ISQLDBRows.Step returns false). There is also another problem (missing feature?) with SynOleDB.TOleDbConnectionProperties.Execute, what if you get multiple result sets? How do you handle them?

Here is a simple script to show those problems:

create database dev;
go
use dev;

create table dummy_table(
	id	integer not null identity,
	col1	integer
)
go

-- this procedure returns 3 resultsets
create procedure test_resultsets(@withNoCount smallint = 0)
as
begin
	if IsNull(@withNoCount, 0) = 1
		SET NOCOUNT ON
	insert into dummy_table(col1) values(1);
	select 'resultset1: aqq 1.1' as col1, 1100 as col2
	union
	select 'resultset1: aqq 1.2' as col1, 1200 as col2;
	
	select 'resultset2: aqq 2' as col1, 2345 as col2;
	
	select 'resultset3, from dummy_table:' as col1, id from dummy_table;
end

To test this you can now open SQL Server Management Studio and connect to your local server instance. After that, press Ctrl+N (it will open "New Query" tab), paste above code and click "execute" button or press F5. It creates new database "dev", table "dummy_table" and stored procedure "test_resultsets". You can now open new query window and type "exec test_resultsets". When you press F5 you see 3 resultsets as here:
When you now switch to "Messages" tab you see messages that SQL Server send to client during execute precess (first message is from insert statement). Here is a screenshot:

Of course second and third result sets are not necessary to show that mORMot handles messages incorrectly (it's enough to execute insert in stored procedure and then select statement), they are here just to show another problem: handling  multiple result sets.

Here is project to test how mORMot handles it:

program mORMot_StoredProcTest;

{$APPTYPE CONSOLE}

{$R *.res}

uses
  SynOleDB,
  SynCommons,
  SynDB,
  SysUtils;

var
  dbConnection: TOleDbConnectionProperties;
  i: integer;

procedure printResultSet(rows: ISQLDBRows);
var
  s, ss, pad: string;
  i: integer;
begin
  pad := ' ';
  s := StringOfChar(' ', 5) + pad;
  ss := StringOfChar('-', 5) + pad;
  for i := 0 to rows.ColumnCount - 1 do
  begin
    s := s + Format('%30s', [trim(rows.ColumnName(i))]) + pad ;
    ss:= ss + StringOfChar('-', 30) + pad ;
  end;
  writeln(s);
  writeln(ss);
  i := 0;
  while rows.Step do
  begin
    inc(i);
    s:= format('%5d ', [i]);
    for i := 0 to rows.ColumnCount - 1 do
      s := s + Format('%30s', [Copy(rows.ColumnString(i), 1, 30)]) + pad ;
    writeln(s);
  end;
  writeln(ss);
  writeln(Format('%d row(s) returned.', [i]));
  writeln;
end;

begin
  // connection made using windows authentication to database "dev"
  // if using SQLServer authentication supply user and passsword too
  dbConnection := TOleDBMSSQLConnectionProperties.Create('localhost\sqlexpress', 'dev', '', '');
  writeln('default behavior ("exec test_resultsets"):');
  writeln;
  printResultSet(dbConnection.Execute('exec test_resultsets ?', [0]));
  writeln('NOCOUNT=ON ("exec test_resultsets 1"):');
  writeln;
  printResultSet(dbConnection.Execute('exec test_resultsets ?', [1]));
  readln;
end.

It looks like mORMot doesn't handle DONE_IN_PROC messages sent by SQL Server correctly (it should ignore them and wait for real result if there is any). You can check documentation here: http://msdn.microsoft.com/en-us/library/ms189837.aspx

Hope this post helps to clarify things a bit.

Regards,
dc

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,660

Website

Re: Transactions and stored procedures in TOleDBConnectionProperties

I've created a ticket for this issue.

See http://synopse.info/fossil/info/e24a204043

Thanks for the report.

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,660

Website

Re: Transactions and stored procedures in TOleDBConnectionProperties

As stated by http://technet.microsoft.com/en-us/libr … 17719.aspx it sounds like if it is pretty common to force SET NOCOUNT OFF for the client side.
What is the problem of using it as workaround?
Sounds like if it is used explicitly when working with OleDB clients.

I was not able to find out how DONE_IN_PROC messages are translated at OleDB level.
In fact, DONE_IN_PROC are part of the TDS-speak.
Is it not a problem of OleDB itself?

dc

Member

Registered: 2012-11-16

Posts: 46

Re: Transactions and stored procedures in TOleDBConnectionProperties

As stated by http://technet.microsoft.com/en-us/libr … 17719.aspx it sounds like if it is pretty common to force SET NOCOUNT OFF for the client side.
What is the problem of using it as workaround?

Sometimes it can be troublesome. When you  use SET NOCOUNT ON there are some good and bad effects, good is speed increase and in case of mORMot correct result set's handling. Bad is that it doesn't support return code value from stored procedures (at least it was a case with SQL Server 2005 and devart drivers). If you only want to get resultset or params from stored procedure it's ok, but I have to check if it behavies properly when you need return code too.

Sounds like if it is used explicitly when working with OleDB clients.

I was not able to find out how DONE_IN_PROC messages are translated at OleDB level.
In fact, DONE_IN_PROC are part of the TDS-speak.
Is it not a problem of OleDB itself?

I think it's not a problem at OleDB level because devart drivers for SQL Server do it properly, and since they've managed to do it I assume there is a way.

Regards,
dc

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,660

Website

Re: Transactions and stored procedures in TOleDBConnectionProperties

Sounds like if they are using IMultipleResults interface in such cases, which is not the case of SynOleDB.

Did you try to access the data via ODBC instead of OleDB?
Maybe ODBC does not suffer from this issue.

dc

Member

Registered: 2012-11-16

Posts: 46

Re: Transactions and stored procedures in TOleDBConnectionProperties

Sounds like if they are using IMultipleResults interface in such cases, which is not the case of SynOleDB.

Did you try to access the data via ODBC instead of OleDB?
Maybe ODBC does not suffer from this issue.

Hi, I've finally found some time to play with mORMot and stored procedures in SQL Server. Now I can confirm that ODBC drivers work correctly and are not affected by SET NOCOUNT. Unfortunately there is another issue I found, have no idea how accessing output result from stored procedure should be done. Here is example procedure I used for my tests:

create procedure test_params 
     @param1 smallint, 
     @param2 int OUTPUT
as
begin
	set @param2 = @param1 + 1000;
	return(18)
end

as you can see it takes 2 parameters, param1 as input and param2 as output. It also returns value 18 as result. You can check it by running this script in SQL Server Management Studio:

declare 
	@rc int,
	@param2 int;

exec @rc = test_params 123, @param2 output
select @rc as result, @param2 as param2

when using dbexpress one would use something like this code:

ds.CommandType:= ctStoredProc;
ds.CommandText:= 'test_params';
ds.Params[1].AsInteger:= 123;
ds.ExecSQL;

rc := ds.Params[0].AsInteger;
param2 := ds.Params[2].AsInteger;

to acces both params and return value, but how do you do it using mORMot?
I've tried this way:

  stmt.Prepare('exec ? = test_params ?, ?', false);
  stmt.Bind(1, 0, paramOut);
  stmt.Bind(2, 123, paramIn);
  stmt.Bind(3, 0, paramOut);
  stmt.ExecutePrepared;
  stmt.ParamToVariant(1, rc, false);
  stmt.ParamToVariant(2, param1, false);
  stmt.ParamToVariant(3, param2, true);

It works when I use TOleDBODBCSQLConnectionProperties (rc = 18, param1 = 123, param2 = 1123). However when i switch to TOleDBMSSQLConnectionProperties i get rc =18 and param1 = 123, but param2 = 0

Is it a bug in OleDB drivers or am I doing it wrong?
And another question: do you have any plans to use IMultipleResults with mORMot?

regards
dc

dc

Member

Registered: 2012-11-16

Posts: 46

Re: Transactions and stored procedures in TOleDBConnectionProperties

Hi, here is another difference between ODBC and OLEDB in mORMot. When you have tables containing dates declared as datetime type there are incorrect results when querying tables by date values. Here is sample table with 1000 rows of dates with millisecond precision:

create table test_dates
(
	id	int not null identity,
	dt datetime not null
)

delete from test_dates
declare 
	@i int,
	@dt datetime;

set @i = 1
set @dt = getdate();

while @i <= 1000
begin
	set @dt = DATEADD(millisecond, 5 * rand(@i)*10000, @dt)
	set @i = @i + 1
	insert into test_dates(dt) values (@dt)
end

pick any date from this table and try to select it like this:

  dt := EncodeDateTime(2013, 3, 16, 23, 18, 21, 293);
  printResultSet(dbConnection.Execute('select top 3 id, dt from test_dates where dt >= ?', [dt]));
  printResultSet(dbConnection.Execute('select top 3 id, dt from test_dates where dt >= ?', [DateTimeToStr(dt)]));

for ODBC both queries work correctly, however for OleDB only second (?!) one. This is very strange as I would expect passing TDateTime to query to behave better than passing string with date, which is very vulnerable for local settings differences between client and server.

dc