How to pass a parameter value into SQL Query dynamically

[Former Member]

Hi All,

I am working on synchrnous Soap (Proxy) to JDBC scenrio. Here JDBC is the reciever adapter. As per my requirement I have to pass a value which is hardcoded now in the SQL query to be user input. i.e. during the proxy execution the user will give the input everytime he executes which can change. This field is the duration field. Like no of days in a year. I have a select statment in my mapping with Action as SQL_QUERY and for Access I have this SQL statment.

(SELECT

DE.EC_NO AS "EC_NO",

DE.PACKAGE_NO AS "PACKAGE_NO",

PC.DATE_TO_FORMAL AS "DATE_TO_FORMAL"

FROM

DAICYO_ECNO DE,

PACKAGECTL PC

WHERE

DE.PACKAGE_NO = PC.PACKAGE_NO AND

PC.DATE_TO_FORMAL > (TRUNC(SYSDATE) - 30)

) DP

I changed the above sql statement to be as

(SELECT

DE.EC_NO AS "EC_NO",

DE.PACKAGE_NO AS "PACKAGE_NO",

PC.DATE_TO_FORMAL AS "DATE_TO_FORMAL"

FROM

DAICYO_ECNO DE,

PACKAGECTL PC

WHERE

DE.PACKAGE_NO = PC.PACKAGE_NO AND

PC.DATE_TO_FORMAL > (TRUNC(SYSDATE) - '$NO_OF_DAYS$')

) DP

But, I am getting the below error:

com.sap.engine.interfaces.messaging.api.exception.MessagingException: Error processing request in sax parser: Error when executing statement for table/stored proc. 'TABLENAME' (structure 'STATEMENT'): java.sql.SQLException: ORA-01722: invalid number

Please let me know if you guys have any thoughts in fixing this...

Thanks in adavance.

Srujanks

[Former Member · Accepted solution]

You have to use a standard structure for JDBC select statement:

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

[Former Member · Accepted solution]

So, does it mean I have to declare that parameter in my Select statement in order to use it? Like Select NO_OF_DAYS from T_APPLICATION_LIST where NO_OF_DAYS = '$NO_OF_DAYS'.

Thanks,

Srujanks

[baskar_gopalakrishnan2 · Accepted solution]

>>So, does it mean I have to declare that parameter in my Select statement in order to use it? Like Select NO_OF_DAYS from T_APPLICATION_LIST where NO_OF_DAYS = '$NO_OF_DAYS'.

Yes, you are right. something similar to sap help link... example as follows. Write sql query in access element. Map source input elements to target elements. Use Key elements of target in the access sql query.

<stmt>

<Customers action="SQL_QUERY">

<access> Select * from Customers where CompanyName=u2019$NAME$u2019 and Address=u2019$ADDRESS$' </access>

<key>

<NAME>Company</NAME>

<ADDRESS>Street 3 </ADDRESS>

</key>

</Customers>

</stmt>

[aashish_sinha]

Hi,

Have a look into below blogs

/people/aashish.sinha/blog/2011/02/24/approaches-where-jdbc-native-sql-fails

/people/francesco.bersani/blog/2009/11/27/preparedstatement-with-jdbc-receiver-adapter

Regards

Aashish Sinha

[aashish_sinha]

Hi,

Have a look into below note and also look into the blog i provided to you. You need to pass a value something like (?) mark, and this value will get replaced by the value you want. this is the way we solve our problem.

https://websmp130.sap-ag.de/sap(bD1lbiZjPTAwMQ==)/bc/bsp/spn/sapnotes/index2.htm?numm=1404778

/people/francesco.bersani/blog/2009/11/27/preparedstatement-with-jdbc-receiver-adapter

Regards

Aashish Sinha

[Former Member]

Try to set the logStatement = true advanced parameter to check the parsed SQL query ! It may help you understand why the variable substitution raises error ...

Chris

[Former Member]

