Best unofficial Apache Server developers community
Username
Forgot password?
Sign in with Twitter account
Sign in with Facebook account

Jdbc and MySql not wanting to play nicely together

1

35 views

I have been working on a java application that has a connection to a mysql database. I can connect and run queries but when I try to take a string and run it as a sql query I get this error.

Exception in thread "main" 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 'INSERT INTO usageData (`COMID`,`Year`,`Month`,`kwhr`,`co2`) 
VALUES ('15650', '2' at line 3
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:409)
at com.mysql.jdbc.Util.getInstance(Util.java:384)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1054)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3566)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3498)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1959)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2113)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2562)
at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1664)
at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1583)

Bellow is the code that I'm using to connect to the database

public static void main(String[] args) throws Exception {

    String dbms = "mysql";
    String serverName = "localhost";
    String portNumber = "8889";
    String DBName = "ConnectDatabase";
    String user = "root";
    String password = "root";

    ArrayList<Integer> yearList = new ArrayList<Integer>();
    ArrayList<CustomerRecord> customerRecordList = getCustomerRecords(args[0], yearList);
    ArrayList<ClimateRecord> climateRecordList = getClimateRecords(args[1]);


    StringBuffer buf = new StringBuffer();

    for (CustomerRecord record : customerRecordList) {
        buf.append(customerRecord2SQL(record));
    }

    for (int i = (climateRecordList.size() - 1); i >= 0; i--) {
        //for (ClimateRecord record : climateRecordList) {
        ClimateRecord record = climateRecordList.get(i);
        buf.append(climateRecord2SQL(record));
    }

    buf.append(cityStats(dbms,serverName,portNumber,DBName,user,password));
    buf.append(zipStats(dbms,serverName,portNumber,DBName,user,password));

    System.out.println(buf.toString());

    //here is the code to go ahead and update the database
    Connection con = null;

    con = DriverManager.getConnection("jdbc:" + dbms + "://" + serverName + ":" + portNumber + "/" + DBName + "?user="+user+"&password=" + password);

    Statement stmt = con.createStatement();

    stmt.executeUpdate(buf.toString());

    BufferedWriter out = new BufferedWriter(new FileWriter(args[2]));
    out.write(buf.toString());
    out.close();
}

asked June 17, 2011 12:35 pm CDT
posted via StackOverflow

3 Answers

1
 

This is not a connection issue. It means that something is wrong with your SQL statement. Try copying the statement as is and executing it directly in the database. See what is wrong and then correct it in the Java code.

I notice that the single quotes around the field names look fancy. That might be a problem.

answered June 17, 2011 1:30 pm CDT
0
 

Your query should be 'INSERT INTO usageData VALUES (COMID,Year,Month,kwhr,co2); and btw COMID,year...what are these..?? and why fancy quotes...??

answered June 17, 2011 1:30 pm CDT
0
Best answer
 

Here's what was happening and what everyone else failed to realize. JDBC will not do more than one query at a time I was trying to run several million queries contained in a string buffer which wouldn't work.

answered June 24, 2011 11:28 pm CDT

Your answer

Join with account you already have


Sign in with Twitter account
Sign in with Facebook account
Sign in with Google Friend Connect

Preview
Similar questions