What component to use to execute a MSSQl script that uses variables

felprie — Sat, 16 Nov 2024 07:51:28 GMT

Hi, I need to execute a T-SQL script that has variables and then pass the resultset to a flow, where I need to further process it.I'm using Talend Cloud Data Management Platform(v7.0.1). I've been trying to use a TDBrow MSSQL component to execute the script and then connecting that component with a tlogrow to check if it worked, but i've been unsuccessful. I need to mention that the schema from the result set is going to change depending on the variables that I’ve set on the query.

Here is the query that I’m using:

DECLARE @sqlString as varchar(max)

DECLARE @p_schema_name as varchar(255)=”+context.MySchema”;

DECLARE @p_table_name as varchar(255)=”+context.MyTable+”;

SET @sqlString = ''

select @p_table_name

SELECT @sqlString = @sqlString +

CASE DATA_TYPE

WHEN 'int' THEN 'ISNULL(RTRIM(CONVERT(varchar(10),' + COLUMN_NAME + ')),'''')'

WHEN 'datetime' THEN 'ISNULL(RTRIM(CONVERT(varchar(10),' + COLUMN_NAME + ',112)),'''')'

WHEN 'datetime2' THEN 'ISNULL(RTRIM(CONVERT(varchar(10),' + COLUMN_NAME + ',112)),'''')'

WHEN 'date' THEN 'ISNULL(RTRIM(CONVERT(varchar(10),' + COLUMN_NAME + ',112)),'''')'

WHEN 'bit' THEN 'ISNULL(RTRIM(CONVERT(varchar(1),' + COLUMN_NAME + ')),'''')'

WHEN 'decimal' THEN 'ISNULL(RTRIM(CONVERT(varchar('+ CONVERT(varchar(2),NUMERIC_PRECISION) +'),' + COLUMN_NAME + ')),'''')'

ELSE 'ISNULL(RTRIM(' + COLUMN_NAME + '),'''')'

END + '+'

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_SCHEMA = @p_schema_name and TABLE_NAME = @p_table_name

select @sqlString

DECLARE @sqlStatementString as varchar(max)

SELECT @sqlStatementString='SELECT '+'ID'+',HASHBYTES(''MD5'','+ @sqlString+'''I'''+')'+' FROM '+@p_table_name

select @sqlStatementString

EXEC (@sqlStatementString)

I've tried a simpler case, of just having declared one variable and then doing a select @variable,with tDBRow but then I check the tlogrow its coming empty:

Can someone please point me out on how to achieve this.

Best regards,

Re: What component to use to execute a MSSQl script that uses variables

vapukov — Sat, 04 Aug 2018 03:25:15 GMT

just use for this tMSSQLInput component and define schema - p_table_name (String)

from tMSSQLInput main row to tFlowToIterate, must resolve You tasks

Re: What component to use to execute a MSSQl script that uses variables

Anonymous — Sat, 04 Aug 2018 03:46:04 GMT

I confirm the proposal of vapukov
I have the same problem and i resolved it by using this method

topic What component to use to execute a MSSQl script that uses variables in Talend Studio

What component to use to execute a MSSQl script that uses variables

Re: What component to use to execute a MSSQl script that uses variables

Re: What component to use to execute a MSSQl script that uses variables