| Author |
Message
|
| chandu |
 Posted: Thu Oct 14, 2010 6:11 am Post subject: DB statement issue |
 |
|
Novice
Joined: 05 Aug 2009
Posts: 16
|
Hi,
I have the same issue. (split from this )
When using
set x = Passthru('select test_seq from dual');
insert into database.schema.table(column1,column2) values (x,y);
I am getting the following error:
The message broker detected an error whilst executing the given statement. An exception has been thrown to cut short the SQL program.
See the following messages for details of the error.
2010-10-14 10:02:58.501373 6440 DatabaseException BIP2321E: Database error: ODBC return code '-1'.
The message broker encountered an error whilst executing a database operation. The ODBC return code was '-1'. See the following messages for information obtained from the database pertaining to this error.
Use the following messages to determine the cause of the error. This is likely to be such things as incorrect datasource or table names. Then correct either the database or message broker configuration.
2010-10-14 10:02:58.501430 6440 DatabaseException BIP2322E: Database error: SQL State ''23000''; Native Error Code '1400'; Error Text ''[IBM][ODBC Oracle driver][Oracle]ORA-01400: cannot insert NULL into ("Schema"."table"."x") ''.
The error has the following diagnostic information: SQL State ''23000'' SQL Native Error Code '1400' SQL Error Text ''[IBM][ODBC Oracle driver][Oracle]ORA-01400: cannot insert NULL into ("schema"."table"."x") '' .
But the SELECT query is working fine in Oracle server.Please help resolve this. |
|
| Back to top |
|
 |
