Using TMSSqlRow to Populate stop words list on SQL Server

Highlighted
One Star

Using TMSSqlRow to Populate stop words list on SQL Server

I have a text file list of bad words for a profanity filter and was trying to load them.
I am trying to execute the following SQL using a TMSSqlRow component
"alter fulltext stoplist BadWords add ? LANGUAGE 'English' "
I have ticked use Prepared Statement and 1 parameter in my list which is to consume the bad words from the connector from the TMap
When I execute it I can see all words moving along the pipeline as the iterations of reading the file and passing them through the tMap are incrementing
Unfortunately, I get no stop words added to the list and the error I get is "Invalid parameter index 0.' for each iteration
I have a connection, a tinputfileDelimeted a tMap and the tMSSQLRow to do this small piece of work
What am I missing? 

One Star

Re: Using TMSSqlRow to Populate stop words list on SQL Server

can you please share the screenshot of your job?
One Star

Re: Using TMSSqlRow to Populate stop words list on SQL Server

Hi Here is the image


I am only interested in the activated part I may use the inactivated part once I understand what causes the problem




Cheers


Peter

Forteen Stars

Re: Using TMSSqlRow to Populate stop words list on SQL Server

Did You check Advanced settings?
-----------
One Star

Re: Using TMSSqlRow to Populate stop words list on SQL Server

I have set "Use prepared statement" and have created a parameter 0 for the badwords value from the row being iterated forward from the map. 
Tried it with the "Propogate query recordset" checked and unchecked 








Hopefully, someone can see the error in what I have done

Cheers
One Star

Re: Using TMSSqlRow to Populate stop words list on SQL Server

2 points:
1. Problem is that in between tMap and tMsSQLRow component, you need to use a tFlowtoIterate component will will allow you to iterate through the output of the tMap
2.  tFlowtoIterator will create globalMap variable for you to access the bad words which you can directly use in place of "?" in your query (""alter fulltext stoplist BadWords add ? LANGUAGE 'English' "" ). That way you do not have to use prepared statement at all and you will get rid of that error
let me know if you are able to code it else I will create a sample program for you.
One Star

Re: Using TMSSqlRow to Populate stop words list on SQL Server

Thanks Sankalp :cool:
That has solved my problem I have been able to execute that process to create a stopwords list in SQL Server from existing words lists available around the net

Cheers
Peter

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 1

Learn how to do cool things with Context Variables

Blog

Migrate Data from one Database to another with one Job using the Dynamic Schema

Find out how to migrate from one database to another using the Dynamic schema

Blog

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog