asked on

java.sql.SQLSyntaxErrorException: Syntax error: Encountered "80" at line 1, column 1100.

I get the exception in the question title when I try to insert like the following! Any idea what this could mean??!

String insertString = "insert into queries (data_id, query, "                 
+ "query_name, query_file_name, status) values(" + currentDataID + ", '" + params[1] + "', '" + params[2] + "', '" + params[3] + "', '" + params[4] + "')"; 

try {      
    Statement stmt = dbconn.createStatement();      
    stmt.execute(insertString, Statement.RETURN_GENERATED_KEYS);      
    ResultSet rs = stmt.getGeneratedKeys();        
    if (rs != null && rs.next()){                 
         currentDataID = (int) rs.getLong(1);       
    }  
} catch (SQLException ex) { 
}

Open in new window

ASKER CERTIFIED SOLUTION

CEHJ

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Rinil

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

snajalm

ASKER

I can see now that I have a lot of "'" characters in the query that is being inserted. I tried using PreparedStatement but for some reason couldn't implement it properly. Could you please let me know how you would alter this to user PreparedStatement rather. I believe that PreparedStatement should take care of those illegal characters to make them insertable into the table...

vignes_waran

Hi,

Just print the sql query and paste here so we can help you quickly whats wrong with the query.

String insertString = "insert into queries (data_id, query, "
+ "query_name, query_file_name, status) values(" + currentDataID + ", '" + params[1] + "', '" + params[2] + "', '" + params[3] + "', '" + params[4] + "')";

System.out.println(insertString );

try {
Statement stmt = dbconn.createStatement();
stmt.execute(insertString, Statement.RETURN_GENERATED_KEYS);
ResultSet rs = stmt.getGeneratedKeys();
if (rs != null && rs.next()){
currentDataID = (int) rs.getLong(1);
}
} catch (SQLException ex) {
}

Thank you

snajalm

ASKER

I managed to use PreparedStatement in my code and got rid of this issue, however, I'm getting a new exception which is

java.sql.SQLDataException: A truncation error was encountered trying to shrink VARCHAR 'SELECT s . . .

Open in new window

Even though my field is set up to be LONG VARCHAR I get this error with an entry of 1200 characters.

Rinil

It is trying to insert the values which is more than the lenght of varchar field.

can you try reducing the length of the fields retrieved either in the query or just increase the field length to hold the same.

CEHJ

You can test Rinil's hypothesis by putting a temporary substring(0, 32) (or some such) on the string params

snajalm

ASKER

Thanks guys!

java.sql.SQLSyntaxErrorException: Syntax error: Encountered &quot;80&quot; at line 1, column 1100.

java.sql.SQLSyntaxErrorException: Syntax error: Encountered "80" at line 1, column 1100.