Error when testing for null parameters in SQL Server Compact (SSCE)

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
jeremyw
Posts: 32
Joined: Thu 29 Apr 2010 17:32

Error when testing for null parameters in SQL Server Compact (SSCE)

Post by jeremyw » Thu 11 Aug 2016 22:09

Hi Devart!

We are using an older version of SDAC (7.1.3), but we will be renewing our maintenance if you can get this bug fixed (as well as this older bug I reported last year).

Once again it seems we have found another issue that affects just SSCE, since the following code works correctly with normal SQL Server connections.

Code: Select all

String name;
String sql = "SELECT [ContactID] FROM [Contact]"
             "WHERE ([Name] = :Name) OR ([Name] IS NULL AND :Name IS NULL)";

TQuery* query = gDataEngine->SQLQuery();
query->SQL->Text = sql;
query->ParamByName("Name")->Value = VariantString(name);  // Returns Null() if an empty string
query->ParamByName("Name")->DataType = ftString;

query->Open();
bool emptyQuery = query->IsEmpty();
A simple test for equality is unfortunately not correct when a string field can be NULL, since the SQL expression NULL = NULL returns false. Thus the more complicated WHERE clause above.

We also want to use a parameterized query here (rather than dynamically create SQL text) since we have to repeat this query many, many times. Unfortunately I cannot find a good way to test for a NULL parameter value within the SQL itself. As mentioned, the above code works great for SQL Server databases, but with SSCE connections an exception is thrown with the following error message:
The specified argument value for the function is not valid. [Argument #: 1, 0, 0,Name of function(if known): isnull,,].
Our development environment currently consists of:
• Windows 7 (64-bit)
• RAD Studio 2010
• MS SQL Server Compact 4.0
• SDAC 7.1.3

ViktorV
Devart Team
Posts: 2299
Joined: Wed 30 Jul 2014 07:16

Re: Error when testing for null parameters in SQL Server Compact (SSCE)

Post by ViktorV » Fri 12 Aug 2016 10:09

This error is returned by SQL Server Compact Edition, and we can't affect this in any way.

jeremyw
Posts: 32
Joined: Thu 29 Apr 2010 17:32

Re: Error when testing for null parameters in SQL Server Compact (SSCE)

Post by jeremyw » Tue 16 Aug 2016 00:18

That seems strange that Microsoft wouldn't support such a basic (essential?) function in SSCE.

Regardless, I appreciate the quick response.

ViktorV
Devart Team
Posts: 2299
Joined: Wed 30 Jul 2014 07:16

Re: Error when testing for null parameters in SQL Server Compact (SSCE)

Post by ViktorV » Tue 16 Aug 2016 07:08

Thank you for being interested in our products.
If you have any questions concerning our products, please don't hesitate to contact us - and we will try to help you resolve them.

Post Reply