Subscribe for automatic updates: RSS icon RSS

Login icon Sign in for full access | Help icon Help
Advanced search

Pages: [1]
  Reply  |  Print  
Author Topic: Execute SQL with null value  (Read 15483 times)
Anderson P.
Posts: 82


« on: July 12, 2016, 03:30:51 pm »

When I prepare some SQL statement with question marks "?" Genero replaces it with some variable value, that works just fine. If the variable value is null, the query also executes and return sqlcode 0, which means no error, but the query does not return the expected values.

So I have a query with a "date" field and the value could be a date or null, both are right and must return some value, but when the date is null ir simply don't return nothing and gives no error.

My question is, how does Genero handle a null variable at the "execute using" command?

Genero Studio 2.50.20 build-136704
fglrun 2.50.11 build-2343.24
IBM Informix Version 11.70
Sebastien F.
Four Js
Posts: 509


« Reply #1 on: July 12, 2016, 04:12:56 pm »

Hello Anderson,

NULL is a special value that causes a lot of trouble in SQL programming.
As a general advice try to avoid it when you can set a default value.

When using ? place holders + EXECUTE USING a NULL value, Genero SQL drivers just pass the parameter as NULL to the database client library, as for any other SQL statement (INSERT, UPDATE, DELETE).

Then database server rules apply regarding NULL comparison...
SQL standard requires NULL comparison with the IS NULL or IS NOT NULL operator.
When using the = equal operator and an operand is NULL, the boolean expression evaluates to FALSE (or NULL, but never to TRUE with NULL=NULL).

Therefore, if you need to search for NULLs and for a specific date in the same query, you will have to adapt the SQL to distinguish "colname = ?" and "colname IS NULL", depending on the NULL state of the parameter...

The problem is that colname = ? requires a USING parameter while colname IS NULL does not.
The number of parameters in the USING clause must match the ? place holders.
So you would have to write different FOREACH USING or EXECUTE USING statements.
The trick here is to add a second parameter to perform the (colname IS NULL) test:

Code
  1. WHERE d=? OR (1 = ? AND d IS NULL)
  2.  

Try the following:

Code
  1. MAIN
  2.    DEFINE vd DATE, nt INT, vk INT
  3.  
  4.    DATABASE test1
  5.    --CONNECT TO "test1+driver='dbmpgs'" USER "pgsuser" USING "fourjs"
  6.  
  7.    CREATE TEMP TABLE tt1 ( k INT, d DATE )
  8.    INSERT INTO tt1 VALUES ( 1, TODAY )
  9.    INSERT INTO tt1 VALUES ( 2, NULL )
  10.  
  11.    DECLARE curs1 CURSOR FROM "SELECT k FROM tt1 WHERE d=? OR (1 = ? AND d IS NULL) ORDER BY k"
  12.  
  13.    DISPLAY "With TODAY in USING parameter:"
  14.    LET vd = TODAY
  15.    LET nt = 0
  16.    FOREACH curs1 USING vd, nt INTO vk
  17.        DISPLAY vk
  18.    END FOREACH
  19.  
  20.    DISPLAY "With NULL in USING parameter:"
  21.    LET vd = NULL
  22.    LET nt = 1
  23.    FOREACH curs1 USING vd, nt INTO vk
  24.        DISPLAY vk
  25.    END FOREACH
  26.  
  27. END MAIN
  28.  

However, I wonder:
Why would you need to fetch (in the same result set) rows with a specific date, and with nulls in the date?
Don't you want you treat these rows in 2 separate queries?

Seb
Anderson P.
Posts: 82


« Reply #2 on: July 12, 2016, 04:45:54 pm »

Sebastien,

Thank you very much for your reply, the solution with WHERE d=? OR (1 = ? AND d IS NULL) solves the problem. Maybe I will also be able to do something like

Code
  1. WHERE d=? OR (? IS NULL AND d IS NULL)

Not sure if this is gonna work, but worth a shot.

I need this for a query that generates a report of sent files and files that will be send. So if the user chose the option to list files sent in a specific date, the query runs fine, but if the user chose the option to list files that will be send, this are the ones with the null date.

In other words, files with the informed date are the ones that was already sent, and files with null date are the ones that will be sent, and i need to list both, according to the option.
Reuben B.
Four Js
Posts: 1047


« Reply #3 on: July 13, 2016, 01:03:07 am »

Quote
My question is, how does Genero handle a null variable at the "execute using" command?

If you are ever unsure of the SQL statement that is actually passed to the database, the output when FGLSQLDEBUG is set http://4js.com/online_documentation/fjs-fgl-manual-html/index.html#c_fgl_EnvVariables_FGLSQLDEBUG.html will show you

