cancel
Showing results for 
Search instead for 
Did you mean: 

JDBC Adapter SQL Query (DBLookup)

Former Member
0 Kudos

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

Accepted Solutions (0)

Answers (3)

Answers (3)

baskar_gopalakrishnan2
Active Contributor
0 Kudos

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>

Former Member
0 Kudos

Hi Baskar,

thanks for your helpful answer. I could solve the problem.

I have a little bit modified your statement. So it is really importend don't forget the inverted comma on the placeholder.

for example '$DATEFORMAT$'

Best reguard

Alex

baskar_gopalakrishnan2
Active Contributor
0 Kudos

Yes, db accepts always quoted string as value.

Former Member
0 Kudos

>

> 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?

Former Member
0 Kudos

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

Former Member
0 Kudos

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.