Custom SQL Adapter Service Error.

, ,
1

I have created a new custom SQL adapter service (using an existing connection that works ok for ‘select’ adpaters), whenever I try and run a SQL statement though I get an error. I have simplified the adapter service to just try and get it to return something but I cannot get it to run with no errors.

I have 2 input fields FIELD1 and FIELD2, the SQL is in the following format:

select FIELD1, FIELD2, FIELD3 from TABLE where FIELD1 = ${FIELD1} and FIELD2 = '${FIELD2}

The outputs for the adapter are defined as FIELD1, FIELD2, FIELD3.

I’ve searched for similar problems and have tried removing the input values from the adapter definition but this makes no difference. If I call this service I get the following error:

(17034) Non supported SQL92 token at position: nnn : “FIELD1”

This corresponds to the position of ${FIELD1} in the query definition.

The actual query I want to run is more complicated but at the moment I can’t get it to run with a very simple statement and I’m stuck for ideas - can anyone help?

2

Are you trying select/custom SQL or dynamic SQL template via AS?

Please upload the screen shots of the Adapter Service configuration and users here can advise more:

HTH,
RMG

3

I’ve got it working now :smiley:

It wasn’t liking the {} around the input variables (I’d taken these from examples I’d seen on this forum and elsewhere). Just replacing the ${FIELD1} with a ? and sequencing the input fields (that I defined on the adapter) in the same order as the fields on the where clause made it work. I’m not sure why the examples I found used ${FIELD1} as the format of the input variable within the sql statement though.