mORMot Open Source

DDGG

Member

Registered: 2018-01-10

Posts: 18

"Commands out of sync" error while 2nd call to a stored procedure

Hello everyone!

I am a newbie of mORMot, and I have a question about calling stored procedure by a TODBCConnectionProperties.

Here is my simple code:

const
  CONN_STRING = 'Driver=MySQL ODBC 5.3 UNICODE Driver;Database=Test;Server=127.0.0.1;Port=3306;UID=root;Pwd=';

var
  Props: TODBCConnectionProperties;

procedure TestCallStoreProcedure;
var
  Row: ISQLDBRows;
  S: RawUTF8;
begin
  Row := Props.Execute('CALL GetUserByID(?)', [1]);
  while Row.Step do
  begin
    S := ToUTF8(Row['name']);
    Writeln(S);
  end;
end;

begin
  Props := TODBCConnectionProperties.Create('', CONN_STRING, '', '');
  try
    try
      TestCallStoreProcedure;
      TestCallStoreProcedure;    // error occurs
    except
      on E: Exception do
        Writeln(E.ClassName, ': ', E.Message);
    end;
  finally
    Props.Free;
  end;
end.

After run, I get a "Commands out of sync" error:

EODBCException: TODBCStatement - TODBCLib error: [HY000] [MySQL][ODBC 5.3(w) Driver][mysqld-5.7.18-log]Commands out of sync; you can't run this command now (2014)

I searched for the "commands out of sync;" on the internet, it seems something needs to freed before the next call.

Row is an interface variant so need not to be freed manually I think, I tried to set Row to nil and surely take no effect.

Props means one of connections in the underlying database connection pool, it should be used continuously I think, but after I make the Props be a local variant (create and free it in TestCallStoreProcedure), the issue solved.

But is this the right approach? Please help to understand, thanks a lot!

Last edited by DDGG (2018-02-21 10:46:54)

DDGG

Member

Registered: 2018-01-10

Posts: 18

Re: "Commands out of sync" error while 2nd call to a stored procedure

@ab Could you help me please?

mpv

Member

From: Ukraine

Registered: 2012-03-24

Posts: 1,571

Website

Re: "Commands out of sync" error while 2nd call to a stored procedure

ODBC is not a good choice for mySql.  Try Zeos instead

DDGG

Member

Registered: 2018-01-10

Posts: 18

Re: "Commands out of sync" error while 2nd call to a stored procedure

ODBC is not a good choice for mySql.  Try Zeos instead

I agree with you, but this is not an ODBC issue I think.

Any other advice?

EgonHugeist

Member

From: Germany

Registered: 2013-02-15

Posts: 190

Re: "Commands out of sync" error while 2nd call to a stored procedure

@DDGG

the advice was: "Try Zeos instead"
but i'm asking my selve if it would help(use the Callable stmt api instead)... mORMot simply isn't prepared/made using multiple results...

AB, what do you think? FYI: That's why Zeos splits statement and result-records... Both are not related

DDGG

Member

Registered: 2018-01-10

Posts: 18

Re: "Commands out of sync" error while 2nd call to a stored procedure

@EgonHugeist

Thanks for your reply!
So it's ok to create a new TODBCConnectionProperties every time I need to query something?
Zeos is strange to me.

EgonHugeist

Member

From: Germany

Registered: 2013-02-15

Posts: 190

Re: "Commands out of sync" error while 2nd call to a stored procedure

@DDGG

I don't wanna be rude, just want to help ... But

So it's ok to create a new TODBCConnectionProperties every time I need to query something?

