Description:
I am porting an application that makes heavy use of
information_schemas to MySQL 5.0.16. This app already runs
on SQL Server and Mimer SQL, both of which have fairly
1999 SQL compliant information schemas.
When moving the app to MySQL I ran into problems (ODBC errors)
because of:
- missing information_schema views/tables
- missing columns
- non-standard column names
Missing View/Tables:
information_schema.referential_constraints
information_schema.parameters
information_schema.check_constraints
information_schema.triggered_update_columns
The first two are absolutely essential, the last two less so.
Missing columns (that cause ODBC errors:
View/Table Column(s)
schemata schema_owner
columns collation_schema
table_constraints is_deferrable, initially_deferred
routines return datatype attributes, e.g.
charmaxlen, precision, scale etc
Suggestion 1: include all infoschema column names as of 1999 SQL,
even if they are not meaningful for MySQL. Just return NULL
(or empty string) if so.
The lack of return datatype attributes is a real problem. ODBC
produces an avalance of errors when these are missing.I noticed
that the DTD_IDENTIFIER column actually holds the
typename + attributes as written in the source. According to
my understanding of the the standard it should be holding the
datatype name only.
Suggestion 2: include the return datatype attributes data_type,
character_maximum_length etc exactly as you do
in information_schema.columns
Non-standard column names (causing ODBC errors):
action_references_old/new_table should be condition_references_old/new...
action_timing should be condition_timing
Documentation:
Column datatype and length are missing in the infoschema tables
documentation.
That is not too much of a problem for me, because I can get them
from the SQL Server and Mimer SQL documentation, but for other
users it may be a problem.
However, I cannot guess what the maximum column width
is for routine_definition, view_definition and trigger actions and
bodies. Mimer maxlen is 400; SQL Server maxlen is 4000.
Best regards
Ken Resander
resander@speed.info.com.ph
How to repeat:
Not applicable
Suggested fix:
Not applicable
Description: I am porting an application that makes heavy use of information_schemas to MySQL 5.0.16. This app already runs on SQL Server and Mimer SQL, both of which have fairly 1999 SQL compliant information schemas. When moving the app to MySQL I ran into problems (ODBC errors) because of: - missing information_schema views/tables - missing columns - non-standard column names Missing View/Tables: information_schema.referential_constraints information_schema.parameters information_schema.check_constraints information_schema.triggered_update_columns The first two are absolutely essential, the last two less so. Missing columns (that cause ODBC errors: View/Table Column(s) schemata schema_owner columns collation_schema table_constraints is_deferrable, initially_deferred routines return datatype attributes, e.g. charmaxlen, precision, scale etc Suggestion 1: include all infoschema column names as of 1999 SQL, even if they are not meaningful for MySQL. Just return NULL (or empty string) if so. The lack of return datatype attributes is a real problem. ODBC produces an avalance of errors when these are missing.I noticed that the DTD_IDENTIFIER column actually holds the typename + attributes as written in the source. According to my understanding of the the standard it should be holding the datatype name only. Suggestion 2: include the return datatype attributes data_type, character_maximum_length etc exactly as you do in information_schema.columns Non-standard column names (causing ODBC errors): action_references_old/new_table should be condition_references_old/new... action_timing should be condition_timing Documentation: Column datatype and length are missing in the infoschema tables documentation. That is not too much of a problem for me, because I can get them from the SQL Server and Mimer SQL documentation, but for other users it may be a problem. However, I cannot guess what the maximum column width is for routine_definition, view_definition and trigger actions and bodies. Mimer maxlen is 400; SQL Server maxlen is 4000. Best regards Ken Resander resander@speed.info.com.ph How to repeat: Not applicable Suggested fix: Not applicable