Search code examples
sql-serveroraclegenero

How can I update a column with double quotes in Genero through Oracle gateway to SQL Server?

We have created a gateway&database link from Oracle to SQL Server, in sqlplus and SQL Developer. After doing so, the following query works:

UPDATE Ckm_cqh@mstest
SET "cqh02" = '2222'
WHERE "cqh01" = '0552'
  AND "cqh02" = '1111';

But when ran in Genero, it fails with the following error:

A program syntax error occurred, in: 'SET' Expected: ':'.
Please check the error message. No.:-6609

Any advice for what might be causing this would be greatly appreciated. Thank you in advance.

Solution

  • You have not indicated if using static or dynamic SQL in Genero, and you are unclear if the error occurs at compile time or at runtime.

    I suspect when you say "ran in Genero" you are actually experiencing an error at compile time, not runtime. With static SQL, the compiler is based on Informix syntax and then at runtime the ODI layer will translate the SQL statement to a form for the target database.

    See https://4js.com/online_documentation/fjs-fgl-manual-html/#fgl-topics/c_fgl_odiagora_028.html and note last paragraphs.

    If you need to include syntax that is native to a particular database then the typical path is to use dynamic SQL in your Genero code. See https://4js.com/online_documentation/fjs-fgl-manual-html/#fgl-topics/c_fgl_DynamicSql_001.html vs https://4js.com/online_documentation/fjs-fgl-manual-html/#fgl-topics/c_fgl_static_sql_001.html

    Hence my first instinct is to remove the unnecessary quotes around the column names. Otherwise use dynamic SQL instead of static SQL if you want to use lots of native Oracle syntax.

    PS You will not find many Genero questions on StackOverflow. You hopefully have a support contract and so normal sequence developers follow when get stuck is 1) your support contact 2) the developer forum on the 4Js website.