How to Configure the Context Variables within the SQL_Query which is running as prepared Statement in tMSSQLRow Component

Seven Stars skh
Seven Stars

How to Configure the Context Variables within the SQL_Query which is running as prepared Statement in tMSSQLRow Component

Dear Folks,

 

I came across a scenario where I need to Iterate the SQL Scripts and load its resultset into the DB. I achieved this logic with combination of the tMSSQLInput, tFlowToIterate, tMSSQLRow Components. The Proplem here is with the Context Variables as my SQL Scripts framed by joining the tables from different Schema's, So I need to mention the Database Schema Names for different tables as am running this scripts in different Environments. If I hard code the schema names then its running fyn but when I configure them with Context_Variables its failing to execute the script in tMSSQLrow Component stating as "Incorrect Syntax".

 

The Following is the One of the Script which was used in the tMSSQLRow Component with Context Variables of the database and ran this query directly in the tMSSQLRow Component without Iterating it from the tFlowToIterate Component. Its working fine, I have used the Escape Character for the ' " ' (Double Quotes with \ (slash) delimitter)

 

"SELECT DISTINCT A.LOAN_ID FROM \""+context.MSSQL_Schema+"\".\""+context.MSSQL_Database+"\".LOAN_REF A
LEFT OUTER JOIN \""+context.MSSQL_STAGE_Schema+"\".\""+context.MSSQL_STAGE_Database+"\".LOAN_MST_DT B WITH (NOLOCK) ON A.LOAN_ID = B.LOAN_ID AND B.MLSTN_TYPE_NM = 'ServiceTransferInDate'
AND B.MLSTN_DTTM >= DATEADD(DAY,-10,CAST(CAST(GETDATE() AS DATE) AS DATETIME))
LEFT OUTER JOIN \""+context.MSSQL_STAGE_Schema+"\".\""+context.MSSQL_STAGE_Database+"\".LOAN_MSG_CD FOR SYSTEM_TIME ALL LMC WITH (NOLOCK) ON A.LOAN_ID = LMC.LOAN_ID AND LMC.MSG_CD = '86' AND LMC.END_DTTM >= DATEADD(DAY,-5,CAST(CAST(GETDATE() AS DATE) AS DATETIME)) WHERE CASE WHEN LMC.LOAN_ID IS NOT NULL THEN 1 WHEN B.LOAN_ID IS NOT NULL THEN 1 ELSE 0 END = 1"

 

If I call this same query from the database table and tried to define in the tMSSQLRow Component as Iterate but its not running and returning the Error as Invalid Syntax near "\."

 

Kindly let me know how can I overcome this Error in this above scenario.

 

Thanks,

Hameed

 

 

 

 

 

Forteen Stars

Re: How to Configure the Context Variables within the SQL_Query which is running as prepared Statement in tMSSQLRow Component

Hi,

 

this is MS SQL, so double quotes around schema and other objects names not required (at least not mandatory)

1. try to remove all \", leave only double quotes for injecting context variables into SQL code.

 

2. you can use [ ] for objects names quote, it makes a text more readable

 

3. construction look bad and without errors - you are put schema name BEFORE database name

 

FROM \""+context.MSSQL_Schema+"\".\""+context.MSSQL_Database+"\"

 

 

You can try this:

"SELECT 
DISTINCT A.LOAN_ID
FROM ["+context.MSSQL_Database+"].["+context.MSSQL_Schema+"].[LOAN_REF] A

LEFT OUTER JOIN ["+context.MSSQL_STAGE_Database+"].["+context.MSSQL_STAGE_Schema+"].[LOAN_MST_DT] B WITH (NOLOCK) ON A.LOAN_ID = B.LOAN_ID AND B.MLSTN_TYPE_NM = 'ServiceTransferInDate'
AND B.MLSTN_DTTM >= DATEADD(DAY,-10,CAST(CAST(GETDATE() AS DATE) AS DATETIME))

