Results 1 to 4 of 4
  1. #1
    Cyrus is offline Member
    Join Date
    Mar 2014
    Posts
    18
    Rep Power
    0

    Default MySQL PreparedStatement problem

    I am having a problem with a PreparedStatement for a MySQL query, where the query seems to get cut off. I can't really post a self contained example, because it involves an SQL database. I think there must just be a typo in my code but I can't see it.

    The part of the code with the problem...
    Java Code:
    public void insertEntry(
    			Hashtable<String, String> strings,
    			Hashtable<String, Integer> integers,
    			Date created, Date paid, boolean enabled)
    			throws ClassNotFoundException, SQLException {
    		Class.forName("com.mysql.jdbc.Driver");
    		
    		String dburl = "jdbc:mysql://" + dbHost + "/" + dbName +
    						"?user=" + dbUser + "&password=" + dbPass;
    		
    		connect = DriverManager.getConnection(dburl);
    		
    		ps = connect.prepareStatement("INSERT INTO " + dbName + ".users INSERT " +
    				"enabled=?, username=?, created=?, paid=?, alias=?, password=?, " +
    				"email=?, bitmessage=?, torchat=?, reputation=?," +
    				"privacy=?, fpmport=?, fpm-template=? ;");
    		
    		java.sql.Date SQLcreated = new java.sql.Date(created.getTime());
    		java.sql.Date SQLpaid = new java.sql.Date(paid.getTime());
    		System.out.println("Debug: SQLpaid = " + SQLpaid.toString());
    		
    		ps.setBoolean(1, enabled);
    		ps.setString(2, strings.get("username"));
    		ps.setDate(3, SQLcreated);
    		ps.setDate(4, SQLpaid);
    		ps.setString(5, strings.get("alias"));
    		ps.setString(6, strings.get("password"));
    		ps.setString(7, strings.get("email"));
    		ps.setString(8, strings.get("bitmessage"));
    		ps.setString(9, strings.get("torchat"));
    		ps.setInt(10, integers.get("reputation"));
    		ps.setInt(11, integers.get("privacy"));
    		ps.setInt(12, integers.get("fpmport"));
    		ps.setString(13, strings.get("fpm-template"));
    		ps.executeUpdate();
    		
    		ps.close();
    		connect.close();
    		resultSet.close();
    	}
    The output of the whole program, though all it has done so far is create some default values and attempt to insert them with the above code, where the problem occurs.

    Debug: SQLpaid = 1990-03-21
    com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorEx ception: 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 enabled=0, username='default_username', created='2000-03-21', paid='1990-' at line 1
    at sun.reflect.NativeConstructorAccessorImpl.newInsta nce0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInsta nce(NativeConstructorAccessorImpl.java:57)
    at sun.reflect.DelegatingConstructorAccessorImpl.newI nstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Construc tor.java:532)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:41 1)
    at com.mysql.jdbc.Util.getInstance(Util.java:386)
    at com.mysql.jdbc.SQLError.createSQLException(SQLErro r.java:1054)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.ja va:4237)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.ja va:4169)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:26 17)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java :2778)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionIm pl.java:2834)
    at com.mysql.jdbc.PreparedStatement.executeInternal(P reparedStatement.java:2156)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(Pre paredStatement.java:2441)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(Pre paredStatement.java:2366)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(Pre paredStatement.java:2350)
    at database.Users.insertEntry(Users.java:297)
    at test.dbUsers.main(dbUsers.java:95)
    Last edited by Cyrus; 03-21-2014 at 01:05 AM.

  2. #2
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    12,009
    Rep Power
    20

    Default Re: MySQL PreparedStatement problem

    Not sure what INSERT syntax you're going for there.

    The general insert is:
    INSERT INTO tbl_name () VALUES();
    Where you list the columns to insert into in the first set of brackets, and the values (or '?' since you're using a PreparedStatement) in the second set of brackets. Comma-separated.
    Please do not ask for code as refusal often offends.

    ** This space for rent **

  3. #3
    gimbal2 is offline Just a guy
    Join Date
    Jun 2013
    Location
    Netherlands
    Posts
    4,016
    Rep Power
    6

    Default Re: MySQL PreparedStatement problem

    Seems like an update statement translated almost directly to an insert statement.
    "Syntactic sugar causes cancer of the semicolon." -- Alan Perlis

  4. #4
    jashburn is offline Senior Member
    Join Date
    Feb 2014
    Posts
    219
    Rep Power
    1

    Default Re: MySQL PreparedStatement problem

    Quote Originally Posted by Cyrus View Post
    com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorEx ception: 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 enabled=0, username='default_username', created='2000-03-21', paid='1990-' at line 1
    The exception is, to an extent, self-explanatory. It states that the SQL Insert statement has a syntax error. The bit in the error message that is confusing is:

    ... near 'INSERT enabled=0, username='default_username', created='2000-03-21', paid='1990-' at line 1

    The value for the paid column is to be quoted. However in the construction of the exception message, the MySQL JDBC driver also quotes the part of the SQL statement where it found the syntax error. The general form of the error message looks like this:

    ... near 'erroneous_part_of_the_SQL_statement' at line some_line

    Notice the opening and closing quotes that wrap erroneous_part_of_the_SQL_statement?

    In other words, it is not that the value for the paid column is truncated when the Insert statement is being executed, the problem is with the syntax of the Insert statement itself:

    Quote Originally Posted by Cyrus View Post
    Java Code:
    		ps = connect.prepareStatement("INSERT INTO " + dbName + ".users INSERT " +
    				"enabled=?, username=?, created=?, paid=?, alias=?, password=?, " +
    				"email=?, bitmessage=?, torchat=?, reputation=?," +
    				"privacy=?, fpmport=?, fpm-template=? ;");
    To make this easier to read, it looks like this, with the erroneous part in bold:

    INSERT INTO dbName.users INSERT enabled=value, username=value, ...;

    The usual SQL Insert syntax, as Tolls pointed out, is INSERT INTO tbl_name () VALUES();. However MySQL has a variant that (some opine) is easier to read. See MySQL :: MySQL 5.7 Reference Manual :: 13.2.5 INSERT Syntax and look for the 2nd Insert form, which is
    Java Code:
    INSERT ...
        SET col_name={expr | DEFAULT}, ...
    See also CS 601 - MySQL INSERT ... SET syntax for some examples.

    Long story short, the syntax error is in the second 'INSERT' in your SQL statement, where you should be using 'SET'. Imo, it'll be better to stick with the usual Insert syntax that Tolls recommended as MySQL's variant is not well supported by other databases.

Similar Threads

  1. Replies: 2
    Last Post: 01-21-2012, 05:34 PM
  2. problem with PreparedStatement
    By Yuvarajsinh in forum JDBC
    Replies: 4
    Last Post: 10-03-2010, 09:14 AM
  3. MySQL problem
    By indra-uolles in forum New To Java
    Replies: 14
    Last Post: 08-07-2010, 08:10 AM
  4. Problem with PreparedStatement?
    By ZeCute in forum New To Java
    Replies: 6
    Last Post: 06-13-2010, 12:50 PM
  5. Replies: 0
    Last Post: 04-22-2008, 11:57 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •