Informix

Hi Paul,

thanks for your reply. Yes, that works. Expected result and on the server side the host variable is type float.

select 2::FLOAT * ? from dsfco
result: 0.0074800001457333565

select (2::FLOAT * ?)::FLOAT from dsfco
result: 0.0074800001457333565

Server side Host Variables
Position Type    Value
0        float     0.0037400000728667

I am wondering why "select 2 * ? from dsfco" with the following code leads to a host variable of type integer although I defined it as float.

String query = "select 2 * ? as result from dsfco";
float val = 0.00374f;
stmt = conn.prepareStatement(query);
stmt.setFloat(1, val);
rs = stmt.executeQuery();

Could it be a problem/bug in the jdbc-15.0.0.0.jar driver?
Or does jdbc send it correctly and the database server converts it afterwards from "float 0.00374" to "int 0"?

BR,
Andreas

Andreas:

The result of the calculation "2 * ?" is being interpreted as type INTEGER because the "2" is an INTEGER as it has not decimal point. You might try "2.0 * ?".

Also note that it is a good practice when including calculations in the projection clause of a SELECT to surround the formula with parenthesis to prevent operators from being misinterpreted. In this case that did not happen, but remember that an asterisk (*) has a special meaning in the projection clause! So next time do:

SELECT (2 * ?) FROM ...

Art

Hi Paul,

thanks for your reply. Yes, that works. Expected result and on the server side the host variable is type float.

select 2::FLOAT * ? from dsfco
result: 0.0074800001457333565

select (2::FLOAT * ?)::FLOAT from dsfco
result: 0.0074800001457333565

Server side Host Variables
Position Type    Value
0        float     0.0037400000728667

I am wondering why "select 2 * ? from dsfco" with the following code leads to a host variable of type integer although I defined it as float.

String query = "select 2 * ? as result from dsfco";
float val = 0.00374f;
stmt = conn.prepareStatement(query);
stmt.setFloat(1, val);
rs = stmt.executeQuery();

Could it be a problem/bug in the jdbc-15.0.0.0.jar driver?
Or does jdbc send it correctly and the database server converts it afterwards from "float 0.00374" to "int 0"?

BR,
Andreas

Art:

Yes, that was also my assumption and "SELECT (2.0 * ?)" gives the expected result.

But "SELECT (2 * ?)" with the old driver (2011) against our productive system (Informix Dynamic Server Version 9.30.UC3) also gives the correct result.

I am surprised that according to InformixHQ SQL Trace a host variable of type INTEGER is created on the new server although float was defined as follows. I don't know if this is transmitted by the ODBC driver or if the database server decides to use an INTEGER type. But because it uses integer the result is 0, that's for sure.

String query = "select (2 * ?)";
stmt = conn.prepareStatement(query);
float val = 0.00374f;
stmt.setFloat(1, val);
rs = stmt.executeQuery();

Could it be that the old jdbc driver converts the integer 2 to float 2.0 because ? was defined as float? Consequently, it sends "SELECT (2.0 * ?)" to the server and I therefore get the correct result? And jdbc-15.0.0.0 simply behaves differently in terms of processing?

Andreas:

The result of the calculation "2 * ?" is being interpreted as type INTEGER because the "2" is an INTEGER as it has not decimal point. You might try "2.0 * ?".

Also note that it is a good practice when including calculations in the projection clause of a SELECT to surround the formula with parenthesis to prevent operators from being misinterpreted. In this case that did not happen, but remember that an asterisk (*) has a special meaning in the projection clause! So next time do:

SELECT (2 * ?) FROM ...

Art

Hi Paul,

thanks for your reply. Yes, that works. Expected result and on the server side the host variable is type float.

select 2::FLOAT * ? from dsfco
result: 0.0074800001457333565

select (2::FLOAT * ?)::FLOAT from dsfco
result: 0.0074800001457333565

Server side Host Variables
Position Type    Value
0        float     0.0037400000728667

I am wondering why "select 2 * ? from dsfco" with the following code leads to a host variable of type integer although I defined it as float.

String query = "select 2 * ? as result from dsfco";
float val = 0.00374f;
stmt = conn.prepareStatement(query);
stmt.setFloat(1, val);
rs = stmt.executeQuery();

