Problem Statement:
In warehouse Builder, adding a presql / postsql AS/400 command that contains quotes fails.
Solution:
Use Warehouse Builder PRE/POST SQL statements with internal quotes. There are now two types of commands you can enter: SQL Statements and Server Commands.
For Server Commands, a change was made to automatically add the CALL QSYS.QCMDEXC wrapper for the user. This function was built for standard AS/400 commands. For commands that have quotes in them the wrapper is not built correctly. The length of the string is not counted properly because control characters are needed by the SQL command processor to ignore the internal quotes. The wrapper counts all characters in the string but the SQL command processor does not count control characters.
Therefore, in the example below the actual length of the command string is 57, but for the command to run in SQL the length needs to be 55, ignoring the two extra quotes to make the command valid to be executed in SQL.
Warehouse Builder counts all characters but the AS/400 command processor does not count the extra quotes since it recognizes them as control characters.
The CALL QSYS/QCMDEXC executes everything inside of the outer quotes, so if the string you want to execute has quotes in it they must be ignored. For the AS/400 to recognize that there is a quote as part of a string you need to have two quotes back to back. That lets the AS/400 know to keep reading the string. In the case of the command above we need to double both of the quotes in the MSG parameter manually for the command to work in SQL
Example:
This statement works in Interactive SQL on the iSeries: Note: That the '' listed below are two single quotes—not one double quote.
call qsys/qcmdexc( 'SNDMSG MSG(''This message has quotes in it.'') TOUSR(DEP)', 0000000055.00000)
In Warehouse Builder you need to enter the command like below:
SNDMSG MSG(''This message has quotes in it.'') TOUSR(DEP)
This generates:
call qsys.qcmdexc( 'SNDMSG MSG(''This message has quotes in it.'') TOUSR(DEP)', 0000000057.00000)
Workaround:
To get commands with embedded quotes in them to run in a PRE or POST SQL statement, enter the command as an SQL statement and add the wrapper yourself. You will probably want to try running the command in SQL on the AS/400 first to be sure it is formatted correctly before adding it as a PRE SQL statement.
Ref#: 1475893