LEFT OUTER JOIN ["+context.MSSQL_STAGE_Database+"].["+context.MSSQL_STAGE_Schema+"].[LOAN_MSG_CD] FOR SYSTEM_TIME ALL LMC WITH (NOLOCK) ON A.LOAN_ID = LMC.LOAN_ID AND LMC.MSG_CD = '86' AND LMC.END_DTTM >= DATEADD(DAY,-5,CAST(CAST(GETDATE() AS DATE) AS DATETIME))

WHERE CASE WHEN LMC.LOAN_ID IS NOT NULL THEN 1 WHEN B.LOAN_ID IS NOT NULL THEN 1 ELSE 0 END = 1"

 

 

 

-----------
Seven Stars skh
Seven Stars

Re: How to Configure the Context Variables within the SQL_Query which is running as prepared Statement in tMSSQLRow Component

Thanks for your quick reply,

 

I do modified the syntax as per your suggestions. But there is a no luck I am getting following Error stating that invalid object name.I ran this query with IterationFlow from tFlowToIterate Component and shows this Error. But if I didnt Iterate this from the Flow directly paste the query its working fine without any Errors. I ran this job with sample Query

 

"select COUNT(b.HOLD_KEY) from ["+context.SUPPORT_SRC_Database+"].["+context.SUPPORT_SRC_Schema+"].[DEMAND_VALIDATION] a inner join
["+context.MASTER_TRG_Database+"].["+context.MASTER_TRG_Schema+"].[DEMAND_VAL_HIST] b on a.LOAN_KEY = b.LOAN_KEY"

 

Exception in component tDBInput_2 (Sample_Job)
java.sql.SQLException: Invalid object name '"+context.SUPPORT_SRC_Database+"."+context.US_SUPPORT_SRC_Schema+".DEMAND_VALIDATION'.
at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:372)
at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2988)
at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2421)
at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:671)
at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQLQuery(JtdsStatement.java:505)
at net.sourceforge.jtds.jdbc.JtdsStatement.executeQuery(JtdsStatement.java:1427)
at hameed_support.sample_job_0_1.Sample_Job.tDBInput_1Process(Sample_Job.java:1164)
at hameed_support.sample_job_0_1.Sample_Job.runJobInTOS(Sample_Job.java:1863)
at hameed_support.sample_job_0_1.Sample_Job.main(Sample_Job.java:1539)
[statistics] disconnected

 

Does this requires any further configuration settings in tMSSQLRow Component when we are passing the SQL_QUERY in Iteration.?

Because if I provide the SQL_QUERY directly in the tMSSQLRow its working fine but when I passing this as a Variable from tFlowToIterate component it simply failing with Errors.

 

Thanks,

Hameed

 

 

 

 

Forteen Stars

Re: How to Configure the Context Variables within the SQL_Query which is running as prepared Statement in tMSSQLRow Component

you have error (it is SQL server error):

 Invalid object name '"+context.SUPPORT_SRC_Database+"."+context.US_SUPPORT_SRC_Schema+".DEMAND_VALIDATION'.

SQL server cannot find object with this name, it is look like in Talend you use additional single quotes around code

1. it is little different query with original - you use different context variables, so may be some other error in text (as it in talend but not here )

"SELECT 
     DISTINCT A.LOAN_ID 
FROM ["+context.MSSQL_Database+"].["+context.MSSQL_Schema+"].[LOAN_REF] A 

LEFT OUTER JOIN ["+context.MSSQL_STAGE_Database+"].["+context.MSSQL_STAGE_Schema+"].[LOAN_MST_DT] B WITH (NOLOCK) ON A.LOAN_ID = B.LOAN_ID AND B.MLSTN_TYPE_NM = 'ServiceTransferInDate' 
AND B.MLSTN_DTTM >= DATEADD(DAY,-10,CAST(CAST(GETDATE() AS DATE) AS DATETIME)) 

