We have always used setString() to set bind variables for String, int, double, and with Oracle it never complained. Due to this we have a lot of queries with similar cases. I am sure, that I will not be able to modify these queries.
Another thing I tried, was before execution using H2, if the values is of double type, then use setDouble, but that also does not give correct result. SELECT 1 - ? FROM DUAL stmt.setDouble(1, 0.1); Output = 1 If setDouble works, will using setDouble be a good idea? a) Will have to test to see if it work for complicated cases, like [SELECT (? - (1 -?) * ?) FROM DUAL] b) Putting an additional check before setting each bind variable to see whether it is a int or double, will impact performance as we execute a lot of queries per request. A patch to use double instead of integer (only in cases where int conversion fails) would impact less in terms of performance. What do you suggest? On Wednesday, June 27, 2012 1:04:13 PM UTC-4, Thomas Mueller wrote: > > Hi, > > I suggest not to use varchar, or cast as appropriate. Another suggestion > is to use 1.0 instead of 1 if you want the result to be decimal. > > drop table test; > create table test(id int); > insert into test values(0); > > select 1 - '0.1' from test; > --> H2 (no matter which mode is used), Data conversion error converting > "0.1" > --> PostgreSQL: ERROR: invalid input syntax for integer: "0.1" > --> Derby: Invalid character string format for type INTEGER. > > select 1.0 - '0.1' from test; > --> H2, PostgreSQL, Derby: 0.9 > > Regards, > Thomas > > > On Tuesday, June 26, 2012, Vinod wrote: > >> I am executing dual queries using H2(in memory). >> But the results do not seem correct. >> >> --------------------------------- >> ***Without bind variables: >> --------------------------------- >> SELECT 1 - '0.1' FROM DUAL >> Output=0 [OracleOutput: 0.9] >> --------------------------------- >> >> --------------------------------- >> ***Using bind variables: >> --------------------------------- >> SELECT 1 - ? FROM DUAL >> stmt.setString(1, "0.1"); >> Output: 0 [OracleOutput: 0.9] >> >> -- tried double >> SELECT 1 - ? FROM DUAL >> stmt.setDouble(1, 0.1); >> Output: 1 [OracleOutput: 0.9] >> >> SELECT (? - (1 -?) * ?) FROM DUAL >> set bind variables ["0.95", "1", "0.025"] using setString. >> Output: 1 [OracleOutput: 0.95] >> --------------------------------- >> >> Am I missing something? I am already using "MODE=Oracle" while creating >> connection. >> >> Any help would be much appreciated. >> >> -- >> You received this message because you are subscribed to the Google Groups >> "H2 Database" group. >> To view this discussion on the web visit >> https://groups.google.com/d/msg/h2-database/-/wwsLFpgcGnYJ. >> To post to this group, send email to [email protected]. >> To unsubscribe from this group, send email to >> [email protected]. >> For more options, visit this group at >> http://groups.google.com/group/h2-database?hl=en. >> > -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To view this discussion on the web visit https://groups.google.com/d/msg/h2-database/-/5GoZ8iM0Wf4J. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