I turned on the SQL log and I get the error as invalid character. Because, in my query I have passed it as $NO_OF_DAYS$ it is not recognizing the $ symbol. Any help would be greatly appreciated.

Thanks,

Srujanks

[baskar_gopalakrishnan2]

Pass it as '$NO_OF_DAYS$'

[Former Member]

I tried that one too...it gives me as invalid number. Not sure, where I am going wrong on this one.

Thanks.

[baskar_gopalakrishnan2]

>>...it gives me as invalid number. Not sure, where I am going wrong on this one.

Now the error message does not show invalid character. it says only invalid number. Check in the database table how that column is defined and what it expects.. Seems not problem at your end....

[Former Member]

Yeah, but if you look up in the SQL statement it is hardcoded as a number which in this case is 30. So, do you think this will be still having a column in the database table with field defined?

Thanks,

Srujanks

[baskar_gopalakrishnan2]

Use to_number( string) or safe_to_number(string) in query

Where string represents number of days

[Former Member]

I tried both of them. For the first one its giving ORA 01722 Invalid number and for Safe_to_number its ORA -00904 Invalid Identifier. I turned on the SQL logs and I am noticing is the place holder which is '$NO_OF_DAYS$' when passed to the adatper its not converting into the value pushed from Proxy like a numeric value and its passed as $NO_OF_DAYS$ causing invalid Number error. Any suggestions.

Thanks for your help. Still researching...

Srujanks

[Former Member]

Hi,

You are using Dynamic SQL with placeholder and not variable and DB type is Oracle so use it like :NO_OF_DAYS (prefix with colon ':').

If you add & then ORA will think it as variable and it should be defined before executing in SQL statement. Don't use ' or anything else but just prefix colon before NO_OF_DAYS.

Edit:

Sorry, I misunderstood your question.

From SAP help:

 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.

Are you using Key tag as defined here:

     <key>
<NO_OF_DAYS> yourvalue</NO_OF_DAYS>
      </key>

Regards,

Gourav

Edited by: Gourav Khare on Mar 24, 2011 12:24 PM

[Former Member]

Yes, I have declared the key as you mentioned. But during runtime the value is not getting populated before making a databse call and its just passed as the string causing the database to throw an error saying invalid number. The placeholder which I have defined or created is not accepting the value entered in the proxy call from ECC.

Thanks,

Srujanks

[baskar_gopalakrishnan2]

Hi

Can you Please provide your source and target data structure and sql query written in access tag... ?

Are you mapping the source field to target key field? You are not passing string that represents the value as expected. Thatswhy you get invalid number during runtime.

something like this.... to_number('$no_of_days$')

[Former Member]

Since this is a synchronous interface, where source is a soap(proxy) call and target is JDBC in the first mapping both of them are request scenarios.

Source Structure:

RootNode

Request 1...unbounded

No_of_Days 1.1 String

Target Structure:

RootNode

Statement

TableName

Action mapped to SQL_QUERY

Access - SELECT DISTINCT AL.EC_NO,DP.DATE_TO_FORMAL FROM T_APPLICATION_LIST AL,(SELECT DE.EC_NO AS "EC_NO", DE.PACKAGE_NO AS "PACKAGE_NO",PC.DATE_TO_FORMAL AS "DATE_TO_FORMAL" FROM DAICYO_ECNO DE,PACKAGECTL PC WHERE DE.PACKAGE_NO = PC.PACKAGE_NO AND PC.DATE_TO_FORMAL > (TRUNC(SYSDATE) - to_number('$No_Of_DAYS$'))) DP WHERE AL.EC_NO IN (SELECT EC_NO FROM DAICYO_ECNO WHERE PACKAGE_NO IN (SELECT PACKAGE_NO FROM PACKAGECTL WHERE DATE_TO_FORMAL > (TRUNC(SYSDATE) - to_number('$No_Of_DAYS$')))) AND (AL.FAMILY = '' or '' is null and AL.FAMILY is not null ) and DP.EC_NO = AL.EC_NO ORDER BY DATE_TO_FORMAL

