1 Topic by mathmathou (edited by mathmathou 2016-10-11 04:47:20)

  • Registered: 2015-02-24
  • Posts: 446

Topic: [Solved]-Checking for NULL values

Hello Dmitry and all MVD fans,


I feel a bit ashamed to ask such a question, but I've been asking it to myself so many times, and so many times found workarounds because I had no answer, that I think it is time to ask it.


Sometimes, I have to check my database for the presence of data.


As I don't known how to do it, instead of looking for null values, I check the count = 0.
if count=0 then data does not exist.


How could I properly check a table field for NULL values ?

For example, if this query returns a NULL value because there is not asset with id 30000

asset_name := SQLExecute('SELECT asset.asset_name FROM asset WHERE asset.id = 30000');

What is the correct DELPHI script syntax ?

if asset_name IS NULL then

or

if asset_name = NULL then

or

if NULL asset_name then

....

Thank you in advance, I've been waiting too long to ask that question smile



Cheers



Mathias

I'm a very good housekeeper !
Each time I get a divorce, I keep the house
Zaza Gabor
  • Registered: 2014-08-13
  • Posts: 94

Re: [Solved]-Checking for NULL values

hi again Mathias

i dont know if this is what you want or not but its what i use and i think thats what its doing.

asset_name := SQLExecute('SELECT IFNULL(MAX(asset.asset_name),0)  FROM asset WHERE asset.id = 30000');

lee

  • Registered: 2014-02-14
  • Posts: 10,294

Re: [Solved]-Checking for NULL values

Hello,


SQLExecute is pretty simple function, without check for nulls values
but you can use this trick to check value of field

if SQLExecute('SELECT IFNULL(fieldname, -1)  FROM asset WHERE asset.id = 30000') = -1 then ShowMessage('null');


or use more advanced function like SQLQuery, an example:

var
    Results: TDataSet;
begin
    SQLQuery('SELECT username FROM MVDB_users WHERE id=999', Results);
    if Results.isEmpty then ShowMessage('empty'); // dataset is empty
    if Results.FieldByName('username').isNull then ShowMessage('null');  //  field "username" is NULL
Dmitry.

4 Reply by mathmathou (edited by mathmathou 2016-10-11 04:47:00)

  • Registered: 2015-02-24
  • Posts: 446

Re: [Solved]-Checking for NULL values

Thank you both for your answers, seems to work fine smile


have a good day and thanks again


Cheers


Mathias

I'm a very good housekeeper !
Each time I get a divorce, I keep the house
Zaza Gabor