Quote
I need this for a query that generates a report of sent files and files that will be send. So if the user chose the option to list files sent in a specific date, the query runs fine, but if the user chose the option to list files that will be send, this are the ones with the null date.

In other words, files with the informed date are the ones that was already sent, and files with null date are the ones that will be sent, and i need to list both, according to the option.

Another approach you may want to investigate is to use Multi-Dialog,  where  the date field is in a CONSTRUCT and your other report options are in an INPUT.
Code
  1. OPTIONS FIELD ORDER FORM
  2. DIALOG
  3.   INPUT report_options...
  4.   END INPUT
  5.   CONSTRUCT date_qbe BY NAME ON sent_date ...
  6.   END CONSTRUCT
  7. ...
  8. END DIALOG
  9.  
  10. IF include_sent_reports THEN
  11.   LET l_sql = l_sql, " AND ", date_qbe
  12. ELSE
  13.   LET l_sql = l_sql, " AND sent_date IS NULL "
  14. END IF

This gives your user the flexibility of date-ranges and multiple dates but if you didn't want that you could add some field validation to ensure a single-date is entered (you may need to add validation to ensure user does not enter "=")

We encourage you to use SQL parameters with dates as it avoids the problem of date formats.  Using CONSTRUCT like this also avoids the problems of date formats, and can be handy in situations where report, enquiry QBE's have multiple options that influence the number of parameters that need to be passed to the SQL and you just want to create the resultant SQL rather than having multiple OPENs to cater for differing number of parameters and permutations

 

Product Consultant (Asia Pacific)
Developer Relations Manager (Worldwide)
Author of https://4js.com/ask-reuben
Contributor to https://github.com/FourjsGenero
Anderson P.
Posts: 82


« Reply #4 on: July 13, 2016, 12:58:20 pm »

Reuben,

Thank you for your answer, the use of "construct" is indeed a good approach to solve various input formats. But in this case, i would like to have the SQL already prepared, as this makes the execution faster, at least in our database, when the SQL is executed multiple times.
Benjamin G.
Posts: 98


« Reply #5 on: July 18, 2016, 03:59:23 pm »

Hi,

Another possible approach for this kind of select is using UNION

select * from table where date=?
union
select * from table where date is null


regards
Anderson P.
Posts: 82


« Reply #6 on: July 18, 2016, 04:06:22 pm »

Benjamin

Thanks for your reply! Unfortunately, the "union" will not return the desired results. This solution will always return the "null" values and i want the null values only if the date is null. If the date has been passed, then i don't want the null values.
Benjamin G.
Posts: 98


« Reply #7 on: July 18, 2016, 04:36:46 pm »

ok then with union you can :

select * from table where date=?
 union all
select * from table where date is null and ? is null

see attached sample

but in fact is the same approach than a combined OR with one select ...

* z.4gl (1.03 KB - downloaded 849 times.)
Benjamin G.
Posts: 98


« Reply #8 on: July 18, 2016, 04:46:27 pm »

if you work with informix you can also use the function Nvl ...

select * from table where Nvl(dat,"311289") = ?

Sebastien F.
Four Js
Posts: 509


« Reply #9 on: July 18, 2016, 05:19:08 pm »

The SQL expression (? is null) will not work with all databases, for example with PostgreSQL you get a param type unknown error...
Anderson P.
Posts: 82


« Reply #10 on: July 18, 2016, 06:16:41 pm »

I agree that this "? is null" approach is a bit odd, but I guess there is no "easy way" to solve this, by easy way I mean an official solution, that will be easy to read and understand.

We have chose to declare the SQL twice, one time with "?" and again with "is null". The down side is the need to declare the "foreach" block twice:

Code
  1. if date is null
  2.  then
  3.       foreach pont_date_null into dates[dates.getLength()+1].*
  4.       end foreach
  5.  else
  6.       foreach pont_date using date into dates[dates.getLength()+1].*
  7.       end foreach
  8. end if
  9.  

Not the most elegant solution, but at least it's easy to read and understand.
Sebastien F.
Four Js
Posts: 509


« Reply #11 on: July 18, 2016, 06:26:53 pm »

Yes your solution is easier to read and certainly the most optimal regarding SQL query performances...
Seb
Benjamin G.
Posts: 98


« Reply #12 on: July 18, 2016, 09:46:05 pm »

the following syntax would  also be possible if we wish only one prepare statement (don't know if progress accept that)

 select * from table where (dat=?) or (dat is null and 1=?)

       open cursor using date_null,1  --> if looking for NULL records
or
       open cursor using date_value,0  --> if looking records matched a specific date

... but this is only "une affaire de goût" because there is not only one "best trick"


Benjamin
Pages: [1]
  Reply  |  Print  
 
Jump to:  

Powered by SMF 1.1.21 | SMF © 2015, Simple Machines