Re: Issue with legacy informix driver

mbristol@my-dejanews.com wrote: > davek@summitdata.com (David Kosenko) wrote: > > mbristol@my-dejanews.com offerred: > > +When the application calls EXEC SQL DESCRIBE stmt INTO sqldba_p; I'm seeing > > +the resultant structure alter the column datatypes (sqlba_p=>sqltype) to > > +something other than that which I'd expect. I'm seeing columns created in > > +the database (and viewable in informix.syscolumns.coltype ...) as REAL, > > +FLOAT, and DECIMAL all returned to me with a DECIMAL type attached to them. > > > > It would help to see the actual query you are applying the describe to, but > > I'll guess that you are doing some math or aggregate calculation on the REAL > > and FLOAT columns in the query? > > Nope, its just any typical query. [...] I've discovered something else > though (you know how the in-your-face stuff is ignored unless you stop for a > while and come back to it). > > This behavior only shows up when crossing a platform boundary into or out of > DEC4.0 land, i.e. a driver built on DEC 4.0d accessing a database on Solaris > 2.6, SunOS 4.14 or HP-UX 10.20 sees the sqltype as SQLDECIMAL, while the same > code running against a native 4.0d database works fine (i.e flag it as > SQLSMALLFLT). Conversely, going from a non-Dec into Dec shows the problem > (same code), but avoiding Dec all together doesn't. So I'm figuring there is > obviously a platform dependancy issue here or else some XX-bit thing I'm > ignorant of. The versions running on either side are one of 7.23 or 7.24, > but the DEC one has a .F?? version tacked on the end, while the other have > .U?? ... OK, there's a simple explanation. Different CPU types use different formats for C double and float (SQL FLOAT and SMALLFLOAT) values. When you communicate across machine types, Informix prefers to get the data across coherently, and the way it does that is to convert to DECIMAL -- it is generally more accurate (and certainly easier) than trying to convert between different native floating-point formats. This assumes you aren't using outlandishly large or incredibly minuscule values -- even though DECIMAL types can store up to 32 digits, the decimal exponent range is only +/- 126 (128, 130, or thereabouts; it isn't symmetric, and I've forgotten the exact values again, but they don't match most of the documentation). I think that the FLOAT->DECIMAL conversion doesn't always occur even between machines with different architectures if the format of the float data type is the same in both machines -- eg IEEE format. But it would still require the data to be stored in big-endian or little-endian format on both machines. If you had read your manuals excruciatingly carefully and if you had monitored the right part of the SQLCA warning structure immediately after connecting to the database, then you'd know that this conversion was going on because Informix sets a flag to warn you that it will happen. And, incidentally and irrelevantly, the biggest problem moving SE databases between platforms is precisely the fields which are stored as FLOAT or SMALLFLOAT. Everything else ports trivially; those values don't, so you have to do dbexport/dbimport or the equivalent. -- Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net) Guardian of DBD::Informix -- see http://www.perl.com/CPAN #include <disclaimer.h>