Informix

Expand all | Collapse all

ESQL/C: DESCRIBE, DESCRIBE OUTPUT and DESCRIBE INPUT

  • 1.  ESQL/C: DESCRIBE, DESCRIBE OUTPUT and DESCRIBE INPUT

    Posted 15 days ago

    Hello!

    I get a bit confused when reading the ESQL/C doc about the DESCRIBE, DESCRIBE OUTPUT and DESCRIBE INPUT statements...

    What is fully supported and what is recommended today, to get fetch buffer information (SELECT list) as well as input parameters (INSERT/UPDATE/DELETE, and also input parameters of a WHERE clause in SELECT...)?

    My understanding so far (to not be dependent from the IFX_UPDDESC env var), is to use DESCRIBE OUTPUT to get information about output data info, and DESCRIBE INPUT to get input parameters info...

    And that the usage of DESCRIBE (without the additional OUTPUT or INPUT keyword) should be avoided because it's deprecated...

    Is this correct?

    To write generic ESQL/C executing SQL statements dynamically (only known at runtime), is it then recommended to use DESCRIBE OUTPUT and DESCRIBE INPUT in any case, to get information about output and input parameters?

    Which one is to be used first (mainly to get the SQL statement type)?

    Is there some overhead to use these instructions or is all information on the client side avec preparing the SQL statement?

    https://www.ibm.com/support/knowledgecenter/SSGU8G_14.1.0/com.ibm.sqls.doc/ids_sqs_0688.htm
    https://www.ibm.com/support/knowledgecenter/SSGU8G_14.1.0/com.ibm.sqls.doc/ids_sqs_0689.htm

    Thanks!
    Seb



    ------------------------------
    SEBASTIEN SF FLAESCH
    ------------------------------


  • 2.  RE: ESQL/C: DESCRIBE, DESCRIBE OUTPUT and DESCRIBE INPUT

    Posted 15 days ago
    Sebastien:

    I have not heard that the general DESCCRIBE without the INPUT or OUTPUT modifier is being deprecated. I hope not because several of my utilities (dbcopy, dbdelete among them) depend on the behavior of DESCRIBE without the modifiers. 

    DESCRIBE alone is equivalent to DESCRIBE OUTPUT and DESCRIBE INPUT together. It returns all available details of the described statement. In my ESQL/C code I use an sqlda structure with the INTO clause rather than using the USING clause with a DESCRIPTOR. That allows me to modify the settings prior to declaring a cursor against the PREPAREd statement that was described. Mostly it is assigning memory locations to the variables for inputs and/or outputs or to configure the sqlda structure to process an ARRAY FETCH (the fastest way to fetch data into the program's memory), but sometimes also to change the data type (kind of like a cast) of the returned data.

    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: ESQL/C: DESCRIBE, DESCRIBE OUTPUT and DESCRIBE INPUT

    Posted 15 days ago

    Thank you Art,

    It depends what we mean by deprecated... to me it mean still available, but to be avoided and replaced but something new.

    The page https://www.ibm.com/support/knowledgecenter/SSGU8G_14.1.0/com.ibm.sqls.doc/ids_sqs_0696.htm shows following Tip:
    Informix versions earlier than 9.40 do not support the INPUT keyword. For compatibility with legacy applications, DESCRIBE without INPUT is supported. In new applications, you should use DESCRIBE INPUT statements to provide information about dynamic parameters in the WHERE clause, in subqueries, and in other syntactic contexts where the old form of DESCRIBE cannot provide information.

    This sounds to me that the recommended way is to use DESCRIBE INPUT instead of DESCRIBE for input parameters, and logically I would then use DESCRIBE OUTPUT for output params...

    And with DESCRIBE (without modifier), there is this limitation on UPDATE statements that requires IFX_UPDDESC env var to be set... you should consider this in your tools if you use UPDATE... DELETE params seem to be totally ignored by DESCRIBE not matter if that env var is set.

    I have tested with code and DESCRIBE OUTPUT / INPUT seems to work much better as DESCRIBE: New statements return sqlda info for INSERT, UPDATE and DELETE (with params in WHERE clause).

    Simple question: If DESCRIBE alone provides input and output data info, what member of the sqlvar_struct indicates if it's an input, output (or both?) data element?

    In the following page: https://www.ibm.com/support/knowledgecenter/en/SSGU8G_14.1.0/com.ibm.sqls.doc/ids_sqs_0692.htm

    I can read:

    "For a prepared INSERT or UPDATE statement, DESCRIBE returns only the dynamic parameters (those expressed with a question mark (?) symbol). Using the OUTPUT keyword, however, prevents these from being returned."

    The more I read the doc, the more I get confused.

    So my conclusion is that one should use DESCRIBE INPUT and DESCRIBE OUTPUT and no longer use DESCRIBE.

    Seb



    ------------------------------
    SEBASTIEN SF FLAESCH
    ------------------------------



  • 4.  RE: ESQL/C: DESCRIBE, DESCRIBE OUTPUT and DESCRIBE INPUT

    Posted 4 days ago
    Hello,

    While playing with DESCRIBE INPUT and DESCRIBE OUTPUT, I have discovered some strange memory leak when freeing the sqlda structure allocated by DESCRIBE INPUT...

    Seems to depend on the order of the FREE statements: This happens when I DECLARE a cursor for a PREPAREd statement. When I do a FREE of the DECLAREd cursor before the FREE of the PREPAREd statement, no memory leak... but when doing the FREE of the PREPAREd statement first, I get this memory leak. I am quite sure it's the sqlda allocated by DESCRIBE INPUT because address sanitizer reports the exact number of bytes used by the sqlda structure...

    I am a bit confused by what happens and any idea is welcome.

    For more details and sample, see https://www.ibm.com/mysupport/s/case/5000z00001RLz8EAAT

    Seb

    ------------------------------
    SEBASTIEN SF FLAESCH
    ------------------------------



  • 5.  RE: ESQL/C: DESCRIBE, DESCRIBE OUTPUT and DESCRIBE INPUT

    Posted 15 days ago
    You could also go lower and use the mi_exec, mi_prepare etc functions and avoid esqlc completely.  Normally they are used with C UDRs but are available in the client libraries as well.

    Cheers
    Paul

    Paul Watson
    Oninit LLC
    +1-913-387-7529
    www.oninit.com
    Oninit®️ is a registered trademark of Oninit LLC





  • 6.  RE: ESQL/C: DESCRIBE, DESCRIBE OUTPUT and DESCRIBE INPUT

    Posted 15 days ago
    Thank you Paul for the suggestion, but we have all our code in ESQL/C and do not plan a full rewrite.
    Seb

    ------------------------------
    SEBASTIEN SF FLAESCH
    ------------------------------



  • 7.  RE: ESQL/C: DESCRIBE, DESCRIBE OUTPUT and DESCRIBE INPUT

    Posted 15 days ago
    Not suggesting rewriting the app just the generic sql handler, I had to use mi_exec etc cos I was in a UDR

    If you want to use esqlc take a look at JL sqlcmd code - everything you need should be in there

    Cheers
    Paul

    Paul Watson
    Oninit LLC
    +1-913-387-7529
    www.oninit.com
    Oninit®️ is a registered trademark of Oninit LLC





  • 8.  RE: ESQL/C: DESCRIBE, DESCRIBE OUTPUT and DESCRIBE INPUT

    Posted 15 days ago
    Ok will have a look thanks!
    Seb

    ------------------------------
    SEBASTIEN SF FLAESCH
    ------------------------------