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

snajalmAsked:
Who is Participating?
 
CEHJConnect With a Mentor Commented:
Is 'status' a char field? I suspect one of your types is wrong

You would be much better off using PreparedStatement btw

0
 
RinilConnect With a Mentor Commented:
hi,

try to print  the string insertString where the query gets formed and see if it works perfectly in database query, the query might be forming with incorrect syntax in that string,

so you can confirm if that string formed is correct if you try to execute in that database you are using,
by executing the query manually in that database.
0
 
snajalmAuthor Commented:
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...
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
vignes_waranCommented:
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
0
 
snajalmAuthor Commented:
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.  
0
 
RinilCommented:
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.
0
 
CEHJCommented:
You can test Rinil's hypothesis by putting a temporary substring(0, 32) (or some such) on the string params
0
 
snajalmAuthor Commented:
Thanks guys!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.