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

itSDS
Member
From: Germany
Registered: 2014-04-24
Posts: 506

ExecuteInlined Different Param Handling MySQL - MSSQL

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)


Rad Studio 12.1 Santorini

Offline

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

mpv
Member
From: Ukraine
Registered: 2012-03-24
Posts: 1,534
Website

Re: ExecuteInlined Different Param Handling MySQL - MSSQL

Firs look at ExtractInlineParameters - this function should extract sad""): 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)

Offline

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

itSDS
Member
From: Germany
Registered: 2014-04-24
Posts: 506

Re: ExecuteInlined Different Param Handling MySQL - MSSQL

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


Rad Studio 12.1 Santorini

Offline

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

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
Website

Re: ExecuteInlined Different Param Handling MySQL - MSSQL

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

Offline

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

itSDS
Member
From: Germany
Registered: 2014-04-24
Posts: 506

Re: ExecuteInlined Different Param Handling MySQL - MSSQL

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


Rad Studio 12.1 Santorini

Offline

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

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
Website

Re: ExecuteInlined Different Param Handling MySQL - MSSQL

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.

Offline

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

itSDS
Member
From: Germany
Registered: 2014-04-24
Posts: 506

Re: ExecuteInlined Different Param Handling MySQL - MSSQL

ok i allready did so smile


Rad Studio 12.1 Santorini

Offline

Board footer

Powered by FluxBB