mORMot Open Source

dc

Member

Registered: 2012-11-16

Posts: 46

[BUG] mORMot always strips last semicolon in queries

I've recently upgraded to current mORMot sources and now I wonder which of my patches to mORMot should go back to your sources. As for now I think you should consider correction for SynDB.pas and SynXXXDB.pas.
Patch is about letting user (not mORMot) decide if he wants to strip last semicolon in query. It's backward compatible of course.

Here is patch for SynDB.pas:

1849d1848
<     fStripSemicolon: boolean;
2333d2331
<     property StripSemicolon: boolean read fStripSemicolon write fStripSemicolon;
6721d6718
<   fStripSemicolon := true;
7444,7449c7441,7443
<     if StripSemicolon then
<     begin
<       if (L>5) and (aSQL[L]=';') and // avoid syntax error for some drivers
<          not IdemPChar(@aSQL[L-4],' END') then
<         fSQL := copy(aSQL,1,L-1) 
<     end else
---
>     if (L>5) and (aSQL[L]=';') and // avoid syntax error for some drivers
>        not IdemPChar(@aSQL[L-4],' END') then
>       fSQL := copy(aSQL,1,L-1) else

and corresponding one for SynOleDB.pas:

1808,1812c1808,1809
<   if StripSemicolon then
<   begin
<     while (L>0) and (fSQL[L] in [#1..' ',';']) do
< 	  dec(L); // trim ' ' or ';' right (last ';' could be found incorrect)
<   end;
---
>   while (L>0) and (fSQL[L] in [#1..' ',';']) do
>     dec(L); // trim ' ' or ';' right (last ';' could be found incorrect)

One of examples when it's not allowed to strip last semicolon is a MERGE statement in SQL.
Here is preparation code:

create table test(
	id	int,
	txt	varchar(100),
	primary key (id)
)

delete from test;
insert into test values (1, 'item 1'), (2, 'item 2');

and actual MERGE statement:

with new(id, txt) as (select 1, 'aqq' union select 5, 'aaaa') 
merge test as old
using new on old.id = new.id
when matched then update set old.txt = new.txt
when not matched then insert values(new.id, new.txt);

if you now run select * from test you should get:

id          txt
----------- ----------------------------------------------------------------------------------------------------
1           aqq
2           item 2
5           aaaa

If you remove semicolon from MERGE statement you get an error:

Msg 10713, Level 15, State 1, Line 5
A MERGE statement must be terminated by a semi-colon (;).

dc

dc

Member

Registered: 2012-11-16

Posts: 46

Re: [BUG] mORMot always strips last semicolon in queries

Any comments for this patch? Is there a chance to include it in official source?

dc

Member

Registered: 2012-11-16

Posts: 46

Re: [BUG] mORMot always strips last semicolon in queries

I have updated this fix to current code base and opened a pull request on github. Can someone please take a look at it?

dc

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,206

Website

Re: [BUG] mORMot always strips last semicolon in queries

Please check http://synopse.info/fossil/info/789eac7281

Sorry for the delay of merging it.
Thanks for the feedback!

dc

Member

Registered: 2012-11-16

Posts: 46

Re: [BUG] mORMot always strips last semicolon in queries

Thank you for merging