Re: SET SQL DIALECT TO POSTGRESQL

The base SQL for the 4GL is always Informix style syntax (I did do an
experimental postgresql build some time ago, but never developed it further)

The SQL DIALECT is basically used for the auto conversions - and if you're
using the postgresql SQLTYPEs - that should be auto set.

Simplest thing is to drop the SQL into a PREPARE string and do it that way.



On Mon, 24 Oct 2022 at 00:12, Iwan AB <[email protected]> wrote:

> 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
>

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