Re^3: Problems with values expressed in scientific notation not being returned through DBI

Replies are listed 'Best First'.
Re^4: Problems with values expressed in scientific notation not being returned through DBI by DazedConfuzed (Novice) on Dec 11, 2017 at 22:13 UTC
I've found out a little more about this problem and it looks like it is probably the lower level Microsoft Access Driver that is passing garbage to Perl and Perl is simply following the GIGO principle. I sent the test case to a colleague who was running it on a newer Win10 computer and he noticed that he got different results depending on how he configured the symbolically named ODBC Data Source. If he used the ODBCJT32.DLL driver that he got the same results as I do with missing values for Singles represented in scientific notation. If instead he configured the data source to use the newer ACEODBC.DLL driver then proper values were returned. Encouraged by his success, I redefined my named data source to use the ACEODBC.DLL driver and hoped to replicate his success. Unfortunately, instead of having the values that were expressed in scientific notation returned properly, all values (Single or Double) expressed in scientific notation are now all shown as simply "E-2" which it not particularly a step in the right direction in terms of solving my problem but it does let me know that the problem is likely occurring well before Perl is ever handed the data. It is somewhat annoying that I don't seem to be able to find an ODBC driver that will not emit bogus data from a query but, as my friend was able to solve this when switching to the newer dated driver ACEODBC.DLL, I assume it may be an error that was corrected in the latest version that handles both .accdb files as well as .mdb files and never fixed in the older driver. This bug may have a limited impact as probably a significant number of installations have moved on from Win7 and Office 2007. I refuse to leave the comfort of an operating system and Office version I know and can understand and will never submit to the indignities of Win10 or anything beyond that--I'll switch to Linux or Mac when my Win7 hits end of life. I just wanted to post this final follow-up letting this forum know that if others experience a similar problem, the answer seems to be to look into the ODBC driver that Perl is using when connecting to a named data source.	[reply]

Replies are listed 'Best First'.

Re^4: Problems with values expressed in scientific notation not being returned through DBI
by DazedConfuzed (Novice) on Dec 11, 2017 at 22:13 UTC

I've found out a little more about this problem and it looks like it is probably the lower level Microsoft Access Driver that is passing garbage to Perl and Perl is simply following the GIGO principle. I sent the test case to a colleague who was running it on a newer Win10 computer and he noticed that he got different results depending on how he configured the symbolically named ODBC Data Source. If he used the ODBCJT32.DLL driver that he got the same results as I do with missing values for Singles represented in scientific notation. If instead he configured the data source to use the newer ACEODBC.DLL driver then proper values were returned.

Encouraged by his success, I redefined my named data source to use the ACEODBC.DLL driver and hoped to replicate his success. Unfortunately, instead of having the values that were expressed in scientific notation returned properly, all values (Single or Double) expressed in scientific notation are now all shown as simply "E-2" which it not particularly a step in the right direction in terms of solving my problem but it does let me know that the problem is likely occurring well before Perl is ever handed the data.

It is somewhat annoying that I don't seem to be able to find an ODBC driver that will not emit bogus data from a query but, as my friend was able to solve this when switching to the newer dated driver ACEODBC.DLL, I assume it may be an error that was corrected in the latest version that handles both *.accdb files as well as *.mdb files and never fixed in the older driver. This bug may have a limited impact as probably a significant number of installations have moved on from Win7 and Office 2007. I refuse to leave the comfort of an operating system and Office version I know and can understand and will never submit to the indignities of Win10 or anything beyond that--I'll switch to Linux or Mac when my Win7 hits end of life.

I just wanted to post this final follow-up letting this forum know that if others experience a similar problem, the answer seems to be to look into the ODBC driver that Perl is using when connecting to a named data source.

[reply]