Parameter index on tMSSqlRow prepared statement

One Star

Parameter index on tMSSqlRow prepared statement

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
Community Manager

Re: Parameter index on tMSSqlRow prepared statement

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
----------------------------------------------------------
Talend | Data Agility for Modern Business