Re: Issue with legacy informix driver

In article <3588b0f4.15438933@nntp.news.netcom.net>, 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. The ESQL/C compiled program is just passing the statement in raw, and I'm checking the reply immediately after the DESCRIBE command goes through. Something like: CREATE TABLE test (colone); INSERT INTO test (colone) values (10.9); COMMIT; SELECT * from test; will flag that 'FLOAT' value as a sqltype=SQLDECIMAL rather than SQLSMALLFLT like I figured it would. ... 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?? ... > Informix likes to do math operations in decimal type, and will > convert other numeric types to decimal when appropriate. So, for example, if you > were to do a SUM(colX) where colX is of type REAL in the database, the resulting > column type from the query will be decimal (I believe they do this to avoid overflows > - a decimal supports up to 32 significant digits). Ahh, good to know. Thanks for pointing this out - I'd noticed some caveats in the manual regarding datatype arithmatic, but I hadn't realized that DECIMAL was the default 'metatype'. I'm kind of surprised that there are all these sub- forms. I'm more used to Oracle where it just apprears to be a whole bunch of types until you get underneath it and realize that they all are just variations of VARCHAR2, FLOAT, and NUMBER. > There are functions available that will convert a decimal into a REAL or FLOAT, or > you can alter the sqlda structure returned from the DESCRIBE and change the sqltype > to CFLOATTYPE, for example. This will cause the engine to convert the data into the > type of your host variable structure. Also good to know - for detective purposes. Thanks again! I'm used to preloading Oracle like this so I can work with displaying things as if they were VACHARs, but I hadn't looked into dealing with Informix in this manner yet. Sicne this is all dynamic, I can't designate certain columns as one type or another, but I can't at least look into uses of those macros. I'm tracing the code out immediately after the DESCRIBE, so I can't see how it might be altering it ... unless something odd is hanging around. Oh well. Thanks for your help. Thanks, Mike mbristol@fastech.com -----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/ Now offering spam-free web-based newsreading