Re: question about column types and lengths
Posted in 2009
Topics: Installation, Setup & Upgrades, Connectivity: ESQL/C, 4GL & Embedded SQL, Security, Permissions & Auditing, Data Types & Schema Design, Triggers, Constraints & Referential Integrity, Migration, Import/Export & Data Conversion
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