SQL - UPPER function in DB2 not working

2
votes

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.

1
javasqldb2jcc
"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
@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
Looks like my guess was wrong. – Bridge
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
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

1 Answers

3
votes

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.