Problem with DB-NULL-Value (RB 20.02)

michaelbroede · February 2020

Hi,

I have the following problem (using RB 20.02, connected to MS-SQL-Server):

There is a resultset with a datetime column. The column value is always null, but I get the numeric value 0. Furthermore the test "column = null" is never true. Here is a code snippet:

var
v: variant;
s: string;
begin
s := SQLDatasource['ADateTime'];
ShowMessage(s);
v := SQLDatasource['ADateTime'];
if v = null then
ShowMessage('v is null');
end;

The first messagebox shows "0".
The second box is not displayed.
Whats going wrong?

Thanks

Michael

nicocizik · February 2020

Hi Michael,

Are you using RAP?

Generally in Delphi, if you want to check for a null record value, you should use the IsNull property of the Field. See the following article on how this is done in RAP (and in Delphi) with ReportBuilder.

http://rbwiki.digital-metaphors.com/rap/fundamentals-rap/check-for-isnull/

michaelbroede · February 2020

Hi Nico,

yes, I'am using RAP.

In the example mentioned above SQLDatasource is a ppDBPipeline, wich contains a query to MS-SQL-Server. With RAP I make tests like "SQLDatasource['ADateTime'] = null" to control the output of correspondig fields. For instance we print the string "No Information" if we get NULL from the database for a field.

Background of my question is that we implemented a pass-through function like this:

With RB 20 this function no longer works correctly. If we pass a DB-Value that is NULL the breakpoint will never reached.
The function works fine for all RB versions we used before (up to 15.05).
Tests like "if SQLDatasource['db_fieldname'] = null" works also in older RB versions correctly.

Thank you for your answer.

Michael

nicocizik · February 2020

Hi Michael,

I installed some older versions (RB 14, RB 12) and did some testing with code similar to yours and SQL Server. The behavior of null values is the same as in RB 20. When retrieving a field value from the datapipeline, any null values are automatically converted to and empty string or 0 based on the datatype.

You can see in the ppDB.pas file inside the GetFieldValueForAlias routine where this is done. This routine is called each time a field value is retrieved from the pipeline and this code has been in place for quite some time.

I'm unsure how this worked for your application in previous versions but moving forward, you will need to check for IsNull, then send the Null value to your pass-through in that case.

michaelbroede · February 2020

Hi Nico,

I've got it. The problem depends on datatype.

A test like "if SQLDatasource['db_fieldname'] = null" works fine in RB 15 if datatype of 'db_fieldname' is dtLargeInt. Such tests don't work in RB 20, general for all datatypes.

I think you can see the difference if you compare the versions of GetFieldValueForAlias in ppDB.pas. Unfortunately I don't have any sourcecode for RB 15.

BTW: The case block in GetFieldValueForAlias for converting varNull to 0 contains dtTime and dtDate. It seems within RAP such NULL-values are not 0 but '' (empty string).

I have a last question in this context: How do I detect within RAP if a value from database query is NULL?

Thank you for your answer.

Michael

nicocizik · March 2020

Hi Michael,

Upon further research, you are correct, this was a bug in RB 15 that was fixed in later versions. The LargeInt type was not being converted to 0 in the null case as it should in RAP. We simply added dtLargeInt to the list of types to convert.

See the link to the article I gave in my first response for how to check for null values from RAP.

Problem with DB-NULL-Value (RB 20.02)

Comments