File to JDBC Sync error

[Former Member]

Hi All,

I am doing File to JDBC sync mode. I have configured the scenario while trying to execute i am getting error in JDBC communication chennal.

I have create the stucture with help of this link

http://help.sap.com/saphelp_nw04/Helpdata/EN/2e/96fd3f2d14e869e10000000a155106/content.htm

Error while parsing or executing XML-SQL document: Error processing request in sax parser: Error when executing statement for table/stored proc. '<STATEMENT> <FND_VALUES_VLaction="SQL_QUERY"> <table>FND_VALUES_VL</table> <access>SELECT BOOKS_ID FROM FND_VALUES_VL</access><Key> <BOOKS_ID>6</BOOKS_ID><KEYFIELD>6</KEYFIELD></Key></FND_FLEX_VALUES_VL></STATEMENT> ' (structure 'STATMENT'): java.sql.SQLException: SQL statement to execute cannot be empty or null

This statement i am passing in Table and also in action attribute.

I have created the MT_JDBC_req like this:

<Statment>

<TABLENAME>

<ACTION> attribute JUST I AM PASSING like this "SQL_QUERY" not XML for this

<TABLE> i am passing the above query

<ACCESS> i am passing the same query

<FILED1>

<FILELD2>

<KEY>

<FILED1>

Thanks,

Venkat.

[Former Member · Accepted solution]

Hi,

go throught the link again which you have said..........i think your JDBC structure is not okay because if you are using SQL_QUERY, then you should not have table node in it........create JDBC structure as:

<Statment>

<MSG>

<ACTION>SQL_QUERY</ACTION>

<ACCESS>write query</ACCESS>

</MSG>

</Statment>

if you want to send some parameter in your SQL query, then send those data in KEY node.

Regards,

Rajeev Gupta

Edited by: RAJEEV GUPTA on Feb 26, 2009 11:31 AM

[Former Member · Accepted solution]

Thanks Rajeev Gupta for your quick reply,

As per you suggestion will create the like this

<StatementName>

<TableName>

<action>

<access>

ok in the link SAP exapled in this format

<StatementName6>

<anyName action=u201D SQL_QUERYu201D | u201CSQL_DMLu201D>

<access>SQL-String with optional placeholder(s)</access>

<key>

<placeholder1>value1</placeholder1>

<placeholder2>value2<placeholder2>

</key>

</anyName >

</StatementName6>

Now i will re-create the Datatype again will check. I have 1 doubt here i need to select data from 2 tables, could you pls let me know how i can pass 2 tables at that time. In sql statment we can write the nested query but do we need to pass the table names for same.

Thanks,

Venkat.

[Former Member · Accepted solution]

Hi,

you do not need to specify the two tablenames explicitly..........just specify a proper SQL qurey having the fields, tables and the join condition of the two tables.

Regards,

Rajeev Gupta

[Former Member · Accepted solution]

as advised by Prateek and you i have chaged the Datatype.

DT_JDBC_Req

<Statment>

<TableName> sub element

<Action>

<Access>sub element

I have chaged in MT and redo the mapping and tried to execute the scenario.

even now also i am getting error

Message processing failed. Cause: com.sap.aii.af.ra.ms.api.RecoverableException: Error processing request in sax parser: Error when executing statement for table/stored proc. 'TABLENAME' (structure 'STATMENT'): java.sql.SQLException: SQL statement to execute cannot be empty or null

Error while parsing or executing XML-SQL document: Error processing request in sax parser: Error when executing statement for table/stored proc. 'TABLENAME' (structure 'STATMENT'): java.sql.SQLException: SQL statement to execute cannot be empty or null

this is the SQL i have given:

<STATMENT>

<FND_FLEX_VALUES_VL action="SQL_QUERY">

<table>FND_VALUES_VL</table>

<access>SELECT BOOKS_ID FROM FND_VALUES_VL where BOOKS_ID=6</access>

</FND_VALUES_VL>

</STATMENT>

This one i have saved as xml and opend in IE and XML is showing with out any error.

