Troubleshooting
Problem
Problem with using parameter with single quote in a SQL statement in any Database Stage. Single quotes are stripped off instead of being escaped. Error reported in job log states: "The provided query statement did not prepare correctly; please verify that your statement is correct."
Symptom
Invalid SQL because single quotes are stripped off.
Cause
Single quotes are stripped off.
Diagnosing The Problem
Potential errors reported are:
The provided query statement did not prepare correctly; please verify that your statement is correct.
OSUP_APPT_T,0: [IIS-CONN-ORA-001003] The OCI function OCIStmtExecute returned status -1. Error code: 1,840, Error message: ORA-01840: input value not long enough for date format. (CC_OraStatement::executeSelect, file CC_OraStatement.cpp, line 1,946)
Resolving The Problem
APT_OSL_PARAM_ESC_SQUOTE should be set.
Set this environment variable in jobs where you want to preserve single quotes in job parameters.
Add APT_OSL_PARAM_ESC_SQUOTE as a user-defined environment variable in DataStage Administrator.
Then add this variable via the job parameters in the job and make sure it is set in jobs where you want to preserve single quotes in job parameters.
8.1/8.5 - Solution is just to set the variable APT_OSL_PARAM_ESC_SQUOTE.
8.0.1 - Solution is to apply patch for JR29181 and then set the variable APT_OSL_PARAM_ESC_SQUOTE.
http://www-01.ibm.com/support/docview.wss?uid=swg1JR29181
Related Information
Was this topic helpful?
Document Information
Modified date:
16 June 2018
UID
swg21386517