Migration from Informix 9 to Informix 15, JDBC Problem with float in prepared statements

Why I don't know but does select 2.0::FLOAT * ? from dsfco select (2.0::FLOAT * ? )::FLOAT from dsfco work as expexted

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 Ittgenshorst ------------------------------

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 ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------

May I ask what the behavior is when using the old JDBC driver against the new server version? ------------------------------ Andreas Legner Informix Dev HCL Software ------------------------------

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 Ittgenshorst ------------------------------

Hello Andreas, thanks for your message. I get the following message when I try: com.informix.asf.IfxASFException: The attempt to connect to the database server failed. I can't tell which version of the jdbc driver it is. The timestamp of the ifxjdbc.jar file is from 2011. The sqlhosts configuration of the servers is different. Informix9: /users/informix/etc/sqlhosts rinnen_lin_t olsoctcp rinnen_lin_t sqlturbo Informix15: /users/informix/etc/sqlhosts.reteco reteco onsoctcp rinnen-lin reteco ------------------------------Andreas Ittgenshorst ------------------------------

> I don't know if this is transmitted by the ODBC driver or if the database server decides to use an INTEGER type. Sorry of course I meant JDBC driver. ------------------------------ Andreas Ittgenshorst ------------------------------

Yes, it is likely that the old driver made the decision to assign the replaceable parameter as an INT instead of a FLOAT or DECIMAL. I ran the query into my sqlstruct utility that just prepares the query and the DESCRIBES it to see what types the engine wants to see and translates that into any of several formats, the default is a simple "C" struct and typedef: art@Gandolf:~/GoogleDrive/MyConsulting/NIB$ sqlstruct -d art -n nam > select (2 * ?); typedef struct nam_s { dec_t (expression); } nam_t, *nam_tp; > select 2 * ? ; typedef struct nam_s { dec_t (expression); } nam_t, *nam_tp; So, the engine (v15.0) sees the result as a dec_t with the parenthesis or without them. Art ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------