| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| SQL Exception in DB insertion Query in WMB |
« View previous topic :: View next topic » |
| Author |
Message
|
| SDS |
 Posted: Wed Feb 29, 2012 12:55 am Post subject: SQL Exception in DB insertion Query in WMB |
 |
|
Apprentice
Joined: 13 Jun 2011
Posts: 47
|
Hi
I am getting an error while executing the following SQL query in Java Compute Node to insert data into a Oracle DB table thru JDBC.
| Code: |
| ResultSet rs1 = stmt.executeQuery("INSERT INTO LKUP_ID_TRN(ID_TRN, AI_TRN, ID_BSN_UN, ID_WS, DC_DY_BSN, TY_TRN, ID_SRC_TRN, LU_SRC_LD, LU_SRC_STRG, QU_TRN_CRT_ATMPT, NM_CRT, TS_CRT, NM_UPDT_LS, TS_UPDT_LS, ID_TRN_CTL) VALUES(666, 1253, 0210, 0052, CURRENT_TIMESTAMP, 'ID', 1234, '00', 'RT', 0, '99', CURRENT_TIMESTAMP, '99', CURRENT_TIMESTAMP,1)"); |
Error:
ora-00984 column not allowed here in WMB
The query executes successfully in SQL Plus but giving this SQL exception is JCN.
Any help will be highly appreciated. |
|
| Back to top |
|
 |
| adubya |
| Posted: Wed Feb 29, 2012 1:23 am Post subject: |
|
|
Partisan
Joined: 25 Aug 2011
Posts: 377
Location: GU12, UK
|
I'd guess it was the CURRENT_TIMESTAMP which is causing the problem. Are the timestamp columns nullable ? If so try removing the timestamp columns from the INSERT (or replace CURRENT_TIMESTAMP with NULL) and see if that works. That will isolate the issue to the timestamp.
I'd use a prepared statement also rather than a hardcoded full SQL string like you have there.
And I see your DBA likes concise column names! |
|
| Back to top |
|
|
| SDS |
| Posted: Wed Feb 29, 2012 1:45 am Post subject: |
|
|
Apprentice
Joined: 13 Jun 2011
Posts: 47
|
The TIMESTAMP columns are not NULLable as it is throwing error in SQL Plus. 
In WMB still getting the same error with NULL values also.  |
|
| Back to top |
|
|
| adubya |
| Posted: Wed Feb 29, 2012 2:20 am Post subject: |
|
|
Partisan
Joined: 25 Aug 2011
Posts: 377
Location: GU12, UK
|
| OK, strip out all of your columns from the INSERT and start adding them back in one by one, running a test each time. Obviously you'll get "column not nullable" errors but ignore those and continue until you get your original error, you then know which column is causing the problem. |
|
| Back to top |
|
|
| mqsiuser |
| Posted: Wed Feb 29, 2012 3:14 am Post subject: |
|
|
Yatiri
Joined: 15 Apr 2008
Posts: 637
Location: Germany
|
| adubya wrote: |
| And I see your DBA likes concise column names! |
I pretty much guess this is a Standard-Software product (like SAP) which is a couple of years old.
It likely is the timestamp: You need to do casts (not necessarily (only) to CHAR, but (also) to DATE, DATETIME or TIMESTAMP) and use format patterns with these. Probably something like:
| Code: |
| CAST(CAST(CURRENT_TIMESTAMP AS CHAR FORMAT PATTERN '<whatever you db accepts>') AS DATETIME) |
(note this is ESQL-Syntax and not tested)
With passthru there definitivly is something like that with Oracle. You are using native (Java) functionality which makes figuring out what happens more difficult.
_________________
Just use REFERENCEs |
|
| Back to top |
|
|
|
|
|
| |
|
Page 1 of 1 |
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
|
|
|
