Pass SQL Query @ Run time not working

[SURA]

Hi there

DS 8.5
OS Win
DB SQL Server 2008

All I am trying to do is, pass the whole SQL Query at the run time. I used SQL Server EE stage to read the data from SQL Server. In the Query Option i passed a variable name. At the run time i am passing the below query

Code: Select all

SELECT job_nme + '|' as JOB_NAME  FROM dbo.table_name where enabled_flg = 1 order by 1

When i did the view data , it is perfect and i can see the output. Whereas when i run the job i am getting the below error.

Code: Select all

main_program: PATH search failure: 
main_program: Could not locate operator definition, wrapper, or Unix command for " as JOB_NAME  FROM dbo.table_name where enabled_flg = 1 order by 1 ) x"; please check that all needed libraries are preloaded, and check the PATH for the wrappers

This is the only place getting Param value and rest are hard coded (for test).

Can any one have any clue ? Any guidance?

Please!

Note: I added the Disable combination is true

[SURA]

When i taken out the + '|' from the SQL Query and handle it in Datastage TFM works fine. Again, the purpose of the run time query option is to have flexibility. Say for example if i given the query like below, again the job is failing.

Code: Select all

SELECT top 5 job_nme as JOB_NAME  FROM dbo.table where enabled_flg = 1 and job_nme like 'LND_%'

I understood clearly it is because of the special meaning for the characters like +,|,% etc Datastage have!!

How to use escape character in this scenario?

Code: Select all

SELECT top 5 job_nme as JOB_NAME  FROM dbo.table where enabled_flg = 1 and job_nme like 'LND_'\%'' ??

The above code is not working for me, but need something like this will allow the job to run!

Any suggestion please!

[ArndW]

The pipe symbol "|" is being interpreted in UNIX to split the line into 2 commands.

Perhaps you could quote the pipe, i.e. "\|".

[ray.wurlod]

The single quotes may also be being stripped out. Try it with

Code: Select all

\'\|\'

[chulett]

Seems like another place where setting APT_OSL_PARAM_ESC_SQUOTE may help.

[arunkumarmm]

In your db stage, did you give the parameter within quotes? If not, I would suggest trying it out...

[SURA]

The pipe symbol "|" is being interpreted .

Hi ArndW

I tried like below

Code: Select all

SELECT job_nme as JOB_NAME  FROM dbo.table_name where enabled_flg = 1  and job_nme like 'LND_"\%"'

and the error is

Code: Select all

main_program: [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near ''.

Please let me know, if i missed anything.

[SURA]

Try it with ....

Hi Ray

SQL Code

Code: Select all

SELECT job_nme as JOB_NAME  FROM dbo.table_name  where enabled_flg = 1 and job_nme like \'LND_\%\'

But job aborted with the below error

Code: Select all

main_program: [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near 'LND_'.

[SURA]

Seems like another place where setting

I added this variable with the value 1 for all the runs. Still no luck.

[SURA]

In your db stage, did you give the parameter within quotes? If not, I would suggest trying it out...

If i made the single quote in front of the variable will work in general, but not in this case. Because if i used the variable only for the where clause value then it is fine, but here i am passing the whole SQL query. So if i made this then the input will be like 'select job_nme from table_name' and it is like a string to the SQL and not the query.

[chulett]

Seems like another place where setting

I added this variable with the value 1 for all the runs. Still no luck.

Its only purpose is to stop single quotes from being stripped from the parameter value. It wasn't mentioned as a solution to the larger problem of all of the overzealous escaping.

[chulett]

I understood clearly it is because of the special meaning for the characters like +,|,% etc Datastage have!!

Those characters have no "special meaning" to DataStage, that SQL would be passed to the database and run there as any other client tool would. As noted, the only "special" thing that may be happening is the single quotes being stripped out, which can cause the syntax errors you are seeing. It ends up trying to run:

Code: Select all

SELECT job_nme + | as JOB_NAME FROM dbo.table_name where enabled_flg = 1 order by 1

Which is obviously invalid syntax.

What happens if you try using the original SQL you posted (without any attempts to escape anything) and add $APT_OSL_PARAM_ESC_SQUOTE set to true to the job? What works or does not work? What error message(s) do you get then?

[SURA]

Code: Select all

SELECT job_nme + | as JOB_NAME FROM dbo.table_name where enabled_flg = 1 order by 1

Which is obviously invalid syntax.

I am not sure, is that the same syntax what i have given in my first thread.

Herewith the my syntax:

Code: Select all

SELECT job_nme + '|' as JOB_NAME  FROM dbo.table_name where enabled_flg = 1 order by 1

In relates with the other question:

What works or does not work? What error message(s) do you get then?

I executed the job and got the below errors.

Code: Select all

APT_OSL_PARAM_ESC_SQUOTE = 1
SELECT job_nme + '|'  as JOB_NAME  FROM dbo.table_name where enabled_flg = 1 order by 1

Errors from the log:

main_program: PATH search failure:
main_program: Could not locate operator definition, wrapper, or Unix command for " as JOB_NAME FROM dbo.table_name where enabled_flg = 1 order by 1"; please check that all needed libraries are preloaded, and check the PATH for the wrappers
main_program: Operator as JOB_NAME FROM dbo.table_name where enabled_flg = 1 order by 1 does not exist in the registry.
main_program: Creation of a step finished with status = FAILED.

Let me know, if i misunderstood your question / suggestion.

[chulett]

No, you did what I asked. To me it seems that even with the APT parameter added your single quotes are still being stripped. At this point I would involve your official support provider.

[SURA]

Launched a PMR with IBM. I will post the outcome.

Thanks to all for your support and suggestions.