Data Conversion issues with DB2 adapter

Doug Griffin

Hiyas. Completely stumped on on what I beleive to be a simple data insert into a AS400 DB2 table using HIS/DB2 adapter.

The file I'm inserting into is very simple. The data types as follows:

R PADR01A
PLANT 1A
TRADEN 1 A
SUBNAME 1A
ALBUMT 1A
ORDERN 6A
SUFFIX1 2A
SUFFIX2 1 A
LINENO 5S 0
TYPE 1A
NAME 35A
ADD1 35A
ADD2 35A
ADD3 35A
ADD4 35A
CITY 20A
ST 2A
CNTY 20A
ZIP 11A
PHN 21A
ADDNUM 3S 0

Mostly strings with a couple decimal fields. If I try to insert the following:

<ns0RDERINSERTrequest xmlns:ns0="http:\\TOG.OrderInsert">
<sync>
<after>
<OSPADR01A PLANT="1" TRADEN="1" SUBNAME="1" ALBUMT="1" ORDERN="1" SUFFIX1="1" SUFFIX2="1" LINENO="1" TYPE="1" NAME="JOHN SMITH" ADD1="1" ADD2="1" ADD3="1" ADD4="1" CITY="BOISE" ST="1" CNTY="1" ZIP="1" PHN="1" ADDNUM="1"></OSPADR01A>
</after>
</sync>
</ns0RDERINSERTrequest>

I get this error:

"The parameter value for parameter 14 could not be converted to a native data type."

Which I interpret to mean it doesn't like something about the "CITY" attribute. Indeed, if I change the value of the "CITY" attribute to"1", like this:

<ns0RDERINSERTrequest xmlns:ns0="http:\\TOG.OrderInsert">
<sync>
<after>
<OSPADR01A PLANT="1" TRADEN="1" SUBNAME="1" ALBUMT="1" ORDERN="1" SUFFIX1="1" SUFFIX2="1" LINENO="1" TYPE="1" NAME="JOHN SMITH" ADD1="1" ADD2="1" ADD3="1" ADD4="1" CITY="1" ST="1" CNTY="1" ZIP="1" PHN="1" ADDNUM="1"></OSPADR01A>
</after>
</sync>
</ns0RDERINSERTrequest>

Then it works just fine. I routinely use the DB2 adapter to insert records into DB2 files with no problems, any thoughts why I might be having trouble with this particular file I thought it might be a offest problem, but my table has 20 fields and my updategram is passing 20 attributes with values.

Also could anyone offer any tips on how to debug parameters In order to narrow down the problem above, i had to change the attribute values in my test doc one by one which of course was very tedious. Is there somewhere where I can view joblog info with more detailed descriptions as to how the adapter is trying to convert each field

Thanks much in advance for any advice anyone can provide.

SaravanaKumar_1977

Hi Dough,

It seems to me the issue is with datatype. Could you please verify the schmea and table datatypes once again (are they in sync) Could you regenerate the schema again and do the testing again While giving connection string please make sure that you are giving the dataconversion code correctly, i.e. EBCIDIC US/Canada ->ANSCII is 37.

Thanks,

Sarbillgates

Doug Griffin

Hi Sarbillgates, thanks for responding.

I've checked and triple checked the schema and the data types match up (18 strings and 2 decimal).

I'm pretty sure I'm using the right code page (CCSID=37;PC Code Page=1252) as I have no trouble with it on any of my other inserts. I have verified on the DB2 side that the table uses CCSID 37.

What is frustrating is that I can save out the suspended message from BT admin, then resubmit that same message outside orchestartion (with port filter) to the same port and it inserts data into my table just fine.

SaravanaKumar_1977

Hi Dough,

Can you post the schema and table structure for the reference

Thanks,

Sarbillgates

Doug Griffin

Sure. The DB2 table structure is:

PLANT 1A COLHDG('PLANT')

TRADEN 1A COLHDG('TRADE' 'NAME')

SUBNAME 1A COLHDG('SUB' 'NAME')

ALBUMT 1A COLHDG('ALBUM' 'TYPE')

ORDERN 6A COLHDG('ORDER' 'NUMBER')

SUFFIX1 2A COLHDG('SUFFIX1')

SUFFIX2 1A COLHDG('SUFFIX2')

LINENO 5S 0 COLHDG('LINE#')

TYPE 1A COLHDG('ADDRESS' 'TYPE')

NAME 35A COLHDG('NAME')

ADD1 35A COLHDG('ADDRESS 1')

ADD2 35A COLHDG('ADDRESS 2')

ADD3 35A COLHDG('ADDRESS 3')

ADD4 35A COLHDG('ADDRESS 4')

CITY 20A COLHDG('CITY')

ST 2A COLHDG('STATE')

