Originally posted by: SystemAdmin

I am using 6.7ver,and when i try to grab data from XML and insert into DB with sink adapter its nt working at all...on my friends system its working but on my system its nt at all working...we created ODBC and also connected to DB thru DFD...but when im writing the rule for DBQUERY in F_MAP everything going on succesfully but the data is nt getting inserted into DB Tables

What could be the possible reasons???

here is the Query im writing
=DBQUERY("INSERT INTO " Subject:ElemDecl Books:In1 "(BookName,Author) values ('"Title:ElemDecl Books:In1"','"Author:ElemDecl Books:In1"')",
" -MDQ db.mdq -DBNAME Samp -TRACE")
Is ther ne mistake in the query??

Looking forward for a fast reply

#IBMSterlingTransformationExtender
#DataExchange
#IBM-Websphere-Transformation-Extender

Originally posted by: janhess

Is it running the functional map? What does the database trace file say?
#DataExchange
#IBM-Websphere-Transformation-Extender
#IBMSterlingTransformationExtender

Originally posted by: SPG

What do you mean by "Inserting into DB" with sink adapter ?
Can you upload your map?
With TX "a map is worth thousand words"..
SPG
#IBM-Websphere-Transformation-Extender
#IBMSterlingTransformationExtender
#DataExchange

Originally posted by: SystemAdmin

Here is the Map u wanted SPG
#IBMSterlingTransformationExtender
#DataExchange
#IBM-Websphere-Transformation-Extender

Originally posted by: SystemAdmin

im sending the xml file...can u plz send me a map creating XML to DB....i will also upload TT,and access db...i attached rar file plz find it
#IBM-Websphere-Transformation-Extender
#DataExchange
#IBMSterlingTransformationExtender

Originally posted by: janhess

The map creates the query ok so the problem must be with the database connection.
#DataExchange
#IBMSterlingTransformationExtender
#IBM-Websphere-Transformation-Extender

Originally posted by: janhess

you are missing a space after INTO
=DBQUERY("INSERT INTO" + Subject:ElemDecl Books:In1+ "(BookName,Author) VALUES ('"Title:ElemDecl Books:In1"','"Author:ElemDecl Books:In1"')","-MDQ SampleMDQ.mdq -DBNAME SampleDB - TRACE")

should be

=DBQUERY("INSERT INTO " + Subject:ElemDecl Books:In1+ "(BookName,Author) VALUES ('"Title:ElemDecl Books:In1"','"Author:ElemDecl Books:In1"')","-MDQ SampleMDQ.mdq -DBNAME SampleDB - TRACE")
#DataExchange
#IBMSterlingTransformationExtender
#IBM-Websphere-Transformation-Extender

Originally posted by: janhess

you might need to remove the space between - TRACE

=DBQUERY("INSERT INTO " + Subject:ElemDecl Books:In1+ "(BookName,Author) VALUES ('"Title:ElemDecl Books:In1"','"Author:ElemDecl Books:In1"')","-MDQ SampleMDQ.mdq -DBNAME SampleDB -TRACE")
#DataExchange
#IBM-Websphere-Transformation-Extender
#IBMSterlingTransformationExtender

Originally posted by: janhess

You should really use the VALID FAIL functions round the DBQUERY

=valid(DBQUERY("INSERT INTO " + Subject:ElemDecl Books:In1+ "(BookName,Author) VALUES ('"Title:ElemDecl Books:In1"','"Author:ElemDecl Books:In1"')","-MDQ SampleMDQ.mdq -DBNAME SampleDB - TRACE"),fail( TEXT ( LASTERRORCODE ( ) ) + " " + LASTERRORMSG ( )))
#IBM-Websphere-Transformation-Extender
#DataExchange
#IBMSterlingTransformationExtender

Originally posted by: SystemAdmin

Hey its working fine now,after doing all what u guys said its working...the DB is able is able to update with the data

Can plz lemme know where exactly is the error???

Thanks a lot to u guys

#IBM-Websphere-Transformation-Extender
#DataExchange
#IBMSterlingTransformationExtender

Originally posted by: janhess

How can I tell you what the error was. I don't know what you changed.
#IBMSterlingTransformationExtender
#IBM-Websphere-Transformation-Extender
#DataExchange

Originally posted by: janhess

Does the map connect to the database? Can you connect to the database manually? What does the trace file say? Where are the type trees?
The rule looks ok but you need to provide more detail.
#IBM-Websphere-Transformation-Extender
#IBMSterlingTransformationExtender
#DataExchange

Originally posted by: SPG

