• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Doubt Regarding PreparedStatement

 
Paramagurusamy Balasubramanian
Ranch Hand
Posts: 55
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Dear Gurus,

I am doing Manipulation in the database by passing update query from my application.My back end is MySQL.

Say my Example Query is update <table_name> set timestamp=date_add(now(),interval '5:30' minute_second) where id=10;

The above query adds 5 minutes and 30 seconds to the timestamp of id's value is 10.
now() returns current date and time..Say for eg 2003-05-29 16:30:00.
so date_add will return 2003-05-29 22:00:00

i do like the following..

1 PreparedStatement psmt=con.preparedStatement("update table1 set timestamp=date_add(now(),interval '?:?' minute_second) where id=?");
2 psmt.setInt(1,5);
3 psmt.setInt(2,30);
4 psmt.setInt(3,10);
5 psmt.executeUpdate();

If i do the above task using PreparedStatement then i get ArrayIndexOutOfBoundsException(at line number 3).Pls advice how to proceed.

Thanks & Regards,
Paramaguru
 
Peter den Haan
author
Ranch Hand
Posts: 3252
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Parama guru:
Dear Gurus,
O humble acolyte,

This PreparedStatement takes just one parameter: the value that id is compared with. This is why you get an exception as soon as you try to set more than one parameter. '?:?' is a string literal embedded in your statement that happens to have question marks in it. You cannot use JDBC parameters that way.
Do not think of JDBC parameters as strings that will be substituted into your SQL statement. That's not how they work; they are syntactic elements of an SQL expression as much as a column name or literal value is.
You can probably do something like - Peter
 
Murali Nanchala
Ranch Hand
Posts: 74
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

will throw a SQLException.
The parameter you are trying to set as int will actually be a String due to the ":".
 
Jamie Robertson
Ranch Hand
Posts: 1879
MySQL Database Suse
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Parama guru:
Howdidly ho neighbo[u]r, and welcome to the Ranch! We hope you’ll enjoy pounding back pints of java help on a regular basis. However, we do have one piddly diddly little rule around these parts. Please change your display name to one that satisfies our piddly diddly little rule( naming policy - The skinny of the policy: just use a real firstname, followed by a space and a real lastname ). That's all! Thanks again and we hope to see you ordering up pints of java help on a regular basis!
Jamie
[ May 29, 2003: Message edited by: Jamie Robertson ]
 
Surendran Velath
Greenhorn
Posts: 19
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
When you say
In the expression:
pstt = con.preparedStatement("update table1 set col1=?,col2=? where id= ?");
each ? is supposed to be bound by a value against a database column
so
pstt.setObject(1, obj);
will set obj as the value for col1 and so on
Within the obj we cannot put a ? and bind it to the statement
 
Don't get me started about those stupid light bulbs.
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic