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

[jeremyw]

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]

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

[jeremyw]

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

Regardless, I appreciate the quick response.

[ViktorV]

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.