Vamsy,
I would definitely suggest all of the things janhess has suggested earlier.
In addition, can you check if you are able to connect to the database using the mdq.
The simplest test for that is, open the mdq, go to the db you want to test connectivity and then double click on "Tables".
If a window comes up with all the tables in that db, your mdq is fine.
Else, figure out why the mdq can't connect (userid, pwd, connection string etc) and then try to run/debug your map.

HTH,
SPG
#DataExchange
#IBM-Websphere-Transformation-Extender
#IBMSterlingTransformationExtender

Originally posted by: SystemAdmin

i am able to get connected to the DB thru MDQ,im uploading the type trees now plz check them...ODBC connection is working fine and the only problem is that the data is not getting upload the map is successfully completed...after the RUN the db is same as old with empty feilds

Thanks for u all for replying to this problem..im attaching TT in a rar file plz find them i will try again with ur suggetions and send u feedback

Regards,
vamsi

NOte-Attched file of .rar TT and SOurce file
#IBM-Websphere-Transformation-Extender
#IBMSterlingTransformationExtender
#DataExchange

Originally posted by: SaiIBM

I got similar problem for my POC , 

DBQUERY("INSERT INTO " + Subject:ElemDecl Books:In1+ "(BookName,Author) VALUES ('"Title:ElemDecl Books:In1"','"Author:ElemDecl Books:In1"')","-MDQ SampleMDQ.mdq -DBNAME SampleDB -TRACE")

â€

