Error when testing for null parameters in SQL Server Compact (SSCE)
Posted: 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.
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:
• Windows 7 (64-bit)
• RAD Studio 2010
• MS SQL Server Compact 4.0
• SDAC 7.1.3
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();
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:
Our development environment currently consists of:The specified argument value for the function is not valid. [Argument #: 1, 0, 0,Name of function(if known): isnull,,].
• Windows 7 (64-bit)
• RAD Studio 2010
• MS SQL Server Compact 4.0
• SDAC 7.1.3