CODEWITHSUNDEEP
javajavadb
Sam
Sam

Reputation: 2792

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

I get the following exception,

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

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) {
}

Table definition,

CREATE TABLE queries (query_id INT not null primary key GENERATED "
                + "ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), data_id "
                + "INTEGER not null, foreign key (data_id) references data "
                + "(data_id), query LONG VARCHAR, query_name VARCHAR(150), "
                + "query_file_name VARCHAR(150),status VARCHAR(20))

Upvotes: 0

Views: 27428

Answers (2)

Philipp Reichart
Philipp Reichart

Reputation: 20961

Try this approach with a prepared statement:

String insert = "INSERT INTO queries (data_id, query, query_name," +
        " query_file_name, status) VALUES (?,?,?,?,?)";

PreparedStatement stmt = dbconn.prepareStatement(insert, Statement.RETURN_GENERATED_KEYS);
// Why do you set this if you want the DB to generate it?
stmt.setInt(1, currentDataID); // or setLong() depending on data type
stmt.setString(2, params[1]); // I assume params is a String[]
stmt.setString(3, params[2]);
stmt.setString(4, params[3]);
stmt.setString(5, params[4]);
stmt.execute();

ResultSet rs = stmt.getGeneratedKeys();
if (rs.next()) {
    // if it's an int, avoid the cast and use rs.getInt(1) instead
    currentDataID = (int) rs.getLong(1);
}

I don't understand why you set the data_id while the later code expects the database to generate it... Your table definition would help.

Upvotes: 5

Simon Nickerson
Simon Nickerson

Reputation: 43177

This is probably happening because one of your params[] contains a quote character.

This problem is exactly why you shouldn't create SQL expressions like this, but should instead use a PreparedStatement. Please read up on SQL injection.

Upvotes: 4

Related Questions