PHP list native_type for PDO getColumnMeta ()

Question

I use the PDO database abstraction library to make sure my code is portable. However, now I have found that I need information about the columns, so I turned to the PDOStatement-> getColumnMeta () method, hoping that it would be somewhat standardized - but from what I found, it actually seems open.

For example, when you call this method from SQLite, it seems that you get one set of possible values:

http://gcov.php.net/PHP_5_3/lcov_html/pdo_sqlite/sqlite_statement.c.gcov.php

null double blob string integer ... 

While the call from the MySQL database contains a list of all other strange values: http://gcov.php.net/PHP_5_3/lcov_html/pdo_mysql/mysql_statement.c.gcov.php

 var_string longlong newdecimal geometry ... 

I, too, can search for the wrong place, but I just cannot find useful data about which values ​​of "native_type" can be related to database switching.

Answer 1

This is one of those areas of PDO that were intentionally reserved undefined to keep the abstraction light.

PDO does not define a standard type representation for this method; Each driver has his own idea of ​​what he can return here.

Answer 2

PDO is not a database abstraction. This is the "only" single access level. If you switch to another database system, you will most likely have to change the code. Each (specific) database driver returns its own set of values ​​and there is no "translation level" for the driver: decl_type info in pdo outside the native_type / pdo_type fields as a result of getColumnMeta ()

Answer 3

I had a hand to change the behavior to more closely match the documentation. You can refer to PHP Bug # 46508 .

Answer 4

One solution that I came across would require you to use the table name as your own alias.

You can call getColumnMeta () and get a unique list of tables, and for each table execute the DESCRIBE {table} statement. Match the column name with the data from the result set to get the actual MySQL data type.

It worked for me and my needs anyway ...

Answer 5

This works more, but create a database with column names like vc_20_Last_Name. Then explode the column name to "_". Position 0 contains vc or VARCHAR. Position 1 contains the column width or 20. Position 2 and above contains Last and Name, which are explanatory. Now you have the opportunity to write a general function for the automatic assembly of HTML forms for INSERT, UPDATE, and DELETE operations. You can pass parameters to exclude (or include, if it's easier) fields that you / don’t want to show. Write the code once and use it forever. Tough luck if you're stuck with other tables.

Answer 6

I thought I could share what I have. Since native_type and pdo_type return such very different values, I use "len" to try to check the lines and text, since everything less than 255 is var char, int or boolean. In addition, pdo_type has only 5 possible values.

 //PDO data types $types = array( PDO::PARAM_BOOL => 'bool', PDO::PARAM_NULL => 'null', PDO::PARAM_INT => 'int', PDO::PARAM_STR => 'string', PDO::PARAM_LOB => 'blob', PDO::PARAM_STMT => 'statement' //Not used right now ); //Get meta $column = $result->getColumnMeta(1); //If the column lenght isn't set - default to ZERO $column['len'] = isset($column['len']) ? $column['len'] : 0; //HACK: If it is longer than 255 chars then it is a text area if($column['len'] > 255) { $column['type'] = 'text'; } else { $column['type'] = $types[$column['pdo_type']]; }