Parameter index on tMSSqlRow prepared statement

Albot248 — Tue, 05 Jul 2016 18:43:27 GMT

Hi
I want to execute a SQL query with the tMSSqlRow component, as input I'm using a tXMLMap component. The SQL query should be executed for every incoming row, using two values (eid, communication_number; both of data type integer) as parameters.
In the basic settings of tMSSql I entered the SQL query with question marks as placeholders for the parameters:

"UPDATE  mytable
SET     eid = ?
WHERE   communication_number = ?"

Under advanced settings I activated "Use PreparedStatement" and I defined the two parameters with parameter indexes 1 and 2.
When I run the job, I got the error message "Invalid parameter index 1". Any ideas what could be the cause for that error?
Thanks
Tom

Re: Parameter index on tMSSqlRow prepared statement

Anonymous — Wed, 06 Jul 2016 08:10:18 GMT

Hi
You need to iterate each input row using tFlowToIterate component, the job looks like
....tXMLMap--main(out1)--tFlowToIterte--iterate--tMssqlRow
on tMssqlRow, write the query as:
"Update mytable set eid="+(Integer)globalMap.get("out1.eid")+"where communication_number="+(Integer)globalMap.get("out1. communication_number")
BTW, why don't you use tMssqlOutput to update the data? For example:
....tXMLMap--main(out1)--tMssqlOutput
Set eid column as a key on the schema, and select 'update' item in the Action on data list.
Regards
Shong

topic Parameter index on tMSSqlRow prepared statement in Talend Studio

Parameter index on tMSSqlRow prepared statement

Re: Parameter index on tMSSqlRow prepared statement