in the above exmple my first field is of integer data type , but using blob item I am trying to insert a row into DB , causing fail of my map ( Idefined same map rule with VALID(PUT(""DB" , "INSERT INTO  Mytable ( F1 , F2 , F3)  VALUES ( NUMBERTOTEXT(Item1) , ITEM2 , DATETOTEXT(ITEM3) , FAIL( "DB INSERT FAIL") , dbl is saying data type mismatch for ITEM1 , I am trying to insert integer data type , into F1 ( DB fields defined as integer) , But using Blob casing the problem , I need to insert data into almost 30 different tables , instead of defining 30 cards ( one card/ Table) , data is huge , XML data with Burst mode , If I define RUN map killing I/O ( echoing data for each record) , is there any logic to insert my first Field into DB , Like that I need to insert date field also , Please share your thoughts , preferably I want to define my map independent of mdq ,  Appreciate your help 

#DataExchange
#IBMSterlingTransformationExtender
#IBM-Websphere-Transformation-Extender

Originally posted by: SaiIBM

full map rule here : 

VALID(PUT(""DB" , "INSERT INTO  Mytable ( F1 , F2 , F3)  VALUES ( NUMBERTOTEXT(Item1) , ITEM2 , DATETOTEXT(ITEM3) , "-MDQ SampleMDQ.mdq -DBNAME SampleDB -TRACE") , FAIL( "DB INSERT FAIL")  , 

 

#IBM-Websphere-Transformation-Extender
#IBMSterlingTransformationExtender
#DataExchange

Originally posted by: paul.brett

Should that read:

=VALID(PUT(""DB" , "INSERT INTO  Mytable ( F1 , F2 , F3)  VALUES ( "+NUMBERTOTEXT(Item1)+" , "+ITEM2+" , "+DATETOTEXT(ITEM3)+")" , "-MDQ SampleMDQ.mdq -DBNAME SampleDB -TRACE") , FAIL( "DB INSERT FAIL") 

...as you have to "step outside" of the query string to native WTX to use functions.

Thank-you.

Paul

Follow me on Twitter

#IBMSterlingTransformationExtender
#DataExchange
#IBM-Websphere-Transformation-Extender

Originally posted by: SaiIBM

PL , Thank you for your reply , I was little lazy to copy actual map rule from Map designer rule , Please check below

 

=VALID(PUT("DB" , "INSERT INTO BBGW_REF.ALT_BLNG_TRNS_TB " +

" (F1 , F2, F3, F4, F5, F6, F7, F8, F9,F10) VALUES ('" +NUMBERTOTEXT(va11:Row )+ "',

 '" + va12:Row +"',  '" +va13:Row +"', '" +va14:Row +"',

 '" +va15:Row +"', '" +va16:Row +"', '" +va17:Row +"',

 '" +va18:Row +"', '" +DATETOTEXT(va19:Row) +"', '" +DATETOTEXT(va110:Row +"' )" ,

 "-MDQ  My_TEST.mdq  -DBNAME tst -TRACE") , FAIL("FAILED TO INSERT ROW"))

 

Tried Below rule also :

=VALID(DBQUERY("INSERT INTO BBGW_REF.ALT_BLNG_TRNS_TB " +

" (F1 , F2, F3, F4, F5, F6, F7, F8, F9,F10 , F11) VALUES ('" +NUMBERTOTEXT(va11:Row )+ "',

 '" + va12:Row +"',  '" +va13:Row +"', '" +va14:Row +"',

 '" +va15:Row +"', '" +va16:Row +"', '" +va17:Row +"',

 '" +va18:Row +"', '" +va19:Row +"', '" +DATETOTEXT(va1_10:Row +"', '" +DATETOTEXT(va1_11:Row +"'  )" ,

 "-MDQ  My_TEST.mdq  -DBNAME tst -TRACE") , FAIL("FAILED TO INSERT ROW"))

DBL here : 

 

<5564-2728>: Validating the adapter command...

<5564-2728>: Database type is ODBC

<5564-276>:  Connecting...

<5564-276>:  Datasource : XYZ

<5564-276>:  UserID     : Db2_user

<5564-276>:  Password   : *****

<5564-276>:  DBQUERY Function, Transaction scope: Map

<5564-276>:  TRACE command specified, file: C:\Users\ADMIN\Desktop\Forum\DB_INSERT\Database.dbl

<5564-276>:  Connection to datasource Rida has been established.

<5564-276>:  Servername/alias:->

<5564-276>:  DB2/AIX64

<5564-276>:  Database  Version/Type:->

<5564-276>:  09.05.0006

<5564-276>:  Client Library/environment(ODBC DriverName):->

<5564-276>:  DB2CLI.DLL

<5564-276>:  Client Library version (ODBC DriverVersion):->

<5564-276>:  08.02.0009

<5564-276>:  Returned status: (0) Success

<5564-276>:  Interface library version 8.1(114)

<5564-276>:  Map: C:\Users\ADMIN\Desktop\Forum\DB_INSERT\Database.mmc,  Timestamp: 09/18/14 22:37:08.

<5564-276>:  Data being retrieved for DBQUERY function.

<5564-276>:  Database adapter: ODBC Version 8.1(114)

<5564-276>:  Starting a database unload...

<5564-276>:  Query      : INSERT INTO MYSCHEMA.MYTABLE  (F1, F2, F3, F4, F5, F6, F7, F8, F9, F10 , F11) 

VALUES ('21000000', ' Description',  '', '', '', '', '',  'Part Number', 'Supplier' , '2007-11-09 08:47:14.819617', '' )

<5564-276>:  DBQUERY Function, Transaction scope: Map

<5564-276>:  TRACE command specified, file: C:\Users\ADMIN\Desktop\Forum\DB_INSERT\Database.dbl

<5564-276>:  Retrieving 1 rows per fetch.

<5564-276>:  Error in SQLExecDirect

Message: [IBM][CLI Driver][DB2/AIX64] SQL0408N  A value is not compatible with the data type of its assignment target.  Target name is "F1".  SQLSTATE=42821

 

SQL State: 22005

<5564-276>:  Returned status: (-1009) Failed_to_execute_the_SQL_statement

<5564-276>:  Cleaning up and closing the transaction...

<5564-276>:  Transaction rollback succeeded.

<5564-276>:  Returned status: (0) Success

as per my understanding , I am trying to insert Text value from NUMBERTOTEXT(F1) into Ist row of my Table if data type Integer , throwing this error , How can I get rid of this , Appreciate your help Thank you

#IBM-Websphere-Transformation-Extender
#DataExchange
#IBMSterlingTransformationExtender

Originally posted by: paul.brett

The trace shows:

INSERT INTO MYSCHEMA.MYTABLE  (F1, F2, F3, F4, F5, F6, F7, F8, F9, F10 , F11) VALUES ('21000000', ' Description',  '', '', '', '', '',  'Part Number', 'Supplier' , '2007-11-09 08:47:14.819617', '' )

Can this query be executed outside of WTX, in your usual SQL client?

If yes, then I suspect a bug, and I urge you to raise a Service Request (PMR).

Thank-you.

Paul

Follow me on Twitter

#DataExchange
#IBM-Websphere-Transformation-Extender
#IBMSterlingTransformationExtender

Originally posted by: SaiIBM

PL -

       Thank you for your inputs , I found some syntactic error , in my statement , for some my reason I missed debugging techinique you suggested , your reply made me to rethink on this , Please check below 

INSERT INTO MYSCHEMA.MYTABLE  (F1, F2, F3, F4, F5, F6, F7, F8, F9, F10 , F11) VALUES ('21000000', ' Description',  '', '', '', '', '',  'Part Number', 'Supplier' , '2007-11-09 08:47:14.819617', '' )

I  removed single quote from passing values query , 

Ex : VALUES (' "  +NUMBERTOTEXT(va11:Row )+ " ',

New value as : ( "  +NUMBERTOTEXT(va11:Row )+ " ,

It is working now , Thanks much for your inputs , Good day‹

#IBM-Websphere-Transformation-Extender
#IBMSterlingTransformationExtender
#DataExchange