It's not a secret anymore!
The moose likes JDBC and Relational Databases and the fly likes Doubt Regarding PreparedStatement Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Doubt Regarding PreparedStatement" Watch "Doubt Regarding PreparedStatement" New topic

Doubt Regarding PreparedStatement

Paramagurusamy Balasubramanian
Ranch Hand

Joined: Aug 17, 2001
Posts: 55
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,
Peter den Haan
Ranch Hand

Joined: Apr 20, 2000
Posts: 3252
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

Joined: Mar 14, 2001
Posts: 74

will throw a SQLException.
The parameter you are trying to set as int will actually be a String due to the ":".

"It is almost always answered in the API" - Murali Nanchala
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

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!
[ May 29, 2003: Message edited by: Jamie Robertson ]
Surendran Velath

Joined: Apr 23, 2003
Posts: 19
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
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
I agree. Here's the link:
subject: Doubt Regarding PreparedStatement
It's not a secret anymore!