2

I am connecting to a DB2 database (DB2 v9.7.400.501) from my Java web application using the IBM DB2 Type 4 driver (db2jcc4.jar). When I try to execute an SQL statement like this,

SELECT * FROM USERS WHERE UPPER(USERNAME) = UPPER('testuser');

I get the following exception:

com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=;;= UPPER('testuser');END-OF-STATEMENT, DRIVER=4.12.55

The problem is from the UPPER function since, a normal select statement executes normally.

Arion
  • 31,011
  • 10
  • 70
  • 88
duncanportelli
  • 3,161
  • 8
  • 38
  • 59
  • "END-OF-STATEMENT" would perhaps infer that however you're running it is interpretting the first single quote as the end of the string? You could try replacing the single quotes with two single quotes to test if it's that. – Bridge Apr 02 '12 at 08:24
  • @Bridge Now i get a different exception: `com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=testuser;ER(LOGIN) = UPPER('';CONCAT, DRIVER=4.12.55` – duncanportelli Apr 02 '12 at 08:35
  • Looks like my guess was wrong. – Bridge Apr 02 '12 at 08:57
  • Have you tried the same statement with an sql tool (preferably via jdbc with the same driver)? If yes, does it produce the same error? – Andreas Apr 02 '12 at 09:02
  • Yes, I connected to the same database with the same driver from the Eclipse Database Tool and executed the same statement. It worked perfectly – duncanportelli Apr 02 '12 at 09:20
  • Maybe you could leave out the semicolon. If the error lies in the jdbc driver thinking there is a second statement consisting of an empty string like @Alex Stybaev pointed out. If you take his approach, maybe you could leave out the upper function, because in most db's like is per default case insensitive. – Andreas Apr 02 '12 at 09:26
  • Thanks @Andreas. I left the semicolon out and it worked. I think that the error lies in the jdbc driver. Now I am going to use PreparedStatements like Alex pointed out – duncanportelli Apr 02 '12 at 10:07

1 Answers1

3

Maybe you should use is this way:

SELECT * FROM USERS WHERE UPPER(USERNAME) LIKE UPPER('testuser');

Your code with '=' is seems ok for SQLite but don't know anbout db2.

UPD. After some investigation, I can say that error is cause by Java code which tries to execute multiple statements in one query using ';' as a delimiter. You should try using the PreparedStatement, addBatch() and executeBatch() for multiple statements.

UPD2. This is DB2 related issue. PostgreSQL, afaik, allows multiple statements in single query.

Alex Stybaev
  • 4,623
  • 3
  • 30
  • 44
  • No, it doesn't make any difference. I got the following exception: `com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=;;KE UPPER('testuser');END-OF-STATEMENT, DRIVER=4.12.55` – duncanportelli Apr 02 '12 at 08:49