mysql prepared statement java

Question

I'm working with a prepared statement I've generated, and I'm getting a syntax error on the statement thrown by java. Yet when i copy and paste the toString of the PS into phpmyadmin for the database, it executes flawlessly. any idea's what could be wrong, i'm fairly stumped?

edit: changed to ps.executeUpdate(query); still doesn't work.

public int addOrder(Order order){
    int rs=false;
    try {
        String query = "INSERT INTO `orders`(`orderNumber`, `productNumber`, `quantity`, `orderer`, `assembler`, "
                + "`meshType`, `beadType`, `beadCount`, `notes`, `dateCompleted`, `dateSubmitted`, `isComplete`) "
                +"VALUES (?,?,?,?,?,?,?,?,?,?,?,?)";
        PreparedStatement ps = con.prepareStatement(query);
        ps.setString(1, order.getOrderNumber());
        ps.setInt(2, order.getProductNumber());
        ps.setInt(3, order.getQuantity());
        ps.setString(4, order.getOrderer());
        ps.setString(5, order.getAssembler());
        ps.setString(6, order.getMesh());
        ps.setString(7, order.getBeadType());
        ps.setInt(8, order.getBeadCount());
        ps.setString(9, order.getNotes());
        ps.setLong(10, order.getDateCompleted().getTime());
        ps.setLong(11, order.getDateSubmitted().getTime());
        ps.setBoolean(12, order.getIsComplete());
        System.out.println(ps.toString());
        rs = ps.executeUpdate(query);

    } 
    catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
    return rs;
}

the error message i get, preceded by the ps.toString() from addOrder. and like i said, if i copy paste the relevant part of the toString into phpmyadmin and execute it works fine. any ideas of what i'm doing wrong?

   com.mysql.jdbc.JDBC4PreparedStatement@40378309: INSERT INTO
 `orders`(`orderNumber`, `productNumber`, `quantity`, `orderer`,
 `assembler`, `meshType`, `beadType`, `beadCount`, `notes`,
 `dateCompleted`, `dateSubmitted`, `isComplete`) VALUES
 ('',251,1,'Mark','','Other','LBB',150,'this is a
 test',1357249393009,1357249393010,0)

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '?,?,?,?,?,?,?,?,?,?,?,?)' at line 1 at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source) at java.lang.reflect.Constructor.newInstance(Unknown Source) at com.mysql.jdbc.Util.handleNewInstance(Util.java:411) at com.mysql.jdbc.Util.getInstance(Util.java:386) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1053) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4096) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4028) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2490) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2651) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2728) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2678) at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:894) at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:732) at cbs.business.internalorders.Database.addOrder(Database.java:232) at cbs.business.internalorders.IOGui$1.widgetSelected(IOGui.java:205) at org.eclipse.swt.widgets.TypedListener.handleEvent(Unknown Source) at org.eclipse.swt.widgets.EventTable.sendEvent(Unknown Source) at org.eclipse.swt.widgets.Widget.sendEvent(Unknown Source) at org.eclipse.swt.widgets.Display.runDeferredEvents(Unknown Source) at org.eclipse.swt.widgets.Display.readAndDispatch(Unknown Source) at cbs.business.internalorders.IOGui.(IOGui.java:218) at cbs.business.internalorders.InternalOrders.main(InternalOrders.java:15)

You don't use single quote but backtick : `\`orders\`` should be 'orders' or it's just a copy&paste situation. — RealHowTo, Jan 03 '13 at 22:23
here you go... https://dl.dropbox.com/u/4115151/tablestructure.jpg — john, Jan 03 '13 at 22:43
Your `dateCompleted` and `dateSubmitted` are creating this problem, In database they are suppose to be as date and they are declared as `bigInt`. Also make sure that they are `DATE` **OR** `TIMESTAMP` — Smit, Jan 03 '13 at 22:47
dateCompleted and dateSubmitted are parsed as integers for storage, if you'll notice int the toString, they are being parsed out, and then I store those values as bigInt's in the database. When i query the order, I then use Date(long) to parse it back into a date object for use in my program. — john, Jan 03 '13 at 22:49
@john I don't see anything else rather than you are setting `Long` values in `BigInt` type. This could cause the problem. Try to typecast first and then insert them. — Smit, Jan 03 '13 at 23:19
Guys, it's a syntax error. Nothing to do with data types whatsoever. — user207421, Jan 04 '13 at 04:19

score 2 · Answer 1 · edited Jan 18 '13 at 02:12

2

@TheCapn's deleted answer is almost correct. Change executeQuery(query) to executeUpdate(), without the parameter.

edited Jan 18 '13 at 02:12

Anthony Accioly

21,918
9
70
118

answered Jan 04 '13 at 04:19

user207421

305,947
44
307
483

score 1 · Answer 2 · answered Jul 18 '14 at 00:07

If you put query in it, it will execute twice. With your case, you should not put query in it. All you need is like this;

ps.executeUpdate(query); <-- remove 'query'

//should be like this
ps.executeUpdate();

executeQuery(); //Generally this use for select statement. The output will be in Resultset. 

executeUpdate(); //Generally this use for insert, update, delete and drop table.

execute(); //If you don't know which method to be used for executing your SQL statements, you can use this.

I don't understand the downvote, this worked for me so +1. Thanks a lot! — Shivalnu, May 24 '18 at 01:22

score 0 · Answer 3 · answered Jan 03 '13 at 22:25

0

String query = "INSERT INTO `orders`(`orderNumber`, `productNumber`, `quantity`, `orderer`, `assembler`, "
            + "`meshType`, `beadType`, `beadCount`, `notes`, `dateCompleted`, `dateSubmitted`, `isComplete`) "
            +"VALUES (?,?,?,?,?,?,?,?,?,?,?,?)";

should read

String query = "INSERT INTO orders(orderNumber, productNumber, quantity, orderer, assembler, "
            + "meshType, beadType, beadCount, notes, dateCompleted, dateSubmitted, isComplete) "
            +"VALUES (?,?,?,?,?,?,?,?,?,?,?,?)";

i.e. no quotes.

answered Jan 03 '13 at 22:25

BevynQ

8,089
4
25
37

I removed the quotes from the query, still get the same result. this is my new string query. String query = "INSERT INTO orders(orderNumber, productNumber, quantity, orderer, assembler, " + "meshType, beadType, beadCount, notes, dateCompleted, dateSubmitted, isComplete) "+"VALUES (?,?,?,?,?,?,?,?,?,?,?,?)"; – john Jan 03 '13 at 22:30
try doing it as a normal Statement see if that gives you something different. – BevynQ Jan 03 '13 at 22:54

score 0 · Answer 4 · answered Jan 04 '13 at 04:27

0

I think the error could be from the data type conversion. Can you try without using parameters and setting the values directly? I had similar issues in ADO.NET before.

answered Jan 04 '13 at 04:27

Mukus

4,870
2
43
56

score 0 · Accepted Answer · answered Jan 25 '13 at 16:50

0

I finally found my solution, For some reason it didn't like my setStrings so I set it the long way and got it to work. thank you!

answered Jan 25 '13 at 16:50

john

775
3
15
31

mysql prepared statement java

5 Answers5

Linked