Regression in mormot.db.sql

itSDS · Yesterday 10:42:28

Im Using "multiStatements" in my Query

Sample:

create table #tmp_ids_FAB4C3C346934CB884E2FAB4B5F0E444 (TempID bigint primary key);
declare @csvids nvarchar(max) = '''';
INSERT INTO #tmp_ids_FAB4C3C346934CB884E2FAB4B5F0E444 (tempid) SELECT CAST([value] AS bigint) FROM OPENJSON(''['' + @csvids + '']'');
select distinct(hd.referenzid) f0 from ha_doku hd inner join #tmp_ids_FAB4C3C346934CB884E2FAB4B5F0E444 t on t.tempid = hd.referenzid where (hd.referenz = :("auftraege"):) and (hd.werteart = :("Photo"):);

You made a change in mormot.db.sql.pas from 15085 to 15115 (Dont now exact which commit)

i patched it replacing the new TReplaceSQL and DoSQLReplace with the old code from 15085 so that ReplaceParamsByNames / ReplaceParamsByNumbers are restored.

The Error Message Displayed is: (MSSQL)

Im Projekt xyz.exe ist eine Exception der Klasse ESqlDBDataset mit der Meldung TSq|DBUniDACStatement.ExecutePrepared expected 0 bound parameters, got 2

ab · Yesterday 13:52:22

I tried to reproduce your issue with
https://github.com/synopse/mORMot2/commit/eb473e1ce

But it seems to process correctly the input, and find 2 parameters.

Can you debug a bit more, and check what is the ReplaceParamsByNames() input?
I am not sure ReplaceParamsByNames() is the culprit.

itSDS · Today 11:39:21

Hi I just testet both Version and logged the Parameter and ANewSQL, in the new Version ANewSQL does not contain :AA/:AB it only has the 2 ?
I have the complete Query now and try to embed it in your Test Programm

itSDS · Today 11:56:11

Here is the new TestCode wich results in an error:

 CheckEqual(s, '(1,2,3,4,5,6,7,8) values (:AA,:AB,:AC,:AD,:AE,:AF,:AG,:AH)');
  CheckEqual(ReplaceParamsByNames(
        'CREATE TABLE #tmp_ids_1DA01A83DBCB47EBA98938FBCF091AAD (TempID bigint PRIMARY KEY);'+
        'DECLARE @csvids nvarchar(MAX) = ''4489,4563,4606,4702,4768,5121,5294,5354,5784'';'+
        'INSERT INTO #tmp_ids_1DA01A83DBCB47EBA98938FBCF091AAD (tempid) SELECT CAST([value] AS BIGINT) FROM OPENJSON(''['' + @csvids + '']'');'+
        'SELECT distinct(hd.referenzid) f0'+
        'FROM ha_doku hd'+
        'INNER JOIN #tmp_ids_1DA01A83DBCB47EBA98938FBCF091AAD t ON t.tempid = hd.referenzid'+
        'WHERE (hd.referenz = ?) AND (hd.werteart = ?);', s), 2);
  CheckEqual(s,
        'CREATE TABLE #tmp_ids_1DA01A83DBCB47EBA98938FBCF091AAD (TempID bigint PRIMARY KEY);'+
        'DECLARE @csvids nvarchar(MAX) = ''4489,4563,4606,4702,4768,5121,5294,5354,5784'';'+
        'INSERT INTO #tmp_ids_1DA01A83DBCB47EBA98938FBCF091AAD (tempid) SELECT CAST([value] AS BIGINT) FROM OPENJSON(''['' + @csvids + '']'');'+
        'SELECT distinct(hd.referenzid) f0'+
        'FROM ha_doku hd'+
        'INNER JOIN #tmp_ids_1DA01A83DBCB47EBA98938FBCF091AAD t ON t.tempid = hd.referenzid'+
        'WHERE (hd.referenz = :AA) AND (hd.werteart = :BA)');
  CheckEqual(ReplaceParamsByNumbers('', s), 0);

with old code no error, witz new Replace function error

ab · Today 13:39:31

Ah now I understand what you meant by "multiStatements".

This was only working by chance by this provider.
It should have never worked at all, since you are preparing a single statement with a "multiStatements".

Split your "multiStatements" into several explicit statements.

itSDS · Today 14:24:10

hm thats not good this special statement the first 3 lines work only as multistatemant, because of the csvids variable

ab · Today 18:21:38

Now I understand better.

I have allowed ';' in the middle by default, and added a new TSqlDBDatasetConnectionProperties.DisallowSemicolonInSql property if needed to be disabled:
https://github.com/synopse/mORMot2/commit/acd73cbc4

mORMot Open Source

#1 Yesterday 10:42:28

Regression in mormot.db.sql

#2 Yesterday 13:52:22

Re: Regression in mormot.db.sql

#3 Today 11:39:21

Re: Regression in mormot.db.sql

#4 Today 11:56:11

Re: Regression in mormot.db.sql

#5 Today 13:39:31

Re: Regression in mormot.db.sql

#6 Today 14:24:10

Re: Regression in mormot.db.sql

#7 Today 18:21:38

Re: Regression in mormot.db.sql

Board footer