Single quotes getting replaced in ODBC stage

[aneesh5142]

Single Quotes is getting replaced in ODBC stage

Job structure is

ODBC - transformer - file

select query is like - select * from Sqlserver_tablename where condition - the whole query is passes from parameter #SQLQuery#

#SQLQuery# is getting value from
Trim(Convert(@FM," ",#Command_Execute.$CommandOutput#)) passed from the sequencer.

#Command_Execute.$CommandOutput#)) is getting value from a cat command

$cat Sql.txt


we had an issue when sql string is select * from table where column = 'FIRST'

THE QUOTES is getting replaced it is executed as - select * from table where column = FIRST and was giving error in datastge

I made the string as select * from table where column = \'FIRST\' and executed sequencer job executed successfully with desired results

The problem here this sql command is executed by client we cannot ask then to give slash before quotes we need to handle this in datastage

Can anyone please help me in this.


Thanks
Aneesh

[priyadarshikunal]

did you try ereplace when you are doing convert and trim?

Also if you are using ODBC connector then did you try "read select statement from file" option?

[aneesh5142]

Thank you for your suggestion.

We are using generic jobs. changing those will affect other jobs.

Not able to figure out how quotes are getting replaced and when we are giving '\' how it is working.

[aneesh5142]

One thing to add .The whole SQL string is parametrized not just the Where clause.

Do we have any Environmental variable which will fix this.

[chulett]

Yes.

[chulett]

Not able to figure out how quotes are getting replaced and when we are giving '\' how it is working.

That backslash is an escape character.