Problem with PreparedStatements...

[Bhiku Mhatre]

int test_id = 1;
String sqlQuery = "INSERT INTO test (test_ID,text) VALUES (?,'sometext')";
PreparedStatement prepstmt = dbConnection.prepareStatement(sqlQuery);
prepstmt.setInt(1,test_id);
WORKS FINE
********************************************************************************************

int test_id = 1;
String text = "sometext";
String sqlQuery = "INSERT INTO test (test_ID,text) VALUES (?,'?')";
PreparedStatement prepstmt = dbConnection.prepareStatement(sqlQuery);
prepstmt.setInt(1,test_id);
prepstmt.setString(2,text);

ERROR : ORA-01006: bind variable does not exist


********************************************************************************************

String text = "sometext";
String sqlQuery = "INSERT INTO test (test_ID,text) VALUES (1,'?')";
PreparedStatement prepstmt = dbConnection.prepareStatement(sqlQuery);
prepstmt.setString(2,text);

ERROR : ORA-17041 Missing IN or OUT parameter at index


********************************************************************************************
Can any one give me hints of why this happens ??
Thanks in advance...

Regards,
Bhiku
[ May 07, 2003: Message edited by: Bhiku Mhatre ]

[Irene Loos]

I think you prepared statement does not undersand single quotes around "?". Try to prepare your string the way that it contains single quotes around the value:

 
int test_id = 1;
String text = "'sometext'";
String sqlQuery = "INSERT INTO test (test_ID,text) VALUES (?,?)";
PreparedStatement prepstmt = dbConnection.prepareStatement(sqlQuery);
prepstmt.setInt(1,test_id);
prepstmt.setString(2,text);
 

[ May 07, 2003: Message edited by: Irene Loos ]

[Andy Bowes]

You should not put the quotes around the string UNLESS you actually want the quotes to be written to the database.
I much prefer the use of PreparedStatements for because you don't need quotes. It makes the SQL much more readable.
p.s.
In the second example you should be referencing parameter number 1 not 2 as there is only a single '?' in the SQL statement.
[ May 07, 2003: Message edited by: Andy Bowes ]

[Irene Loos]

Andy, I do not know what are the rules for all DB but DB2 and UDB will require single quotes on insert statement for string.
Good catch with second example.
[ May 07, 2003: Message edited by: Irene Loos ]

[Jacob George]

Hi,
You don't have to put the single quotes around ? in a PreparedStatement. The driver puts the quotes for you if a String value is being set. So please use query of the form,
pstmt= conn.prepareStatement("INSERT INTO table1 values(?,?)");
pstmt.setInt(1,100);
pstmt.setString(2,"Test");
regards Jacob