JDBC Sender and Receiver

[Former Member]

Hello:

I have an scenario FILE-to-FILE and it's working ok. So now I want to change it into FILE-to-JDBC so I thought just to change the Communication Channel RECEIVER, instead of file, use JDBC adapter.

I've read this blog <a href="/people/sap.user72/blog/2005/06/01/file-to-jdbc-adapter-using-sap-xi-30 to JDBC Adapter using SAP XI 3.0</a> but I don't have clear where tu place the SQL Insert statement, or to know which table to affect. I am using this settings on the RECEIVER communication channel:

Adapter type: JDBC

Transfer Protocol : JDBC 2.0

Message Protocol : XML SQL Format

-- Database Connection --

JDBC Driver : net.sourceforge.jtds.jdbc.Driver

Connection: jdbc:jtds:sybase://alma.mex.sap.corp:2638/demo

I've noticed thta if the Communication channel is SENDER, I do get a parameter called Update SQL Statement, but it's not there when it's a RECEIVER, why is that?

Greetings!

Alejandro

[Former Member · Accepted solution]

Hi Alex,

For the receiver JDBC you set the insert or update in the Integration repository .Please note that in such case you define your 'Data Type' as directed by SAP-XI documentaion and apply 'INSERT' or 'UPDATE' there itself.

That is also the reson why you will not find this parameter in Communication channel of RECEIVER type

Check this URL for JDBC

http://help.sap.com/saphelp_erp2005vp/helpdata/en/22/b4d13b633f7748b4d34f3191529946/frameset.htm

and

http://help.sap.com/saphelp_erp2005vp/helpdata/en/22/b4d13b633f7748b4d34f3191529946/frameset.htm

Regards

Bipin Joshi

[Former Member · Accepted solution]

Thanks for the info

Reading the help site, I find that the xml message must have a certain structure, that will be interpreted by the database. If I want to send an INSERT, I would have an xml message like this:

[code]

<root>

<stmt>

<Customers action="SQL_DML">

<access> UPDATE Customers SET CompanyName=’$NAME$’, Address=’$ADDRESS$' WHERE CustomerID='$KEYFIELD$’

</access>

<key>

<NAME>Company</NAME>

<ADDRESS>Street 3 </ADDRESS>

<KEYFIELD>CO</KEYFIELD>

</key>

</Customers>

</stmt>

</root>

[/code]

And as I understand I <b>must define those fields</b> on my data type XSD so the mesagge from the sender arrives with this structure right?

so I created an XSD like this

[code]

<?xml version="1.0" encoding="ISO-8859-1"?>

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="urn://alex.com/ftp001" targetNamespace="urn://alex.com/ftp001">

<xsd:complexType name="JDBC_DATA_TYPE_001">

<xsd:sequence>

<xsd:element name="root">

<xsd:annotation>

<xsd:appinfo source="http://sap.com/xi/TextID">

89e2af209ce511dbaf580015587b9f42

</xsd:appinfo>

</xsd:annotation>

<xsd:complexType>

<xsd:sequence>

<xsd:element name="smtp">

<xsd:annotation>

<xsd:appinfo source="http://sap.com/xi/TextID">

89e520209ce511dbc9230015587b9f42

</xsd:appinfo>

</xsd:annotation>

<xsd:complexType>

<xsd:sequence>

<xsd:element name="Person">

<xsd:annotation>

<xsd:appinfo source="http://sap.com/xi/TextID">

89e520219ce511db832b0015587b9f42

</xsd:appinfo>

</xsd:annotation>

<xsd:complexType>

<xsd:sequence>

<xsd:element name="access" type="xsd:string">

<xsd:annotation>

<xsd:appinfo source="http://sap.com/xi/TextID">

89e520239ce511db99c60015587b9f42

</xsd:appinfo>

</xsd:annotation>

</xsd:element>

<xsd:element name="key">

<xsd:annotation>

<xsd:appinfo source="http://sap.com/xi/TextID">

89e520249ce511db95d80015587b9f42

</xsd:appinfo>

</xsd:annotation>

<xsd:complexType>

<xsd:sequence>

<xsd:element name="FirstName" type="xsd:string">

<xsd:annotation>

<xsd:appinfo source="http://sap.com/xi/TextID">

89e520259ce511db81520015587b9f42

</xsd:appinfo>

</xsd:annotation>

</xsd:element>

<xsd:element name="LastName" type="xsd:string">

<xsd:annotation>

<xsd:appinfo source="http://sap.com/xi/TextID">

89e520269ce511dba6d30015587b9f42

</xsd:appinfo>

</xsd:annotation>

</xsd:element>

<xsd:element name="Age" type="xsd:string">

<xsd:annotation>

<xsd:appinfo source="http://sap.com/xi/TextID">

89e520279ce511dbc9070015587b9f42

</xsd:appinfo>

</xsd:annotation>

</xsd:element>

</xsd:sequence>

</xsd:complexType>

</xsd:element>

</xsd:sequence>

<xsd:attribute name="action" type="xsd:string">

