DB2 Syntax Error in Java, Works Fine in SQL editor

Question

I have the following java code to create a prepared statement from the passed in connection. This is only a portion of the code and I've changed the name of the table for anonymity.

private static final String preparedStatementSQL = "INSERT INTO TABLE (STORE_ID,"
        + "BRAND_NAME,BUSINESS_DATE,HOUR,FCST_SYSSLS_AMT,FCST_USERSLS_AMT,FCST_SYSTRN_CNT,"
        + "FCST_USERTRN_CNT,ACTION_TIMESTAMP) VALUES (?,?,?,?,?,?,?,?,(current timestamp))";

private PreparedStatement ps = null;

public TABLE(Connection con) throws SQLException{
    this.ps = con.prepareStatement(preparedStatementSQL);
}

When it runs the following line:

this.ps = con.prepareStatement(preparedStatementSQL);

I get the following SQL error:

java.sql.SQLSyntaxErrorException: Syntax error: Encountered "HOUR" at line 1, column 67.

I copied the statement into my SQL editor (SQuirreL) and put in some made up values, and it worked fine (no syntax error!).

It is an IBM DB2 database.

I tried deleting the column names to just do:

INSERT INTO TABLE VALUES (?,?,?,?,?,?,?,?,(current timestamp))

And it fixed the problem, but I have to have it use the (COLUMNS) VALUES (?... ) format.

Anyone have any ideas?

HOUR is probably a reserved word, try \"HOUR\" inside your your String constant. And btw: current_timestamp doesn't need paranthesises. — user330315
– user330315, Commented Jul 17, 2013 at 21:04

Gordon Linoff · Accepted Answer · 2013-07-17 21:09:43Z

1

HOUR is a reserved word in DB2. The list is here.

You should put double quotes (") around it in the column list.

TABLE is also a reserved word. I assume that you have a real table name there, though.

answered Jul 17, 2013 at 21:09

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Andrew Mairose Over a year ago

I tried that before and thought it didn't work because I got the same error. I realized now that when I put the quotes in there, the exception is getting thrown later in my test cases when I use dbunit to compare my output to a database snapshot I have in an xml file. Looks like dbunit is trying to do the same insert that I was to insert the rows in my xml file into a second in-memory database for comparison, and is running into the same error. Don't think this is going to be quite as easy of a fix...

Collectives™ on Stack Overflow

DB2 Syntax Error in Java, Works Fine in SQL editor

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related