CNTY 20A COLHDG('COUNTRY')

ZIP 11A COLHDG('ZIP')

PHN 21A COLHDG('PHONE')

ADDNUM 3S 0 COLHDG('ADDRESS' 'NUMBER')

The schema (relevent node highlighted in red)

< xml version="1.0" encoding="utf-16" >
<xschema xmlns:tns="http:\\TOG.Artco.ICSM.OrderInsert" xmlns:b="http://schemas.microsoft.com/BizTalk/2003" targetNamespace="http:\\TOG.Artco.ICSM.OrderInsert" xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="ORDERINSERTrequest">
<xs:complexType>
<xsequence>
<xs:element xmlns:updategram="urnchemas-microsoft-com:xml-updategram" updategramrefix="updg" minOccurs="1" maxOccurs="unbounded" name="sync">
<xs:complexType>
<xsequence>
<xs:element updategramrefix="updg" minOccurs="0" maxOccurs="unbounded" name="after">
<xs:complexType>
<xsequence>
<xs:element minOccurs="0" maxOccurs="1" name="OSPHDR01A">
<xs:complexType>
<xs:attribute name="STATUS" type="xstring" />
<xs:attribute name="S_ID" type="xstring" />
<xs:attribute name="I_BAT" type="xsecimal" />
<xs:attribute name="S_SEQ" type="xsecimal" />
<xs:attribute name="S_ORDER" type="xstring" />
<xs:attribute name="S_LINE" type="xsecimal" />
<xs:attribute name="R_ID" type="xstring" />
<xs:attribute name="PLANT" type="xstring" />
<xs:attribute name="TRADEN" type="xstring" />
<xs:attribute name="SUBNAME" type="xstring" />
<xs:attribute name="ALBUMT" type="xstring" />
<xs:attribute name="ORDERN" type="xstring" />
<xs:attribute name="SUFFIX1" type="xstring" />
<xs:attribute name="SUFFIX2" type="xstring" />
<xs:attribute name="LINENO" type="xsecimal" />
<xs:attribute name="DATAID" type="xstring" />
<xs:attribute name="VERSID" type="xsecimal" />
<xs:attribute name="INV" type="xstring" />
<xs:attribute name="ODYR" type="xsecimal" />
<xs:attribute name="ODMN" type="xsecimal" />
<xs:attribute name="ODDY" type="xsecimal" />
<xs:attribute name="DDYR" type="xsecimal" />
<xs:attribute name="DDMN" type="xsecimal" />
<xs:attribute name="DDDY" type="xsecimal" />
<xs:attribute name="SRC" type="xsecimal" />
<xs:attribute name="BILLC" type="xstring" />
<xs:attribute name="FILR" type="xstring" />
</xs:complexType>
</xs:element>
<xs:element minOccurs="0" maxOccurs="unbounded" name="OSPADR01A">
<xs:complexType>
<xs:attribute name="PLANT" type="xstring" />
<xs:attribute name="TRADEN" type="xstring" />
<xs:attribute name="SUBNAME" type="xstring" />
<xs:attribute name="ALBUMT" type="xstring" />
<xs:attribute name="ORDERN" type="xstring" />
<xs:attribute name="SUFFIX1" type="xstring" />
<xs:attribute name="SUFFIX2" type="xstring" />
<xs:attribute name="LINENO" type="xsecimal" />
<xs:attribute name="TYPE" type="xstring" />
<xs:attribute name="NAME" type="xstring" />
<xs:attribute name="ADD1" type="xstring" />
<xs:attribute name="ADD2" type="xstring" />
<xs:attribute name="ADD3" type="xstring" />
<xs:attribute name="ADD4" type="xstring" />
<xs:attribute name="CITY" type="xstring" />
<xs:attribute name="ST" type="xstring" />
<xs:attribute name="CNTY" type="xstring" />
<xs:attribute name="ZIP" type="xstring" />
<xs:attribute name="PHN" type="xstring" />
<xs:attribute name="ADDNUM" type="xsecimal" />
</xs:complexType>
</xs:element>
<xs:element minOccurs="0" maxOccurs="unbounded" name="OSLADR01S">
<xs:annotation>
<xs:appinfo>
<recordInfo xmlns="http://schemas.microsoft.com/BizTalk/2003" />
</xs:appinfo>
</xs:annotation>
<xs:complexType>
<xs:attribute name="PLANT" type="xstring" />
<xs:attribute name="TRADEN" type="xstring" />
<xs:attribute name="SUBNAME" type="xstring" />
<xs:attribute name="ALBUMT" type="xstring" />
<xs:attribute name="ORDERN" type="xstring" />
<xs:attribute name="SUFFIX1" type="xstring" />
<xs:attribute name="SUFFIX2" type="xstring" />
<xs:attribute name="LINENO" type="xsecimal" />
<xs:attribute name="TYPE" type="xstring" />
<xs:attribute name="NAME" type="xstring" />
<xs:attribute name="ADD1" type="xstring" />
<xs:attribute name="ADD2" type="xstring" />
<xs:attribute name="ADD3" type="xstring" />
<xs:attribute name="ADD4" type="xstring" />
<xs:attribute name="CITY" type="xstring" />
<xs:attribute name="ST" type="xstring" />
<xs:attribute name="CNTY" type="xstring" />
<xs:attribute name="ZIP" type="xstring" />
<xs:attribute name="PHN" type="xstring" />
<xs:attribute name="ADDNUM" type="xsecimal" />
</xs:complexType>
</xs:element>
<xs:element minOccurs="0" maxOccurs="1" name="OSPADR02A">
<xs:complexType>
<xs:attribute name="PLANT" type="xstring" />
<xs:attribute name="TRADEN" type="xstring" />
<xs:attribute name="SUBNAME" type="xstring" />
<xs:attribute name="ALBUMT" type="xstring" />
<xs:attribute name="ORDERN" type="xstring" />
<xs:attribute name="SUFFIX1" type="xstring" />
<xs:attribute name="SUFFIX2" type="xstring" />
<xs:attribute name="LINENO" type="xsecimal" />
<xs:attribute name="TYPE" type="xstring" />
<xs:attribute name="FAX" type="xstring" />
<xs:attribute name="EMAIL" type="xstring" />
<xs:attribute name="IP" type="xstring" />
</xs:complexType>
</xs:element>
<xs:element minOccurs="0" maxOccurs="unbounded" name="OSPCNT01A">
<xs:complexType>
<xs:attribute name="PLANT" type="xstring" />
<xs:attribute name="TRADEN" type="xstring" />
<xs:attribute name="SUBNAME" type="xstring" />
<xs:attribute name="ALBUMT" type="xstring" />
<xs:attribute name="ORDERN" type="xstring" />
<xs:attribute name="SUFFIX1" type="xstring" />
<xs:attribute name="SUFFIX2" type="xstring" />
<xs:attribute name="LINENO" type="xsecimal" />
<xs:attribute name="TYPE" type="xstring" />
<xs:attribute name="NAME" type="xstring" />
<xs:attribute name="PHONE" type="xstring" />
<xs:attribute name="FAX" type="xstring" />
<xs:attribute name="EMAIL" type="xstring" />
<xs:attribute name="TCPIP" type="xstring" />
<xs:attribute name="COMPN" type="xstring" />
</xs:complexType>
</xs:element>
<xs:element minOccurs="0" maxOccurs="1" name="OSPCST01A">
<xs:complexType>
<xs:attribute name="PLANT" type="xstring" />
<xs:attribute name="TRADEN" type="xstring" />
<xs:attribute name="SUBNAME" type="xstring" />
<xs:attribute name="ALBUMT" type="xstring" />
<xs:attribute name="ORDERN" type="xstring" />
<xs:attribute name="SUFFIX1" type="xstring" />
<xs:attribute name="SUFFIX2" type="xstring" />
<xs:attribute name="LINENO" type="xsecimal" />
<xs:attribute name="CUSTN" type="xstring" />
<xs:attribute name="PON" type="xstring" />
<xs:attribute name="RELN" type="xstring" />
<xs:attribute name="REQN" type="xstring" />
<xs:attribute name="DSTN" type="xstring" />
<xs:attribute name="PHNN" type="xstring" />
<xs:attribute name="CCN" type="xstring" />
<xs:attribute name="LOCID" type="xstring" />
<xs:attribute name="SDOC" type="xstring" />
<xs:attribute name="SRCODE" type="xstring" />
<xs:attribute name="FILR" type="xstring" />
</xs:complexType>
</xs:element>
<xs:element minOccurs="0" maxOccurs="unbounded" name="OSPEXT01A">
<xs:complexType>
<xs:attribute name="PLANT" type="xstring" />
<xs:attribute name="TRADEN" type="xstring" />
<xs:attribute name="SUBNAME" type="xstring" />
<xs:attribute name="ALBUMT" type="xstring" />
<xs:attribute name="ORDERN" type="xstring" />
<xs:attribute name="SUFFIX1" type="xstring" />
<xs:attribute name="SUFFIX2" type="xstring" />
<xs:attribute name="LINENO" type="xsecimal" />
<xs:attribute name="OEXC" type="xstring" />
<xs:attribute name="REXC" type="xstring" />
<xs:attribute name="RDSC" type="xstring" />
<xs:attribute name="OQTY" type="xsecimal" />
<xs:attribute name="RQTY" type="xsecimal" />
<xs:attribute name="OUOM" type="xstring" />
<xs:attribute name="RUOM" type="xstring" />
<xs:attribute name="CHGEXT" type="xstring" />
<xs:attribute name="EXTPRC" type="xsecimal" />
<xs:attribute name="FILR" type="xstring" />
</xs:complexType>
</xs:element>
<xs:element minOccurs="0" maxOccurs="unbounded" name="OSPEXT02A">
<xs:complexType>
<xs:attribute name="PLANT" type="xstring" />
<xs:attribute name="TRADEN" type="xstring" />
<xs:attribute name="SUBNAME" type="xstring" />
<xs:attribute name="ALBUMT" type="xstring" />
<xs:attribute name="ORDERN" type="xstring" />
<xs:attribute name="SUFFIX1" type="xstring" />
<xs:attribute name="SUFFIX2" type="xstring" />
<xs:attribute name="LINENO" type="xsecimal" />
<xs:attribute name="EXITEM" type="xstring" />
<xs:attribute name="UPRC" type="xsecimal" />
<xs:attribute name="EXPRC" type="xsecimal" />
<xs:attribute name="PTYPE" type="xstring" />
</xs:complexType>
</xs:element>
<xs:element minOccurs="0" maxOccurs="unbounded" name="OSPITM01Z">
<xs:complexType>
<xs:attribute name="PLANT" type="xstring" />
<xs:attribute name="TRADEN" type="xstring" />
<xs:attribute name="SUBNAME" type="xstring" />
<xs:attribute name="ALBUMT" type="xstring" />
<xs:attribute name="ORDERN" type="xstring" />
<xs:attribute name="SUFFIX1" type="xstring" />
<xs:attribute name="SUFFIX2" type="xstring" />
<xs:attribute name="LINENO" type="xsecimal" />
<xs:attribute name="TKT" type="xsecimal" />
<xs:attribute name="RITM" type="xstring" />
<xs:attribute name="RQTY" type="xsecimal" />
<xs:attribute name="MQTY" type="xsecimal" />
<xs:attribute name="RUOM" type="xstring" />
<xs:attribute name="CHRG" type="xstring" />
<xs:attribute name="RPRT" type="xstring" />
<xs:attribute name="RDYR" type="xsecimal" />
<xs:attribute name="RDMN" type="xsecimal" />
<xs:attribute name="RDDY" type="xsecimal" />
<xs:attribute name="EVYR" type="xsecimal" />
<xs:attribute name="EVMN" type="xsecimal" />
<xs:attribute name="EVDY" type="xsecimal" />
<xs:attribute name="MAIN" type="xstring" />
<xs:attribute name="DISC" type="xsecimal" />
<xs:attribute name="RUSH" type="xstring" />
<xs:attribute name="SMPL" type="xstring" />
<xs:attribute name="PSTG" type="xstring" />
<xs:attribute name="CATCD" type="xstring" />
<xs:attribute name="ADDRESSN" type="xsecimal" />
<xs:attribute name="CPROG" type="xstring" />
<xs:attribute name="SOURCE" type="xstring" />
<xs:attribute name="CATVERS" type="xstring" />
<xs:attribute name="PAGENUM" type="xstring" />
<xs:attribute name="REQID" type="xstring" />
<xs:attribute name="PARTNER" type="xstring" />
<xs:attribute name="USERFLD" type="xstring" />
</xs:complexType>
</xs:element>
<xs:element minOccurs="0" maxOccurs="unbounded" name="OSPITM02Z">
<xs:complexType>
<xs:attribute name="PLANT" type="xstring" />
<xs:attribute name="TRADEN" type="xstring" />
<xs:attribute name="SUBNAME" type="xstring" />
<xs:attribute name="ALBUMT" type="xstring" />
<xs:attribute name="ORDERN" type="xstring" />
<xs:attribute name="SUFFIX1" type="xstring" />
<xs:attribute name="SUFFIX2" type="xstring" />
<xs:attribute name="LINENO" type="xsecimal" />
<xs:attribute name="PARTN" type="xstring" />
<xs:attribute name="AREAN" type="xstring" />
<xs:attribute name="ITEMT" type="xstring" />
<xs:attribute name="BORDER" type="xstring" />
<xs:attribute name="PSTOCK" type="xstring" />
<xs:attribute name="PENV" type="xstring" />
<xs:attribute name="LENV" type="xstring" />
<xs:attribute name="CITM" type="xstring" />
<xs:attribute name="ORUN" type="xsecimal" />
<xs:attribute name="LOGO" type="xstring" />
<xs:attribute name="LGOA" type="xstring" />
<xs:attribute name="PRFC" type="xstring" />
<xs:attribute name="SNUM" type="xsecimal" />
<xs:attribute name="SCRP" type="xsecimal" />
<xs:attribute name="BIND" type="xstring" />
<xs:attribute name="ARTR" type="xstring" />
<xs:attribute name="VERSE" type="xstring" />
<xs:attribute name="VERSET" type="xstring" />
<xs:attribute name="LYOUT" type="xstring" />
<xs:attribute name="CLOGO" type="xstring" />
<xs:attribute name="PORN" type="xstring" />
<xs:attribute name="THRD" type="xstring" />
</xs:complexType>
</xs:element>
<xs:element minOccurs="0" maxOccurs="unbounded" name="OSPITM03A">
<xs:complexType>
<xs:attribute name="PLANT" type="xstring" />
<xs:attribute name="TRADEN" type="xstring" />
<xs:attribute name="SUBNAME" type="xstring" />
<xs:attribute name="ALBUMT" type="xstring" />
<xs:attribute name="ORDERN" type="xstring" />
<xs:attribute name="SUFFIX1" type="xstring" />
<xs:attribute name="SUFFIX2" type="xstring" />
<xs:attribute name="LINENO" type="xsecimal" />
<xs:attribute name="TCPIP" type="xstring" />
<xs:attribute name="GDIR" type="xstring" />
<xs:attribute name="GFILN" type="xstring" />
<xs:attribute name="GTYPE" type="xstring" />
<xs:attribute name="GINK" type="xstring" />
<xs:attribute name="GALGN" type="xstring" />
<xs:attribute name="GAREA" type="xstring" />
</xs:complexType>
</xs:element>
<xs:element minOccurs="0" maxOccurs="unbounded" name="OSPITM04A">
<xs:complexType>
<xs:attribute name="PLANT" type="xstring" />
<xs:attribute name="TRADEN" type="xstring" />
<xs:attribute name="SUBNAME" type="xstring" />
<xs:attribute name="ALBUMT" type="xstring" />
<xs:attribute name="ORDERN" type="xstring" />
<xs:attribute name="SUFFIX1" type="xstring" />
<xs:attribute name="SUFFIX2" type="xstring" />
<xs:attribute name="LINENO" type="xsecimal" />
<xs:attribute name="OUNIT" type="xsecimal" />
<xs:attribute name="OUEXT" type="xsecimal" />
</xs:complexType>
</xs:element>
<xs:element minOccurs="0" maxOccurs="unbounded" name="OSPITM05A">
<xs:complexType>
<xs:attribute name="PLANT" type="xstring" />
<xs:attribute name="TRADEN" type="xstring" />
<xs:attribute name="SUBNAME" type="xstring" />
<xs:attribute name="ALBUMT" type="xstring" />
<xs:attribute name="ORDERN" type="xstring" />
<xs:attribute name="SUFFIX1" type="xstring" />
<xs:attribute name="SUFFIX2" type="xstring" />
<xs:attribute name="LINENO" type="xsecimal" />
<xs:attribute name="QUAL" type="xstring" />
<xs:attribute name="VALUE" type="xstring" />
</xs:complexType>
</xs:element>
<xs:element minOccurs="0" maxOccurs="unbounded" name="OSPMSG01A">
<xs:complexType>
<xs:attribute name="PLANT" type="xstring" />
<xs:attribute name="TRADEN" type="xstring" />
<xs:attribute name="SUBNAME" type="xstring" />
<xs:attribute name="ALBUMT" type="xstring" />
<xs:attribute name="ORDERN" type="xstring" />
<xs:attribute name="SUFFIX1" type="xstring" />
<xs:attribute name="SUFFIX2" type="xstring" />
<xs:attribute name="LINENO" type="xsecimal" />
<xs:attribute name="SEQ" type="xsecimal" />
<xs:attribute name="CODE" type="xstring" />
<xs:attribute name="INST" type="xstring" />
</xs:complexType>
</xs:element>
<xs:element minOccurs="0" maxOccurs="unbounded" name="OSPPAY01A">
<xs:complexType>
<xs:attribute name="PLANT" type="xstring" />
<xs:attribute name="TRADEN" type="xstring" />
<xs:attribute name="SUBNAME" type="xstring" />
<xs:attribute name="ALBUMT" type="xstring" />
<xs:attribute name="ORDERN" type="xstring" />
<xs:attribute name="SUFFIX1" type="xstring" />
<xs:attribute name="SUFFIX2" type="xstring" />
<xs:attribute name="LINENO" type="xsecimal" />
<xs:attribute name="CAAMT" type="xsecimal" />
<xs:attribute name="CHKNUM" type="xstring" />
<xs:attribute name="TYPE" type="xstring" />
<xs:attribute name="KEYV" type="xstring" />
<xs:attribute name="ENCACC" type="xstring" />
<xs:attribute name="ENROUT" type="xstring" />
<xs:attribute name="CHKNAM" type="xstring" />
</xs:complexType>
</xs:element>
<xs:element minOccurs="0" maxOccurs="unbounded" name="OSPPAY02A">
<xs:complexType>
<xs:attribute name="PLANT" type="xstring" />
<xs:attribute name="TRADEN" type="xstring" />
<xs:attribute name="SUBNAME" type="xstring" />
<xs:attribute name="ALBUMT" type="xstring" />
<xs:attribute name="ORDERN" type="xstring" />
<xs:attribute name="SUFFIX1" type="xstring" />
<xs:attribute name="SUFFIX2" type="xstring" />
<xs:attribute name="LINENO" type="xsecimal" />
<xs:attribute name="NAME" type="xstring" />
<xs:attribute name="CCAMT" type="xsecimal" />
<xs:attribute name="CCNUM" type="xstring" />
<xs:attribute name="TYPE" type="xstring" />
<xs:attribute name="CCEXP" type="xstring" />
</xs:complexType>
</xs:element>
<xs:element minOccurs="0" maxOccurs="unbounded" name="OSPPAY02EC">
<xs:complexType>
<xs:attribute name="PLANT" type="xstring" />
<xs:attribute name="TRADEN" type="xstring" />
<xs:attribute name="SUBNAME" type="xstring" />
<xs:attribute name="ALBUMT" type="xstring" />
<xs:attribute name="ORDERN" type="xstring" />
<xs:attribute name="SUFFIX1" type="xstring" />
<xs:attribute name="SUFFIX2" type="xstring" />
<xs:attribute name="LINENO" type="xsecimal" />
<xs:attribute name="KEY" type="xstring" />
<xs:attribute name="CCNBR" type="xstring" />
<xs:attribute name="CCTYP" type="xstring" />
<xs:attribute name="CCEXP" type="xstring" />
<xs:attribute name="CCSEC" type="xstring" />
</xs:complexType>
</xs:element>
<xs:element minOccurs="0" maxOccurs="unbounded" name="OSPSHP01A">
<xs:complexType>
<xs:attribute name="PLANT" type="xstring" />
<xs:attribute name="TRADEN" type="xstring" />
<xs:attribute name="SUBNAME" type="xstring" />
<xs:attribute name="ALBUMT" type="xstring" />
<xs:attribute name="ORDERN" type="xstring" />
<xs:attribute name="SUFFIX1" type="xstring" />
<xs:attribute name="SUFFIX2" type="xstring" />
<xs:attribute name="LINENO" type="xsecimal" />
<xs:attribute name="NAME" type="xstring" />
<xs:attribute name="FILR" type="xstring" />
</xs:complexType>
</xs:element>
<xs:element minOccurs="0" maxOccurs="unbounded" name="OSPSHP02A">
<xs:complexType>
<xs:attribute name="PLANT" type="xstring" />
<xs:attribute name="TRADEN" type="xstring" />
<xs:attribute name="SUBNAME" type="xstring" />
<xs:attribute name="ALBUMT" type="xstring" />
<xs:attribute name="ORDERN" type="xstring" />
<xs:attribute name="SUFFIX1" type="xstring" />
<xs:attribute name="SUFFIX2" type="xstring" />
<xs:attribute name="LINENO" type="xsecimal" />
<xs:attribute name="PREQ" type="xstring" />
<xs:attribute name="CAR" type="xstring" />
<xs:attribute name="SERV" type="xstring" />
<xs:attribute name="BILL" type="xstring" />
<xs:attribute name="PSTG" type="xstring" />
<xs:attribute name="SOVR" type="xsecimal" />
<xs:attribute name="FILR" type="xstring" />
</xs:complexType>
</xs:element>
<xs:element minOccurs="0" maxOccurs="unbounded" name="OSPSHP04A">
<xs:complexType>
<xs:attribute name="PLANT" type="xstring" />
<xs:attribute name="TRADEN" type="xstring" />
<xs:attribute name="SUBNAME" type="xstring" />
<xs:attribute name="ALBUMT" type="xstring" />
<xs:attribute name="ORDERN" type="xstring" />
<xs:attribute name="SUFFIX1" type="xstring" />
<xs:attribute name="SUFFIX2" type="xstring" />
<xs:attribute name="LINENO" type="xsecimal" />
<xs:attribute name="SPCL" type="xstring" />
<xs:attribute name="CHRG" type="xsecimal" />
</xs:complexType>
</xs:element>
<xs:element minOccurs="0" maxOccurs="unbounded" name="OSPTAX01A">
<xs:complexType>
<xs:attribute name="PLANT" type="xstring" />
<xs:attribute name="TRADEN" type="xstring" />
<xs:attribute name="SUBNAME" type="xstring" />
<xs:attribute name="ALBUMT" type="xstring" />
<xs:attribute name="ORDERN" type="xstring" />
<xs:attribute name="SUFFIX1" type="xstring" />
<xs:attribute name="SUFFIX2" type="xstring" />
<xs:attribute name="LINENO" type="xsecimal" />
<xs:attribute name="TYPE" type="xstring" />
<xs:attribute name="AMOUNT" type="xsecimal" />
</xs:complexType>
</xs:element>
<xs:element minOccurs="0" maxOccurs="unbounded" name="OSPTXT01Z">
<xs:complexType>
<xs:attribute name="PLANT" type="xstring" />
<xs:attribute name="TRADEN" type="xstring" />
<xs:attribute name="SUBNAME" type="xstring" />
<xs:attribute name="ALBUMT" type="xstring" />
<xs:attribute name="ORDERN" type="xstring" />
<xs:attribute name="SUFFIX1" type="xstring" />
<xs:attribute name="SUFFIX2" type="xstring" />
<xs:attribute name="LINENO" type="xsecimal" />
<xs:attribute name="FMTID" type="xstring" />
<xs:attribute name="SURF" type="xsecimal" />
<xs:attribute name="AREA" type="xstring" />
<xs:attribute name="LINEN" type="xsecimal" />
<xs:attribute name="SLINEN" type="xsecimal" />
<xs:attribute name="TEXT" type="xstring" />
<xs:attribute name="INK" type="xstring" />
<xs:attribute name="ALGN" type="xstring" />
<xs:attribute name="FONTID" type="xstring" />
<xs:attribute name="FONTSZ" type="xsecimal" />
<xs:attribute name="MAINL" type="xstring" />
<xs:attribute name="TEXTID" type="xstring" />
<xs:attribute name="HQUAD" type="xstring" />
<xs:attribute name="VLEAD" type="xstring" />
</xs:complexType>
</xs:element>
<xs:element minOccurs="0" maxOccurs="unbounded" name="OSPUSR01A">
<xs:complexType>
<xs:attribute name="PLANT" type="xstring" />
<xs:attribute name="TRADEN" type="xstring" />
<xs:attribute name="SUBNAME" type="xstring" />
<xs:attribute name="ALBUMT" type="xstring" />
<xs:attribute name="ORDERN" type="xstring" />
<xs:attribute name="SUFFIX1" type="xstring" />
<xs:attribute name="SUFFIX2" type="xstring" />
<xs:attribute name="LINENO" type="xsecimal" />
<xs:attribute name="QUAL" type="xstring" />
<xs:attribute name="UDATA" type="xstring" />
</xs:complexType>
</xs:element>
</xsequence>
</xs:complexType>
</xs:element>
</xsequence>
</xs:complexType>
</xs:element>
</xsequence>
</xs:complexType>
</xs:element>
<xs:element name="ORDERINSERTresponse">
<xs:complexType>
<xsequence>
<xs:element name="Success">
<xs:complexType>
<xsequence>
<xs:element minOccurs="0" maxOccurs="1" name="RowsAffected" type="xs:int" />
<xs:element minOccurs="0" name="ResultSets" type="xs:anyType" />
</xsequence>
</xs:complexType>
</xs:element>
</xsequence>
</xs:complexType>
</xs:element>
</xschema>

