Mega Search
23.2 Million


Sign Up

Make a donation  
General syntax error raised from CONNECT BY query in Informi  
News Group: comp.databases.informix

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.

Vote for best question.
Score: 0  # Vote:  0
Date Posted: 6-Aug-2014, at 7:00 AM EST
From: Lukas Eder
 
Re: General syntax error raised from CONNECT BY query in Inf  
News Group: comp.databases.informix
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.





Am Mittwoch, 6. August 2014 16:00:53 UTC+2 schrieb 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.


Vote for best answer.
Score: 0  # Vote:  0
Date Posted: 1-Sep-2014, at 12:35 PM EST
From: e