Using the right SQL parameter type

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

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

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

Thank you Art and Gerd for your answers! I will do some perf tests. ------------------------------ Sebastien FLAESCH ------------------------------

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

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.ecart@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 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.

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

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

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