DELIMIDENT

All: We have a[n ancient] app that has a bunch of special characters in object names, which requires DELIMIDENT to be set. The problem is, if it's set at all , even to empty string , it's considered "set" and double-quotes are taken to signify identifiers rather than constant strings. The problem is that DELIMIDENT is set in the engine environment, and there are a few client programs that access that are using the double quotes to signify constants. Does anyone know if there's a way to override the setting of DELIMIDENT at the session level? It's easy enough to turn it on for a session if it's off at the engine; but I haven't been able to figure a way to turn it off for a session if it's on at the engine. ------------------------------ TOM GIRSCH ------------------------------ #Informix

Tom: You can certainly set DELIMIDENT at the session level. Witness: art@Elbereth : ~/GoogleDrive/Latest Art's Stuff/myschema.d $ dbaccess art -Database selected. > select count(*) from "systables";201: A syntax error has occurred. Error in line 1Near character position 31 > Database closed. art@Elbereth : ~/GoogleDrive/Latest Art's Stuff/myschema.d $ DELIMIDENT=1 dbaccess art - Database selected. > select count(*) from "systables";(count(*)) 325 1 row(s) retrieved. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. ------Original Message------ All: We have a[n ancient] app that has a bunch of special characters in object names, which requires DELIMIDENT to be set. The problem is, if it's set at all , even to empty string , it's considered "set" and double-quotes are taken to signify identifiers rather than constant strings. The problem is that DELIMIDENT is set in the engine environment, and there are a few client programs that access that are using the double quotes to signify constants. Does anyone know if there's a way to override the setting of DELIMIDENT at the session level? It's easy enough to turn it on for a session if it's off at the engine; but I haven't been able to figure a way to turn it off for a session if it's on at the engine. ------------------------------ TOM GIRSCH ------------------------------ #Informix

Art: You misunderstand my question. Look at the last sentence of my OQ more carefully. I'm aware that I can set it at the session level when it is not set at the engine level. What I want to be able to do is the reverse: the engine was started with DELIMIDENT set, but I want a session to be able to run against that engine as if it's not set. In other words, I want to be able to turn it OFF at the session level. $ onstat -g env | grep DELIMDBDELIMITER | DELIMIDENT 1 $ onstat -g env | grep DELIMIDDELIMIDENT 1 $ env | grep DELIMIDDELIMIDENT=1 $ dbaccess sysmaster < <EOF > SELECT COUNT(*) FROM systables WHERE tabid >= 100 AND tabtype = "T";> EOF Database selected. 217: Column (T) not found in any table in the query (or SLV is undefined). Error in line 1Near character position 67 Database closed. $ unset DELIMIDENT $ env | grep DELIMIDENT $ dbaccess sysmaster < <EOF > SELECT COUNT(*) FROM systables WHERE tabid >= 100 AND tabtype = "T";> EOF Database selected. 217: Column (T) not found in any table in the query (or SLV is undefined). Error in line 1Near character position 67 Database closed. $ export DELIMIDENT= $ dbaccess sysmaster < <EOF > SELECT COUNT(*) FROM systables WHERE tabid >= 100 AND tabtype = "T";> EOF Database selected. 217: Column (T) not found in any table in the query (or SLV is undefined). Error in line 1Near character position 67 Database closed. ------------------------------ TOM GIRSCH ------------------------------

Ahh, misunderstood. Yeah, no. There is no way to turn it off that I am aware of. Indeed, setting DELIMIDENT='n' in your environment is supposed to turn DELIMIDENT behavior off for Java API apps (where the default is ON). But I found that even setting DELIMIDENT=n turns the behavior on for dbaccess and other ESQL apps where the default is OFF. Here is a session with my sqlstruct utility: art@Elbereth : ~/GoogleDrive/Latest Art's Stuff/myschema.d $ sqlstruct -d art -n t > select * from "syscolumns"; Cannot PREPARE statement, error: -201.Error at character 28. Statement is: select * from "syscolumns" ;art@Elbereth : ~/GoogleDrive/Latest Art's Stuff/myschema.d $ DELIMIDENT=y sqlstruct -d art -n t > select * from "syscolumns";typedef struct t_s { char colname[129]; int tabid; short colno; short coltype; short collength; int colmin; int colmax; int extended_id; int seclabelid; short colattr; } t_t, *t_tp; > art@Elbereth : ~/GoogleDrive/Latest Art's Stuff/myschema.d $ DELIMIDENT=n sqlstruct -d art -n t > select * from "syscolumns";typedef struct t_s { char colname[129]; int tabid; short colno; short coltype; short collength; int colmin; int colmax; int extended_id; int seclabelid; short colattr; } t_t, *t_tp; > Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. ------Original Message------ Art: You misunderstand my question. Look at the last sentence of my OQ more carefully. I'm aware that I can set it at the session level when it is not set at the engine level. What I want to be able to do is the reverse: the engine was started with DELIMIDENT set, but I want a session to be able to run against that engine as if it's not set. In other words, I want to be able to turn it OFF at the session level. $ onstat -g env | grep DELIMDBDELIMITER | DELIMIDENT 1 $ onstat -g env | grep DELIMIDDELIMIDENT 1 $ env | grep DELIMIDDELIMIDENT=1 $ dbaccess sysmaster < <EOF > SELECT COUNT(*) FROM systables WHERE tabid >= 100 AND tabtype = "T";> EOF Database selected. 217: Column (T) not found in any table in the query (or SLV is undefined). Error in line 1Near character position 67 Database closed. $ unset DELIMIDENT $ env | grep DELIMIDENT $ dbaccess sysmaster < <EOF > SELECT COUNT(*) FROM systables WHERE tabid >= 100 AND tabtype = "T";> EOF Database selected. 217: Column (T) not found in any table in the query (or SLV is undefined). Error in line 1Near character position 67 Database closed. $ export DELIMIDENT= $ dbaccess sysmaster < <EOF > SELECT COUNT(*) FROM systables WHERE tabid >= 100 AND tabtype = "T";> EOF Database selected. 217: Column (T) not found in any table in the query (or SLV is undefined). Error in line 1Near character position 67 Database closed. ------------------------------ TOM GIRSCH ------------------------------ #Informix