sql-serveroracle-databasegenero

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 Name resolution of SQL objects and note the last paragraphs:

    ORACLE

    With Oracle®, an object name takes the following form:

    [(schema|"schema").](identifier|"identifier")[@database-link]

    Oracle has separate namespaces for different classes of objects (tables, views, triggers, indexes, clusters).

    Object names are limited to 30 chars in ORACLE.

    Unlike Informix, Oracle database object names are stored in UPPERCASE in system catalogs. That means that SELECT "col1" FROM "tab1" will produce an error because those objects are identified by "COL1" and "TAB1" in Oracle system catalogs.

    An Oracle database schema is owned by a user (usually, the application administrator) and this user must create PUBLIC SYNONYMS to provide a global scope for his table names. PUBLIC SYNONYMS can have the same name as the schema objects they point to.

    Solution

    To write portable SQL, regarding database object names:

    1. Use simple database object names (without any owner/schema prefix)

    2. Do not use double quotes to surround database object identifiers.

    3. If needed, define public synonyms to reference database objects in others databases/schema.

    4. Specify database object identifiers in lowercase.

    See also Naming database objects.

    Without double quotes around the database object names, all names will be converted to uppercase letters by ORACLE before executing the SQL.

    Check that you do not use single-quoted or double-quoted table names or column names in your source. Those quotes must be removed because the database interface automatically converts double quotes to single quotes, and Oracle does not allow single quotes as database object name delimiters.

    See also the issue Database Concepts

    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 Dynamic SQL management vs Static SQL statements

    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.