Could you pls help me.

Thanks,

Venkat

[former_member206760 · Accepted solution]

Dear Venakt,

create the structure as

statement

+node1(any name)

++action

++access

+key

++bookid

in access -> write the entire select qry like select...from abc where bookid = '$bookid$'.

note the $ sign this will bring the enties from table abc where bookid equal to value in target structure book id field

In Action : write SQL_Query.

nothing else need to be done.

This has worked for me

Giving points is another way to say thanks

[Former Member · Accepted solution]

Hi,

this is the SQL i have given:

<STATMENT>

<FND_FLEX_VALUES_VL action="SQL_QUERY">

<table>FND_VALUES_VL</table>

<access>SELECT BOOKS_ID FROM FND_VALUES_VL where BOOKS_ID=6</access>

</FND_VALUES_VL>

</STATMENT> - Can you tell me why table node is still there in FND_VALUES_VL in STATMENT with value FND_VALUES_VL - Please remove this table node...........

Please check in target database that on running your query you are getting any record or not.......

Regards,

Rajeev Gupta

[Former Member · Accepted solution]

Hi,

I have removed the table tag and chaged my Datatype and Message Type and after that i have done the mapping.

data types i have created like this

DT_jdbc_req

+statment 0.unbound sub element

++anyName 1.1 sub element under statment

action requred

access 1.1

<STATMENT>

SELECT BOOKS_ID FROM FND_VALUES_VL where BOOKS_ID=6

</STATMENT>

still the error like this

Error while parsing or executing XML-SQL document: Error processing request in sax parser: Error when executing statement for table/stored proc. 'anyName' (structure 'STATMENT'): java.sql.SQLException: ORA-00900: invalid SQL statement

Message processing failed. Cause: com.sap.aii.af.ra.ms.api.RecoverableException: Error processing request in sax parser: Error when executing statement for table/stored proc. 'anyName' (structure 'STATMENT'): java.sql.SQLException: ORA-00900: invalid SQL statement

But the SQL statment is running while i am running through SQL Developer and getting records.

pls suggest me on this.

and one more thing i have obvserved my file adaptor also shoing some error

Error: com.sap.aii.af.ra.ms.api.DeliveryException: XIAdapterFramework:GENERAL:com.sap.aii.af.ra.ms.api.DeliveryException

for File sync mode i am using the help this link FiletoRFCtoFile

https://www.sdn.sap.com/irj/scn/wiki?path=/display/xi/file-rfc-file%2528without+bpm%2529

i am using SP 22 and XI 3.0

Thanks,

Venkat

[former_member206760 · Accepted solution]

dear venkat ,

you have still not developed the node "key" and inside that field "book_id" and then you can mapp constant 6 to book_id and then us the query with $ as i have specified above.

giving points is another way to say thanks

[Former Member · Accepted solution]

Hi,

I have chaged the DT and redo the mapping. Now in my JDBC receiver adaptor i am seeing success msg

2/27/09 12:47:01 AM 0f749d66-91be-443f-242d-9511764fcad6 Message processing completed successfully

2/27/09 12:47:01 AM 0f749d66-91be-443f-242d-9511764fcad6 Message with ID 0f749d66-91be-443f-242d-9511764fcad6 processed

2/27/09 12:47:01 AM 0f749d66-91be-443f-242d-9511764fcad6 Message processing started

but now when i check the sxmb_moni i am getting like this

<?xml version="1.0" encoding="utf-8" ?>

<ns0:MT_B_JDBC_REQ_response xmlns:ns0="http://test-xi.com/oraclegl/bcgl" />

there is no response through the query and also there is error in File sender adaptor

Error: com.sap.aii.af.service.util.transaction.api.TxManagerException: Unable to roll back transaction: com.sap.engine.services.ts.exceptions.BaseIllegalStateException

In file sender adaptor i have used the

1 AF_Modules/RequestResponseBean 1

2 CallSapAdapter 2

3 AF_Modules/ResponseOnewayBean 3

1 passThrough true

3 receiverChannel myreceiverChennalname

