Page 1 of 2
Pass SQL Query @ Run time not working
Posted: Mon Aug 26, 2013 9:14 pm
by 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
Posted: Mon Aug 26, 2013 9:54 pm
by 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!
Posted: Tue Aug 27, 2013 12:41 am
by ArndW
The pipe symbol "|" is being interpreted in UNIX to split the line into 2 commands.
Perhaps you could quote the pipe, i.e. "\|".
Posted: Tue Aug 27, 2013 2:58 am
by ray.wurlod
The single quotes may also be being stripped out. Try it with
Posted: Tue Aug 27, 2013 6:54 am
by chulett
Seems like another place where setting APT_OSL_PARAM_ESC_SQUOTE may help.
Posted: Tue Aug 27, 2013 12:11 pm
by arunkumarmm
In your db stage, did you give the parameter within quotes? If not, I would suggest trying it out...
Posted: Tue Aug 27, 2013 8:57 pm
by SURA
ArndW wrote: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.
Posted: Tue Aug 27, 2013 9:00 pm
by SURA
ray.wurlod wrote: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_'.
Posted: Tue Aug 27, 2013 9:02 pm
by SURA
chulett wrote:Seems like another place where setting
I added this variable with the value 1 for all the runs. Still no luck.
Posted: Tue Aug 27, 2013 9:04 pm
by SURA
arunkumarmm wrote: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.
Posted: Tue Aug 27, 2013 9:55 pm
by chulett
SURA wrote:chulett wrote: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.
Posted: Tue Aug 27, 2013 10:06 pm
by chulett
SURA wrote: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?
Posted: Tue Aug 27, 2013 10:49 pm
by SURA
chulett wrote: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:
chulett wrote: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.
Posted: Wed Aug 28, 2013 6:54 am
by 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.
Posted: Wed Aug 28, 2013 5:57 pm
by SURA
Launched a PMR with IBM. I will post the outcome.
Thanks to all for your support and suggestions.