General syntax error raised from CONNECT BY query in Informix when using quoted table identifiers

[Lukas Eder]

When running the following query on an Informix database, the database reports a general syntax error (without any indication with respect to what causes the problem). The same query runs perfectly on CUBRID or Oracle databases, both of which also support the CONNECT BY syntax:

------------------------------------------------------------
select
lower(connect_by_root "t_directory"."name"),
connect_by_isleaf,
connect_by_iscycle,
substr(
sys_connect_by_path(lower("t_directory"."name"), '/'),
2) "dir"
from "t_directory"
start with "t_directory"."parent_id" is null
connect by nocycle prior "t_directory"."id" = "t_directory"."parent_id"
order siblings by lower("t_directory"."name") asc
------------------------------------------------------------

The database I'm using is a Developer Edition of Informix 12.10.FC2DE on Windows. I'm running the query from a JDBC driver with the following connection URL (to allow for quoted table identifiers):


jdbc:informix-sqli://localhost:9092/test:INFORMIXSERVER=ol_informix;DELIMIDENT=y


The exact issue here is the fact that prior doesn't accept quoted table identifiers, although quoted column identifiers seem to be fine. This query runs perfectly well:

------------------------------------------------------------
select
lower(connect_by_root "t_directory"."name"),
connect_by_isleaf,
connect_by_iscycle,
substr(
sys_connect_by_path(lower("t_directory"."name"), '/'),
2) "dir"
from "t_directory"
start with "t_directory"."parent_id" is null
connect by nocycle prior t_directory."id" = "t_directory"."parent_id"
order siblings by lower("t_directory"."name") asc
------------------------------------------------------------

... with the difference being:

------------------------------------------------------------
-- Bad:
connect by nocycle prior "t_directory"."id" = "t_directory"."parent_id"

-- Good:
connect by nocycle prior t_directory."id" = "t_directory"."parent_id"
------------------------------------------------------------

Note that the owner/schema identifier seems to be completely prohibited. The actual column name would be informix.t_directory.id, but that doesn't work at all.

In other databases supporting CONNECT BY (CUBRID, Oracle), this is not the case.

[s***@t-online.de]

Hello Lukas,


i have no V12 sorry however you could try mode ansi databases;


eq

create database myansi in somedbspace log mode ansi;
-- check the syntax...
create database myansi log mode ansi;
-- creates it in rootdbs.


Be aware that you get repeatable read isolation level when you logon,
(the spl sysdbopen can be used to change that...)

Dono if this will fix it...


hope it does.


kind regards

Superboer.

Post by Lukas Eder
------------------------------------------------------------
select
lower(connect_by_root "t_directory"."name"),
connect_by_isleaf,
connect_by_iscycle,
substr(
sys_connect_by_path(lower("t_directory"."name"), '/'),
2) "dir"
from "t_directory"
start with "t_directory"."parent_id" is null
connect by nocycle prior "t_directory"."id" = "t_directory"."parent_id"
order siblings by lower("t_directory"."name") asc
------------------------------------------------------------
jdbc:informix-sqli://localhost:9092/test:INFORMIXSERVER=ol_informix;DELIMIDENT=y
------------------------------------------------------------
select
lower(connect_by_root "t_directory"."name"),
connect_by_isleaf,
connect_by_iscycle,
substr(
sys_connect_by_path(lower("t_directory"."name"), '/'),
2) "dir"
from "t_directory"
start with "t_directory"."parent_id" is null
connect by nocycle prior t_directory."id" = "t_directory"."parent_id"
order siblings by lower("t_directory"."name") asc
------------------------------------------------------------
------------------------------------------------------------
connect by nocycle prior "t_directory"."id" = "t_directory"."parent_id"
connect by nocycle prior t_directory."id" = "t_directory"."parent_id"
------------------------------------------------------------
Note that the owner/schema identifier seems to be completely prohibited. The actual column name would be informix.t_directory.id, but that doesn't work at all.
In other databases supporting CONNECT BY (CUBRID, Oracle), this is not the case.