JDBC Adapter: Dynamic SQL;; ORA-01006: bind variable does not exist

, ,
1

I am trying to create a dynamic SQL statement

1.) My first try was to write the complete SQL-Statement into the SQL-field

UPDATE FT_MASTER SET master_value=‘ES01010101’ WHERE master_key=12

that worked correctly

2.) then I placed a SQL-Variable into the SQL-Field
${sqlString}

I inserted a Input Field named sqlString into the input Parameterlist
VARCHAR java.lang.string sqlString

I run the service and I put the SQL-String into my parameter sqlString
UPDATE FT_MASTER SET MASTER_VALUE=‘ES01010101’ WHERE MASTER_KEY=12

I am receiving an error Message
ORA-01006: bind variable does not exist

Can somebody help me

2

I found the error by myself.

the bug was that I added a variable named sqlString to the parameterlist.

If a variable (which will keep the complete SQL-String) is defined in the SQL-Field, it is automatically added by webMethods into the parameter Input List. You don’t have to add this parameter manually.

So the correct steps are:
a.) place a SQL-Variable into the SQL-Field e.g. ${sqlString}
b.) webMethods adds automatically this variable to the Input-List
c.) run the service. It will ask for input adapterservice/sqlString

of course in previous steps the sqlString may be generated dynamically by various conditions.

It would be a good job for webMethods to give more detailed examples and information in the JDBC-Adapters Guide.

hope this helps someone

3

Joe,

I must ask: assuming that your SQL statement is indeed as simple as “UPDATE FT_MASTER SET master_value=‘ES01010101’ WHERE master_key=12,” why not just use the UpdateSQL Template?

  • Percio
4

Joe,
Also, if you need to update many records at a time, you can use UpdateBatchSQL template.

5

Joe,

The steps you have mentioned is the default behaviour of JDBC DynamicSQL template.Yes this basic info should have been there in the JDBC docs.

Thanks for updating the query though.

HTH,
RMG