Informix

Expand all | Collapse all

DELIMIDENT

  • 1.  DELIMIDENT

    Posted 29 days ago

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


  • 2.  RE: DELIMIDENT

    Posted 29 days ago
    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 1
    Near 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


    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.








  • 3.  RE: DELIMIDENT

    Posted 29 days ago
    Edited by TOM GIRSCH 29 days ago

    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 DELIM
    DBDELIMITER         |
    DELIMIDENT          1
    $ onstat -g env | grep DELIMID
    DELIMIDENT          1
    $ env | grep DELIMID
    DELIMIDENT=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 1
    Near 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 1
    Near 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 1
    Near character position 67
    Database closed.


    ------------------------------
    TOM GIRSCH
    ------------------------------



  • 4.  RE: DELIMIDENT

    Posted 29 days ago
    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


    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.