this IS strange to me. (;

Nope i don't think so. mORMot just needs the GetMoreResults logic to query all pending results. And as i said, it's a question of design..
Propose you flush the SP's and use native queries for the job, if it is acceptable for you.

DDGG

Member

Registered: 2018-01-10

Posts: 18

Re: "Commands out of sync" error while 2nd call to a stored procedure

@EgonHugeist

OK, I will continue studying, thank you!

DDGG

Member

Registered: 2018-01-10

Posts: 18

Re: "Commands out of sync" error while 2nd call to a stored procedure

Could someone please tell me how to release things which should be release in above situation, so that I can continue to use the same TODBCConnectionProperties?

Sometimes I can't use a new TODBCConnectionProperties because I have to make the call to a stored procedure in a single transaction.

Last edited by DDGG (2018-04-02 11:23:14)

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,661

Website

Re: "Commands out of sync" error while 2nd call to a stored procedure

I don't understand well what your are talking about... what do you mean by "release things"? What is a "thing"?

What was written above is that if you want to run stored procedures, use the underlying ZDBC layer directly. Using SynDB or our ORM won't make any benefit, and was not meant to execute stored procedures.

DDGG

Member

Registered: 2018-01-10

Posts: 18

Re: "Commands out of sync" error while 2nd call to a stored procedure

Thanks for your reply! @ab

We plan to gradually move from the stored procedure to using ORM, but currently we can not discard all legacy stored procedure.
And we want to use the mORMot framework, so this is the problem I have to face.

This is the step to reproduce the problem:

Create a new MySQL database named test, then create a new table named user:

CREATE TABLE `user` (
  `id` bigint(255) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `age` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Create a stored procedure named GetUserByLevel:

CREATE DEFINER=`root`@`%` PROCEDURE `GetUserByLevel`(IN `ALevel` int(4))
BEGIN
  IF ALevel = 0 THEN
    SELECT `name`, `age` FROM `user` WHERE `age` BETWEEN 0 AND 6 ORDER BY `age`;
  ELSEIF ALevel = 1 THEN
    SELECT `name`, `age` FROM `user` WHERE `age` BETWEEN 6 AND 18 ORDER BY `age`;
  ELSE
    SELECT `name`, `age` FROM `user` WHERE `age` > 18 ORDER BY `age`; 
  END IF;

END

Then run this simple program:

program Project11;

{$APPTYPE CONSOLE}

{$R *.res}

uses
  System.SysUtils, SynCommons, SynDB, SynDBODBC;

var
  Props: TODBCConnectionProperties;

procedure InsertRecords;
begin
  Props.Execute('TRUNCATE TABLE user', []);
  Props.Execute('INSERT INTO user (name, age) VALUES (?, ?)', ['John', 1]);
  Props.Execute('INSERT INTO user (name, age) VALUES (?, ?)', ['Mary', 10]);
  Props.Execute('INSERT INTO user (name, age) VALUES (?, ?)', ['Lucas', 20]);
end;

procedure CallStoredProcedure;
var
  Row: ISQLDBRows;
begin
  Row := Props.Execute('CALL GetUserByLevel(?)', [1]);
  while Row.Step do
    Writeln(FormatUTF8('name: %, age: %', [Row['name'], Row['age']]));
end;

const
  CONN_STRING = 'Driver=MySQL ODBC 5.3 UNICODE Driver;Database=test;Server=127.0.0.1;Port=3306;UID=test;Pwd=';
begin
  Props := TODBCConnectionProperties.Create('', CONN_STRING, '', '');
  try
    try
      InsertRecords;                 // If comment this line
      CallStoredProcedure;
      CallStoredProcedure;           // error occurs
    except
      on E: Exception do
        Writeln(E.ClassName, ': ', E.Message);
    end;
  finally
    Props.Free;
  end;
end.

After the first run (no error occurs), comment the line `InsertRecords`, run it again, then error occurs.

I need your help, thanks a lot!

Last edited by DDGG (2018-04-04 04:51:26)

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,661

Website

Re: "Commands out of sync" error while 2nd call to a stored procedure

As written above, try to use SynDBZeos with MySQL, then directly call the ZDBC layer for your stored procedure, and regular SynDB classes for the ORM.
There is the TSQLDBZEOSConnection.Database property to access the underlying associated ZEOS connection instance.

As a side benefit, it will use directly the MySQL driver, no you won't need to install the MySQL ODBC provider.