| fjb_saper |
| Posted: Thu Oct 14, 2010 6:50 am Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
you error is completely different from that 8 year old post...
The reason you are getting this, is because you are trying to insert a null value in one of the columns but no null value is allowed...
Check your code and fix it! 
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| WMBDEV1 |
| Posted: Thu Oct 14, 2010 6:54 am Post subject: |
|
|
Sentinel
Joined: 05 Mar 2009
Posts: 888
Location: UK
|
Do you not need to select NEXTVAL / CURVAL from the sequence?
I'm assuming you've changed the schema and table names also which is fine but it would be nice to be told this instead of having to guess, if not it may be worth getting a bit more creative with your table names! |
|
| Back to top |
|
|
| chandu |
| Posted: Thu Oct 14, 2010 7:56 am Post subject: |
|
|
Novice
Joined: 05 Aug 2009
Posts: 16
|
i executed the same SELECT(' test_seq.nextval from dual') in oracle which is returning value.I am using a passthru in broker with the same above query.
I know it is returning NULL.My question is why is it returning NULL instead of a unique value.How to get it resolved.
The test_seq is something like this:
CREATE SEQUENCE test_seq
START WITH 1
MAXVALUE 999999999999999999999999999
MINVALUE 1
NOCYCLE
CACHE 100
NOORDER;
When calling above seq from oracle using select test_seq from dual, it is returning value but not in broker using Passthru.Please help resolve this. |
|
| Back to top |
|
|
| chandu |
| Posted: Thu Oct 14, 2010 8:08 am Post subject: |
|
|
Novice
Joined: 05 Aug 2009
Posts: 16
|
Sorry for tha first post which is not correctly posted.
set x = passthru('select * from test_seq.nextval from dual)
insert into database.schema.table(column1,column2) values (x,y);
When executed inserting NULL.What is the problem.In oracle it is working fine. |
|
| Back to top |
|
|
| Vitor |
| Posted: Thu Oct 14, 2010 8:43 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| chandu wrote: |
Sorry for tha first post which is not correctly posted.
|
So you thought that you'd go back to this thread and incorrectly post it again?? 
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Thu Oct 14, 2010 11:47 am Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
| chandu wrote: |
Sorry for tha first post which is not correctly posted.
set x = passthru('select * from test_seq.nextval from dual)
insert into database.schema.table(column1,column2) values (x,y);
When executed inserting NULL.What is the problem.In oracle it is working fine. |
Shouldn't x be of type row?
You have to look at the types being returned. Obviously you will not be able to insert with x being of type row..
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| chandu |
| Posted: Thu Oct 14, 2010 4:58 pm Post subject: |
|
|
Novice
Joined: 05 Aug 2009
Posts: 16
|
I declared X as Row type.
Let me put clear what i have done.
DECLARE X Row;
SET Environment.X = Passthru('select test_seq.nextval from dual');
insert into database.schema.table(column1,column2) values (Environment.x[1],y);
Is there a work around for this.Do some casting to get singular value out of the returned Row. |
|
| Back to top |
|
|
| WMBDEV1 |
| Posted: Thu Oct 14, 2010 9:44 pm Post subject: |
|
|
Sentinel
Joined: 05 Mar 2009
Posts: 888
Location: UK
|
| chandu wrote: |
I declared X as Row type.
|
Indeed you have, however you are then not using this variable....
| Quote: |
SET Environment.X = Passthru('select test_seq.nextval from dual');
|
Following on from fjbs point, what happpens if you try:
| Code: |
SET Environment.X[] = Passthru('select test_seq.nextval from dual');
|
|
|
| Back to top |
|
|
| chandu |
| Posted: Fri Oct 15, 2010 6:26 am Post subject: |
|
|
Novice
Joined: 05 Aug 2009
Posts: 16
|
SET Environment.x[] = PASSTHRU('Select test_seq.nextval from dual);
Insert into Database.schema.table(col1,col2) values(Environment.x[1],y);
The datatypes of col1 and col2 is NUMBER.
Result: It is taking NULL value only
Trace file:
Executing statement ''SET Environment.MESSAGE_HEADER_ID[ ] = DEFAULTPASSTHRU('SELECT MESSAGE_HEADER_SEQ.nextval FROM dual');'' at
2010-10-15 10:12:33.368991 11160 UserTrace BIP2538I: Node .ogActualRequest': Evaluating expression ''DEFAULTPASSTHRU('SELECT test_seq.nextval FROM dual')''
2010-10-15 10:12:33.369194 11160 UserTrace BIP2544I: Node 'ProcessMemoPostTransactionRQ.logActualRequest': Executing database SQL statement ''SELECT test_seq.nextval FROM dual'' returning expressions ''''; resulting parameter values ''''.
2010-10-15 10:12:33.372215 11160 UserTrace BIP2562I: Node 'ProcessMemoPostTransactionRQ.logActualRequest': Assigning a list to ''Environment.MESSAGE_HEADER_ID[]''.
BIP2321E: Database error: ODBC return code '-1'.
The message broker encountered an error whilst executing a database operation. The ODBC return code was '-1'. See the following messages for information obtained from the database pertaining to this error.
Use the following messages to determine the cause of the error. This is likely to be such things as incorrect datasource or table names. Then correct either the database or message broker configuration.
2010-10-15 10:12:33.542091 11160 DatabaseException BIP2322E: Database error: SQL State ''23000''; Native Error Code '1400'; Error Text ''[IBM][ODBC Oracle driver][Oracle]ORA-01400: cannot insert NULL into ("IALOGGING"."MESSAGE_HEADER"."MESSAGE_HEADER_ID") ''.
The error has the following diagnostic information: SQL State ''23000'' SQL Native Error Code '1400' SQL Error Text ''[IBM][ODBC Oracle driver][Oracle]ORA-01400: cannot insert NULL into ("IALOGGING"."MESSAGE_HEADER"."MESSAGE_HEADER_ID") '' |
|
| Back to top |
|
|
| BDS |
| Posted: Thu Oct 28, 2010 2:37 am Post subject: |
|
|
Newbie
Joined: 21 Oct 2010
Posts: 8
|
Hi chandu
Not sure if you got this working - no solution is posted.
The following pseudo works for an Oracle db and will hopefully get you going.
| Code: |
DECLARE column2 CHARACTER;
DECLARE column3 CHARACTER;
DECLARE column4 CHARACTER;
DECLARE column5 CHARACTER;
SET coulmn2 = InputRoot.......whatever you like;
SET coulmn3 = InputRoot.......whatever you like;
SET coulmn4 = InputRoot.......whatever you like;
SET coulmn5 = InputRoot.......whatever you like;
DECLARE seqVal ROW;
SET Environment.seqVal[] = PASSTHRU('SELECT YOUR_SEQ.NEXTVAL FROM YOUR_TABLE');
INSERT INTO Database.YOUR_TABLE (COLUMNNAME1,COLUMNNAME2,COLUMNNAME3,COLUMNNAME4,COLUMNNAME5)
VALUES (Environment.seqVal.NEXTVAL,column2,column3,column4,column5);
Best wishes
B |
|
|
| Back to top |
|
|
| inder |
| Posted: Thu Oct 28, 2010 6:57 am Post subject: |
|
|
Apprentice
Joined: 24 Mar 2003
Posts: 49
Location: USA
|
Simple answer is you are getting a Key/Value(Name/Value) pair to environment variables.
i.e
| Quote: |
| SET Environment.x[] = PASSTHRU('Select test_seq.nextval from dual); |
sets Environment.x[1-n].nextval = value(test_seq.nextval )
instead you may use
| Quote: |
| SET Environment.x = THE (select ITEM(test_seq.nextval) from dual); |
if you are sure that you will be getting only one record. which makes
sets Environment.x = value(test_seq.nextval )
Thanks |
|
| Back to top |
|
|
| chandu |
| Posted: Sun Nov 07, 2010 1:06 pm Post subject: |
|
|
Novice
Joined: 05 Aug 2009
Posts: 16
|
Thanks all for your replies.It is working fine now.
Here is the solution.
DECLARE X ROW;
DECLARE Y INTEGER;(it is declared as NUMBER in Oracle)
SET X = Passthru('cast(select SEQ.nextval as VARCHAR(10) from sys.dual);
i want to extract the returned value which will always be a single value.
SET Y = FIELDVALUE(X.*[1]);
the returned value is scalar integer value which i am using in insert into statement and assigning to corresponding col.
INSERT INTO DATABASE.SCHEMA.TABLE(col1,col2....) VALUES (Y,VALUE2...) |
|
| Back to top |
|
|
| mqjeff |
| Posted: Sun Nov 07, 2010 4:13 pm Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| Did you want ITEM or THE, or both? |
|
| Back to top |
|
|
| chandu |
| Posted: Sun Nov 07, 2010 6:37 pm Post subject: |
|
|
Novice
Joined: 05 Aug 2009
Posts: 16
|
| I am trying to use the value obtained from NEXTVAL function.I am using that value to be inserted in a column.The use of THE or ITEM with passthru did not work correctly.So i used the above described method. |
|
| Back to top |
|
|
|
|
