Dynamic SQL - 17003 Invalid column index

, ,
1

I use DynamicSql Adapter Service with this simple query:[INDENT]SELECT REF_TASK_CD FROM ACT_TASK WHERE ACT_TASK_ID = ${ActTaskId}.

[/INDENT]I defined ActTaskId: Input JDBC Type VARCHAR, Input Field Type java.lang.String.
I defined an output field REF_TASK_CD: Output JDBC Type VARCHAR, Output Field Type java.lang.String.

I got this message:[INDENT]Cannot execute the SQL statement “SELECT REF_TASK_CD FROM ACT_TASK WHERE ACT_TASK_ID = 318”. “(17003) Invalid column index”

[/INDENT]In BD, column ACT_TASK_ID is define as NUMBER.
If I copy/paste the query in SQLPlus, it works.

What have I missed?

PS: Oracle Database 10g Express Edition; webMethods 7.1

2

Out of curiosity, it would not appear that you need the dynamic SQL adapter service for the example you provide. A simple select service would do the trick.

Mark

3

I use this simple query to test the DynamicSQL. I only want to know why this DynamicSQL don’t work !!! What have I missed ?

4

I forgot to mention that there is no DynamicSQL that works on my PC.

5

Add ACT_TASK_ID as a selected column.

SELECT REF_TASK_CD, ACT_TASK_ID FROM ACT_TASK WHERE ACT_TASK_ID = ${ActTaskId}

6

Same message :

[INDENT]com.wm.app.b2b.server.ServiceException: [ART.117.4002] Adapter Runtime (Adapter Service): Unable to invoke adapter service BNG_ActivationTaskMgtService.Queries:qryIsResolved.
[ADA.1.316] Cannot execute the SQL statement “SELECT REF_TASK_CD, ACT_TASK_ID FROM ACT_TASK WHERE ACT_TASK_ID = 318”. "
(17003) Index de colonne non valide"
Index de colonne non valide
[/INDENT]

7

Perhaps the info in this thread is helpful.

[URL=“wmusers.com”]wmusers.com

8

It works! Thank you !!!

9

In this thread [URL=“wmusers.com”]wmusers.com, sai said:

[INDENT]Not defining InputJDBCType, InputFieldType and InputField in
DynamicSql Tab returned the results as expected.
[/INDENT]You don’t have to define any input field for your ${INPUT_FIELD_NAME}. In the panel DynamicSql of the adapter service, you will NOT see any auto-generated variable but in the Input/Output panel, you will see all of your ${INPUT_FIELD_NAME}.

If your DynamicSQL contains «?» placeholder, you have to define an input field for each «?» placeholder like any other adapter service.

Thank you sai.