Sql – DB2 SQL error sqlcode=-104 sqlstate=42601

db2sql

I am relatively new to DB2 but not to SQL. I am experiencing the below error when trying a SELECT statement while subtracting a few minutes from a timestamp:

SELECT * TCCAWZTXD.TCC_COIL_DEMODATA 
WHERE CURRENT_INSERTTIME  BETWEEN(CURRENT_TIMESTAMP)-5 minutes AND CURRENT_TIMESTAMP

The error that I am experiencing is:

DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601,
SQLERRMC=TCCAWZTXD;SELECT*;, DRIVER=3.66.46

How can I fix this error?

Best Answer

You miss the from clause

SELECT *  from TCCAWZTXD.TCC_COIL_DEMODATA WHERE CURRENT_INSERTTIME  BETWEEN(CURRENT_TIMESTAMP)-5 minutes AND CURRENT_TIMESTAMP

Related Solutions

Java – SQL – UPPER function in DB2 not working

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.

DB2 SQL Error: SQLCODE=-514, SQLSTATE=26501

Here's a link to the DB2 error codes, so you can look up your next error yourself.

DB2 thinks that your select is using a cursor. Try

select count(*) from TableName where col1 = 9 and col2 = ?

and see if the error goes away.

Here's the full explanation of the error from the IBM DB2 Error code manual.

-514

THE CURSOR cursor-name IS NOT IN A PREPARED STATE

Explanation

The application program has tried to use a cursor, 'cursor-name,' that is not in a prepared state. The cursor is associated with a statement that:

Was never prepared.
Was invalidated by a commit or rollback operations

System action

The statement cannot be processed. Programmer response

For case 1, ensure that you prepare the statement that is named in the DECLARE CURSOR statement for 'cursor-name' before you try to open the cursor.

For case 2, take one of the following actions:

Use the WITH HOLD option of DECLARE CURSOR.
Do not execute a commit or rollback operation until you are finished using the cursor.
Prepare the statement again after the commit or rollback.