dynamic sql date insert

, ,
1

Hi , I’m trying to insert a date into a table using dynamic sql. If I create a dynamic sql adaptor service with no inputs and supply the following sql…
insert into datetest (field1,field2) values (‘field1value’, to_date(‘2007/11/12 09:35:35’, ‘yyyy/mm/dd hh24:mi:ss’))
…it works fine.
However if i create two input fields and supply the values at run time using the following sql…
insert into datetest (field1,field2) values (‘${field1}’, to_date(‘${field2}’, ‘yyyy/mm/dd hh24:mi:ss’))
…i get the following error…

Could not run ‘insertDynamic’.

com.wm.app.b2b.server.ServiceException: [ART.117.4002] Adapter Runtime (Adapter Service): Unable to invoke adapter service OracleDates.O8i:insertDynamic.
[ADA.1.316] Cannot execute the SQL statement “insert into datetest (field1,field2)
values (‘field1value’, to_date(‘2007/11/13 10:40:23’, ‘yyyy/mm/dd hh24:mi:ss’))”. "
(17003) Invalid column index"
Invalid column index

Any ideas?

2

p.s. i should have said that I’m using jdbc adaptor 6.5 and I’ve tried it against both an oracle 8i and 10i