SET SQL DIALECT TO POSTGRESQL

Dear Mike,

Trying to create a cursor with native postgresql query as follows:

DATABASE testdb
MAIN
    DEFINE  w_json CHAR(2048)

    DECLARE cur1 CURSOR FOR
      SELECT array_to_json(array_agg(row_to_json(t1)))
          FROM (SELECT field1, field2 FROM t_test) t1

    FOREACH cur1 INTO w_json
        DISPLAY w_json CLIPPED
    END FOREACH

END MAIN

I got the following error:

DATABASE testdb
MAIN
    DEFINE  w_json CHAR(2048)

    DECLARE cur1 CURSOR FOR
      SELECT array_to_json(array_agg(row_to_json(t1)))
          FROM (SELECT field1, field2 FROM t_test) t1
|__________________________________________________^
| Error at line 7, character 52
| syntax error, unexpected CLOSE_BRACKET ())

    FOREACH cur1 INTO w_json
        DISPLAY w_json CLIPPED
    END FOREACH

END MAIN

Adding SET SQL DIALECT statement

DATABASE testdb
MAIN
    DEFINE  w_json CHAR(2048)

    SET SQL DIALECT TO POSTGRESQL

    DECLARE cur1 CURSOR FOR
      SELECT array_to_json(array_agg(row_to_json(t1)))
          FROM (SELECT field1, field2 FROM t_test) t1

    FOREACH cur1 INTO w_json
        DISPLAY w_json CLIPPED
    END FOREACH

END MAIN

I got the following error:

DATABASE testdb
MAIN
    DEFINE  w_json CHAR(2048)

    SET SQL DIALECT TO POSTGRESQL
|______________________________^
| Error at line 5, character 32
| syntax error, unexpected 'SET'  (SET)

    DECLARE cur1 CURSOR FOR
      SELECT array_to_json(array_agg(row_to_json(t1)))
          FROM (SELECT field1, field2 FROM t_test) t1

    FOREACH cur1 INTO w_json
        DISPLAY w_json CLIPPED
    END FOREACH

END MAIN

How to use SET SQL DIALECT statement at run-time to execute native DBMS
query other than informix?

Best regards,

Iwan

_______________________________________________
Aubit4gl-discuss mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/aubit4gl-discuss