ExecuteInlined Different Param Handling MySQL - MSSQL

itSDS · 2019-02-25 14:52:52

Hi AB,
i found a problem with the Param generation in Executeinlined

we give the following query to Executelined

select anyvalue from thetable where textvalue is null or textvalue = :(""):

in MySQL the generated Query is correct:

select anyvalue from thetable where textvalue is null or textvalue = ''

in MSSQL the Query is wrong generated

select anyvalue from thetable where textvalue is null or textvalue = null

why is the Empty "" String in MSSQL converted to null ?

Where do i have to correct the Problem ?

Last edited by itSDS (2019-02-25 14:54:47)

mpv · 2019-02-25 15:44:42

Firs look at ExtractInlineParameters - this function should extract ""): as a empty string parameter
Second - look how parameter is binded to the statement. It's depends on implementation you use to access MSSQL( you can use Zeos or OLEDB or ODBC).

Last edited by mpv (2019-02-25 15:45:54)

itSDS · 2019-02-26 07:19:43

Thank you - Just to add it here i was using TSQLDBZEOSConnectionProperties for MySQL and OleDBMSSQL2012ConnectionProperties for MSSQL I try now the ZEOS implementation fpr MS-SQL

ab · 2019-02-26 11:05:42

I guess it would be the same with Zeos, since it is a feature needed by MSSQL to bind void string as null.

See TSQLDBConnectionProperties.StoreVoidStringAsNull property documentation:
https://synopse.info/files/html/api-1.1 … C_BC21129A

itSDS · 2019-02-26 13:14:30

I understand whats causing my query problem - TY

What i don't understand is why is not "allowed" to store '' in Textfields in MSSQL - we do this for over 20 years now. - What do you mean with allowed ?
In my understanding
if text = null then the Values is "not set" or Empty
if text = '' then the Field ist Empty String

Sometimes it is neccessary to make a difference between '' and null

ab · 2019-02-26 15:36:38

IIRC it was rejected by the client to store a void string in Access/Jet - and early MSSQL versions at least.

If you prefer, just set StoreVoidStringAsNull := false and see if it is more your liking.

itSDS · 2019-02-26 16:20:50

ok i allready did so

mORMot Open Source

#1 2019-02-25 14:52:52

ExecuteInlined Different Param Handling MySQL - MSSQL

#2 2019-02-25 15:44:42

Re: ExecuteInlined Different Param Handling MySQL - MSSQL

#3 2019-02-26 07:19:43

Re: ExecuteInlined Different Param Handling MySQL - MSSQL

#4 2019-02-26 11:05:42

Re: ExecuteInlined Different Param Handling MySQL - MSSQL

#5 2019-02-26 13:14:30

Re: ExecuteInlined Different Param Handling MySQL - MSSQL

#6 2019-02-26 15:36:38

Re: ExecuteInlined Different Param Handling MySQL - MSSQL

#7 2019-02-26 16:20:50

Re: ExecuteInlined Different Param Handling MySQL - MSSQL

Board footer