Informix

nested-group-icon.png

DB2

Expand all | Collapse all

Using the right SQL parameter type

  • 1.  Using the right SQL parameter type

    Posted 22 days ago
    Hi all,

    I try to convince my colleagues about this:

    No matter the SQL API (we do ESQL/C), to me it's always better to use the right program variable data type for SQL parameters, that corresponds to the SQL column type. For ex in ESQL/C, use a date/int for a DATE column... dec_t for DECIMAL(P,S)

    The discussion is about writing generic code. Others argue that it's ok to bind SQL parameters always as char strings for any sort of target SQL column type... it's the job of the DB client / engine to deal with this. Of course the string value must represent a correct target SQL type value (I mean "12/23/2020" for a DATE column for example when DBDATE="MDY4/")

    Me I would NOT use char string variables to hold any SQL date type, just because any SQL type value can be serialized and de-serialized as string. I would always use a dec_t structure for a DECIMAL(P,S) column and not a char[35]... this is just simply obvious to me.

    I would appreciate if anyone can share some experience where using a char/varchar/string SQL parameter causes functional problems or performance problems, when the SQL column type is not a char string type.

    Thanks in advance!
    Seb

    ------------------------------
    Sebastien FLAESCH
    ------------------------------


  • 2.  RE: Using the right SQL parameter type

    Posted 22 days ago
    Sebastien:

    I have always used the correct host data type that corresponds to the server's type even in dynamic code. You can see that most prominently in my dbcopy utility. The only time I would do otherwise is for specific purposes. If I was fetching an integer that I'm only going to print out, I might fetch it into a string. Similarly I might fetch a CHAR type into a CHAR, STRING, or FIXCHAR type depending on what I will be doing with it. Otherwise, I will always use the appropriate type.

    I cannot say that I can report on any plusses or minuses of using string as your colleagues do mainly because I have never considered doing so.

    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: Using the right SQL parameter type

    Posted 21 days ago

    Hello Sebastien,

    I noticed a big disadvantage in performance if the variables differ in data type.

    The database server INFORMIX casts data types to the most suitable format implicit without asking.

    At a customer we could improve performance by 20% by avoiding this internal casts when handling numbers. They used CHAR(8) and the cpu load and prformance was much better after they changed to INT.

    Regards,

    Gerd



    ------------------------------
    Gerd Kaluzinski
    ------------------------------



  • 4.  RE: Using the right SQL parameter type

    Posted 21 days ago
    Thank you Art and Gerd for your answers!
    I will do some perf tests.

    ------------------------------
    Sebastien FLAESCH
    ------------------------------



  • 5.  RE: Using the right SQL parameter type

    Posted 18 days ago
    Hello!

    I found a case where using a string parameter "does not work".

    Informix can use a DATE parameter for EXTEND(?, YEAR TO MINUTE), but when using a VARCHAR formatted from DBDATE is fails with error -1262 ...

    This is just obvious and normal to me, but wanted to find a real case.

    MAIN
    DEFINE da DATE
    DEFINE vc VARCHAR(10)
    DEFINE id INTEGER
    DEFINE dt DATETIME YEAR TO MINUTE

    DATABASE test1

    LET da = MDY(10,20,1998)
    DISPLAY "da = ", da
    LET vc = da
    DISPLAY "vc = ", vc

    WHENEVER ERROR CONTINUE

    SELECT EXTEND(da, YEAR TO MINUTE) INTO dt FROM systables WHERE tabid = 5
    DISPLAY "1: ", SQLCA.SQLCODE

    SELECT EXTEND(vc, YEAR TO MINUTE) INTO dt FROM systables WHERE tabid = 5
    DISPLAY "2: ", SQLCA.SQLCODE

    SELECT tabid INTO id FROM systables WHERE tabid = 5
    AND DATETIME(2020-10-20 11:22) YEAR TO MINUTE
    - EXTEND(da, YEAR TO MINUTE)
    > INTERVAL(0) DAY TO DAY
    DISPLAY "3: ", SQLCA.SQLCODE

    SELECT tabid INTO id FROM systables WHERE tabid = 5
    AND DATETIME(2020-10-20 11:22) YEAR TO MINUTE
    - EXTEND(vc, YEAR TO MINUTE)
    > INTERVAL(0) DAY TO DAY
    DISPLAY "4: ", SQLCA.SQLCODE

    END MAIN


    ------------------------------
    Sebastien FLAESCH
    ------------------------------



  • 6.  RE: Using the right SQL parameter type

    Posted 18 days ago
    Sebastian:

    But this would work:

    DEFINE da VARCHAR(20)

    LET da = "10/20/1998"
    SELECT EXTEND(da::DATE, YEAR TO MINUTE) INTO dt FROM systables WHERE tabid = 5
    Here's my version in ESQL/C, should work in 4GL as well:

    ======================================================
    exec sql include sqlca;
    EXEC SQL INCLUDE sqlhdr;
    exec sql include datetime;
    #include <stdio.h>
    #include <unistd.h>

    int main() {

      exec sql begin declare section;
      dtime_t created_dt;
      dtime_t *p_created_dt;
      int da;
      string buff[128];
      exec sql end declare section;

      p_created_dt = &created_dt;

      exec sql database art;

      ifx_strdate( "12/13/2020", &da, 'R' );

      exec sql declare dt_c cursor for
        select extend( :da::DATE, YEAR TO MINUTE) as created_dt FROM systables WHERE tabid = 5;
      printf( "Declare: sqlcode: %d, sqlca.sqlerrd[1] = %d.\n", sqlca.sqlcode, sqlca.sqlerrd[1] );

      exec sql open dt_c;
      printf( "Open: sqlcode: %d, sqlca.sqlerrd[1] = %d.\n", sqlca.sqlcode, sqlca.sqlerrd[1] );
      exec sql fetch dt_c into :created_dt;
      printf( "Fetch: sqlcode: %d, sqlca.sqlerrd[1] = %d.\n", sqlca.sqlcode, sqlca.sqlerrd[1] );
      dttoasc( &created_dt, buff );
      printf( "    created: %s.\n", buff );
      exec sql close dt_c;
      printf( "Close: sqlcode: %d, sqlca.sqlerrd[1] = %d.\n", sqlca.sqlcode, sqlca.sqlerrd[1] );
    }
    =======================================================
    Test run:

    art@Elbereth:~$ esql -o testdt testdt.ec           
    art@Elbereth:~$ ./testdt
    Declare: sqlcode: 0, sqlca.sqlerrd[1] = 0.
    Open: sqlcode: 0, sqlca.sqlerrd[1] = 0.
    Fetch: sqlcode: 0, sqlca.sqlerrd[1] = 0.
       created: 2020-12-13 00:00.
    Close: sqlcode: 0, sqlca.sqlerrd[1] = 0.
    art@Elbereth:~$

    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.








  • 7.  RE: Using the right SQL parameter type

    Posted 18 days ago
    Hello Art,

    Good point... that double-colon cast operator can be useful in such case.

    However, our goal it to write generic code, that can execute simple SQL statements without using DB-vendor specific syntax elements.

    The SQL statements should be portable and work with different database engines (sorry for that, this is what we sell: Genero BDL www.4js.com)

    But thanks for the tip!

    ------------------------------
    Sebastien FLAESCH
    ------------------------------



  • 8.  RE: Using the right SQL parameter type

    Posted 18 days ago
    Sebastien:

    Gotcha. Yeah, definitely harder to do dynamic type detection in any of the 4GL dialects. If you were sticking to Informix you could probably write the PREPARE/DECLARE/FETCH/CLOSE in ESQL/C and get away with it. 

    Hmm, that might work even for other databases in Querix Lycia since they have their own ESQL/C compiler (haven't tried, but interesting exercise), but certainly not in Genero, no, so doesn't help you.

    Eric any thoughts on that last? Have you tried it in Lycia?

    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.








  • 9.  RE: Using the right SQL parameter type

    Posted 18 days ago
    The Aubit compiler might work as well - big advantage is it free - saved one of my customers serious money.  And as you have the compiler source you can add tweaks as required

    Cheers
    Paul

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