Invalid data conversion: Parameter instance 50.0/1

Question

String expression = CHEMICAL_REORDERPOINT + "*" + searchRequest.getReorderPercentage() + "/100)";

Before :

String expression = CHEMICAL_REORDERPOINT + "*" + searchRequest.getReorderPercentage() + "/100)";

searchRequest.getReorderPercentage() comes dynamically from browser after submit value. Lets take value for searchRequest.getReorderPercentage() = 50

so String expression = CHEMICAL_REORDERPOINT*50/100; 

This is getting populated in a prepared statement of JDBC in my application, so to maintain the prepare statement rule i have used in below way:

After :

String expression = CHEMICAL_REORDERPOINT + "*?)"
String str = searchRequest.getReorderPercentage() + "/100";
params.add(str)

here params is a list from which the parameters will be iterated and will be placed in postion parameters of prepare statement while executing it.

But now i m getting exception like Invalid data conversion: Parameter instance 50.0/100 is invalid for the requested conversion. ERRORCODE=-4461, SQLSTATE=42815

Pls can any one help me out. Thanks

Answer 1

If I had to guess I would say it is an auto conversion issue.

You had something like

5 * 50.0 / 100

and it worked.

No you have

50.0 / 100

and it fails.

It seems in the first it converted all operands to integers and in the second it converts them to floating point. But 100 is not a valid floating point constant. So change your new output to

50.0 / 100.0

or

50 / 100

Answer 2

Your parameterized query string should look like "whatever is in CHEMICAL_REORDERPOINT * (? / 100)", and you should set the percentage value using setDouble() or setFloat(), not as a String. Right now you are trying to tell DB2 to multiply whatever by a string, which doesn't make much sense.