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.
Comments (17)
-
reporter -
repo owner 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.
-
reporter 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...
-
reporter - edited description
-
repo owner 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.
-
reporter 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?
-
repo owner 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.
-
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:
- those that can be used with ExecSQL and that you don't need to return a recordset
- 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
-
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
-
repo owner 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
-
reporter 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.
-
repo owner fixed issue in ZEOS adapter (fixes issue
#228)→ <<cset 7e029a4b36e4>>
-
reporter thnx! Its worked!
-
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.
-
repo owner Look into my second last comment.
-
Ohh ok. I didnt looked you also posted the fix for the memory leaks, that's why the reason of my comment. Thanks Stefan
-
repo owner - changed version to 1.2
- Log in to comment
if exec sql:
then will be exception. if first exec sql:
close connection and now run
then request be good O_o.
maybe its problem of ZeosLib?