custom sql

, ,
1

Hi, I am trying to create custom sql with sql query “SELECT LE_ID, LEGAL_NAME FROM MV_SCI_ENTMT_REFRESH WHERE LE_ID IN ?”
and passing input as (‘1111’,‘2222’,‘3333’) in a variable. but I am get invalid number exception.

is this query works in custom template…?

custom.png
custom.png819×460 20.3 KB

2

Yes it should work with the custom SQL and passing param but you might have send it as 3 different fields and concat with ()…try that also:

Also all the values in IN are varchars or integers in the DB data types?

HTH.,
RMG

3

Thanks RMG,

Its working in custom sql “SELECT LE_ID, LEGAL_NAME FROM MV_SCI_ENTMT_REFRESH WHERE LE_ID IN (?,?,?)”

with 3 different fields, But In my scenario input is dynamic. I have achieved this with dynamic sql.

I am trying to do in custom is it possible…?

4

@Murali,

It seems customSQL does not support IN. You go for dynamicSQL.

SELECT LE_ID, LEGAL_NAME FROM MV_SCI_ENTMT_REFRESH WHERE LE_ID IN ${INPUT_FIELD_NAME}.

But use dynamicSQL with caution, copied from JDBC Adapter Guide:

JDBC Adapter does not validate the input parameters of a DynamicSQL service for any malicious SQL injections. When you use a variable input parameter such as the text ${INPUT_FIELD_NAME} in a DynamicSQL service, you must take extra measures to avoid potential security risks by, for example, using a wrapper service for your DynamicSQL service that will validate the variable input parameters.

HTH.

Thanks,
Rankesh

2 Likes
5

Thanks, Rankesh

I have done this with Dynamic SQL. :slight_smile:

6

Yes that is what I was going to say with DynamicSQL template sorry for the delay…and glad to hear it worked out fine.

HTH,
RMG

7

I am getting the error below

Launch started: 2014-04-09 12:27:36.574
Configuration name: new_Select_adapterService_CustomSql
Configuration location: C:/Users/Public/Work_imp/.metadata/.plugins/org.eclipse.debug.core/.launches/new_Select_adapterService_CustomSql.launch

Could not run ‘new_Select_adapterService_CustomSql’
com.wm.pkg.art.error.DetailedServiceException: [ART.117.4002] Adapter Runtime (Adapter Service): Unable to invoke adapter service BPM_409377:new_Select_adapterService_CustomSql.
[ADA.1.316] Cannot execute the SQL statement “select * from dbo.Address$ where addressline1 like ${AddressLine1}”. "
(S1093/0) The index 1 is out of range."
The index 1 is out of range.

Server error occurred
Launch completed: 2014-04-09 12:27:39.317

Please give me solution as i am very new in custom sql.

8

Hi Chandra,

From error I can see the SQL is not correct. Can you please paste a screen shot?

Thanks,
Rankesh

1 Like
9

Hi rakesh,

Thank you for your reply but i can not paste screen shot. Can i talk to you directly and explain you.

Regards,
Bhushan

10

Please reach me on email Id. Mentioned below.