SQL - UPPER function in DB2 not working

java sql db2 jcc
duncanportelli picture duncanportelli · Apr 2, 2012 · Viewed 8.9k times · Source

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.

Answer

Alex Stybaev picture Alex Stybaev · Apr 2, 2012

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.

Related questions

What can SQL CODE -104 (error) represent?

I am executing an SQL query via jcc to run a report. When I opened the error log file for the program and examined the SQL query, everything seems to be fine (There are no extra or missing brackets, commas, …

Read More
How do you get values from all columns using ResultSet.getBinaryStream() in jdbc?

How do I to write an entire table to a flat file (text file) using jdbc? So far I've attempted the following: Statement statement = connection.createStatement(); ResultSet result = statement.executeQuery("SELECT * FROM tablename"); BufferedInputStream buffer; FileOutputStream out = new FileOutputStream("flatfile.…

Read More
ERRORCODE=-4461, SQLSTATE=42815 in DB2

I have exported some data from schema A (table x)to XML and I am reading the XML and inserting the data into schema B(table y). while inserting the data after 20000 records it says com.ibm.db2.jcc.am.…

Read More