As Rhino said, the INFORMATION_SCHEMA views (data dictionary) are coming
in v5.x (so they are close but not here yet). Until then a more "native"
alternative is to parse the results of either "SHOW CREATE TABLE
tablename" or "SHOW COLUMNS FROM tablename LIKE 'columnname'" in whatever
language you use.
Does *your* database connection library expose any of that metadata (like
in Rhino's Java example)? (for example, I also know that it's possible to
use ADO or ODBC properties to get that information, too). It may not be as
easy to get at the column size information as performing an INFORMATION
SCHEMA query but it's an option.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
"Rhino" <[EMAIL PROTECTED]> wrote on 01/06/2005 12:28:52 PM:
> The only way I know to access anything resembling a "data dictionary" in
> MySQL V4.* is via the Java MetaData interfaces. I use these occasionally
in
> my programs. In your case, you want the getColumnDisplaySize() method in
the
> ResultSetMetaData Interface. This code fragment demonstrates the
technique:
>
>
----------------------------------------------------------------------------
> ------------------------
> /* Determine the maximum length of the event_name column in the Events2
> table. */
> String query2 = "select event_name as event from SDAC.Events2";
> ResultSetMetaData rsmd2 = null;
> try {
> stmt = conn01.createStatement();
> rs = stmt.executeQuery(query2);
> rsmd2 = rs.getMetaData();
> String columnName = rsmd2.getColumnName(1);
> String columnLabel = rsmd2.getColumnLabel(1);
> String columnTypeName = rsmd2.getColumnTypeName(1);
> int columnSize = rsmd2.getColumnDisplaySize(1);
> System.out.println("The name of the event_name column is " +
> columnName);
> System.out.println("The label of the event_name column is " +
> columnLabel);
> System.out.println("The column type name of the event_name column
is "
> + columnTypeName);
> System.out.println("The size of the event_name column is " +
> columnSize);
> } catch(SQLException sql_excp) {
> sql_excp.printStackTrace();
> }
>
>
----------------------------------------------------------------------------
> ------------------------
>
>
> This is the result of running that fragment:
>
>
----------------------------------------------------------------------------
> ------------------------
> The name of the event_name column is event
>
> The label of the event_name column is event
>
> The column type name of the event_name column is VARCHAR
>
> The size of the event_name column is 100
>
>
----------------------------------------------------------------------------
> ------------------------
>
> (Note: If you omit the 'as event' from the query, both the column name
and
> the label name are 'event_name', not 'event. This may be a driver bug: I
> would have though the original query should give 'event_name' as the
column
> name and 'event' as the label name.)
>
>
> Aside from that, I think you have to wait until V5.* before you have any
> command line SQL access to the MySQL catalog (or what you call a "data
> dictionary").
>
> Then again, there may be some third party tools that give you that
> information which I don't know about.
>
> Rhino
>
> ----- Original Message -----
> From: "Ben Edwards" <[EMAIL PROTECTED]>
> To: <mysql@lists.mysql.com>
> Sent: Thursday, January 06, 2005 10:25 AM
> Subject: Data Dictionary
>
>
> > how do I access the Data Dictionary through SQL? What I want to do is
> > loookup the length of a varchar for validation?
> >
> > Ben
> > --
> > Ben Edwards - Poole, UK, England
> > WARNING:This email contained partisan views - dont ever accuse me of
> > using the veneer of objectivity
> > If you have a problem emailing me use
> > http://www.gurtlush.org.uk/profiles.php?uid=4
> > (email address this email is sent from may be defunct)
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
> >
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
>
