| Author |
Message
|
| Raj2000 |
 Posted: Wed Sep 30, 2009 10:47 pm Post subject: database insert node issue |
 |
|
Apprentice
Joined: 03 Aug 2009
Posts: 47
|
Actually i am using the Datainsert Node and using the mapping to insert the values from the source i get the below error:
SQL State ''23502'' SQL Native Error Code '-407' SQL Error Text ''[IBM][CLI Driver][DB2/NT] SQL0407N Assignment of a NULL value to a NOT NULL column "TBSPACEID=2, TABLEID=2, COLNO=0" is not allowed. SQLSTATE=23502 '
where i assigned the values by selecting the value from source.
 |
|
| Back to top |
|
 |
| elvis_gn |
| Posted: Wed Sep 30, 2009 11:08 pm Post subject: |
|
|
Padawan
Joined: 08 Oct 2004
Posts: 1905
Location: Dubai
|
Hi Raj2000,
Check which are the not null columns in your table and see if the field mappings for those columns are coming with data. After that it depends on your business logic...i.e if you still want to insert with blanks or throw exceptions etc.
Regards. |
|
| Back to top |
|
|
| nheng |
| Posted: Wed Sep 30, 2009 11:09 pm Post subject: Re: database insert node issue |
|
|
Apprentice
Joined: 07 Dec 2007
Posts: 39
|
| Raj2000 wrote: |
Actually i am using the Datainsert Node and using the mapping to insert the values from the source i get the below error:
SQL State ''23502'' SQL Native Error Code '-407' SQL Error Text ''[IBM][CLI Driver][DB2/NT] SQL0407N Assignment of a NULL value to a NOT NULL column "TBSPACEID=2, TABLEID=2, COLNO=0" is not allowed. SQLSTATE=23502 '
where i assigned the values by selecting the value from source.
|
Plz check your value for insert into first column in your table . |
|
| Back to top |
|
|
| Raj2000 |
| Posted: Thu Oct 01, 2009 8:06 am Post subject: |
|
|
Apprentice
Joined: 03 Aug 2009
Posts: 47
|
| I have made a thorough check that they are coming with data but when i try to insert the data using the database insert node the above error occurs. |
|
| Back to top |
|
|
| Raj2000 |
| Posted: Thu Oct 01, 2009 10:48 am Post subject: |
|
|
Apprentice
Joined: 03 Aug 2009
Posts: 47
|
| My input data is xml data with data value as xsd:string and the data column where i have to insert the data in the database is odCHARACTER type does this makes any difference?? |
|
| Back to top |
|
|
| Raj2000 |
| Posted: Thu Oct 01, 2009 3:32 pm Post subject: |
|
|
Apprentice
Joined: 03 Aug 2009
Posts: 47
|
When i debugged it i have the following error message in the debug mode:
Message
LocalEnvironment
LOCAL_STACK
SOURCE_STACK
nestingLevel
occurrence
sourceLiteral
field XMLNSC
MQRFH2
GLOBAL_STACK
frame
frameDepth 0
sourceParserCategory 2
Environment
ExceptionList
RecoverableException
File F:\build\S610_P\src\DataFlowEngine\ImbDataFlowNode.cpp
Line 957
Function ImbDataFlowNode::createExceptionList
Type ComIbmMQInputNode
Name MIF_MF#FCMComposite_1_1
Label MIF_MF.MQInput
Catalog BIPv610
Severity 3
Number 2230
Text Node throwing exception
RecoverableException
File F:\build\S610_P\src\DataFlowEngine\ImbDatabaseNode.cpp
Line 295
Function ImbDatabaseNode::evaluate
Type ComIbmDatabaseNode
Name MIF_MF#FCMComposite_1_3.ComIbmDataInsert#FCMComposite_1_1
Label MIF_MF.DataInsert.ComIbmDatabase
Catalog BIPv610
Severity 3
Number 2230
Text Caught exception and rethrowing
RecoverableException
File F:\build\S610_P\src\DataFlowEngine\ImbRdl\ImbRdlStatementGroup.cpp
Line 602
Function SqlStatementGroup::execute
Type ComIbmDatabaseNode
Name MIF_MF#FCMComposite_1_3.ComIbmDataInsert#FCMComposite_1_1
Label MIF_MF.DataInsert.ComIbmDatabase
Catalog BIPv610
Severity 3
Number 2488
Text Error detected, rethrowing
Insert
Type 5
Text .IBM_WBIMB_MIF_MF_DataInsert.MAIN
Insert
Type 5
Text 3.1
Insert
Type 5
Text MIF_MF_DataInsert(Root, LocalEnvironment);
RecoverableException
File F:\build\S610_P\src\DataFlowEngine\ImbRdl\ImbRdlRoutine.cpp
Line 548
Function SqlRoutine::invoke
Type ComIbmDatabaseNode
Name MIF_MF#FCMComposite_1_3.ComIbmDataInsert#FCMComposite_1_1
Label MIF_MF.DataInsert.ComIbmDatabase
Catalog BIPv610
Severity 3
Number 2934
Text Error occured in procedure
Insert
Type 5
Text MIF_MF_DataInsert
RecoverableException
File F:\build\S610_P\src\DataFlowEngine\ImbRdl\ImbRdlStatementGroup.cpp
Line 586
Function SqlStatementGroup::execute
Type ComIbmDatabaseNode
Name MIF_MF#FCMComposite_1_3.ComIbmDataInsert#FCMComposite_1_1
Label MIF_MF.DataInsert.ComIbmDatabase
Catalog BIPv610
Severity 3
Number 2488
Text Error detected, rethrowing
Insert
Type 5
Text .MIF_MF_DataInsert
Insert
Type 5
Text 61.2
Insert
Type 5
Text INSERT INTO Database.MIFCHUNK.MHRHKL.CHUNKDATA ( VRI_TRN, VRI_STATUS ) VALUES (source_body.(XMLNSC.Field)tns1:VRI, source_body.(XMLNSC.Field)tns1:VRIStatus )
DatabaseException
File F:\build\S610_P\src\DataFlowEngine\ImbOdbc.cpp
Line 323
Function ImbOdbcHandle::checkRcInner
Type
Name
Label
Catalog BIPv610
Severity 3
Number 2321
Text Root SQL exception
Insert
Type 2
Text -1
DatabaseException
File F:\build\S610_P\src\DataFlowEngine\ImbOdbc.cpp
Line 438
Function ImbOdbcHandle::checkRcInner
Type
Name
Label
Catalog BIPv610
Severity 3
Number 2322
Text Child SQL exception
Insert
Type 5
Text 23502
Insert
Type 2
Text -407
Insert
Type 5
Text [IBM][CLI Driver][DB2/NT] SQL0407N Assignment of a NULL value to a NOT NULL column "TBSPACEID=2, TABLEID=4, COLNO=0" is not allowed. SQLSTATE=23502 |
|
| Back to top |
|
|
| nheng |
| Posted: Thu Oct 01, 2009 7:36 pm Post subject: answer |
|
|
Apprentice
Joined: 07 Dec 2007
Posts: 39
|
|
| Back to top |
|
|
| Raj2000 |
| Posted: Thu Oct 01, 2009 8:53 pm Post subject: |
|
|
Apprentice
Joined: 03 Aug 2009
Posts: 47
|
| i did not code it i am doing it by mapping in datainsert node |
|
| Back to top |
|
|
| nheng |
| Posted: Fri Oct 02, 2009 12:51 am Post subject: Answer |
|
|
Apprentice
Joined: 07 Dec 2007
Posts: 39
|
Database.MIFCHUNK.MHRHKL.CHUNKDATA ( VRI_TRN, VRI_STATUS ) VALUES (source_body.(XMLNSC.Field)tns1:VRI, source_body.(XMLNSC.Field)tns1:VRIStatus )
Your problem may be
1)source_body.(XMLNSC.Field)tns1:VRI may be null.Plz ensure it.
2)CHUNKDATA table has 2 rows or not?.If it has many fields.Plz insert correct variable in insert statement
3)Check value in Datasource textbox of DataInsert node. |
|
| Back to top |
|
|
| Raj2000 |
| Posted: Fri Oct 02, 2009 8:05 am Post subject: |
|
|
Apprentice
Joined: 03 Aug 2009
Posts: 47
|
| Quote: |
Your problem may be
1)source_body.(XMLNSC.Field)tns1:VRI may be null.Plz ensure it. |
Hello heng thank you for the response but as i have said i have cross checked the content of the input message in the debug mode after it enters the datainsert node ther is no issue with it
| Quote: |
| 2)CHUNKDATA table has 2 rows or not?.If it has many fields.Plz insert correct variable in insert statement |
if you mean two columns or fields yes they are 2 fields and the variables seems to be correct.
| Quote: |
| 3)Check value in Datasource textbox of DataInsert node. |
yes cross checked even the datasource initially i have forgot to mention the odbc driver but then i have corrected it but of no use and that is the reason i have posted the debug data may be i am doing something more wrong which i shouldn't..
|
|
| Back to top |
|
|
| mqjeff |
| Posted: Fri Oct 02, 2009 8:14 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
Don't use debug.
Use UserTrace.
You will see exactly what is passed to the insert statement. |
|
| Back to top |
|
|
| Raj2000 |
| Posted: Fri Oct 02, 2009 12:54 pm Post subject: |
|
|
Apprentice
Joined: 03 Aug 2009
Posts: 47
|
Thanks jeff for the advice it worked!!!
 |
|
| Back to top |
|
|
|
|
