coalesce returning null rather than empty string

BIZ TMS RemoteDB
1

I have an issue with coalesce returning null rather than empty string when using TXDataset, if I test the same SQL statement with TFDQuery is works correctly and returns empty string '' (not null).
example
XDataset1.SQL.Add('select coalesce(list(TOUNIT),'''') as ITEMUNITLIST from ITEMUNITS where ITEMCODE = ''xx''');
XDataset1.Open;
if XDataset1.FieldByName('ITEMUNITLIST').IsNull then
showmessage('null')
else
showmessage('not null');

I could easily code around this in my application but we use a scripting engine for clients to do custom scripts and this issue came from one of those, so I really need to fix In our application rather than get clients to change their script code.
Thanks

2

Can you please be more specific as:

  1. The database structure
  2. What is "list"?
  3. The RDBMS you are using

Ideally, steps to reproduce.

3
  1. Don't think the structure matters here
  2. "List" is a Firebird aggregate function that concatenates non-NULL values from a group into a single string. Note: you can replicate the issue without using the list function, but you have separate null handling for blob and non blob fields in (RemoteDB.Client.Dataset.pas) so this is good for testing as the "list" function returns a blob field.
  3. I am using a Firebird 5 database
  4. Steps to reproduce, do a simple query 'select coalesce(your_string_field_that_is_null,'') from yourtable' using TXdataset the field is [null] (it should be an empty string) do the same SQL with TFDQuery the field is an empty string (correct)

I managed to make changes to fix local connection, but then had another issue specifically with remote, have now got that to work.
A quick summary of the areas that I changed to get this to work...
RemoteDB.Client.Dataset.pas FillRecordData, GetFieldData
RemoteDB.Server.Wrappers.pas FieldsToStream, ResultRowToStream
RemoteDB.Client.Database.pas TRemoteDBProvider, LoadRowData, IsNull, Close

4

Any update on this one? would be good not to have to apply fix for each update

5

I took the effort to reproduce the issue, and I can't. See project attached.
Hence why we always ask for a sample project reproducing the issue. Tested with MS SQL Server.

TestCoalesce.zip (7.0 KB)

6

Sorry it’s been a while I can’t seem to fault the non blob result right now, I have reverted to what I think is the original RemoteDB.Client.Dataset.pas and I can reproduce the issue when using the aggregate function “List” (Firebird) so I have a created a sample project for that, I figure we will just start with local connection first to see if you get the same result. You maybe able to test with your MS SQL project if there is an aggregate function that returns a blob result.

Project includes Test.fdb and embedded Firebird files

TEST.zip (1.2 MB)

7

I see this is a very specific issue with blob fields. RemoteDB treats all blobs with size 0 as nulls. I don't think changing this at this point is safe. It's been working like this for years and years.

Nevertheless, I have created a new very specific property CheckNullBlobs for this edge case that when True will treat blob nullability different from blob size. Will be included in next release.