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

In my more complicated code (before I distilled it down to this simple example), I used Data::Dumper to take a peek at the row returned from the fetch. The actual row contained a few null columns that were correctly returned and displayed as 'undef' but the Single values that were rendered in exponential notation in the DB were not shown as 'undef' but simply as '' (as if the number was a string--possibly, just guessing here, triggered by the "E" in the exponential format).

When I view the Single values that are giving me problems they appear simply as floating point numbers expressed in scientific notation:
3.507194E-02
8.992806E-02

As I had to develop a workaround for this issue I decided upon limiting the mantissa of these values by rounding these numbers to 5 decimal digits when creating the table by using "Round( ... ,5)" in the code. By limiting the resolution of the mantissa these numbers are kept from switching into scientific notation which is all that is needed to get around this bug so that I can complete my work. My other option was to create my table using Double instead of Single for any floating point numbers but I had a distaste for the excessive precision that resulted. I'm still wondering what is going on deep in the bowels of DBI and if there is some rare subtle bug that has remained hidden lo these many years or if I'm just being boneheaded somehow and don't yet see it.

Comment on Re^2: Problems with values expressed in scientific notation not being returned through DBI

Replies are listed 'Best First'.
Re^3: Problems with values expressed in scientific notation not being returned through DBI by Anonymous Monk on Dec 10, 2017 at 13:51 UTC
It could well be that the data is being returned in character form – and that these characters are somehow not being parsed correctly, e.g. by some driver layer – but that appears quite nonsensical since exponential notation is well-understood to be part of any floating-point number's possible character expression. Could there be some Microsoft DAO or dot-Net option afoot here?	[reply]
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]