on 01-27-2011 12:18 PM
Hi @all,
at the moment, I try to generate an JDBC request-message (message type) via integration designer of sap xi 3.0.
The target is to implement an datebase lookup with the jdbc adapter.
Here the SQL-Statment to represent:
select * from <table> where to_char(LASTMODIFIED, 'yyyy-mm-dd') between '2011-01-10' and '2011-01-16''
Well, the problem is to image the sql-statment on a jdbc request-message.
I don't know how can I represent an sql-function and the placeholder for date1 and date2 in the request message.
I hope anybody can help!
Best regards
Alex
You can modify the query and try
<root>
<stmt>
<lookup action="SQL_QUERY">
<access> select * from <table> where to_char(LASTMODIFIED, $DATEFORMAT$) between $DATE1$ and $DATE2$
</access>
<key>
<DATEFORMAT>yyyy-mm-dd</DATEFORMAT>
<DATE1>2011-01-10</DATE1>
<DATE2>2011-01-16</DATE2>
</key>
</lookup>
</stmt>
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
>
> Hi @all,
>
> at the moment, I try to generate an JDBC request-message (message type) via integration designer of sap xi 3.0.
>
> The target is to implement an datebase lookup with the jdbc adapter.
>
> Here the SQL-Statment to represent:
>
> select * from <table> where to_char(LASTMODIFIED, 'yyyy-mm-dd') between '2011-01-10' and '2011-01-16''
>
> Well, the problem is to image the sql-statment on a jdbc request-message.
>
> I don't know how can I represent an sql-function and the placeholder for date1 and date2 in the request message.
>
> I hope anybody can help!
>
> Best regards
> Alex
Hi,
Do you want to do a DB Lookup in MessageMapping, or a simply DB Select?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi spantaleoni,
i would like to do dblookup via message mapping. I try to generate an db-request.
for example (XML Document Format for the Message Protocol XML SQL Format).
http://help.sap.com/saphelp_nw04/helpdata/en/2e/96fd3f2d14e869e10000000a155106/frameset.htm
Best regards
Alex
You can't do that with a simple sql statement.
You must create an stored procedure with two parameters (date1, date2) an execute it .
Your target message must be like:
<StatementName>
<storedProcedureName action=u201D EXECUTEu201D>
<table>realStoredProcedureeName</table>
<param1 [isInput=u201Dtrueu201D] [isOutput=true] type=SQLDatatype>val1</param1>
</storedProcedureName >
</StatementName>
REgards,
Carme.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
84 | |
25 | |
12 | |
9 | |
6 | |
6 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.