Locales, ODBC and SPL

Hi, I am using VB6 (RDO20.dll) and the newest Informix ODBC driver (3.32 .0000 2.50 tc2) to connect to Informix.2000 on Windows 2000. The client workstation is running NT4(sp5) The database was created with the default locale (en_US.CP1252) Client Settings: ODBC Driver setup, environment: Client Locale: de_de.CP1252 Database Locale: en_US.CP1252 Setnet32: Client Locale: de_de.CP1252 Database Locale: en_US.CP1252 GL_DATE: %d.%m.%Y Windows Regional Settings: Decimal Symbol: , Grouping Symbol: . Date Short Style: dd.MM.yyyy (de_de.CP1252 is the German local and was used because it uses the "," as a decimal symbol) The SQL: Select balance, balance || ' ' , today , today || ' ' from table xyz returns (the 'balance' field is of type decimal(16,3) and holds 123.89): 12389 123,89 15.02.2001 15.02.2001 which seems to suggest that RDO ignores the Informix locales and confuses the Windows regional settings as it comes up with 12389. Informix when converting balance || ' ' and today || ' ' to strings uses correctly the locale settings. When I restore the ODBC locale settings to the default ones: ODBC Driver setup, environment: Client Locale: en_US.CP1252 Database Locale: en_US.CP1252 and leave everything else the same and run the SQL again I get: 123,89 123.89 15.02.2001 02.15.2001 Which seems to suggest that RDO now is able to use the client locale in Setnet32 or the Windows regional settings (and correctly displays balance as 123,89). Informix is ignoring the Setnet32 locale settings (even the GL_DATE) and follows what is in the ODBC set up. No matter how I tried, I was not able to come to a scenario where both RDO and Informix internal conversions would give the same output. There seems to be a more complicated issue then conversions are done inside an SPL. e.g: .. define s1,s2 char(10); define d date; define a decimal(16,3); let d = today; let s1 = d; let a = 2/3; let s2 = a; return s1, s2; Here the values in s1 and s2 reflect the client locale settings at the time of the procedure creation! For example if the procedure is created with the locale settings as described in the 1st case above the result would always be in line with that locale, even if the procedure is called from a different client with different client locales. If the procedure is dropped and recreated with different locale settings the results change to the new locale. All the above findings make it really hard to support more than one locale with ODBC and SPL. Any explanation to clear out the confusion is welcome. Thanks in advance Lambros

Shouldn't you be using Client SDK Version 2.60 to connect to an Informix 2000 Server ??? Yours -- Earle A Long (Senior DBA) SinglePoint Limited "Lambros Papadopoulos" <lambros@ctl.com.cy> wrote in message news:3a8c10e2.0@news.spidernet.net... > Hi, > > I am using VB6 (RDO20.dll) and the newest Informix ODBC driver (3.32 .0000 > 2.50 tc2) to connect to Informix.2000 on Windows 2000. The client > workstation is running NT4(sp5) > > The database was created with the default locale (en_US.CP1252) > > Client Settings: > > ODBC Driver setup, environment: > Client Locale: de_de.CP1252 > Database Locale: en_US.CP1252 > > Setnet32: > Client Locale: de_de.CP1252 > Database Locale: en_US.CP1252 > GL_DATE: %d.%m.%Y > > Windows Regional Settings: > Decimal Symbol: , > Grouping Symbol: . > Date Short Style: dd.MM.yyyy > > (de_de.CP1252 is the German local and was used because it uses the "," as a > decimal symbol) > > The SQL: Select balance, balance || ' ' , today , today || ' ' from table > xyz > > returns (the 'balance' field is of type decimal(16,3) and holds 123.89): > > 12389 > 123,89 > 15.02.2001 > 15.02.2001 > > which seems to suggest that RDO ignores the Informix locales and confuses > the Windows regional settings as it comes up with 12389. Informix when > converting balance || ' ' and today || ' ' to strings uses correctly the > locale settings. > > When I restore the ODBC locale settings to the default ones: > > ODBC Driver setup, environment: > Client Locale: en_US.CP1252 > Database Locale: en_US.CP1252 > > and leave everything else the same and run the SQL again I get: > > 123,89 > 123.89 > 15.02.2001 > 02.15.2001 > > > Which seems to suggest that RDO now is able to use the client locale in > Setnet32 or the Windows regional settings (and correctly displays balance > as 123,89). Informix is ignoring the Setnet32 locale settings (even the > GL_DATE) and follows what is in the ODBC set up. > > No matter how I tried, I was not able to come to a scenario where both RDO > and Informix internal conversions would give the same output. > > There seems to be a more complicated issue then conversions are done inside > an SPL. > e.g: > .. > define s1,s2 char(10); define d date; define a decimal(16,3); > let d = today; > let s1 = d; > let a = 2/3; > let s2 = a; > return s1, s2; > > Here the values in s1 and s2 reflect the client locale settings at the time > of the procedure creation! For example if the procedure is created with the > locale settings as described in the 1st case above the result would always > be in line with that locale, even if the procedure is called from a > different client with different client locales. If the procedure is dropped > and recreated with different locale settings the results change to the new > locale. > > All the above findings make it really hard to support more than one locale > with ODBC and SPL. > > Any explanation to clear out the confusion is welcome. > > Thanks in advance > > Lambros > > > >