Consider null = null and null is null in Blackfish SQL

Not only CodeGear's Blackfish SQL, but database NULL values are a lot of trouble.
The difference between null = null and null is null is one of them.

*

First, the difference between = and is.
The verification environment is all Blackfish SQL.

null = null evaluates to false
null is null evaluates to true.

You can check with the following SQL.

 SELECT * FROM [テーブル名] WHERE NULL=NULL SELECT * FROM [テーブル名] WHERE NULL IS NULL 

In the latter case, all the data in the table will be displayed.

So what's going to be a problem is when you create an ASP.NET Web form and the search value is NULL.

Even if ConvertEmptyStringToNull is false, it is troublesome if null and DBNull are different.

What is the SQL?

 SELECT * FROM [テーブル名] WHERE [カラム]= CASE [パラメータ値] WHEN NULL THEN [カラム] ELSE [パラメータ値] END 

If the "Parameter value" passed is "NULL" , all the data of the table will be acquired with "[Column] = [Column]" as the condition.

If the passed “parameter value” is “some value” , “[column] = [parameter value]” is the condition, and the data that matches the condition is acquired.

In other words, it can be used to implement search functions.
This method is basically effective for SQL Server.

However, in Blackfish SQL, null = null is false, so if " Parameter Value" is "NULL", no data will be retrieved .

Therefore, we decided to implement the desired behavior by writing the following SQL using Blackfish SQL functions.

 SELECT * FROM [テーブル名] WHERE {FN IFNULL([カラム], -1)}= CASE {FN IFNULL([パラメータ値], -1)} WHEN -1 THEN {FN IFNULL([カラム], -1)} ELSE [パラメータ値] END 

NULL is converted to a value (in the example, -1) that never enters the value of "column", and it is used instead of the NULL value.

There are various database specifications, so this time I tried to deal with it.

Unfortunately, there is little information about Blackfish SQL.

I just wore this topic ...

e? I didn't back up my website! ?

He / she will recover even in case of emergency
"WordPress external backup service"

■ Three features
・ You don't have to do it yourself
・ With recovery in case of emergency
・ Backup to external server

コメントを残す