Error in syntax when you insert an object (Marshmallow)

[Максим Сысоев]

I use to connect to the MySQL server Zeos(7.1.4-stable) library. If you try to add a new record to the table - getting an exception with text:

EZSQLException: SQL Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '; SELECT LAST_INSERT_ID()' at line 1

Code for replay this error:

program Project16;

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

uses
  System.SysUtils,
  ZConnection,
  Spring.Persistence.Adapters.Zeos,
  Spring.Persistence.Core.Interfaces,
  Spring.Persistence.Core.Session,
  Spring.Persistence.Mapping.Attributes,
  Spring.Persistence.SQL.Interfaces;

Type

  [Entity]
  [Table('Specialists')]
  TpsSpecialist = Class
  private
    [UniqueConstraint]
    [AutoGenerated]
    [Column('SID', [cpRequired, cpNotNull, cpPrimaryKey], 0, 0, 0, 'Primary Key')]
    FId: Integer;
    FFio: String;
    FPost: String;
  public
    property ID: Integer read FId;
    [Column('Fio', [], 50, 'ФИО')]
    property Fio: String read FFio write FFio;
    [Column('Post', [], 200, 'Должность')]
    property Post: String read FPost write FPost;
  End;

Procedure SampleError;
var
  ZeosConnect: TZConnection;
  SpringSession: TSession;
  SpringConnection: IDBConnection;
Begin
  ZeosConnect := TZConnection.Create(Nil);
  try
    ZeosConnect.LibraryLocation := 'libmysql.dll';
    ZeosConnect.Protocol := 'mysql';
    ZeosConnect.HostName := 'localhost';
    ZeosConnect.Port := 3312;
    ZeosConnect.Database := 'apm_passport';
    ZeosConnect.User := 'root';
    ZeosConnect.Password := 'root';
    ZeosConnect.Connect;
    Writeln('ZeosConnect.Ping: ', ZeosConnect.Ping);
    SpringConnection := TZeosConnectionAdapter.Create(ZeosConnect);
    SpringConnection.QueryLanguage := qlMySQL;
    SpringSession := TSession.Create(SpringConnection);
    try
      SpringSession.Insert(TpsSpecialist.Create);
    finally
      SpringSession.Free;
    end;
  finally
    ZeosConnect.Free;
  end;
End;

begin
  try
    { TODO -oUser -cConsole Main : Insert code here }
    SampleError;
  except
    on E: Exception do
    Begin
      Writeln(E.ClassName, ': ', E.Message);
      Readln;
    End;
  end;

end.

[Максим Сысоев]

if exec sql:

INSERT INTO Specialists ( `Fio`, `Post`) VALUES ("1", "1"); 
SELECT LAST_INSERT_ID();

then will be exception. if first exec sql:

INSERT INTO Specialists ( `Fio`, `Post`) VALUES ("1", "1");

close connection and now run

SELECT LAST_INSERT_ID()

then request be good O_o.

maybe its problem of ZeosLib?

[Stefan Glienke]

Looks like a ZEOS (or rather MySQL) thing - see http://zeoslib.sourceforge.net/viewtopic.php?t=3098

You can try to set the parameter that Mark mentions in the last post and see if that works.

[Максим Сысоев]

Ok. I'm set

ZeosConnect.Properties.AddPair('CLIENT_MULTI_STATEMENTS','TRUE');
ZeosConnect.Properties.AddPair('CLIENT_MULTI_RESULTS', 'TRUE');

but now new exception:

EZSQLException: Can not open a Resultset

I'm try change from

query.Open;

to

query.ExecSQL;

in Spring.Persistence.Adapters.Zeos.TZeosStatementAdapter.ExecuteQuery and give new exception:

EZSQLException: SQL Error: Commands out of sync; you can't run this command now

ohhh...

[Stefan Glienke]

Don't need CLIENT_MULTI_RESULTS, as there is only one select statement - did you try only CLIENT_MULTI_STATEMENTS?

Also ExecSQL will not work as you want some resultset (the one containing the last id)

If that does not work either consult a ZEOS/MySQL expert or write your own adapter that splits the insert into two operations.

[Максим Сысоев]

i'm try only CLIENT_MULTI_STATEMENTS, but give exception

EZSQLException: Can not open a Resultset

For fix I'm add new function:

    /// <summary>
    ///   Open or ExecSQL
    /// </summary>
    /// <param name="SQL">
    ///   Sql line
    /// </param>
    /// <returns>
    ///   If true need use Query.Open else Query.ExecSQL
    /// </returns>
function TZeosStatementAdapter.DoIsOpenQuery(const SQL: String): Boolean;
var
  KEY_FOR_OPEN: TArray<string>;
  I: Integer;
begin
  Result := False;
  KEY_FOR_OPEN := ['SELECT'];
  for I := Low(KEY_FOR_OPEN) to High(KEY_FOR_OPEN) do
    if SQL.ToUpper.Trim.StartsWith(KEY_FOR_OPEN[I]) then
    Begin
      Result := True;
      Break;
    End;
end;

and rewrite

function TZeosStatementAdapter.ExecuteQuery(serverSideCursor: Boolean): IDBResultSet;
var
  query: TZQuery;
  LScript: String;
  I: Integer;
