• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Problem with PreparedStatements...

 
Ranch Hand
Posts: 127
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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 ]
 
Ranch Hand
Posts: 78
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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 ]
 
Ranch Hand
Posts: 171
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 78
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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 ]
 
Ranch Hand
Posts: 46
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Consider Paul's rocket mass heater.
reply
    Bookmark Topic Watch Topic
  • New Topic