Win a copy of Functional Reactive Programming this week in the Other Languages forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

SQL formatting question

 
David Spurgeon
Greenhorn
Posts: 14
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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)



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
Greenhorn
Posts: 18
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator


Try the above code, one comma was missing in the query.
 
Jan Cumps
Bartender
Posts: 2602
13
C++ Linux Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
A PreparedStatement, with parameter binding, would help you to create readable - and better - code:


 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34973
379
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Mahendra: Welcome to CodeRanch!

For more on what "better" means, see the PreparedStatement wiki page.
 
David Spurgeon
Greenhorn
Posts: 14
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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!



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
Greenhorn
Posts: 14
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Bartender
Posts: 2602
13
C++ Linux Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
    Bartender
    Posts: 2407
    33
    Linux Oracle Postgres Database Python Scala
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    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
    Greenhorn
    Posts: 14
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    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
    Bartender
    Posts: 2407
    33
    Linux Oracle Postgres Database Python Scala
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    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.
     
    • Post Reply
    • Bookmark Topic Watch Topic
    • New Topic