begin
  inherited;
  query := TZQuery.Create(nil);
  query.connection := Statement.connection;
  query.SQL.Text := Statement.SQL.Text;
  query.Params.AssignValues(Statement.Params);
  query.DisableControls;
  LScript := Statement.SQL.Text;
  try
    while NOT LScript.Trim.IsEmpty do
    Begin
      query.Close;
      query.SQL.Text := LScript.Substring(0, LScript.IndexOf(';') + 1).Trim;
      if DoIsOpenQuery(query.SQL.Text) then
        query.Open
      else
        query.ExecSQL;
      LScript := LScript.Remove(0, LScript.IndexOf(';') + 1);
    End;
    Result := TZeosResultSetAdapter.Create(query, exceptionHandler);
  except
    on E: Exception do
    begin
      query.Free;
      raise HandleException(Format(SCannotOpenQuery, [E.Message]));
    end;
  end;
end;

maybe you rewrite this code in you code-style and push in bitbucket?

[Stefan Glienke]

Actually I want to first confirm that this is indeed an unsolvable problem in ZEOS/MySQL before we come up with a probably error prone way of cutting a multistatement SQL into pieces to execute them each - keep in mind that this adapter might not only be used for MySQL but also other databases which work perfectly fine.

[Francisco Armando Dueñas Rodriguez]

As long as I can remeber the issue is not MYSQL, Zeoslib was never been good managing multi select statements (more than one recordset retrieved).

if you use:

ZConnection1.Properties.Add('CLIENT_MULTI_STATEMENTS=TRUE');

and use

ZQuery1.ExecSQL;

it will work perfectly, the issue comes when retrieving the recordsets

ZQuery1.Open;

This is an old Issue of ZeosLib, it doesnt manages Multi recordsets perfectly.

In the link that Stefan Posted: http://zeoslib.sourceforge.net/viewtopic.php?t=3098, mdaems, One of Zeos Developer Team mentioned that it is not possible to use Multistatements with a SELECT statement at the end to fill the Dataset

So Maybe my suggestion is to post first that issue in Zeos Support site and see if they can fix that issue for MySQL. It could take time because the change must be done more deeply in Zeos.

Maybe it will be better if you can group statements in single query objects:

1. those that can be used with ExecSQL and that you don't need to return a recordset
2. those that you need to retreive recordsets (dataset) from them.

This is because there is an issue: In your new Adapter's code, it manages perfectly 'SELECT' statements, but what happens if you use Store procedures that return a recordset?

Call Myprocedure(1,2,3);

Your adapter will use 'ExecSQL' and not 'Open' as Expected.

BTW AnyDAC/FireDAC manages multiple statements (including mixing multiple SELECT statements) without problems.

Also for as a TIP, please change:

if SQL.ToUpper.Trim.StartsWith(KEY_FOR_OPEN[I])

To

If StartsText( KEY_FOR_OPEN[I], Trim( Upper(SQL)) )

To make them compatible with prior Delphi xe2 versions

[Francisco Armando Dueñas Rodriguez]

Regarding The issue I have told you about using Stored procedures that return a Dataset, I have rewritten your Adapter's function ExecuteQuery, using ZeosDbo DBC API and got rid of the DoIsOpenQuery function:

function TZeosStatementAdapter.ExecuteQuery(serverSideCursor: Boolean): IDBResultSet;
var
  query: TZQuery;
  LScript: String;
  I: Integer;
begin
  inherited;
  query := TZQuery.Create(nil);
  query.connection := Statement.connection;
  query.SQL.Text := Statement.SQL.Text;
  query.Params.AssignValues(Statement.Params);
  query.Options := query.Options+[doSmartOpen]; // This prevents TZQuery to throw exceptions if there is not Recordset returned;
  query.DisableControls;
  LScript := Statement.SQL.Text;
  if (NOT LScript.Trim.IsEmpty) and
     (LScript.IndexOf(';')=-1) then
  begin
   LScript := LScript+';';
  end;  

  try
    while NOT LScript.Trim.IsEmpty do
    Begin
      query.Active := false; //Used Instead of 'Close' to Prevent Exceptions in some delphi versions
      query.SQL.Text := LScript.Substring(0, LScript.IndexOf(';') + 1).Trim;
      query.Active := true;
      if not assigned(query.DbcResultSet) then
      begin
       query.active := false; //If there no data returned by the server, close the Dataset to prevent exceptions in Dataset binded controls that try to read data from the query.
      end;
      LScript := LScript.Remove(0, LScript.IndexOf(';') + 1);
    End;
    Result := TZeosResultSetAdapter.Create(query, exceptionHandler);
  except
    on E: Exception do
    begin
      query.Free;
      raise HandleException(Format(SCannotOpenQuery, [E.Message]));
    end;
  end;
end;

The only think pending is to rewrite this code for being Prior Delphi XE2 compatible

[Stefan Glienke]

I looked a bit into ZEOS and found a way to make it work: http://zeoslib.sourceforge.net/viewtopic.php?f=36&t=3098&p=84063#p84063

[Максим Сысоев]

Many thanks for the patch. He works. But! If I add a new entity to the database - memory leaks begin. This code also works by Francisco Armando Dueñas Rodriguez and no memory leaks.

Leaks: http://i.imgur.com/2bQYJD6.png

[Stefan Glienke]

fixed issue in ZEOS adapter (fixes issue #228)

→ <<cset 7e029a4b36e4>>

[Максим Сысоев]

thnx! Its worked!

[Francisco Armando Dueñas Rodriguez]

Hi Stefan many Thaks, just one doubt I see you made patch for zeosLib, did you suggested those changes to ZeosLib Forum? So those changes can be applied in future versions of zeos. If Not, I would be glad to do it.

[Stefan Glienke]

Look into my second last comment.

[Francisco Armando Dueñas Rodriguez]

Ohh ok. I didnt looked you also posted the fix for the memory leaks, that's why the reason of my comment. Thanks Stefan