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

Topic: IS NULL / IS NOT NULL

Hello Dmitry and all MVD fans,


Sometimes, to check the existence of a value in database I perform a 

SELECT COUNT(id)....

Obviously, if there is not occurrence of what I am looking for, the returned result is 0 because I asked for a count.


But sometimes it's not possible or not handy to count, and I just check the database with something like 

SELECT id_bundle FROM bundle_asset WHERE id_asset="'+IntToStr(asset_id)+'"

then I make a conditional statement to act upon the result.


But if there is no result, how should I test the result in Delphi script?

if id_bundle IS NULL then...
if id_bundle 'IS NULL' then...
if id_bundle (IS NULL) then...
if id_bundle = 0 then...

the = 0 seems to work but I am not confortable to compare 'no result' with 0....


Any idea ?


Mathias

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

Re: IS NULL / IS NOT NULL

Hello MAthmatou

Long, long ago, for this kind of search, I used to use this function :

function IfNull( const Value, Default : OleVariant ) : OleVariant;
begin
  if MyValue = NULL then
    Result := Default
  else
    Result := MyValue;
end;

then I apply it something as  (this is far I am writing it memory) :

Result := IfNull( MyTable(MyField.Value,0) ) then ....  else ....;

My time, it worked (although I'm not really sure of the above syntax)

Regards

JB

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

Re: IS NULL / IS NOT NULL

try this:

var
   id_bundle: string;
begin
   id_bundle := SQLExecute('SELECT id_bundle FROM bundle_asset');
   if id_bundle = '' then ShowMessage ('NULL');
Dmitry.
  • Registered: 2015-02-24
  • Posts: 446

Re: IS NULL / IS NOT NULL

Clever transforming the integer value into a string to test emptiness.... clever.

Thanks Dmitry !!

I'm a very good housekeeper !
Each time I get a divorce, I keep the house
Zaza Gabor
  • Registered: 2014-02-14
  • Posts: 10,294

Re: IS NULL / IS NOT NULL

Also you can use integer variable:

var
   id_bundle: integer;
begin
   id_bundle := SQLExecute('SELECT IFNULL(id_bundle,-1) FROM bundle_asset');
   if id_bundle = -1 then ShowMessage ('NULL');
Dmitry.
  • Registered: 2015-02-24
  • Posts: 446

Re: IS NULL / IS NOT NULL

Thanks Dmitry, I'll try that !

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