Could it be a problem/bug in the jdbc-15.0.0.0.jar driver?
Or does jdbc send it correctly and the database server converts it afterwards from "float 0.00374" to "int 0"?

BR,
Andreas

Art:

Yes, that was also my assumption and "SELECT (2.0 * ?)" gives the expected result.

But "SELECT (2 * ?)" with the old driver (2011) against our productive system (Informix Dynamic Server Version 9.30.UC3) also gives the correct result.

I am surprised that according to InformixHQ SQL Trace a host variable of type INTEGER is created on the new server although float was defined as follows. I don't know if this is transmitted by the ODBC driver or if the database server decides to use an INTEGER type. But because it uses integer the result is 0, that's for sure.

String query = "select (2 * ?)";
stmt = conn.prepareStatement(query);
float val = 0.00374f;
stmt.setFloat(1, val);
rs = stmt.executeQuery();

Could it be that the old jdbc driver converts the integer 2 to float 2.0 because ? was defined as float? Consequently, it sends "SELECT (2.0 * ?)" to the server and I therefore get the correct result? And jdbc-15.0.0.0 simply behaves differently in terms of processing?

Andreas:

The result of the calculation "2 * ?" is being interpreted as type INTEGER because the "2" is an INTEGER as it has not decimal point. You might try "2.0 * ?".

Also note that it is a good practice when including calculations in the projection clause of a SELECT to surround the formula with parenthesis to prevent operators from being misinterpreted. In this case that did not happen, but remember that an asterisk (*) has a special meaning in the projection clause! So next time do:

SELECT (2 * ?) FROM ...

Art

Hi Paul,

thanks for your reply. Yes, that works. Expected result and on the server side the host variable is type float.

select 2::FLOAT * ? from dsfco
result: 0.0074800001457333565

select (2::FLOAT * ?)::FLOAT from dsfco
result: 0.0074800001457333565

Server side Host Variables
Position Type    Value
0        float     0.0037400000728667

I am wondering why "select 2 * ? from dsfco" with the following code leads to a host variable of type integer although I defined it as float.

String query = "select 2 * ? as result from dsfco";
float val = 0.00374f;
stmt = conn.prepareStatement(query);
stmt.setFloat(1, val);
rs = stmt.executeQuery();

Could it be a problem/bug in the jdbc-15.0.0.0.jar driver?
Or does jdbc send it correctly and the database server converts it afterwards from "float 0.00374" to "int 0"?

BR,
Andreas

Hi Paul,

thanks for your reply. Yes, that works. Expected result and on the server side the host variable is type float.

select 2::FLOAT * ? from dsfco
result: 0.0074800001457333565

select (2::FLOAT * ?)::FLOAT from dsfco
result: 0.0074800001457333565

Server side Host Variables
Position Type    Value
0        float     0.0037400000728667

I am wondering why "select 2 * ? from dsfco" with the following code leads to a host variable of type integer although I defined it as float.

String query = "select 2 * ? as result from dsfco";
float val = 0.00374f;
stmt = conn.prepareStatement(query);
stmt.setFloat(1, val);
rs = stmt.executeQuery();

Could it be a problem/bug in the jdbc-15.0.0.0.jar driver?
Or does jdbc send it correctly and the database server converts it afterwards from "float 0.00374" to "int 0"?

BR,
Andreas

May I ask what the behavior is when using the old JDBC driver against the new server version?

Hi Paul,

thanks for your reply. Yes, that works. Expected result and on the server side the host variable is type float.

select 2::FLOAT * ? from dsfco
result: 0.0074800001457333565

select (2::FLOAT * ?)::FLOAT from dsfco
result: 0.0074800001457333565

Server side Host Variables
Position Type    Value
0        float     0.0037400000728667

I am wondering why "select 2 * ? from dsfco" with the following code leads to a host variable of type integer although I defined it as float.

String query = "select 2 * ? as result from dsfco";
float val = 0.00374f;
stmt = conn.prepareStatement(query);
stmt.setFloat(1, val);
rs = stmt.executeQuery();

Could it be a problem/bug in the jdbc-15.0.0.0.jar driver?
Or does jdbc send it correctly and the database server converts it afterwards from "float 0.00374" to "int 0"?

BR,
Andreas