aspose file tools*
The moose likes JDBC and the fly likes SQL formatting question Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "SQL formatting question" Watch "SQL formatting question" New topic
Author

SQL formatting question

David Spurgeon
Greenhorn

Joined: Oct 20, 2010
Posts: 14
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

Joined: Apr 22, 2011
Posts: 18


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


http://www.java-connect.com
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2503
    
    8

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



OCUP UML fundamental and ITIL foundation
youtube channel
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30764
    
156

Mahendra: Welcome to CodeRanch!

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


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
David Spurgeon
Greenhorn

Joined: Oct 20, 2010
Posts: 14
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

Joined: Oct 20, 2010
Posts: 14
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

Joined: Dec 20, 2006
Posts: 2503
    
    8

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

    Joined: Mar 01, 2009
    Posts: 1775
        
      14

    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.


    No more Blub for me, thank you, Vicar.
    David Spurgeon
    Greenhorn

    Joined: Oct 20, 2010
    Posts: 14
    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

    Joined: Mar 01, 2009
    Posts: 1775
        
      14

    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.
     
    I agree. Here's the link: http://aspose.com/file-tools
     
    subject: SQL formatting question