How to use Native SQL String

[Former Member]

Hi all,

How do i use Native SQL String in the Reciver JDBC Adapter.

Do i need to change the message format could u suggest me some blogs on the same.

Also please can anyone let me knw if i can use this for stored procedure.

[Former Member]

Thnks

[Former Member]

Will there be any Message Interface in the target side as well as message type.If yes then wht should i select in it.

Rgds

Aditya

[prateek]

No specific structure.

It could be a Message Type with only 1 field of type string into which whole SQL command is passed.

Regards,

Prateek

[bhavesh_kantilal]

actually if you use Graphical Mapping the output is going to be XML and this is not what Native SQL expects. My understading here is that you can use any message type but you need to use Java mapping as the mapping output has to be a single sql string and this is possible only with Java Mapping.

regards

Bhavesh

[Former Member]

So if I have understood it correctly, for my scenario, i.e SAP (Proxy) to JDBC, I will first have a mappinf that would create the exact SQl statement as expected by DB, then I will have to use Java Mapping to put in a single string, i.e without XML.

But this java mapping would be in Adapter Module or second step of mapping in Interface mapping?

Even if there is a single field in the target structure, still there be a root xml tag around the sql statement that I generate...

Regards,

XIer

[Former Member]

hi aditya,

selecting native sql as msg protocol means u have to pass the sql statement as message content. this statement will be transferred to the database for processing without unalteration.

for msg protocol xml sql format the sql statement is formed from the structure unlike native sql format.

yes u can use it for stored procedures.

regards,

latika.

[Former Member]

so wht should the format be for Native SQL String

i mean the structure, do i need to change the structure

or wil it automatically do the change

[bhavesh_kantilal]

I have not given this a try but if my Understanding is correct, there is no Strucutre as such.

The output of your mapping has to be the SQL Statement to be invoked on the Database. The output of the mappinghence has to be non XML and you might have to use a Java mapping.

Why do you want to use Native SQL String though?

Regards

Bhavesh

[prabhu_s2]

follow the link and as pointed out the declaration needs to be like in xml format. this ensure the execution of the native sql commands.

http://help.sap.com/saphelp_nw70/helpdata/en/2e/96fd3f2d14e869e10000000a155106/content.htm

[bhavesh_kantilal]

Hi Prabhu,

the output should be in the XML format only when the Message Protocol is XML-SQL Format in the receiver JDBC adapter.

When the message protocol is Native SQL String, the output of the mapping is passed directly to the database. And so the output has to be your SQL statement.

Regards

Bhavesh

[Former Member]

hi aditya,

there shud be no format as such. for sql xml format there are specific structure. but for native sql there shudnt be any specific structure.

as pointed in sap documentaion:

"

Instead of an XML document format, a text is expected that represents any valid SQL statement.

When inserting a line into a table the corresponding document looks as follows:

INSERT INTO tableName (column-name1, column-name2, column-name3) VALUES(‘column-value1’, ‘column-value2’, ‘column-value3’)

“

so jus make sure that u give a valid sql statement becoz if will be passed as it is to the database and try ur scenario.

regards,

latika.

[prabhu_s2]

shhhh.....sorry for the confusion. i was so blank and i was not referring to the message protocol option in CC. Instead i was refering on how to make use of native sql in xml strucutre. I do agree on the point of message protocol (in CC) refering to Native SQL string

[bhavesh_kantilal]

happens to all of us

Regards

Bhavesh

[prabhu_s2]

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