Retaining single quotes in passed parameters?

[Richard615]

I'm guessing there has to be a simple answer for this, but after looking through the 8.0 documentation and going buggy-eyed from forum searches, I'm not finding anything.

In a nutshell, I want to pass SQL as an SQLPARM to a job, and have that show up in an odbc stage in the job in the select statement as #SQLPARM#.

The problem is I want to pass "Select to_char(APPCT_DATE,'YYYYMMDD') from X22.TABLE_NAME". If I look at the first entry in the job log, this is indeed getting passed to the job. But then Oracle dumps an error about bad sql, and displays "Select to_char(APPCT_DATE,YYYYMMDD) from X22.TABLE_NAME". So it seems by the time DataStage passes that parm to the actual stage, the single quotes have been interpreted as metacharacters, resulting in the bad sql syntax.

Any idea how I can retain them? In DS 7.5 preceding the single quote with a backslash ("\") did the trick. Not so in 8.0. We've tried every combination of single and double quotes that you can imagine, all to no avail. We tried putting quotes around the "#SQLPARM# - no luck.

Any clues, or suggestions?

Thanks! -> Richard

P.S. Before you ask, the reason we're not doing the reformatting in DataStage is because we're trying to use the same DS job for many many different data dumps, as not to have to maintain many distinct DS jobs.

[chulett]

Hmmm... perhaps opening a case with your official support provider is in order, if all of the usual tricks to stop the quote stripping no longer work in this new version.

How similar are your various queries? Worst case, can you break things up into separate parameters, something like...

Code: Select all

Select to_char(#DATE_FIELD#,'#DATE_MASK#') from #TABLE_NAME#

?

[wahi80]

We had the same problem, got a patch from IBM and it worked with / as escape character

[crouse]

In Version 7.x (and your version of 8.x it sounds like) you need to "escape" your quotes. The value of the job parm would be: "Select to_char(APPCT_DATE,\'YYYYMMDD\') from X22.TABLE_NAME"

This will work at runtime.
If you do a "view data", you need to remove the backslashes. Nice, huh?

Here's what IBM told me:
"Hi Craig,

There is a patch for this problem for 8.0.1 but no such patch has been built for 7.5.2.

Another workaround that is possible is to set the parameter to the value of the data without the single quotes and in the SQL statement it self specify the single quotes:

'#parameter#'

This should be resolved ok at runtime and in the View Data.

I can request the 8.0.1 patch be investigated for back port to 7.5.2 but I am not sure if it is possible. Let me know if you want to pursue this route or if the wrokaround helps."
-end quote-

So, in my opinion, the better work around is to escape the quotes.
Via IBM's suggestion, you'd have to create 3 seperate job parameters (stuff before the quote, stuff between the quotes, stuff after the quotes). Not real attractive.

Beware... I've been told that if you incorporate the escapes in 7.x code and them migrate to 8.x, the code may not work anymore depending on if your version has the patch applied or not.

So, you should request the patch from IBM for your 8.x version first. If not available, use the escapes. Then when the patch is available you'll just need to update your code that passes the job parm value.

[Richard615]

Thanks for the replies. Our main IBM contact person is off this week, so we'll definitely be following up with them next Monday regarding patches and suggestions. Just thought I'd try and do a little digging in the meantime.

Breaking down the sql in pieces really isn't an option. The actual sql varies greatly from one job instance to the next with dates all over the place - the short bit I provided here was just the shortest example I could come up with to illustrate the problem.

And we did try the \ escape with no luck - this is actually functioning 7.5 code that we're trying to port to 8.0.

Thanks again,

-> Richard

[Richard615]

By the way, anyone got a patch number for the patch in question handy?

-> Richard

[DS_FocusGroup]

Hi Richard,

Did you find a solution for this? I'm facing the same problem in v8.1 while passing single quotes to a parameter (SQL WHERE clause).

Thanks!