Results 1 to 13 of 13
  1. #1
    Jack_Tauson_Sr is offline Senior Member
    Join Date
    Nov 2011
    Posts
    114
    Rep Power
    0

    Default Unable to figure out the MySQL Syntax error root cause

    I am getting the following error/exception:

    Java Code:
    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 '' at line 1
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
        at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
        at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
        at com.mysql.jdbc.Util.getInstance(Util.java:386)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1054)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4237)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4169)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2617)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2778)
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2825)
        at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2156)
        at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2459)
        at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2376)
        at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2360)
        at ebmextractor.ResultsReader.call(ResultsReader.java:347)
        at ebmextractor.ResultsReader.call(ResultsReader.java:38)
        at javafx.concurrent.Task$TaskCallable.call(Task.java:1259)
        at java.util.concurrent.FutureTask.run(FutureTask.java:262)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
        at java.lang.Thread.run(Thread.java:744)

    And the line 347 at
    Java Code:
    ebmextractor.ResultsReader.call(ResultsReader.java:347)
    mentioned above is line 218 in the following code.


    Please refer to the following code for line 218:

    Am I missing something while using Prepared Statement?


    Dump of SQL is as follows:



    Java Code:
     Checking Prepared Statement:com.mysql.jdbc.JDBC4PreparedStatement@1626ac0: UPDATE test.selectiontable SET DTSStatusType_ti = 3,Queued_DialerIP_vch = '11.11.1.111' WHERE DTSId_int IN ( 98001, 98002, 98003, 98004, 98005, 98006, 98007, 98008, 98009, 98010, 98635, 98636, 98637, 98638, 98639, 98640, 98641, 98642, 98643, 98644, 98645, 98646, 98647, 98648, 98649, 98650, 98651, 98652, 98653, 98654, 98655, 98656, 98657, 98658, 98659, 98660, 98661, 98662, 98663, 98664, 98665, 98666, 98667, 98668, 98669, 98670, 98671, 98672, 98673, 99995, 99996, 99997, 99998, 99999, 100000)

    Should I add some else condition for paramCount ? Please advise as the above dump seems to be correct.


    For Quick reference, I am pasting my prepared Statement Code below:

    Java Code:
    StringBuilder sqlSelect = new StringBuilder(1024);
    sqlSelect.append("UPDATE test.selectiontable ");
    sqlSelect.append("SET DTSStatusType_ti = 3,");
    sqlSelect.append("Queued_DialerIP_vch = ? ");
    sqlSelect.append("WHERE DTSId_int IN ( ");
    int paramCount = ids.size();
     
    int j = 0;
     
    if(paramCount > 0)
    {
    for(j=0; j < paramCount; j++ )
    {
    sqlSelect.append( j > 0 ? ", ?" : "?" );
     
    }// for each param
    sqlSelect.append( ")");
     
     
    // make the prepare statement (pst) with the above sql string
    PreparedStatement pst = connRemote.prepareStatement( sqlSelect.toString() );
     
    //now set the parameter values in the query
    int paramIndex = 1;
     
    pst.setString(paramIndex++, ip);
     
    if( paramCount > 0)
    {
    for( j = 0; j < paramCount; j++)
    {
    pst.setLong(paramIndex++,((Integer)ids.get(j)).intValue());
     
    }// for each param
     
    }// if ids list is not empty
     
    pst.executeUpdate();
    connRemote.commit();
    System.out.println("Checking Prepared Statement:"+pst);
    System.out.println("Remote IP Just Completed is:" +ip);
    Last edited by Jack_Tauson_Sr; 04-10-2014 at 04:11 AM.

  2. #2
    kneitzel is offline Senior Member
    Join Date
    Feb 2014
    Posts
    447
    Rep Power
    1

    Default Re: Unable to figure out the MySQL Syntax error root cause

    Hi,

    your SQL Query seems to be cut after the WHERE DTSId.

    Please make sure, that you give us the full sql query that you try to execute.

    And I would prefer if you post the code on the forum and not at a different site! Your code that builds the sql query needs some optimisation:
    - If paramcout is not > 0, then you end with some invalid SQL query
    - You check paramcout a second time inside the first if-block. it wasn't changed so is is just code that is not needed.

    Konrad

  3. #3
    Tolls is online now Moderator
    Join Date
    Apr 2009
    Posts
    12,016
    Rep Power
    20

    Default Re: Unable to figure out the MySQL Syntax error root cause

    Quote Originally Posted by Jack_Tauson_Sr View Post
    Checking Prepared Statement:com.mysql.jdbc.JDBC4PreparedStatement@16 26ac0:
    UPDATE test.selectiontable SET DTSStatusType_ti = 3,Queued_DialerIP_vch = '11.11.1.111' WHERE DTSId_int IN ( 98001, 98002, 98003, 98004, 98005, 98006, 98007, 98008, 98009, 98010, 98635, 98636, 98637, 98638, 98639, 98640, 98641, 98642, 98643, 98644, 98645, 98646, 98647, 98648, 98649, 98650, 98651, 98652, 98653, 98654, 98655, 98656, 98657, 98658, 98659, 98660, 98661, 98662, 98663, 98664, 98665, 98666, 98667, 98668, 98669, 98670, 98671, 98672, 98673, 99995, 99996, 99997, 99998, 99999, 100000)
    What happens when you run that query against the database?

    And can you post the PreparedStatement code here. Code on other website's may not be visible behind corporate firewalls.
    Please do not ask for code as refusal often offends.

    ** This space for rent **

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

    Default Re: Unable to figure out the MySQL Syntax error root cause

    Quote Originally Posted by kneitzel View Post
    your SQL Query seems to be cut after the WHERE DTSId.
    It isn't, that's just the CSS formatting of this site not allowing a horizontal scroll bar. If you see the HTML source of this page you'll see the whole thing, it's not cut and looks valid. Its just a long list of IDs.
    "Syntactic sugar causes cancer of the semicolon." -- Alan Perlis

  5. #5
    kneitzel is offline Senior Member
    Join Date
    Feb 2014
    Posts
    447
    Rep Power
    1

    Default Re: Unable to figure out the MySQL Syntax error root cause

    Thank you gimbal2.

    I was missing that. Was wondering already where Tolls got all that information from :)

    Marking the line and using copy & paste seems to work fine, too.

    Sorry that I missed that.

    With kind regards,

    Konrad

  6. #6
    Jack_Tauson_Sr is offline Senior Member
    Join Date
    Nov 2011
    Posts
    114
    Rep Power
    0

    Default Re: Unable to figure out the MySQL Syntax error root cause

    I don't get any error when I run the query on MySQL Workbench against the database:

    Here is what I got:
    1000 row(s) affected Rows matched: 1000 Changed: 1000 Warnings: 0 0.078 sec
    I have also updated the code in my question. Please take a look at it. Thanks.

    Quote Originally Posted by Tolls View Post
    What happens when you run that query against the database?

    And can you post the PreparedStatement code here. Code on other website's may not be visible behind corporate firewalls.
    Last edited by Jack_Tauson_Sr; 04-10-2014 at 04:12 AM.

  7. #7
    Tolls is online now Moderator
    Join Date
    Apr 2009
    Posts
    12,016
    Rep Power
    20

    Default Re: Unable to figure out the MySQL Syntax error root cause

    Does this fail every time?
    Or is it only sometimes?

    What version of MySQL is this being run against?
    What version of the connector are you using?

    I'm running out of things to ask that might shed some light on this...
    Please do not ask for code as refusal often offends.

    ** This space for rent **

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

    Default Re: Unable to figure out the MySQL Syntax error root cause

    do I spot that your param index starts counting at 0? I think that should start counting at 1. I'm not 100% sure but as far as I know everything related to SQL classes is 1-based.

    EDIT: nope, I just need more coffee. paramCount != paramIndex.
    "Syntactic sugar causes cancer of the semicolon." -- Alan Perlis

  9. #9
    Jack_Tauson_Sr is offline Senior Member
    Join Date
    Nov 2011
    Posts
    114
    Rep Power
    0

    Default Re: Unable to figure out the MySQL Syntax error root cause

    Okay, let me try changing it and starting from 1 instead of 0. Just read your coffee related comment. Won't try making any changes.

    Quote Originally Posted by gimbal2 View Post
    do I spot that your param index starts counting at 0? I think that should start counting at 1. I'm not 100% sure but as far as I know everything related to SQL classes is 1-based.

    EDIT: nope, I just need more coffee. paramCount != paramIndex.
    Last edited by Jack_Tauson_Sr; 04-17-2014 at 07:59 AM.

  10. #10
    Jack_Tauson_Sr is offline Senior Member
    Join Date
    Nov 2011
    Posts
    114
    Rep Power
    0

    Default Re: Unable to figure out the MySQL Syntax error root cause

    It fails only when it selects the last set of records based on LIMIT condition. Basically, it does it's job, completes everything but in the end it leaves the error which I don't want to see.

    Here are answers to your questions:

    1) Version: MySQL 5.5

    2) Connector mysql-connector-java-5.1.27-bin

    Please let me know if you have more questions. Thanks

    Quote Originally Posted by Tolls View Post
    Does this fail every time?
    Or is it only sometimes?

    What version of MySQL is this being run against?
    What version of the connector are you using?

    I'm running out of things to ask that might shed some light on this...

  11. #11
    Tolls is online now Moderator
    Join Date
    Apr 2009
    Posts
    12,016
    Rep Power
    20

    Default Re: Unable to figure out the MySQL Syntax error root cause

    What LIMIT condition?
    There's no LIMIT in that SQL.
    Please do not ask for code as refusal often offends.

    ** This space for rent **

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

    Default Re: Unable to figure out the MySQL Syntax error root cause

    Quote Originally Posted by Jack_Tauson_Sr View Post
    Okay, let me try changing it and starting from 1 instead of 0.
    No don't, it will be wrong. JDBC is 1-based.
    "Syntactic sugar causes cancer of the semicolon." -- Alan Perlis

  13. #13
    Jack_Tauson_Sr is offline Senior Member
    Join Date
    Nov 2011
    Posts
    114
    Rep Power
    0

    Default Re: Unable to figure out the MySQL Syntax error root cause

    You can ignore that, it's not used in my update statement
    Quote Originally Posted by Tolls View Post
    What LIMIT condition?
    There's no LIMIT in that SQL.

Similar Threads

  1. Replies: 2
    Last Post: 03-31-2014, 03:32 PM
  2. Replies: 2
    Last Post: 08-31-2009, 02:50 PM
  3. Java JDBC/MySQL appropriate Syntax
    By thelinuxguy in forum Advanced Java
    Replies: 7
    Last Post: 02-10-2009, 07:57 PM
  4. Unable to figure out this problem! jndi lookup exception.....
    By vrk in forum Enterprise JavaBeans (EJB)
    Replies: 0
    Last Post: 01-19-2009, 11:38 AM
  5. unable to update MYSQL with values from jsp page
    By koushika in forum JavaServer Pages (JSP) and JSTL
    Replies: 0
    Last Post: 03-27-2008, 01:35 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
  •