Re: [DB] Suggestion for field types

Thanks for the suggestions Hans. I would like to solve the problem within PEAR, though. It seems simple enough - each ext could implement a translation array eg. ( 'SQLSMINT' => 'integer', ...), or alternatively a group of functions isNumber, etc (as per my original post). Or perhaps better yet, tableInfo() should include an element 'generictype', which is one of a few standard types [BTW I am busy with completinging PEARDB::tableInfo() for Informix]. Hans Lellelid wrote: > Well, I think the bigger issue here is the fact that there is no > resultset metadata available using the native drivers in PHP. There are I'm not sure I understand you here. I am getting metadata back when using PEAR to mysql, pgsql, and informix databases, as a result of a call to $db->query(). This allows me to display the colomn names for say 'select name, title from guys', where that sql is composed by the user. That tablemap idea you mention sounds like a good idea. This would also avoid developers adding dummy queries to get column info Kind regards, Peter Hans Lellelid wrote: > > Peter wrote: > > >Hi all. Is there a mechanism in pear DB to ask about the generic field type? Eg. the > >following would be useful: > > > >... $i = ... column number of result > > if ($result->isNumber($i)) ... code to right justify result > > if ($result->isChar($i)) ... code to quote/do case conversion etc. > >... > > > >This would avoid having to worry about strange types like bpchar, SQLSMINT, int2. My > >feeling is the native types should in fact be entirely invisible via PEAR - that's what the > >native API's are for. > > > > > Well, I think the bigger issue here is the fact that there is no > resultset metadata available using the native drivers in PHP. There are > a number of abstraction layers that do have abstracted db types -- MDB2 > (i believe), ADOdb, Creole -- but it's not available as part of a > resultset: you would have to perform an additional query to get metadata > for the table in order to know which columns were numeric, data, string, > etc. > > Obviously one solution is just to use functions like is_numeric() and > strtotime() which can help you guess at the underlying datatypes. > > Your best solution, however, is probably to use a more managed > data-access layer. Paul mentions DB_Table has this support -- if so, > that may be the perfect package for your needs. DB_DataObject also has > knowledge of underlying datatypes -- not sure if it provides an API for > easily returning that info, though. Propel is another (non-PEAR) > solution for object persistence; Propel builds tablemap classes that > provide a quick (static, no queries) way to get detailed information > about table columns (and indexes, primary key, foreign keys, etc.). > > Cheers, > Hans