QUESTION abt "SQL_DML" of the Receiver JDBC Adapter

[brian_luo]

HI ALL,

i hav a doubt abt "SQL_DML" of the Receiver JDBC Adapter.

i wanna use two SQL Statements in the "SQL_DML" structure to manipulate two tables.

i.e.

<root>

<stmt>

<Customers action="SQL_DML">

<access>

INSERT INTO Customers VALUES (u2019$NAME$u2019, u2019$ADDRESS$', '$KEYFIELD$u2019);

INSERT INTO Companys VALUES('$NAME$')

</access>

<key>

<NAME>Company</NAME>

<ADDRESS>Street 3 </ADDRESS>

<KEYFIELD>CO</KEYFIELD>

</key>

</Customers>

</stmt>

</root>

BUT i cant ascertain if it permit like this writting above ??

Requset help in this regards.

Thx in advance.

brian

[Former Member · Accepted solution]

Hi,

You need to have two different statements. Each for one insert query. Your query will come under access tag and palceholders under kay tag. Action will remain same as you have given, but under both statements.

Ex:

<root>

<stmt1>

<Customers action="SQL_DML">

<access>

INSERT INTO Customers VALUES (u2019$NAME$u2019, u2019$ADDRESS$', '$KEYFIELD$u2019);

</access>

<key>

<NAME>Company</NAME>

<ADDRESS>Street 3 </ADDRESS>

<KEYFIELD>CO</KEYFIELD>

</key>

</Customers>

</stmt1>

<stmt2>

<Customers action="SQL_DML">

<access>

INSERT INTO Companys VALUES('$NAME$')

</access>

<key>

<NAME>Company</NAME>

</key>

</Customers>

</stmt2>

</root>

Hope this helps.

Regards,

Siddhesh S.Tawate

Edited by: siddhesh tawate on Apr 24, 2009 8:10 AM

[brian_luo · Accepted solution]

oh,thx.

but i dont know how to do for getting two different statements like u gave above.

[Former Member · Accepted solution]

Hi,

In your target structure, make two statements under one root tag. Then do mapping accordingly. This will essentailly trigger two insert queries on the respectice tables.

Regards,

Siddhesh S.Tawate

[brian_luo · Accepted solution]

sorry,im configuring in the Enterprise Service Builder.

but i now also dont know how to map a record from a database to the target message type 4 getting the structure that u mention.

i dont think one record can map to target message type that contain two <access> tag(i think u mean i should respectively put the two sql stmt into the two <access> tag).

maybe my means is wrong.pls detailedly tell the method.

thx very much.

[brian_luo · Accepted solution]

Hi,Siddhesh

so sorry, hah,now i understand what u said.

but i wanna know if i can use the way below?

<root>

<StatementName>

<dbTableName action=u201DSQL_DMLu201D>

<access>

INSERT INTO Customers VALUES (u2019$NAME$u2019, u2019$ADDRESS$', '$KEYFIELD$u2019)

</access>

<access>

INSERT INTO Companys VALUES('$NAME$')

</access>

<key>

<NAME>Company</NAME>

<ADDRESS>Street 3 </ADDRESS>

<KEYFIELD>CO</KEYFIELD>

</key>

</dbTableName>

</StatementName>

</root>

thx 4 ur patience.

brian.law

[Former Member · Accepted solution]

No, I dont think so.....

Because in case of SQL_DML/SQL_QUERY you can have exactly one access tag.

Maintain two different statements as described above and that shouls serve your purpose.

Please check below points as well.. this might be helpful:

*************

action= SQL_QUERY | SQL_DML This structure enables you to transfer more complex SQL statements to the database directly using the adapter. You have the option of using placeholders in these SQL statements, which can be listed in the subsequent keyblock. This makes it easy to generate complex, parameterisable SQL statements.

Details on the structure:

○ The name of the structure is arbitrary. Unlike in the usual statement types, no table name or stored procedure name is expected in the default setting.

○ If the SQL statement represents a query to the database (SELECT), choose Action=SQL_QUERY.

○ If it represents a call from the SQL Data Manipulation Language (UPDATE, INSERT, DELETE), choose Action=SQL_DML.

○ The first element in the structure must have the name <access> and contain a valid SQL call for the respective mode, optionally with placeholders (see below).

○ If you use placeholders, these must be listed in the element with the name <key>. The names of the placeholder elements must be identical to those used in the SQL string (where they still have the $ character). In the above example <StatementName6>, the strings $placeholder1$ and $placeholder2$ contained in the SQL string are replaced with value1 or value2 before the SQL statement is executed.

○ If you are not using placeholders, then the <key>block can be omitted or left empty. In both cases, you must not select the Key Tags Mandatory field in the configuration, as this will cause runtime errors.

*************

Regards,

Siddhesh S.Tawate

[brian_luo · Accepted solution]

hi Siddhesh,

i will do followwin ur advice.

thx 4 ur answer.

rgds,

brian law.

[Former Member]

Hi,

I think this can be done by stored procedure.

Check this,

[http://help.sap.com/saphelp_nw2004s/helpdata/en/2e/96fd3f2d14e869e10000000a155106/content.htm]

Ravi.