I have software appliction in java that has SQL statement.The software communicates with SQL server and DB2 as the backend databse. The SQL statement is a dynamically generated one that uses application variables in the where clause...Here is how the SQL in the code looks like..

void getData(int pIDvarFromAppl)
{
Statement st= createStatement();
String stSQl= "SELECT * FROM " + toUppercaseFunc("sampletbl") + " WHERE p_id = '"+ pIDvarFromAppl+"'";
return st.executeQuery(stSQL);
}

In the snippet above toUppercaseFunc() is a method which converts the string to upperCase.
p_id is the collum in the table sampletbl.Its int type.
pIDvarFromAppl is the int variable in the application which is assigned some integer value like for eg pIDvarFromAppl =45.

This code works perfectly with SQL database but not on DB2.In DB2 the error got is SQLcode: -0401 SQLSTATE:42818

Any idea on what may be the possible reason.....

Thanks

You compared integer(p_id) and character constant(for eg '45').
Please try to remove quotation marks before and after pIDvarFromAppl.

By the way, it will be better to include whole error messages in your post.

For example, you may be able to know the operation in error.

Thanks a million for your answer. Looks like that was the thing that i was missing. For some reason, I think ,SQL server is quite forgiving, in the sense the code runs perfectly for SQL server with the quotes, but it seems db2 dosent like it.

I will run the code with the changes made on monday and will let you know the results.

Once again thanks a lot for your help.

DB2 is not case sensitive when it comes to object names (like table name) so long as you leave the quotes off.

Otherwise said, the following should also work (and is a bit more readable):
Also, consider replacing "SELECT *" by an explicit SELECT of the columns you want.
For one thing, this will save you surprises when the table would ever be modified to have more columns;
and also, this will improve the program's readability because of the better visibility (and order) of the column names.
And finally, in case your program doesn't need all table columns, it will improve the performance.

Thanks a lot to everybody for your help. The extra quote was an issue. I took it off and issue resolved.

Once again a BIG Thanks to all of you.