Dynamic SQL

system · October 13, 2014, 12:45pm

Hello Team,

I’m getting below error while trying to execute a dynamic sql function :

Context :

Created a dynamic SQL adapter service. (Which requires 2 parameters at run time.)
Through a flow service, trying to invoke above adapter - Resulting in Error :
```
"(S1093/0) The index 1 is out of range."The index 1 is out of range"  
```

Help required,

Thanks Team !!!

Mahesh_K_Sreenivas · October 13, 2014, 3:57pm

What are you trying to do in a dynamic SQL? Can you share the details/requirements please?

system · October 14, 2014, 7:55am

Steps :

Create a dynamic SQL adapter service - which will get 2 inputs at run time
In a flow service, invoke above dynamic SQL adapter service by passing 2 inputs
At this point, I’m getting below mentioned error.

Thanks

Mahesh_K_Sreenivas · October 14, 2014, 8:07am

DynamicSQL statement?

system · October 14, 2014, 10:37am

Yup, Dynamic SQL

I select a new DynamicSQL template while creating a new adapter service connection.

Thanks.

system · October 14, 2014, 10:41am

Yup, Dynamic SQL

While creating a new Adapter service connection, I select a DynamicSQL template and create it. In that , 2 parameters are passed at run time. I’ve defined them also.

Thanks.

Martin_Wroblinski · October 15, 2014, 10:28am

A dynamic SQL is for passing whole parts of SQL which are generated at runtime. For passing parameters you do not need to us dynamic SQL. Select a standard SQL template (select, insert, update) and just define the required inputs in the where or insert clause. This Way the required signature of the service is generated automatically. For complex SQL you may use custom SQL.

P.S.: Please post you questions to the appropriate forums, this here is just the welcome and introdtuction, not for technical questions.

Sam_A · October 17, 2014, 8:45pm

The best way to execute any SQL is through Stored procedures, because they are pre-compiled and easy to maintain.

Create an Stored procedure with the requred parameters and call the SP From WM.

Mahesh_K_Sreenivas · October 18, 2014, 6:46am

I don’t think so

You can also use the custom SQL to execute your SQL statements.

Vikram_Lingampalli1 · May 7, 2016, 12:34am

Can we make a dynamic sql adapter template which takes in 2 inputs one is the actual variable value and the other one would be a dynamic variable name…

example below:

select is_freight from s_equipment se INNER JOIN shipment_s_equipment_join ssej ON ssej.s_equipment_gid = se.s_equipment_gid
where ssej.${stop_type} = ? and ssej.shipment_gid = ?

inputs:
stop_type
stop_num
shipment_gid

values
DROPOFF_STOP_NUM
6
XYZ

Expected result:(SQLStatement)
select is_freight from s_equipment se INNER JOIN shipment_s_equipment_join ssej ON ssej.s_equipment_gid = se.s_equipment_gid
where ssej.DROPOFF_STOP_NUM = ‘6’ and ssej.shipment_gid = ‘XYZ’

Getting Error:

(99999/17003) Invalid column index"
Invalid column index

MR_as173d · May 7, 2016, 2:20pm

Hello – please try below one.

select is_freight from s_equipment se INNER JOIN shipment_s_equipment_join ssej ON ssej.s_equipment_gid = se.s_equipment_gid
where ssej.${stop_type} = ${VALUE1} and ssej.shipment_gid = ${VALUE2}

Let me know if you see any issues.

Thanks,

Vikram_Lingampalli1 · May 9, 2016, 1:54am

No luck, again getting same exception…

But this time A/s was able to substitute the values correctly in query…

com.wm.app.b2b.server.ServiceException: [ART.117.4002] Adapter Runtime (Adapter Service): Unable to invoke adapter service XYZ.adapters:checkFreightedShipments.
[ADA.1.316] Cannot execute the SQL statement “select is_freight from s_equipment se INNER JOIN shipment_s_equipment_join ssej ON ssej.s_equipment_gid = se.s_equipment_gid
where ssej.DROPOFF_STOP_NUM = 6 and ssej.shipment_gid = 123456-SMMULTISTOP1”. "
(99999/17003) Invalid column index"
Invalid column index

Vikram_Lingampalli1 · May 9, 2016, 1:54am

No luck, again getting same exception…

But this time A/s was able to substitute the values correctly in query…

com.wm.app.b2b.server.ServiceException: [ART.117.4002] Adapter Runtime (Adapter Service): Unable to invoke adapter service XYZ.adapters:checkFreightedShipments.
[ADA.1.316] Cannot execute the SQL statement “select is_freight from s_equipment se INNER JOIN shipment_s_equipment_join ssej ON ssej.s_equipment_gid = se.s_equipment_gid
where ssej.DROPOFF_STOP_NUM = 6 and ssej.shipment_gid = 123456-SMMULTISTOP1”. "
(99999/17003) Invalid column index"
Invalid column index

MR_as173d · May 9, 2016, 5:17pm

I tried, it worked for me. Please cross check once again.

Thanks,

Percio_Castro1 · May 9, 2016, 9:50pm

${VALUE2} appears to be alphanumeric. Put single quotes around it in your query.

Percio

Vikram_Lingampalli1 · May 9, 2016, 10:30pm

Just did a small change and now its working for me…

Earlier i’ve used three input feilds, but for dynamic variables there is no need to define inputs as ‘VARCHAR’ or something else. The Adapter template by default adds dynamic inputs to input document type like (stop_type) here…which can be substituted with PICKUP_STOP_NUM or DROPOFF_STOP_NUM

So now i’ve changed the other two variables to regular types and defined only first one as dynamic(stop_type)

inputs:

stop_num
shipment_gid

select is_freight from s_equipment se INNER JOIN shipment_s_equipment_join ssej ON ssej.s_equipment_gid = se.s_equipment_gid
where ssej.${stop_type} = ? and ssej.shipment_gid = ?

MR_as173d · May 12, 2016, 6:41pm

Yes, in general we don’t give input, output datatype details as part of dynamic SQL’s. Going forward when you are reporting any issue, kindly attach snapshots which gives us some clarity, we can also better assist. Thanks anyways for fixing the issue.

Thanks,