MySQL - PreparedStatement problem
Posted by: Dirk Schumacher
Date: November 15, 2004 03:49AM

Here is my table:

CREATE TABLE musicstyle (
id int(11) NOT NULL default '0',
name varchar(30) NOT NULL default '',

I have a problem with the a PreparedStatement.
The code snippet underneath just will not work. And I don't know why.

Here is my code, which does not work (the row in the table which is changed exists and is valid):
int id = 9;
String table = "musicstyle";
String newValue = new Date().toLocaleString() + "_test";
String colName = "name";

DriverManager.registerDriver((Driver)Class.forName( "com.mysql.jdbc.Driver" ).newInstance( ));

Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/dev", "localhost", "");

query = "UPDATE "+ table +" SET ? = ? WHERE id = ?";

PreparedStatement ps = connection.prepareStatement(query);
ps.setObject( 1, colName );
ps.setObject( 2, newValue );
ps.setLong( 3, id );



The id field matches to a LONG type. At least this is what the ResultSetMetaData tells me.
Do I have to call the proper preparedStatement setMethod for the data types to be filled, or is it enough to to call

setObject( <index>, <object> ) ?

Best way for me is to call a setObject, since I do not have to run thru a bunch of ifs to call the proper method of the preparedstatement.

The use of a regular statement will work well. But later I want to fill in Blobs. And as far as I know this just works with prepearedstatements.

I already tried several combinations of setObject (setLong, ...), drivers and syntax (as shown underneath).

What is the difference of these lines?:
"UPDATE "+ table +" SET ? = ? WHERE id = ?"
"UPDATE "+ table +" SET ? = (?) WHERE id = (?)"
"UPDATE "+ table +" SET ? = ('?') WHERE id = ('?')"

This Exception is always thrown:

java.sql.SQLException: Syntax error or access violation message from server: "You have an error in your SQL syntax near ''name' = '15.11.2004 10:19:10_test' WHERE id = 9' at line 1"
at com.mysql.jdbc.MysqlIO.checkErrorPacket(
at com.mysql.jdbc.MysqlIO.sendCommand(
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(
at com.mysql.jdbc.Connection.execSQL(
at com.mysql.jdbc.PreparedStatement.execute(

I am really looking forward for some help, I just cannot figure it!-(
Thanx in advance and till later...

Options: ReplyQuote

Written By
MySQL - PreparedStatement problem
November 15, 2004 03:49AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.