2
votes

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)

5
You don't use single quote but backtick : `orders` should be 'orders' or it's just a copy&paste situation.RealHowTo
I've removed the backticks, still get the same result.john
Can you post the table structure?jlordo
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
Guys, it's a syntax error. Nothing to do with data types whatsoever.user207421

5 Answers

2
votes

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

1
votes

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.
0
votes
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.

0
votes

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.

0
votes

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!