JDBC Receiver Document Format

[Former Member]

Hi,

I have to pull the data from DB.

The SQL Query is:

Select field1

from systemName.table

where field2 = field1

order by field1.

Kindly suggest the way to define the data type to realize the SQL query on receiver side.

Regards

Piyush

[Former Member · Accepted solution]

Hi Piyush,

Import this .xsd as external definition and proceed.

<xsd:schema elementFormDefault="qualified" xmlns:xsd="http://www.w3.org/2001/XMLSchema">

<xsd:element name="Receiver_JDBC" type="Statement1"/>

<xsd:complexType name="Statement1">

<xsd:sequence>

<xsd:element name="statement1">

<xsd:complexType>

<xsd:sequence>

<xsd:element name="Vishal">

<xsd:complexType>

<xsd:sequence>

<xsd:element name="access"/>

<xsd:element name="key">

<xsd:complexType>

<xsd:sequence></xsd:sequence>

</xsd:complexType>

</xsd:element>

</xsd:sequence>

<xsd:attribute name="action">

<xsd:simpleType>

<xsd:restriction base="xsd:string">

<xsd:pattern value="SQL_QUERY|SQL_DML"/>

</xsd:restriction>

</xsd:simpleType>

</xsd:attribute>

</xsd:complexType>

</xsd:element>

</xsd:sequence>

</xsd:complexType>

</xsd:element>

</xsd:sequence>

</xsd:complexType>

</xsd:schema>

You can generate this .xsd using xml spy or any other xml tool.

Thanks and Regards

Vishal Kumar

[udo_martens]

Hi Pijush,

you can use of course action "SQL_QUERY" or "SQL_DML", but you loose nearly complete the dynamic of your request.

I would recommend to use action "SELECT" like describe in <a href="http://help.sap.com/saphelp_nw04/helpdata/en/2e/96fd3f2d14e869e10000000a155106/content.htm">library</a>. The problem is the clause "order by", what cant be solved by this way in JDBC adapter, but easy with an (additional) XSLT mapping.

Use element xsl:sort as child from xsl:apply-templates or xsl:for-each. Use,if nessecary, attribute order with values "ascending" / "descending". Use may be attribute data-type with values "number"/"text".

Regards,

Udo

[Former Member]

Hi Udo,

I have executed the quey using sql_query, but the thing is i need to do dynamically.

JDBC adapter supports table name, action, access, key, compare operation.

Does it support more tags than this then let me know.

Regards

Piyush.

[udo_martens]

Piyush,

are you joking with me? Why do you send me questions which i just have answered - you did the same a few days ago?

>I have executed the quey using sql_query, but the thing is i need to do dynamically.

I just recommended you to avoid SQL_QUERY to be dynamic!

>JDBC adapter supports table name, action, access, key, compare operation.

Does it support more tags than this then let me know.

I just told you that there is NO more tag, but you can solve your task with xsl:sort!

If it was a joke, my view of good jokes is little bit different

Regards,

Udo

[MichalKrawczyk]

hi,

maybe you can use action= SQL_QUERY | SQL_DML ?

something like:

<root>

<stmt>

<systemName action="SQL_DML">

<access> Select field1 from systemName where field2=’$field1$’ order by field1

</access>

<key>

<field1>value</field1>

</key>

</systemName>

</stmt>

</root>

I haven't tried that but you can give it a try

if not you can always try with a stored procedure

Regards,

michal