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

[hami1]

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

[vapukov]

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 ..."

[vapukov]

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