LEFT OUTER JOIN ["+context.MSSQL_STAGE_Database+"].["+context.MSSQL_STAGE_Schema+"].[LOAN_MSG_CD] FOR SYSTEM_TIME ALL LMC WITH (NOLOCK) ON A.LOAN_ID = LMC.LOAN_ID AND LMC.MSG_CD = '86' AND LMC.END_DTTM >= DATEADD(DAY,-5,CAST(CAST(GETDATE() AS DATE) AS DATETIME)) 

WHERE CASE WHEN LMC.LOAN_ID IS NOT NULL THEN 1 WHEN B.LOAN_ID IS NOT NULL THEN 1 ELSE 0 END = 1"

 

you need debug - what query you are send to sql server.

as variant, add context variable SQLtext

 

before tMSSQLRow add tJava component (connect both by OnSubjobOk)

and in tJava

set context variable

context.SQLtext= ..... all what you want to use as SQL

then print it

System.out.println(context.SQLtext);

 

in tMSQSQL row use just context.SQLtext without any other quotes

but first - check what it print for You

 

-----------
Seven Stars skh
Seven Stars

Re: How to Configure the Context Variables within the SQL_Query which is running as prepared Statement in tMSSQLRow Component

 

1. it is little different query with original - you use different context variables, so may be some other error in text (as it in talend but not here )

yes this one is the different query which I sent you earlier, and also the context variables I changed them according to the Query.

 

When I run in de-bug mode its passing the same sql_query. Let me debug by placing the tJava Component.

 

Thanks

 

 

 

 

 

Forteen Stars

Re: How to Configure the Context Variables within the SQL_Query which is running as prepared Statement in tMSSQLRow Component

sure, always test - what you send to SQL Server

 

as You can see from previous post - it look like you have syntax error - additional single quotes in Talend (but I not sure)

-----------
Seven Stars skh
Seven Stars

Re: How to Configure the Context Variables within the SQL_Query which is running as prepared Statement in tMSSQLRow Component

Hi Vapukov,

But there is a no change at all in the Error. I guess we are missing something here. The following given below are the Screen-Shots of the same.

I modified the job design as per your suggestions.

01 Job Screen-Shots : defined the SQL Query directly in the tMSSQLInput Component and it ran successfully.

02 Job Screen-Shots : Passed the "context.SQLtext" context variable and it returns the Error.

Am wondering why its not working when we are passing the SQL Query as a parameter which intern carries the context variables...?

 

Any Suggestions would be more helpful.

 

Thanks

 

 

 

 

Highlighted
Forteen Stars

Re: How to Configure the Context Variables within the SQL_Query which is running as prepared Statement in tMSSQLRow Component

please attach - what you have in tJava - you do something wrong

-----------
Seven Stars skh
Seven Stars

Re: How to Configure the Context Variables within the SQL_Query which is running as prepared Statement in tMSSQLRow Component

In tJava am just assiging 

 

context.SQLtext = ((String)globalMap.get("row1.SQL_QUERY"));

System.out.println(context.SQLtext);

Forteen Stars

Re: How to Configure the Context Variables within the SQL_Query which is running as prepared Statement in tMSSQLRow Component

--- 

-----------
Forteen Stars

Re: How to Configure the Context Variables within the SQL_Query which is running as prepared Statement in tMSSQLRow Component

of course - this is will not work!

 

text from database already enquoted, and it come as string

 

"SELECT \"+comntext+\" from  " - as single string, it would be automatically enquoted and escaped!

but not as

"SELECT "+context+" FROM ..."

-----------
Forteen Stars

Re: How to Configure the Context Variables within the SQL_Query which is running as prepared Statement in tMSSQLRow Component

as variant, because it came from the database table - you could use replaceAll()

 

and replace string +context.XXXX+ to the value of context

You must print real SQL query in debug tJava

-----------

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 4

Pick up some tips and tricks with Context Variables

Blog

How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration

Read

Tutorial

Introduction to Talend Open Studio for Data Integration.

Watch