Meaningless Drivel is fun!*
The moose likes JDBC and the fly likes Problem with PreparedStatements... Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


JavaRanch » Java Forums » Databases » JDBC
Bookmark "Problem with PreparedStatements..." Watch "Problem with PreparedStatements..." New topic
Author

Problem with PreparedStatements...

Bhiku Mhatre
Ranch Hand

Joined: Apr 08, 2002
Posts: 127
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 ]

The difference between winner and loser is making things happen and letting things happen.
Irene Loos
Ranch Hand

Joined: Apr 15, 2002
Posts: 78
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:

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

Irene Loos
Andy Bowes
Ranch Hand

Joined: Jan 14, 2003
Posts: 171
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 ]

Andy Bowes<br />SCJP, SCWCD<br />I like deadlines, I love the whoosing noise they make as they go flying past - Douglas Adams
Irene Loos
Ranch Hand

Joined: Apr 15, 2002
Posts: 78
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
Ranch Hand

Joined: Jun 26, 2001
Posts: 46
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
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Problem with PreparedStatements...