JDBC problem for INSERT INTO...SELECT statement

desmondtanck · #1 (**permalink**) 04-23-07, 23:05

When i tried to execute the exact statement in the DB2 Control Center GUI
it works perfectly, but when i use it from JAVA, it prompts error.
com.ibm.db2.jcc.c.SqlException: DB2 SQL error: SQLCODE: -418, SQLSTATE: 42610, SQLERRMC: null

below is the similar statement:
Insert into
table1(uid,username,createby,register_date)
Select
a.uid,
b.username,
'admin',
current_timestamp
from
table2 as a,
table3 as b
where
a.uid= 102
and
a.uid=b.uid

grofaty · #2 (**permalink**) 04-24-07, 01:51

Hi,
are you sure this is working in DB2 Control Center?

Insert into
table1(uid,username,createby,register_date)
Select
a.uid,
b.username,
'admin',
current_timestamp
from
table2 as a,
table3 as b, <-- use without comma!
where
a.uid= 102
and
a.uid=b.uid

Hope this helps,
Grofaty

desmondtanck · #3 (**permalink**) 04-24-07, 01:57

actually the statement is simplified from my original statement,
so you can avoid the comma,
it really works in the control center.

Peter.Vanroose · #4 (**permalink**) 04-24-07, 02:22

Quote:

Originally Posted by desmondtanck

actually the statement is simplified from my original statement, so you can avoid the comma, it really works in the control center.

Then try avoiding the comma in Java, too.

desmondtanck · #5 (**permalink**) 04-24-07, 04:16

actually that is just a spelling mistake when i post this title
there is nothing to do with my original statement,
i mean it really can be executed at control center

but it somehow throw exception when i use it at Java.

grofaty · #6 (**permalink**) 04-24-07, 06:41

Hi,
have you checked the error message documentation?

Code:

SQL0418N A statement contains a use of a parameter marker that
          is not valid.

Explanation:

Untyped parameter markers cannot be used:

o   in a SELECT list

o   as the sole argument of a datetime arithmetic operation

o   in some cases as the sole argument of a scalar function

o   as a sort key in an ORDER BY clause



 Parameter markers can never be used:

o   in a statement that is not a prepared statement

o   in the fullselect of a CREATE VIEW statement

o   in the triggered action of a CREATE TRIGGER statement

o   in a query captured by DB2 Query Patroller



 The statement cannot be processed.

User Response:

Correct the syntax of the statement.  If untyped parameter
markers are not allowed, use the CAST specification to give the
parameter marker a data type.

 sqlcode :  -418

 sqlstate :  42610

stolze · #7 (**permalink**) 04-24-07, 07:53

Did I get this right that you post a statement here that has nothing to do with your actual query? And the actual query fails in your application but supposedly works in CC?

Does the statement you posted here work in CC and fail in your application? If the answer is no (same behavior in both cases), then it is completely useless here...

desmondtanck · #8 (**permalink**) 04-24-07, 23:26

Thanks for your replies,

yes, it has nothing to do with my actual query as i cant post the real query here, just to tell that the real query is similar to the one i've posted.

What i want to make sure is that, is it possible that some query can be executed in CC and will fail when i execute using my application?

grofaty · #9 (**permalink**) 04-25-07, 00:46

Hi,
it shouldn't be working like that. Query is query. Are you sure your JDBC connection is working properly? What is your operating system name and version? What is you db2 edition and version (commands: db2level , db2licm -l)?

Try using db2ca from command shell and test your JDBC connection. Is it working properly?

Are you 100% sure the same query is working on CC and JDBC?

Hope this helps,
Grofaty

desmondtanck · #10 (**permalink**) 04-25-07, 02:40

yes, my jdbc can work perfectly with other statement,
i can even successfully login to my system using db2.
until i'm trying to do some INSERT INTO...SELECT..statement, it fails.

i'm using DB2 Express Edition 9.1, the db2 is stored at Linux, and i
have my application at Window 2000.

the jdbc works with other query at my application, so that means that
it works correctly right?

desmondtanck · #11 (**permalink**) 04-25-07, 02:57

I've found out a weird thing here,
i tried to remove the WHERE clause from my actual query,
it works fine.
For example,
insert into table1(username,createdate) select username, current_timestamp
from table2

The example above works fine in my application, but when i add the WHERE clause, it fails....
insert into table1(username,createdate) select username, current_timestamp
from table2 where username='tanck'

it is the WHERE clause make the query fails, any suggestion?

Thanks,
Desmond

stolze · #12 (**permalink**) 04-25-07, 04:53

Quote:

Originally Posted by desmondtanck

What i want to make sure is that, is it possible that some query can be executed in CC and will fail when i execute using my application?

No, that (usually) not possible.

CC uses JDBC to talk to DB2 - as does your application. So you have the same environment. Therefore, I believe you some other differences between your app and JDBC.

The real problem is that you didn't show us a query that works in one case and fails in the other. You should find such a query (which you can post here), first.

What I recommend right now is that you collect a JDBC trace to see what is really going on in the communication with DB2. There you may see something...

desmondtanck · #13 (**permalink**) 04-25-07, 05:12

Statement 1:
insert into table1(username,access_time) select username, current_timestamp from table2

Statement 1 works fine in my application and CC.

Statement 2:
insert into table1(username,access_time) select username, current_timestamp from table2 where uid=102

Statement 2 fails at my application but works nicely at CC. where uid is the auto increment primary key of table2.