topic Re: Using TMSSqlRow to Populate stop words list on SQL Server in Talend Studio

Using TMSSqlRow to Populate stop words list on SQL Server

Anonymous — Tue, 04 Oct 2016 03:00:49 GMT

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?

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

Anonymous — Tue, 04 Oct 2016 05:34:54 GMT

can you please share the screenshot of your job?

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

Anonymous — Tue, 04 Oct 2016 06:49:57 GMT

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

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

vapukov — Tue, 04 Oct 2016 10:27:36 GMT

Did You check Advanced settings?

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

Anonymous — Tue, 04 Oct 2016 22:57:17 GMT

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

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

Anonymous — Wed, 05 Oct 2016 05:01:39 GMT

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.

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

Anonymous — Thu, 06 Oct 2016 06:10:46 GMT

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