On 30/12/2012 04:19, Darren Duncan wrote:
Yes, that is useful.
I think you should add a column such that your leftmost column is some
canonical type name you made for the report, and have the SQL standard
name(s) in a separate column like the ODBC standard names are.
As far as I understand thus far, and one of the DBI devs may correct me
here, the ODBC and SQL standard names are the same for all those types
with codes >= 1. Although I agree having some distinction between them
other than the numbers would be useful. The column marked ODBC is SQL
Server, it's just using the ODBC driver, I'll need to correct that in
the next version to make it more clear.
I'm not sure what types names I could create in a new left column that
wouldn't match the current left column. I'm open to suggestions if you
want to send me some ideas.
This works best when no one list is a superset of the others, which is
surely the case, then you don't have say the confusion about which
things in the first column are SQL standard actual vs some placeholder
you added from ODBC/etc.
The more I look at this, the more I find things that seem a little out
of place. For example:
The MySQL driver is declaring it has type SQL_BIT, but that's actually
it's CHAR(1) which is a byte, not a bit (at least in LATIN1). According
to MySQL it does have a proper BIT. It also has a BOOL, but that's just
a synonym for TINYINT(1).
PostgreSQL BYTEA that's currently in SQL_VARBINARY, would seem a better
fix for SQL_LONGVARBINARY... But then what would you have in
SQL_VARBINARY... Similar situation for PostgreSQL character. It's like
it fits into both categories really. The standard itself doesn't specify
lengths for data types, extra names like LONGVARBINARY were created by
the implementations when they wanted to offer larger objects than they
had before. Reviewing the PostgreSQL documentation on CHARACTER it
mentions things like short, long and very long character strings, but
lacks detail so I've emailed them about it. MySQL's FLOAT and DOUBLE are
linked to several ODBC types, perhaps PostgreSQL could do the same? Or
is that bad practice on the MySQL drivers part?
For SQL_DECIMAL PostgreSQL and MySQL seem to disagree on whether FLOAT
or DOUBLE should be used.
PostgreSQL has SQL_DOUBLE associated with it's INT8 (also called
LONGINT) instead of it's FLOAT(25 - 53) or DOUBLE PRECISION which gives
double precision. It also has SQL_VARCHAR assoicated with TEXT instead
of VARCHAR.
I have a feeling that MySQL's DATETIME could, or maybe should be
associated with SQL_TYPE_TIMESTAMP.
I'm sure there are more things that look a bit odd or out of place. I'm
not sure whether there is good reasoning behind them, or whether the DBD
developers have been doing a best guess and we might possibly want to
consider making things more consistent?
Lyle
-- Darren Duncan
Lyle wrote:
Hi All,
Whilst working on another project it made sense to write a tool for
comparing the various RDBMSs. I'm talking about the database
management systems themselves, not databases within them. So far I've
done parts that use $dbh->type_info_all() to compare what types SQL
Server, Postgres, Oracle and MySQL have available and their details.
Generating reports like:
http://cosmicperl.com/rdbms/compare_types.html
http://cosmicperl.com/rdbms/compare_type_details.html
I'm not yet sure as to whether the mapping from the RDBMSs local sql
type to the ones the DBI recognises is done by the DBD driver, or
whether this is already predetermined by the RDBMS...
Let me know if this isn't interesting to you all and I'll keep it off
list.
Lyle
