Help with MyODBC and dates
Eduardo García-Mádico Portabella <[email protected]> Wed, 7 Mar 2012 15:55:01 +0100
| Newsgroups | gmane.comp.db.mysql.odbc |
|---|---|
| Message-ID | <[email protected]> |
--GID0FwUMdk1T2AWN Content-Type: text/plain; charset=utf-8 Content-Disposition: inline Content-Transfer-Encoding: quoted-printable X-MIME-Autoconverted: from 8bit to quoted-printable by acsinet15.oracle.com id q28BAOil014423 Hi, I am Eduardo from Eurogar=C3=A1n Inform=C3=A1tica, S.L. I have a customer that has an application that uses an Interbase server. In this server the date format is DD-MM-YYYY. The customer asked us for a MySQL migration but the date format in MyODBC is YYYY-MM-DD as you may know. The application is very difficult to adapt because it is huge and the GUI does not provide facilities for doing a massive edition of the DATE values and fields. So I proposed to the customer the possibility of adapting MyODBC source code to do the translation automatically. We have been working over the code for a few days and modified some funct= ions in driver/utility.c and driver/results.c but, for the moment, our changes have not worked. So we would be grateful if someone helped us in finding why these changes do not work. I am not an experienced programmer but an experienced systems administrator. So please, do not expect very nice code. We attach the actual changes we have made for the sources. Thanks in advance. --=20 +----------------------------------+---------------------+ | Eduardo Garc=C3=ADa-M=C3=A1dico Portabella | Telf. 93.555.40.00 | | Director T=C3=A9cnico | Fax. 93.540.33.87 | +----------------------------------+---------------------+ | Eurogar=C3=A1n Inform=C3=A1tica, S.L. (http://www.eurogaran.com) | | c/ Las Guiller=C3=ADas, 3 - 08320 EL MASNOU (Barcelona) | +--------------------------------------------------------+ La informaci=C3=B3n incluida en este email es CONFIDENCIAL, siendo para u= so exclusivo del destinatario arriba mencionado. Si Ud lee este mensaje y no es el destinatario indicado, le informamos que est=C3=A1 totalmente prohibida cualquier utilizaci=C3=B3n, divulgaci=C3=B3n, distribuci=C3=B3n= y/o reproducci=C3=B3n de esta comunicaci=C3=B3n, total o parcial, sin autoriz= aci=C3=B3n expresa en virtud de la legislaci=C3=B3n vigente. Si ha recibido este mensaje por error, le rogamos nos lo notifique inmediatamente por esta v=C3=ADa y proceda a su eliminaci=C3=B3n junto co= n sus ficheros anexos sin leerlo ni grabarlo. En virtud de lo establecido en la Ley 15/1999, y la LSSICE 34/2002, le informamos que sus datos forman parte de un fichero automatizado titularidad de EUROGARAN INFORMATICA, S.L. La informaci=C3=B3n registrada se utilizar=C3=A1 para informarle por cualquier medio electr=C3=B3nico de nuestras novedades comerciales. Puede ejercer los derechos de acceso, rectificaci=C3=B3n, cancelaci=C3=B3= n y oposici=C3=B3n en: C/Les Guilleries, 3, 08320 El Masnou (Barcelona). En cumplimiento de la Ley 34/2002 del 11 de julio, de Servicios de la Sociedad de la Informaci=C3=B3n y de Comercio Electr=C3=B3nico, le inform= amos que puede revocar en cualquier momento, de forma sencilla y gratuita, el consentimiento para la recepci=C3=B3n de correo electr=C3=B3nico envia= ndo un e-mail con su solicitud a: [email protected] La informaci=C3=B3 inclosa en aquest email =C3=A9s CONFIDENCIAL. Si vost=C3= =A8 llegeix aquest missatge i no =C3=A9s el destinatari indicat, l=E2=80=99in= formem que est=C3=A0 totalment prohibida qualsevol utilitzaci=C3=B3, divulgaci=C3=B3= , distribuci=C3=B3 i/o reproducci=C3=B3 d=E2=80=99aquesta comunicaci=C3=B3,= total o parcial, sense autoritzaci=C3=B3 expressa en virtut de la legislaci=C3=B3 vigent. = Si ha rebut aquest missatge per error, li preguem que ens ho notifiqui immediatament per aquesta via i procedeixi a la seva eliminaci=C3=B3 junt amb els fitxers annexes sense llegir-lo ni gravar-lo. En virtut d=E2=80=99 all=C3=B2 establert a la Llei 15/1999 i la LSSICE 34= /2002, l=E2=80=99informem que les seves dades formen part d=E2=80=99un fitxer au= tomatitzat titularitat d=E2=80=99EUROGARAN INFORMATICA, S.L. La informaci=C3=B3 regi= strada s=E2=80=99utilitzar=C3=A0 per informar-li, per qualsevol mitj=C3=A0 elect= r=C3=B2nic, de les nostres novetats comercials. Vost=C3=A8 pot exercir els seus drets d=E2=80=99acc=C3=A9s, rectificaci=C3= =B3, cancel=C2=B7laci=C3=B3 i oposici=C3=B3 a la seg=C3=BCent adre=C3=A7a: C/Les Guilleries, 3, 08320 E= l Masnou (Barcelona). En compliment de la Llei 34/2002 d=C2=B411 de juliol de Serveis de la Societat de la Informaci=C3=B3 i del Comer=C3=A7 Electr=C3=B2nic, l=E2=80= =99informem que pot revocar en qualsevol moment, de forma senzilla i gratu=C3=AFta, el consentiment per a la recepci=C3=B3 de correu electr=C3=B2nic, enviant un= correu electr=C3=B2nic amb la seva sol=C2=B7licitud a: [email protected] --GID0FwUMdk1T2AWN Content-Type: text/x-diff; charset=iso-8859-1 Content-Disposition: attachment; filename="ddmmyyyy_date_format.diff" Content-Transfer-Encoding: quoted-printable X-MIME-Autoconverted: from 8bit to quoted-printable by acsinet15.oracle.com id q28BAOil014423 diff -urN mysql-connector-odbc-5.1.10-src/ChangeLog mysql-connector-odbc-= 5.1.10.1-src-ddmmyyyy/ChangeLog --- mysql-connector-odbc-5.1.10-src/ChangeLog 2012-01-24 15:36:54.0000000= 00 +0100 +++ mysql-connector-odbc-5.1.10.1-src-ddmmyyyy/ChangeLog 2012-03-07 14:37= :31.019435954 +0100 @@ -1,3 +1,14 @@ +5.1.10.1 (13-Feb-2012) Eduardo Garcia-Madico Portabella <informatica@e= urogaran.com> + + Functionality added or changed: + * Converted DATE format to DD-MM-YYYY in the client side to allow prog= rams + made for Interbase interact with MySQL with no changes (Interbase fo= rmat + is DD-MM-YYYY while ODBC SQL format is YYYY-MM-DD). + + Bugs fixed: + +---- + 5.1.10 =20 Functionality added or changed: diff -urN mysql-connector-odbc-5.1.10-src/CMakeLists.txt mysql-connector-= odbc-5.1.10.1-src-ddmmyyyy/CMakeLists.txt --- mysql-connector-odbc-5.1.10-src/CMakeLists.txt 2012-01-24 15:36:54.00= 0000000 +0100 +++ mysql-connector-odbc-5.1.10.1-src-ddmmyyyy/CMakeLists.txt 2012-03-07 = 14:37:31.021435946 +0100 @@ -162,7 +162,7 @@ SET(NEW_FLAGS "${CMAKE_${TYPE}_FLAGS${CFG}}") =20 # fix up static libc flags - STRING(REPLACE "/MD" "/MT" NEW_FLAGS "${NEW_FLAG= S}") + STRING(REPLACE "/MD" "/MT" NEW_FLAGS "${NEW_FLAGS}") =20 # Add some additional help for debug builds IF(CMAKE_BUILD_TYPE STREQUAL "Debug") diff -urN mysql-connector-odbc-5.1.10-src/compilar.bat mysql-connector-od= bc-5.1.10.1-src-ddmmyyyy/compilar.bat --- mysql-connector-odbc-5.1.10-src/compilar.bat 1970-01-01 01:00:00.0000= 00000 +0100 +++ mysql-connector-odbc-5.1.10.1-src-ddmmyyyy/compilar.bat 2012-03-07 14= :37:31.022435938 +0100 @@ -0,0 +1,7 @@ +set MYSQL_DIR=3D"C:\Archivos de programa\MySQL\MySQL Server 5.5" +cmake -G "Visual Studio 9 2008" +devenv MYSQL_Connector_ODBC.sln /build Release +rem devenv MYSQL_Connector_ODBC.sln /build RelWithDebInfo +rem Debug Build +rem cmake -G "Visual Studio 10" -DCMAKE_BUILD_TYPE=3DDebug +rem cl MySQL_Connector_ODBC.sln /build Debug diff -urN mysql-connector-odbc-5.1.10-src/CreateBinaryMsi.bat mysql-conne= ctor-odbc-5.1.10.1-src-ddmmyyyy/CreateBinaryMsi.bat --- mysql-connector-odbc-5.1.10-src/CreateBinaryMsi.bat 2012-01-24 15:36:= 54.000000000 +0100 +++ mysql-connector-odbc-5.1.10.1-src-ddmmyyyy/CreateBinaryMsi.bat 2012-0= 3-07 14:37:31.023435939 +0100 @@ -58,6 +58,7 @@ copy Licenses_for_Third-Party_Components.txt ..\wix-installer\bin\mysql-= connector-odbc-%1-win32\Windows\System32 copy COPYING ..\wix-installer\bin\mysql-connector-odbc-%1-win32\Window= s\System32 copy LICENSE.* ..\wix-installer\bin\mysql-connector-odbc-%1-win32\Window= s\System32 +copy *.bat ..\wix-installer =20 REM Creating Commercial msi... cd ..\wix-installer diff -urN mysql-connector-odbc-5.1.10-src/driver/conversion_eg.txt mysql-= connector-odbc-5.1.10.1-src-ddmmyyyy/driver/conversion_eg.txt --- mysql-connector-odbc-5.1.10-src/driver/conversion_eg.txt 1970-01-01 0= 1:00:00.000000000 +0100 +++ mysql-connector-odbc-5.1.10.1-src-ddmmyyyy/driver/conversion_eg.txt 2= 012-03-07 14:37:31.025435951 +0100 @@ -0,0 +1,3 @@ +ficheros implicados: + +- utility.c -=20 diff -urN mysql-connector-odbc-5.1.10-src/driver/driver.h mysql-connector= -odbc-5.1.10.1-src-ddmmyyyy/driver/driver.h --- mysql-connector-odbc-5.1.10-src/driver/driver.h 2012-01-24 15:36:53.0= 00000000 +0100 +++ mysql-connector-odbc-5.1.10.1-src-ddmmyyyy/driver/driver.h 2012-03-07= 14:37:31.026435964 +0100 @@ -114,13 +114,14 @@ #define MY_MAX_PK_PARTS 32 =20 #if MYSQL_VERSION_ID >=3D 50500 -# define x_free(A) { void *tmp=3D (A); if (tmp) my_free((char *) tmp); } +/* # define x_free(A) { void *tmp=3D (A); if (tmp) my_free((char *) tmp)= ; } */ +# define x_free(A) { void *tmp=3D (A); if (tmp) my_free((char *) tmp,MYF= (MY_WME+MY_FAE)); } # ifndef NEAR # define NEAR=20 # endif -#else +#else*/ # define x_free(A) { void *tmp=3D (A); if (tmp) my_free((char *) tmp,MYF= (MY_WME+MY_FAE)); } -#endif +#endif*/ =20 =20 /* We don't make any assumption about what the default may be. */ diff -urN mysql-connector-odbc-5.1.10-src/driver/execute.c mysql-connecto= r-odbc-5.1.10.1-src-ddmmyyyy/driver/execute.c --- mysql-connector-odbc-5.1.10-src/driver/execute.c 2012-01-24 15:36:53.= 000000000 +0100 +++ mysql-connector-odbc-5.1.10.1-src-ddmmyyyy/driver/execute.c 2012-03-0= 7 14:38:26.629681588 +0100 @@ -453,10 +453,15 @@ DATE_STRUCT *date=3D (DATE_STRUCT*) data; if (dbc->ds->min_date_to_zero && !date->year && (date->month =3D=3D date->day =3D=3D = 1)) - sprintf(buff, "0000-00-00"); + /* sprintf(buff, "0000-00-00"); */ + sprintf(buff, "00-00-0000"); else + /* Eurogar=E1n conversion from 00-00-0000 to 0000-00-00 sprintf(buff, "%04d-%02d-%02d", date->year, date->month, date->day); + */ + sprintf(buff, "%02d-%02d-%04d", + date->day, date->month, date->year); data=3D buff; length=3D 10; break; @@ -477,11 +482,15 @@ TIMESTAMP_STRUCT *time=3D (TIMESTAMP_STRUCT*) data; if (dbc->ds->min_date_to_zero && !time->year && (time->month =3D=3D time->day =3D=3D = 1)) - sprintf(buff, "0000-00-00 %02d:%02d:%02d", + /* sprintf(buff, "0000-00-00 %02d:%02d:%02d", */ + sprintf(buff, "00-00-0000 %02d:%02d:%02d", /* Conversi=F3n Eurogar=E1= n DD-MM-YYYY */ time->hour, time->minute, time->second); else - sprintf(buff, "%04d-%02d-%02d %02d:%02d:%02d", - time->year, time->month, time->day, + /* sprintf(buff, "%04d-%02d-%02d %02d:%02d:%02d", + time->year, time->month, time->day, */ + /* Conversi=F3n Eurogar=E1n DD-MM-YYYY */ + sprintf(buff, "%02d-%02d-%04d %02d:%02d:%02d", + time->day, time->month, time->year, time->hour, time->minute, time->second); data=3D buff; length=3D 19; diff -urN mysql-connector-odbc-5.1.10-src/driver/myutil.h mysql-connector= -odbc-5.1.10.1-src-ddmmyyyy/driver/myutil.h --- mysql-connector-odbc-5.1.10-src/driver/myutil.h 2012-01-24 15:36:53.0= 00000000 +0100 +++ mysql-connector-odbc-5.1.10.1-src-ddmmyyyy/driver/myutil.h 2012-03-07= 14:37:31.029435982 +0100 @@ -225,7 +225,7 @@ int unireg_to_c_datatype(MYSQL_FIELD *field); int default_c_type(int sql_data_type); ulong bind_length(int sql_data_type,ulong length); -my_bool str_to_date(SQL_DATE_STRUCT *rgbValue, const char *str, +my_bool str_to_date(SQL_DATE_STRUCT *rgbValue, char *str, uint length, int zeroToMin); my_bool str_to_ts(SQL_TIMESTAMP_STRUCT *ts, const char *str, int zeroToM= in); my_bool str_to_time_st(SQL_TIME_STRUCT *ts, const char *str); diff -urN mysql-connector-odbc-5.1.10-src/driver/results.c mysql-connecto= r-odbc-5.1.10.1-src-ddmmyyyy/driver/results.c --- mysql-connector-odbc-5.1.10-src/driver/results.c 2012-01-24 15:36:53.= 000000000 +0100 +++ mysql-connector-odbc-5.1.10.1-src-ddmmyyyy/driver/results.c 2012-03-0= 7 15:30:30.562600154 +0100 @@ -257,6 +257,25 @@ case SQL_C_BINARY: { char buff[21]; + /* DD-MM-YYYY to YYYY-MM-DD by Eurogaran */ + /* 0123456789 0123456789 */ + char buff2[21]; + if (length =3D=3D 10) { + if (value[2] =3D=3D '-' && value[4] =3D=3D '-') { + buff2[0]=3Dvalue[8]; + buff2[1]=3Dvalue[9]; + buff2[2]=3Dvalue[7]; + buff2[3]=3Dvalue[5]; + buff2[4]=3Dvalue[6]; + buff2[5]=3Dvalue[4]; + buff2[6]=3Dvalue[0]; + buff2[7]=3Dvalue[1]; + buff2[8]=3Dvalue[2]; + buff2[9]=3Dvalue[3]; + value =3D buff2; + } + } + if (field->type =3D=3D MYSQL_TYPE_TIMESTAMP && length !=3D 19) { /* Convert MySQL timestamp to full ANSI timestamp format. */ @@ -385,7 +404,16 @@ (SQLINTEGER) atol(value + 8)); } else - *((SQLINTEGER *)rgbValue)=3D (SQLINTEGER) atol(value); + /* Conversion to/for DD-MM-YYYY by Eurogaran */ + if (length >=3D 10 && value[2] =3D=3D '-' && value[5] =3D=3D '-' && + (!value[10] || value[10] =3D=3D ' ')) + { + *((SQLINTEGER *)rgbValue)=3D ((SQLINTEGER) atol(value) + + (SQLINTEGER) atol(value + 3) * 100L + + (SQLINTEGER) atol(value + 5) * 10000L); + } + else + *((SQLINTEGER *)rgbValue)=3D (SQLINTEGER) atol(value); else *((SQLINTEGER *)rgbValue)=3D (SQLINTEGER)(numericValue & (SQLUINTEGER)(-1)); diff -urN mysql-connector-odbc-5.1.10-src/driver/utility.c mysql-connecto= r-odbc-5.1.10.1-src-ddmmyyyy/driver/utility.c --- mysql-connector-odbc-5.1.10-src/driver/utility.c 2012-01-24 15:36:53.= 000000000 +0100 +++ mysql-connector-odbc-5.1.10.1-src-ddmmyyyy/driver/utility.c 2012-03-0= 7 14:38:26.631681596 +0100 @@ -1999,12 +1999,32 @@ converted to the min valid ODBC date */ =20 -my_bool str_to_date(SQL_DATE_STRUCT *rgbValue, const char *str, +my_bool str_to_date(SQL_DATE_STRUCT *rgbValue, char *str, uint length, int zeroToMin) { uint field_length,year_length,digits,i,date[3]; + uint cdigits; const char *pos; const char *end=3D str+length; + const char *cpos; + char tmp[10]; + char *cstr =3D str; + + /* Conversion from DD-MM-YYYY to YYYY-MM-DD by Eurogaran */ + for ( ; !isdigit(*cstr) && cstr !=3D end ; ++cstr ) ; + for ( cpos=3D cstr; cpos !=3D end && isdigit(*cpos) ; ++cpos ) ; + cdigits=3D (uint) (cpos-cstr); + /* 2 first number of digits is format DD-MM-YYYY */ + if (cdigits =3D=3D 2 && length >=3D 10) { + memcpy(tmp+8, str , 2); /* DD */ + memcpy(tmp+7, str+2, 1); /* - */ + memcpy(tmp+5, str+3, 2); /* MM */ + memcpy(tmp+4, str+5, 1); /* - */ + memcpy(tmp , str+6, 4); /* YYYY */ + } + memcpy(str, tmp, 10); + /* End conversion */ + for ( ; !isdigit(*str) && str !=3D end ; ++str ) ; /* Calculate first number of digits. diff -urN mysql-connector-odbc-5.1.10-src/util/MYODBCUtil.h mysql-connect= or-odbc-5.1.10.1-src-ddmmyyyy/util/MYODBCUtil.h --- mysql-connector-odbc-5.1.10-src/util/MYODBCUtil.h 2012-01-24 15:36:53= .000000000 +0100 +++ mysql-connector-odbc-5.1.10.1-src-ddmmyyyy/util/MYODBCUtil.h 2012-03-= 07 14:37:31.033435990 +0100 @@ -30,6 +30,10 @@ # define _GNU_SOURCE #endif =20 +#ifdef __cplusplus +extern "C" { +#endif + #include <ctype.h> #include <stdio.h> #include <stdlib.h> @@ -252,6 +256,3 @@ #endif =20 #endif - - - diff -urN mysql-connector-odbc-5.1.10-src/util/stringutil.h mysql-connect= or-odbc-5.1.10.1-src-ddmmyyyy/util/stringutil.h --- mysql-connector-odbc-5.1.10-src/util/stringutil.h 2012-01-24 15:36:53= .000000000 +0100 +++ mysql-connector-odbc-5.1.10.1-src-ddmmyyyy/util/stringutil.h 2012-03-= 07 14:37:31.035436003 +0100 @@ -41,11 +41,11 @@ #include <sqlext.h> =20 #ifndef x_free -# if MYSQL_VERSION_ID >=3D 50500 +/*# if MYSQL_VERSION_ID >=3D 50500 # define x_free(A) { void *tmp=3D (A); if (tmp) my_free((char *) tmp); = } -# else +# else*/ # define x_free(A) { void *tmp=3D (A); if (tmp) my_free((char *) tmp,MY= F(MY_WME+MY_FAE)); } -# endif +/*# endif*/ #endif =20 #define myodbc_min(a, b) ((a) < (b) ? (a) : (b)) --GID0FwUMdk1T2AWN Content-Type: text/plain; charset=us-ascii -- MySQL ODBC Mailing List For list archives: http://lists.mysql.com/myodbc To unsubscribe: http://lists.mysql.com/myodbc --GID0FwUMdk1T2AWN--