<xsd:annotation>

<xsd:appinfo source="http://sap.com/xi/TextID">

89e520229ce511db82120015587b9f42

</xsd:appinfo>

</xsd:annotation>

</xsd:attribute>

</xsd:complexType>

</xsd:element>

</xsd:sequence>

</xsd:complexType>

</xsd:element>

</xsd:sequence>

</xsd:complexType>

</xsd:element>

</xsd:sequence>

</xsd:complexType>

</xsd:schema>

and I must send the SQL Query statement in the ACCESS node.

Right?

Thanks

Alejandro

[/code]

[Former Member · Accepted solution]

Hi -

<i>>>>And as I understand I must define those fields on my data type XSD so the mesagge from the sender arrives with this structure right?</i>

Yes, your target message type must be defined this way. Since your scenario is File --> JDBC, you'll need to map your file-xml to the sql-jdbc-xml so that the receiver jdbc adapter can understand the request and make a jdbc call to the database.

The XSD you posted needs a slight adjustment so the message can be found when you pull it into the graphical mapping. I've pasted it below. Also, if you or no one else on your team hasn't done a jdbc scenario before, you'll need to install the appropriate database specific jdbc drivers for your scenario to work. See this <a href="https://www.sdn.sap.comhttp://www.sdn.sap.comhttp://www.sdn.sap.com/irj/servlet/prt/portal/prtroot/docs/library/uuid/3867a582-0401-0010-6cbf-9644e49f1a10">how-to guide</a> for this.

<?xml version="1.0" encoding="ISO-8859-1"?>

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="urn://alex.com/ftp001" targetNamespace="urn://alex.com/ftp001">

<xsd:element name="root" type="JDBC_DATA_TYPE_001"/>

<xsd:complexType name="JDBC_DATA_TYPE_001">

<xsd:sequence>

<xsd:element name="smtp">

<xsd:annotation>

<xsd:appinfo source="http://sap.com/xi/TextID">

89e520209ce511dbc9230015587b9f42

</xsd:appinfo>

</xsd:annotation>

<xsd:complexType>

<xsd:sequence>

<xsd:element name="Person">

<xsd:annotation>

<xsd:appinfo source="http://sap.com/xi/TextID">

89e520219ce511db832b0015587b9f42

</xsd:appinfo>

</xsd:annotation>

<xsd:complexType>

<xsd:sequence>

<xsd:element name="access" type="xsd:string">

<xsd:annotation>

<xsd:appinfo source="http://sap.com/xi/TextID">

89e520239ce511db99c60015587b9f42

</xsd:appinfo>

</xsd:annotation>

</xsd:element>

<xsd:element name="key">

<xsd:annotation>

<xsd:appinfo source="http://sap.com/xi/TextID">

89e520249ce511db95d80015587b9f42

</xsd:appinfo>

</xsd:annotation>

<xsd:complexType>

<xsd:sequence>

<xsd:element name="FirstName" type="xsd:string">

<xsd:annotation>

<xsd:appinfo source="http://sap.com/xi/TextID">

89e520259ce511db81520015587b9f42

</xsd:appinfo>

</xsd:annotation>

</xsd:element>

<xsd:element name="LastName" type="xsd:string">

<xsd:annotation>

<xsd:appinfo source="http://sap.com/xi/TextID">

89e520269ce511dba6d30015587b9f42

</xsd:appinfo>

</xsd:annotation>

</xsd:element>

<xsd:element name="Age" type="xsd:string">

<xsd:annotation>

<xsd:appinfo source="http://sap.com/xi/TextID">

89e520279ce511dbc9070015587b9f42

</xsd:appinfo>

</xsd:annotation>

</xsd:element>

</xsd:sequence>

</xsd:complexType>

</xsd:element>

</xsd:sequence>

<xsd:attribute name="action" type="xsd:string">

<xsd:annotation>

<xsd:appinfo source="http://sap.com/xi/TextID">

89e520229ce511db82120015587b9f42

</xsd:appinfo>

</xsd:annotation>

</xsd:attribute>

</xsd:complexType>

</xsd:element>

</xsd:sequence>

</xsd:complexType>

</xsd:element>

</xsd:sequence>

</xsd:complexType>

</xsd:schema>

[Former Member · Accepted solution]

Hello Jin

Thanks a lot, I see that you removed the root element. I'll try to send the sql statements there.

I have already deployed the right JDBC driver and created a program in Java that uses it so I know that the driver and the SQL statement I'll be sending work. So now I need to created the right messages on XI

Thanks a lot

Alejandro

[Former Member · Accepted solution]

Hi Alejandro -

<i>>>>...I see that you removed the root element...</i>

Actually I didn't "remove" it, just moved it so that it's visible as the root tag of the message. Maybe you meant the same thing.

Cheers.

Jin

[Former Member]

Hi Alejandro,

Plz check the following blog, it may usefull:

/people/bhavesh.kantilal/blog/2006/07/03/jdbc-receiver-adapter--synchronous-select-150-step-by-step

regards