Troubleshooting > Resolving the SQL Syntax Error Caused by Single Quotes in an Environment Variable

Resolving the SQL Syntax Error Caused by Single Quotes in an Environment Variable

Article #: Product: Version:

Summary

On UNIX/Linux, a SQL syntax error is generated when a DMExpress task submits a user-defined SQL statement containing single quotes through an environment variable.

To resolve the error, modify the SQL statement by inserting an escape character, '\', before the single quote or by enclosing the entire SQL statement within double quotes.

Symptom

On UNIX/Linux, when single quotes are used within the SQL statement in the User-Defined SQL Statement tab of a database source or target table, a SQL syntax error is generated when the DMExpress task runs.

The SQL syntax error varies depending on the database management system (DBMS) and where the single quotes occur. DB2, for example, can return the error SQL0104N (An unexpected token was found).

Resolution

On UNIX/Linux platforms, you must protect the single quotes within the value of an environment variable that is used in a DMExpress database source or target user-defined SQL statement.

Consider the following SQL statement, which is defined in the environment variable MY_SQL_STMT:

SELECT ID, FNAME || ' ' || LNAME AS NAME FROM PEOPLE

When $MY_SQL_STMT is used in the User-Defined SQL Statement tab of a source database table, the statement parses correctly in the DMExpress Task Editor, and the task runs successfully on Windows. On UNIX/Linux, however, the value of $MY_SQL_STMT must be modified to successfully pass this environment variable through a task.

To retain single quotes in a SQL statement stored in an environment variable, UNIX/Linux command interpreters or "shells," such as sh, ksh, bash, and csh, require that a single quote is either preceded by an escape character (\') or that the entire SQL statement is enclosed in double quotes ("<SQL statement>").

In the Job Editor, select Run > Define Environment Variables, and update MY_SQL_STMT in one of the following ways:

SELECT ID, FNAME || \' \' || LNAME AS NAME FROM PEOPLE

"SELECT ID, FNAME || ' ' || LNAME AS NAME FROM PEOPLE"

If you are running a job from a command line or from within a shell script, MY_SQL_STMT is exported as follows:

export MY_SQL_STMT="SELECT ID, FNAME || ' ' || LNAME AS NAME FROM PEOPLE"

dmxjob /run MyJob.dxj

Last updated: