[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1294
  • Last Modified:

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

0
snajalm
Asked:
snajalm
  • 3
  • 2
  • 2
  • +1
2 Solutions
 
CEHJCommented:
Is 'status' a char field? I suspect one of your types is wrong

You would be much better off using PreparedStatement btw

0
 
RinilCommented:
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now