Re: [libdbi-users] Error -7 When retrieving NULL flag

[Markus H. <mar...@mh...>]

Vikram Noel Ambrose <noe...@gm...> uttered:

> Example query:
>
> SELECT id,ISNULL(data) FROM....
> or
> SELECT id,data IS NULL FROM....
> or
> SELECT id,CASE WHEN data IS NULL THEN 0 ELSE 1 END AS is_data_null FROM....
>
> dbi_result_get_int_idx(result,2);
>
> Gives me an error:
> -7: The requested variable type does not match what libdbi thinks it
> should be
>
> I'm pretty sure the last two queries are ANSI SQL.
>
> Any ideas how I can retrieve the result of those queries?

I think this is not a problem of the queries themselves. If they  
failed, you'd get a different error. It is just a matter of how the  
database engine returns your values. The libdbi error indicates that  
it obviously doesn't use INT as return type.

There are a couple of options to deal with this:

1) use dbi_result_get_field_type() or dbi_result_get_field_type_idx()  
to check the return type before retrieving the value. Different  
database engines may use different types.

2) use explicit return types like text as they might cause less  
problems, something along the lines of:

SELECT id,CASE WHEN data IS NULL THEN 'null' ELSE 'notnull' END AS  
is_data_null FROM...

3) use libdbi metadata functions to check for NULL values instead of  
using SQL. See dbi_result_field_is_null() and  
dbi_result_field_is_null_idx()

4) the upcoming libdbi release will provide XXX_as_string() and  
XXX_as_longlong() functions which attempt to cast the return values  
regardless of their original types. These might serve as last resorts  
as well as the casting is done in a driver-specific manner.

regards,
Markus



-- 
Markus Hoenicka
http://www.mhoenicka.de
AQ score 38