Re: question about column types and lengths
Or you can avail yourself of the various open source programs that dissect the system catalog and manipulate types. The include Art's MySchema and my SQLCMD and DBD::Informix. On Fri, Apr 17, 2009 at 16:01, Art Kagel <art.kagel@gmail.com> wrote: > The length column for DECIMAL, MONEY, INTERVAL, and DATETIME are NOT the > actual length but instead encode the resolution and/or precision of the > column. Look at the header files in $INFORMIXDIR/incl/esql especially > datetime.h and decimal.h for details on how to interpret the length. The > coltype also encodes whether the column is NOT NULL. If so 256 is added to > the actual column type. Column types and a macro for interpreting the > coltype are in sqltypes.h. > > Art > > Art S. Kagel > Oninit (www.oninit.com) > IIUG Board of Directors (art@iiug.org) > > Disclaimer: Please keep in mind that my own opinions are my own opinions and > do not reflect on my employer, Oninit, the IIUG, nor any other organization > with which I am associated either explicitly or implicitly. Neither do > those opinions reflect those of other individuals affiliated with any entity > with which I am affiliated nor those of the entities themselves. > > > > On Fri, Apr 17, 2009 at 12:46 PM, Floyd Wellershaus <floyd@fwellers.com> > wrote: >> >> Hi, >> I apologize beforehand for how long this is. I have a hard time explaining >> my thoughts without a lot of words. >> >> >> I'm tracking deltas of table rows for a migration effort. >> Going to go the brute force route and install a trigger on each column in >> each table and create and audit record for each insert update delete. >> the audit record will look like this: >> ACTION | COLUMN | COLUMN | ...... | TIMESTAMP. >> The ACTION will either by i, u or d for insert update or delete. >> >> I'm writing a script to generate all the triggers. Having issues with >> being able to know exactly which column type for each column because the >> coltype, collength etc.. from syscolumns isn't easy to interpret. I have >> values in there that aren't even documented ( for eg.. 269 for a varchar ). >> Plus how to tell if a datetime is year to day or year to second, I don't >> know all the different collength values. >> >> So here's what I'm thinking. For the audit table, when I stor e a row all >> I need to do is ensure there is enough room for the values that get stored, >> and I need to know if I should quote the data value when regenerating a >> query for the target database. >> >> An example would be, I have a table called student with 4 columns sid, >> fname and lname,date >> I insert the values 1,'fred' ',flinstone',today >> >> My audit record would look like: >> 1, fred flinstone 4/17/2009 >> >> I will generate a query from that. >> >> so 2 things I need to know are: >> 1) how much room I have to store the columns in the audit table ( if the >> lname column for that table is a varchar(50) I need to make sure the lname >> col in it's audit table is also at least 50 characters. >> 2) if the datatype is a type that needs to be quoted when inserting or >> updating. For eg, when generating an insert the sid doesn't need quotes >> around it, but the string values do. >> >> To make it simple on myself I'm thinking of using the collength column >> from sysco lumns to be the determiner of how big the audit table column >> should be. Will that work ? So for datetime year to day, the collength from >> syscolumns is 2052. So I can just make the column in the audit table that >> would be used to hold that audit record to be a varchar(2052). That will >> ensure no data gets truncated in the audit table. >> Will that work ? >> >> To make the decision about whether a value needs quoting, I am thinking >> that only datatypes that are these below: can be inserted without quotes >> around them. Everything else will need to be quoted. >> 1 = SMALLINT >> 2 = INTEGE R >> 3 = FLOAT >> 4 = SMALLFLOAT >> 5 = DECIMAL >> 6 = SERIAL * >> >> >> thanks in advance ! >> >> Floyd >> >> >> >> _______________________________________________ >> Informix-list mailing list >> Informix-list@iiug.org >> http://www.iiug.org/mailman/listinfo/informix-list >> > > > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list > > -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2008.0513 -- http://dbi.perl.org/ "Blessed are we who can laugh at ourselves, for we shall never cease to be amused." NB: Please do not use this email for correspondence. I don't necessarily read it every week, even. Dan Quayle - "I love California, I practically grew up in Phoenix." - http://www.brainyquote.com/quotes/authors/d/dan_quayle.html