Key

No_Of_Days 1..1 String

In Return I am expecting a JDBC response from the Oracle Database as:

Source Structure:

RootNode

STATEMENT_response 1...unbounded

row 0...undbounded

EC_NO 1..1 String

Target Structure:

RootNode

RESPONSE

row

EC_NO 1..1 String

[baskar_gopalakrishnan2]

I want you to try this...

Declare your target key field as NO_OF_DAYS (make sure upper case)

and in the query to_number('$NO_OF_DAYS$') and test.

Are you supposed to pass only NO_OF_DAYS value only in the select query? Just curious..

[Former Member]

I tried that with upper case as NO_OF_DAYS and defining it as a key field. But, it didn't get converted to numeric value before leaving the adapter engine. It shows on the adapter engine as $NO_OF_DAYS$ without quotes instead of a number. No luck. Yes, that's right since in the query its hardcoded earlier needs to be parameter where user can key in from ECC to get the info from the database.

@Aashish,

I tried your way also like I gave as

SELECT DISTINCT AL.EC_NO,DP.DATE_TO_FORMAL FROM T_APPLICATION_LIST AL,(SELECT DE.EC_NO AS "EC_NO", DE.PACKAGE_NO AS "PACKAGE_NO",PC.DATE_TO_FORMAL AS "DATE_TO_FORMAL" FROM DAICYO_ECNO DE,PACKAGECTL PC WHERE DE.PACKAGE_NO = PC.PACKAGE_NO AND PC.DATE_TO_FORMAL > (TRUNC(SYSDATE) - (NO_OF_DAYS=?))) DP WHERE AL.EC_NO IN (SELECT EC_NO FROM DAICYO_ECNO WHERE PACKAGE_NO IN (SELECT PACKAGE_NO FROM PACKAGECTL WHERE DATE_TO_FORMAL > (TRUNC(SYSDATE) - (No_OF_DAYS=?)))) AND (AL.FAMILY = '' or '' is null and AL.FAMILY is not null ) and DP.EC_NO = AL.EC_NO ORDER BY DATE_TO_FORMAL

But, it didn't help either. One question is I don't have a where condition for this field like "where NO_OF_DAYS" = (NO_OF_DAYS=?). So, I how should be do this?

Thanks,

Srujanks

Edited by: SRUJANKS on Mar 24, 2011 5:06 PM

Edited by: SRUJANKS on Mar 24, 2011 5:07 PM

[aashish_sinha]

You need to use only question mark in place of No_OF_DAYS. Have a closer look to blog and use the sqlBindMode in the same manner. With the help of the parameter logSQLStatement we have a look to the SQL Statement in the audit log. after running this scenario, can you please paste what you getting in CC monitoring, audit log..

Regards

Aashish Sinha

[baskar_gopalakrishnan2]

>>But, it didn't help either. One question is I don't have a where condition for this field like "where NO_OF_DAYS" = (NO_OF_DAYS=?). So, I how should be do this?

You can try passing ? (preparedstatment way) .. since you have one key field ... whereever you specify ? it should pass keyfield only... Test it and let us knpw. or if that does not work declare your target key field is integer or float to match oracl e numeric and While mapping convert the string to integer or float ... Then use your current query and test.

[Former Member]

Ok, I solved it. I did a replace string for the incoming value with the NO_OF_DAYS field mapped on the source side. So, whenever I see a constant 30 replace it with NO_OF_DAYS field and this will be populated in the proxy when the interface is run in that way when it goes through the mapping it will search for this string and replaces it with a new value which will be changing dynamically when passing from adapter engine to the Database for querying. Thanks all for your help / suggestions.

Srujanks

Edited by: SRUJANKS on Mar 24, 2011 6:40 PM