SQL formatting question

[David Spurgeon]

When running a program it executes the sql code as follows (the database is there, connected, etc., so the problem is with format):
Database fields are named the same as the entries I will be putting into them. The parameters have values other than the parameter name, of course.
Prior to this code, the field values are populated into the parameters specified. (ie, studentID = 123456)

 Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/database",
                    "login", "admin");
            helper = conn.createStatement();

String addStatement = "INSERT INTO student Set (studentID, firstName, lastName, gpa, status, mentor, level, creditHours) Values ('" 
            + studentID + "' '" + firstName + "', '" + lastName + "', '" + gpa + "', '" + status + "', '" + mentor + "', '" + level + "')";
    helper.executeUpdate(addStatement);

Error generated
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax;

I tried to get a class teacher to tell me what is wrong, but no response. If someone could please point out the dumbarse syntax thing I'm missing, I would be very grateful!!

[Mahendra Suda]

String addStatement = "INSERT INTO student Set (studentID, firstName, lastName, gpa, status, mentor, level, creditHours) Values ('"   
            + studentID + "' ,'" + firstName + "', '" + lastName + "', '" + gpa + "', '" + status + "', '" + mentor + "', '" + level + "')";

Try the above code, one comma was missing in the query.

[Jan Cumps]

A PreparedStatement, with parameter binding, would help you to create readable - and better - code:

    PreparedStatement pInsert = 
        con.prepareStatement("insert into table students (studentID, firstName, lastName, gpa, status, mentor, level, creditHours) values (?, ?, ?, ?, ?, ?, ?, ?)");
    pInsert.setString(1, studentID); ...

[Jeanne Boyarsky]

Mahendra: Welcome to CodeRanch!

For more on what "better" means, see the PreparedStatement wiki page.

[David Spurgeon]

I fixed the comma error...yeah, I knew I had dome SOMETHING stupid. (Then again, if I liked programming I would have been a computer science major, which I'm not.) Now I have a whole list of errors!

    String addStatement = "INSERT INTO student Set (studentID, firstName, lastName, gpa, status, mentor, level) Values ('" 
            + studentID + "', '" + firstName + "', '" + lastName + "', '" + gpa + "', '" + status + "', '" + mentor + "', '" + level + "')";
    helper.executeUpdate(addStatement);

I don't yet understand this "PreparedStatement" thing, but I am looking into it. I just don't understand why the current statement does not work.

Error output messages:
ERROR: Either cannot connect to the DB or error with the SQL statement
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 '(studentID, firstName, lastName, gpa, status, mentor, level) Values ('123', 'sfd' at line 1
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:406)
at com.mysql.jdbc.Util.getInstance(Util.java:381)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1030)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3491)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3423)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1936)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2060)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2536)
at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1564)
at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1485)

[David Spurgeon]

No ideas on why the original statement doesn't work? This is the only problem I'm having with my program.
Thank you.

[Jan Cumps]

Seems you have mixed these syntaxes for inser table:

insert into table (

insert into table set

http://stackoverflow.com/questions/861722/mysql-insert-into-table-values-vs-insert-into-table-set

[chris webster]

One small observation: Watch out for reserved words if you're simply using the same names for your Java class properties/DB table columns. For example, LEVEL is a pseudo-column used with tree queries in Oracle, and other DBs will have their own set of key words beyond the standard SQL SELECT etc, which may cause strange errors in some circumstances.

[David Spurgeon]

Thank you, everyone. It worked when I eliminated the set command, so I'm good.

One quick note: I was trying to add a record where some additional unmentioned fields could have been ignored, so I didn't want to fill every field with a value. In the current (now workable) form, I have to populate the additional fields with 'none'. But it works, so I'm satisfied.

[chris webster]

If you don't want to populate all of your table columns during an INSERT, you can leave the unwanted columns out of the SQL altogether. You just put the columns you want to populate in the first part of the INSERT, and the corresponding values in the VALUES(...) part. Pretty easy, really.

So if you have a table my_table with columns col1, col2, col3 and col4, but you don't want to populate col4, you can do this:

INSERT INTO my_table (col1, col2, col3) VALUES (val1, val2, val3);

Of course, this only works if the missing column allows NULL. If col4 is defined as NOT NULL, then you'll get an error when you try to insert a record without a value for that column.

Also, check out Jeanne's PreparedStatement tip, because there are lots of reasons why you really don't want to be building your SQL as a dumb string concatenation.