| Author |
Message
|
| Glass |
 Posted: Fri Nov 14, 2008 7:18 am Post subject: Error while using DataInsert node |
 |
|
Acolyte
Joined: 02 Mar 2006
Posts: 56
|
Hi,
I am using Broker 6.02 and using the ‘DataInsert’ node to insert some values to the Oracle database. There is a column in the table where I am inserting called ‘ITEM’. When I map the value in the DataInsert node and deploy it, it gives me the following error:
| Code: |
BIP2405E: (.Load_SKU_DataInsert, 76.40) : Syntax error : 'ITEM' is a keyword , an identifier was expected.
The token caused a syntax error.
Correct the syntax of your ESQL expression in node '.Load_SKU_DataInsert', around line and column '76.40', then redeploy the message flow. |
If I use the Compute node to do the insert by typing out the sql insert statement it works fine.
Has anybody faced this issue? I’m not sure what identifier to use. I tried using the database.ITEM or schema.ITEM or database.schema.ITEM but nothing works.
Cheers! |
|
| Back to top |
|
 |
| wraymore |
| Posted: Fri Nov 14, 2008 7:58 am Post subject: |
|
|
Centurion
Joined: 16 Aug 2005
Posts: 114
Location: Burlington, NC USA
|
Shouldn't your code be:
INSERT INTO database.schema.table(ITEM) VALUES (Item_Value) |
|
| Back to top |
|
|
| Glass |
| Posted: Fri Nov 14, 2008 8:21 am Post subject: |
|
|
Acolyte
Joined: 02 Mar 2006
Posts: 56
|
| Thats right. I can get it to work if I type the sql code using the Compute node. But the problem is when I do the drag/drop mapping using the dataInsert node. |
|
| Back to top |
|
|
| wraymore |
| Posted: Tue Nov 18, 2008 3:14 pm Post subject: |
|
|
Centurion
Joined: 16 Aug 2005
Posts: 114
Location: Burlington, NC USA
|
Have you resolved this issue?
Here is a sample that I generated:
1. Add the Database Definition File from the Application Database
2. Generated a Message Set that contained the data items for the specific table
3. Created the Message Flow
MQInput Node - Set the input Message Parsing Properties from the message set created in item 2
DataInsert Node - set the datasource on the Properties tab; Selected Source - Message Set and Target - DB2 table; mapped corresponding items and set Create_Tmstp as current timestamp
4. Saved flow
5. created bar file, deployed, and executed.
6. Data stored in database
Here is a copy of the Test_DataInsert.msgmap:
<?xml version="1.0" encoding="UTF-8"?>
<xmi:XMI xmi:version="2.0" xmlns:xmi="http://www.omg.org/XMI" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:gplang="http://www.ibm.com/wbi/2005/gplang" xmlns:maplang="http://www.ibm.com/wbi/2005/maplang" xmlns:msg="http://www.ibm.com/wbi/2005/msg" xmlns:rdb="http://www.ibm.com/wbi/2005/rdb">
<maplang:MapOperation name="Test_DataInsert" prefixes="/0/@namespaces.0/@prefix /0/@namespaces.1/@prefix">
<blockContents xsi:type="rdb:InsertStatement" targetMapName="$db:insert" dsnName="Database" schemaName="Schema" tableName="Table">
<blockContents xsi:type="rdb:ColumnStatement" mappableName="FK_SERVICE_SRCE_CD">
<blockContents xsi:type="maplang:MapFromStatement">
<value xsi:type="maplang:MappableReferenceExpression" name="$source" text="$source/vndr:VendorData/vndr:VendorName"/>
</blockContents>
</blockContents>
<blockContents xsi:type="rdb:ColumnStatement" mappableName="ACTIVE_IND">
<blockContents xsi:type="maplang:MapFromStatement">
<value xsi:type="maplang:MappableReferenceExpression" name="$source" text="$source/vndr:VendorData/vndr:ActiveInd"/>
</blockContents>
</blockContents>
<blockContents xsi:type="rdb:ColumnStatement" mappableName="HIGH_VALUE_NUM">
<blockContents xsi:type="maplang:MapFromStatement">
<value xsi:type="maplang:MappableReferenceExpression" name="$source" text="$source/vndr:VendorData/vndr:UpperRange"/>
</blockContents>
</blockContents>
<blockContents xsi:type="rdb:ColumnStatement" mappableName="LOW_VALUE_NUM">
<blockContents xsi:type="maplang:MapFromStatement">
<value xsi:type="maplang:MappableReferenceExpression" name="$source" text="$source/vndr:VendorData/vndr:LowerRange"/>
</blockContents>
</blockContents>
<blockContents xsi:type="rdb:ColumnStatement" mappableName="STAT_MIN_OVRDE_NUM">
<blockContents xsi:type="maplang:MapFromStatement">
<value xsi:type="maplang:MappableReferenceExpression" name="$source" text="$source/vndr:VendorData/vndr:OverrideInd"/>
</blockContents>
</blockContents>
<blockContents xsi:type="rdb:ColumnStatement" mappableName="CREATE_TMSTP">
<blockContents xsi:type="maplang:MapFromStatement">
<value xsi:type="gplang:FunctionCallExpression" name="esql:current-timestamp" text="esql:current-timestamp()"/>
</blockContents>
</blockContents>
</blockContents>
<eParameters xsi:type="msg:MsgSourceMapRoot" name="$source" eType="/1"/>
<namespaces namespaceName="http://www.w3.org/2001/XMLSchema">
<prefix prefixName="xsd"/>
</namespaces>
<namespaces namespaceName="http://www.cmpny.com/11/17/2008/V1/Vendor.xsd">
<prefix prefixName="vndr"/>
</namespaces>
</maplang:MapOperation>
<msg:MessageHandle messageSetName="InputMapping_MessageSet" namespaceName="http://www.cmpny.com/11/17/2008/V1/Vendor.xsd" simpleName="VendorData" messageKind="assembly">
<headers name="Properties"/>
</msg:MessageHandle>
</xmi:XMI>
And a copy of Input Message Body:
<?xml version="1.0" encoding="UTF-8"?>
<VendorData xmlns:xs="http://www.w3.org/2001/XMLSchema-instance" xmlns:vndr="http://www.cmpny.com/11/17/2008/V1/Vendor.xsd">
<vndr:VendorName>XYZFV</vndr:VendorName>
<vndr:ActiveInd>0</vndr:ActiveInd>
<vndr:UpperRange>0</vndr:UpperRange>
<vndr:LowerRange>0</vndr:LowerRange>
<vndr:OverrideInd>0</vndr:OverrideInd>
</VendorData> |
|
| Back to top |
|
|
| Glass |
| Posted: Wed Nov 19, 2008 7:40 am Post subject: |
|
|
Acolyte
Joined: 02 Mar 2006
Posts: 56
|
Thanks wraymore for the response. No it is still an issue.
The steps you mentioned is what I had done to create the DataInsert node to insert in the db. I do have the DataInsert node working for tables that do not contain the column name 'ITEM'. The issue is only when I use the DataInsert node and map to the column ITEM. |
|
| Back to top |
|
|
| Glass |
| Posted: Wed Nov 19, 2008 9:11 am Post subject: |
|
|
Acolyte
Joined: 02 Mar 2006
Posts: 56
|
| Got this resolved. Defined the column name as "ITEM". |
|
| Back to top |
|
|
| wraymore |
| Posted: Wed Nov 19, 2008 9:15 am Post subject: |
|
|
Centurion
Joined: 16 Aug 2005
Posts: 114
Location: Burlington, NC USA
|
 I finally understand your issue. The usage of ITEM as a column name; where "ITEM" is a reserved keyword in the WMB. |
|
| Back to top |
|
|
|
|
