Page 1 of 2
Passing SQL through a string parameter
Posted: Thu Jul 26, 2018 10:22 am
by mjohnson62982
I am currently trying to pass some SQL through a parameter that will allow the user to choose whether to do a full load of information from a table or to just load data from the previous day. The where clause of the select statement in the JDBC Connector looks like this:
Code: Select all
FROM "#Library_ps.Library_Name#"."#TableName#" x
WHERE TRIDTR || '-' || MARQTR IN ('30-0', '32-1', '32-6', '41-77', '40-2', '83-0', '39-0')
AND"#SFPTR_JACKPOT_Load_Type#"
ORDER BY DHYRTR
The SFPTR_JACKPOT_Load_Type parameter set has to options: Full_Load and Partial_Load. The value of the Full_Load option is:
Code: Select all
DHYRTR <= (select days (current date) - days (date('1900-01-01'))+1 FROM SYSIBM.SYSDUMMY1)
The Partial_Load option is:
Code: Select all
DHYRTR <= (select days (current date) - days (date('1900-01-01'))+1 FROM SYSIBM.SYSDUMMY1)
When I try to view the data from the table I get the following error:
The connector failed to add missing columns to the output link. The rported error is: com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-199, SQLSTATE=42601, SQLERRMC=ORDER;< > = <> <= !< !> != >= IN NOT, DRIVER=3.66.46
I would appreciate any help or suggestions to accomplish the task.
Posted: Thu Jul 26, 2018 12:15 pm
by chulett
Passing SQL (or anything else for that matter) with single quotes in it as a parameter has always been an issue.
This solution is a bit old but should solve your problem.
Posted: Thu Jul 26, 2018 12:27 pm
by mjohnson62982
I attempted this fix, however I am still getting the same message.
Posted: Thu Jul 26, 2018 12:39 pm
by chulett
Interesting.
Do you know what the actual SQL that it ends up trying to run looks like? Trying to see if there's something more than the typical "stripping of quotes" going on here.
Oh - and just noticed that you marked this as a Server job problem, is that the case? AFAIK, that APT fix only works on PX jobs...
Posted: Thu Jul 26, 2018 12:42 pm
by mjohnson62982
I think it looks like this...
Code: Select all
DHYRTR <= (select days (current date) - days (date('1900-01-01'))+1 FROM SYSIBM.SYSDUMMY1)
I wonder is there a problem with putting characters like "<=" in a string parameter?
Posted: Thu Jul 26, 2018 1:06 pm
by chulett
I don't think so but others may know more better. So... Server job?
Posted: Thu Jul 26, 2018 1:10 pm
by mjohnson62982
Well it was a job transferring specified information from an iSeries table to a DB2 table. Not sure what it should have be labeled as. Sorry for any confusion.
Posted: Thu Jul 26, 2018 2:48 pm
by chulett
I only asked because you marked the Job Type property in the initial post as Server. I'm going to assume you are using Parallel jobs as Server job questions here are (sadly) few and far between.
Posted: Fri Jul 27, 2018 2:22 pm
by chulett
Took another look at what you posted while I wait for something to complete and have a couple of comments. First off, your full and partial load parameter values look remarkably similar.
Secondly, perhaps try this minor change:
Code: Select all
FROM "#Library_ps.Library_Name#"."#TableName#" x
WHERE TRIDTR || '-' || MARQTR IN ('30-0', '32-1', '32-6', '41-77', '40-2', '83-0', '39-0')
AND DHYRTR <= #SFPTR_JACKPOT_Load_Type#
ORDER BY DHYRTR
Then your parameter string would just be:
Code: Select all
(select days (current date) - days (date('1900-01-01'))+1 FROM SYSIBM.SYSDUMMY1)
I don't see a need for the double-quotes in that part of the where clause. Be curious if that changes the error message at all.
Posted: Tue Jul 31, 2018 7:46 am
by mjohnson62982
I tried it without the double quotes and got the same message. But the reason I am using the parameter to give me the 2 options is that I need to allow the user to run the query to pull every row from the table or just the rows from the previous day.
Posted: Tue Jul 31, 2018 7:54 am
by mjohnson62982
I'm sorry the partial load SQL is:
Code: Select all
DHYRTR = (select days (current date) - days (date('1900-01-01')) FROM SYSIBM.SYSDUMMY1)
Posted: Tue Jul 31, 2018 8:10 am
by chulett
I understand the why of it. So did you try the modification I suggested or just without the double quotes? The latter was just an off-hand comment, the former was an attempt to get something working for you.
Posted: Tue Jul 31, 2018 8:28 am
by mjohnson62982
It works if it is not in a parameter.
Posted: Tue Jul 31, 2018 9:55 am
by chulett
Sure. So... you're not going to try the minor change I mentioned? As a test.
Posted: Tue Jul 31, 2018 10:13 am
by mjohnson62982
The error I get now is:
Code: Select all
The reported error is: com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL error: SQLCODE=-206, SQLSTATE=42703, SQLERRMC=SFPTR_JACKPOT_FULL_LOAD;*N.N, DRIVER=3.66.46