Select on a JDBC integration

[Former Member]

Hi experts,

I am doing a synchonous integration between a DB and another system with a PI 7.1. It is everything done and works except the SQL sentence:

I did as I reed in here:

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

What I want is doing a Select between 2 given dates. It works if I send this XML:

<?xml version="1.0" encoding="UTF-8"?>

<ns0:MT_UBICACIONES xmlns:ns0="urn:repsol.com:gestion_patrimonio:rrhh:autocadmap">

<STATEMENT>

<UBICACIONESPRE action="SQL_QUERY">

<access>Select * from UBICACIONESPRE where FECHAMOD BETWEEN '13/05/2009' AND '17/05/2009' </access>

<table>Ubicacionespre</table>

<key>

<FECHA_INICIAL>13/05/2009</FECHA_INICIAL>

<FECHA_FINAL>17/06/2009</FECHA_FINAL></key>

</UBICACIONESPRE>

</STATEMENT></ns0:MT_UBICACIONES>

But it does not works if I send the same XML but with the field access with a different sentence:

<access>Select * from UBICACIONESPRE where FECHAMOD BETWEEN '$FECHA_INICIAL$' AND '$FECHA_FINAL$' </access>

Does anybody knows the right sentence for doing a select using the content of the fields FECHA_INICIAL and FECHA_FINAL?

Regards

Gonzalo

[Shabarish_Nair · Accepted solution]

the format needs to be this;

Format of SQL_QUERY Statement

<root>

   <StatementName>

                  <anyName action=u201D SQL_QUERYu201D>

              <access>SQL-String with optional placeholder(s)</access>

              <key>

                              <placeholder1>value1</placeholder1>

                 <placeholder2>value2<placeholder2>    

              </key>

          </anyName > 

      </StatementName>

</root>

refer: http://help.sap.com/saphelp_nwpi71/helpdata/en/44/7b7855fde93673e10000000a114a6b/content.htm

[Former Member]

there is always problem with date in queries. your date fields are of which type in source ,PI and target ?

with this please use following statement

Select * from UBICACIONESPRE where FECHAMOD >= '$FECHA_INICIAL$' AND FECHAMOD<= '$FECHA_FINAL$'.