SaravanaKumar_1977

The schema looks fine. In the first message you said, after changing to "1" it was worked and in the second reply you said if you are resubmitting it is working fine. I hope you are resubmitting without changing the message.

Answer me for the following question

Are you testing after restarting the BizTalk Can you submit the same message again and do the testing

Thanks,

Sarbillgates

Doug Griffin

Right. If I change the value of the attribute to "1", it works fine both in orchestration and port filter submit method.

If I have a non-numeric value in the attribute such as "Chicago", it complains about the data type when I submit via orch, but no problems with "Chicago" when I submit via port filter.

Restarting biztalk has no effect on the scenario. I have tried it with restarting and without and same results both ways.

I should mention that my development machine is Vista, but I spent a good chunk of time today deploying to a XP development machine, but had identical results.

SaravanaKumar_1977

Can you create a linked server with the same connection string, try to insert using ordinary insert query with the same values and let me know if it works

Thanks,

Sarbillgates

MayurNagda

We are facing the same issue of "parameter xx Can not convert to native data type" with DB@ adapter in BTS 2006.

When we try to insert records (single or multiple ) in single table using updategram there is no issue.But when we are trying to insert records in multiple tables in DB2 database using single schema( manually copy pasting entire table structure as described in

http://msdn2.microsoft.com/en-us/library/ms935658.aspx for SQL databse) we are getting error "parameter xx Can not convert to native data type".

According to my observation all those fields are of type CHAR in db2 and string in schema. while inserting in individual tables all fields are taking values according to datatype specified but when inserting in multiple table same values are giving error. We tried the same thing as Doug Griffin did, we tried to insert numeric values in those fields ,and it stated accepting it.

Can you help me on this.

Doug Griffin

Sarbillgates - I am not sure what you mean by linked server. Could you clarify

Mayur - Yes, I did not encounter this issue until I started using the schema for multiple table inserts. I have to insert data into 23 different tables and I did not want to have 23 different schemas/maps to maintain. So I took each individual schema generated by the "Add generated items" wizard and combinded them into a single schema".

At first I tried to create a single map for all my inserts. The message generated from the map looked something like this:

<ns0RDERINSERTrequest xmlns:ns0="http:\\TOG.OrderInsert">
<sync>
<after>
<TABLE01 VALUE="x"></TABLE01>
<TABLE02 VALUE="x"></TABLE01>
<TABLE03 VALUE="x"></TABLE01>
<TABLE04 VALUE="x"></TABLE01>
<TABLE05 VALUE="x"></TABLE01>
</after>
</sync>
</ns0RDERINSERTrequest>

I then passed the message to a port. What I found is that the inserts would succeed for TABLE01, TABLE02, and TABLE03, but I would get the "cannot convert to native data type" error on TABLE04.

So then, using the same schema, I created 3 different maps, built 3 different messages and tried to pass all of them through a single port (same namespace and root element so should be no prob).

<ns0RDERINSERTrequest xmlns:ns0="http:\\TOG.OrderInsert">
<sync>
<after>
<TABLE01 VALUE="x"></TABLE01>
</after>
</sync>
</ns0RDERINSERTrequest>

<ns0RDERINSERTrequest xmlns:ns0="http:\\TOG.OrderInsert">
<sync>
<after>
<TABLE02 VALUE="x"></TABLE01>
</after>
</sync>
</ns0RDERINSERTrequest>

<ns0RDERINSERTrequest xmlns:ns0="http:\\TOG.OrderInsert">
<sync>
<after>
<TABLE03 VALUE="x"></TABLE01>
</after>
</sync>
</ns0RDERINSERTrequest>

When I did this, TABLE03 insert succeeded, however TABLE01 and TABLE02 inserts failed.

MayurNagda

Doug : what kind of error you getting

i am also trying the same thing . trying to insert in individual tables. not tried with different map.

any idea abt this behaviour

Sarbillgates : how can i trace what query is being fired on Db2 is there any way in biztalk that we can put logging on adapter

SaravanaKumar_1977

Mayur and Doug,

As for as my knowledge is concern I don't think we can track what query has been fired, like SQL Profiler. If the DB2 adapter has this issue we can go for linked server approach. We have integrated DB2 using SQL Adapter via linked server in BizTalk 2004. Please created a linked server in SQL Server 2000, refer Sql Server documentation for creating linked server, then connect the SQL Server's linked serverv via SQL Adapter. Before doing unit testing manually using query analyzer test the insertion.

Thanks,

Saravana Kumar

Ricardo Mendes - MSFT

Doug,

Can you pleasew share the DDL (CREATE TABLE) statement for the test tables you are using

Thanks,

Ricardo

MayurNagda

Hi Doug and Saravana

Finally i got the problem (may be ...... ) and the work around.............

before I was facing the problem in inserting into multiple table . but the n i was getting same error while inserting data in single table too ...

after observing the schema , i found that all fields giving error where having datatype character and all where just after decimal field .

I grouped all datatype and moved character fields before decimal fields and Guess what .....IT WORKED.........

Doug can u check the same .. i can see in ur schema there are lots of decimal fields.... u can try and tell me whether it works for u or not

i think BTS doesnt form the query properly .

i was trying to post frm many days but i was getting error while posting .... sry for the delay ..........

Thanks

Mayur

Doug Griffin

Hi Ricardo. I no longer have the test tables I was using as I switched to "one at a time" insert approach and moved on to other things. Our tables aren't created with SQL, but rather we use the Data Description Specification (DDS) language to create our physical files.

The DDS would look something like this for every table:

A UNIQUE
A R PSHP01A TEXT('SHIPEE INFO')
A ORDERN 6 COLHDG('ORDER' 'NUMBER')
A NAME 35 COLHDG('SHIP' 'IN' 'NAME' 'OF')
A LINENO 5S 0 COLHDG('LINE#')
A K ORDERN

I would like to know if my attempt to combine the schemas of many tables into one schema is a legitimate approach or if I was trying something inapproriate if you have any thoughts on that.