3 receiverService myreceiverchennalservice

Thanks,

Venkat

[Former Member · Accepted solution]

Hi Gurus,

Can any suggest me where i am doing wrong this scenario. some time i am getting sender file adaptor red and saying that target folder does exists. target folder is existed in FTP and have checked the spellings for folders. but file is picking up but its not deleting after that JDBC receiver adaptor is triggering but no data.

Thanks,

Venkat.

[Former Member · Accepted solution]

Hi,

Still i am getting error in File Adaptor

Returning synchronous error notification to calling application: com.sap.aii.af.ra.ms.api.DeliveryException: XIAdapterFramework:GENERAL:com.sap.aii.af.ra.ms.api.DeliveryException.

Transmitting the message using connection http://SAPRtest.com:8142/sap/xi/engine?type=entry failed, due to: com.sap.aii.af.ra.ms.api.DeliveryException: XIAdapterFramework:GENERAL:com.sap.aii.af.ra.ms.api.DeliveryException.

Attempt to process file failed with com.sap.aii.af.service.util.transaction.api.TxManagerException: Unable to roll back transaction: com.sap.engine.services.ts.exceptions.BaseIllegalStateException

i amm getting error in file sender adaptor. I have used the Modules as suggested in FILE2RFC2FILE

Thanks,

Venkat

[former_member206760 · Accepted solution]

Have u defined the proper response structure to catch the response that you are getting from the database table...

the target interface that you have created ..make it as synchronous

and specify a response message type there......suppose your request message type is MT_A

response message type should be MT_A_response

+row

+field1

+field2

[Former Member · Accepted solution]

Hi,

I have done the same thing. my response i have created like MT_JDBC_REQ_response

i can see in sxmb_moni from file to JDBC request its going jdbc response i am not getting.

Error: com.sap.aii.af.ra.ms.api.DeliveryException: XIAdapterFramework:GENERAL:com.sap.aii.af.ra.ms.api.DeliveryException

Error: com.sap.aii.af.service.util.transaction.api.TxManagerException: Unable to roll back transaction: com.sap.engine.services.ts.exceptions.BaseIllegalStateException

again now in JDBC reciever also i am getting error.

Error while parsing or executing XML-SQL document: Error processing request in sax parser: Error when executing statement for table/stored proc.

in moni response msg its showing like this

<?xml version="1.0" encoding="UTF-8" standalone="yes" ?>

- <!-- Call Adapter

-->

- <SAP:Error xmlns:SAP="http://sap.com/xi/XI/Message/30" xmlns:SOAP="http://schemas.xmlsoap.org/soap/envelope/" SOAP:mustUnderstand="1">

<SAP:Category>XIAdapterFramework</SAP:Category>

<SAP:Code area="MESSAGE">GENERAL</SAP:Code>

<SAP:P1 />

<SAP:P2 />

<SAP:P3 />

<SAP:P4 />

<SAP:AdditionalText>com.sap.aii.af.ra.ms.api.DeliveryException</SAP:AdditionalText>

<SAP:ApplicationFaultMessage namespace="" />

<SAP:Stack />

<SAP:Retry>M</SAP:Retry>

</SAP:Error>

could you pls help me where i am doing wrong.

Thanks,

Venkat

[Former Member · Accepted solution]

Hi Tarang Shah , Rajiv,

Thanks for your help, now my porblem solved i am getting the JDBC response data into file.

Thanks,

Venkat

[Former Member · Accepted solution]

hi

how do you solve the problem?

could you share me your way

thank you

Edited by: ALANWANG on May 14, 2009 10:49 AM

[Former Member · Accepted solution]

hi

so much need your help .

the same thing happened to my scenario (com.sap.aii.af.ra.ms.api.DeliveryException)

thank you

[prateek]

The structure should be

<Root>

<STATEMENT>

<FND_VALUES_VL>

<@action> // Here action is attribute of the above node

<access> Your Query </access>

</FND_VALUES_VL>

</STATEMENT>

</Root>

Regards,

Prateek