Dynamic SQL

, ,
1

Hello Team,

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

Context :

  1. Created a dynamic SQL adapter service. (Which requires 2 parameters at run time.)

  2. 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 !!!

2

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

3

Steps :

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

Thanks

4

DynamicSQL statement?

5

Yup, Dynamic SQL

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

Thanks.

6

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.

7

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.

1 Like
8

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.

9

I don’t think so :slight_smile:

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

10

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

11

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,

1 Like
12

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

13

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

14

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

Thanks,

15

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

Percio

1 Like
16

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

1 Like
17

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,