Beefy Boxes and Bandwidth Generously Provided by pair Networks
Keep It Simple, Stupid
 
PerlMonks  

Re: DBI: interpreting column_info()

by 7stud (Deacon)
on Feb 17, 2013 at 17:11 UTC ( #1019171=note: print w/ replies, xml ) Need Help??


in reply to DBI: interpreting column_info()

column_info

$sth = $dbh->column_info( $catalog, $schema, $table, $column );

The statement handle returned has at least the following fields in the order shown below. Other fields, after these, may also be present. TABLE_CAT: The catalog identifier. This field is NULL (undef) if not applicable to the data source, which is often the case. This field is empty if not applicable to the table. TABLE_SCHEM: The schema identifier. This field is NULL (undef) if not applicable to the data source, and empty if not applicable to the table. TABLE_NAME: The table identifier. Note: A driver may provide column metadata not only for base tables, but also for derived objects like SYNONYMS etc. COLUMN_NAME: The column identifier. DATA_TYPE: The concise data type code. TYPE_NAME: A data source dependent data type name. COLUMN_SIZE: The column size. This is the maximum length in characters for character data types, the number of digits or bits for numeric data types or the length in the representation of temporal types. See the relevant specifications for detailed information. BUFFER_LENGTH: The length in bytes of transferred data. DECIMAL_DIGITS: The total number of significant digits to the right of the decimal point. NUM_PREC_RADIX: The radix for numeric precision. The value is 10 or 2 for numeric data types and NULL (undef) if not applicable. NULLABLE: Indicates if a column can accept NULLs. The following values are defined: SQL_NO_NULLS 0 SQL_NULLABLE 1 SQL_NULLABLE_UNKNOWN 2 REMARKS: A description of the column. COLUMN_DEF: The default value of the column, in a format that can be used directly in an SQL statement. Note that this may be an expression and not simply the text used for the default value in the original CREATE TABLE statement. For example, given: col1 char(30) default current_user -- a 'function' col2 char(30) default 'string' -- a string literal where "current_user" is the name of a function, the corresponding COLUMN_DEF values would be: Database col1 col2 -------- ---- ---- Oracle: current_user 'string' Postgres: "current_user"() 'string'::text MS SQL: (user_name()) ('string') SQL_DATA_TYPE: The SQL data type. SQL_DATETIME_SUB: The subtype code for datetime and interval data types. CHAR_OCTET_LENGTH: The maximum length in bytes of a character or binary data type column. ORDINAL_POSITION: The column sequence number (starting with 1). IS_NULLABLE: Indicates if the column can accept NULLs. Possible values are: 'NO', 'YES' and ''. SQL/CLI defines the following additional columns: CHAR_SET_CAT CHAR_SET_SCHEM CHAR_SET_NAME COLLATION_CAT COLLATION_SCHEM COLLATION_NAME UDT_CAT UDT_SCHEM UDT_NAME DOMAIN_CAT DOMAIN_SCHEM DOMAIN_NAME SCOPE_CAT SCOPE_SCHEM SCOPE_NAME MAX_CARDINALITY DTD_IDENTIFIER IS_SELF_REF Drivers capable of supplying any of those values should do so in the corresponding column and supply undef values for the others.

Where is a list of translations for the codes appearing in some fields?

Based on my searching, they are not readily available. You probably need to go to an SQL forum and ask some experts where those can be found.


Comment on Re: DBI: interpreting column_info()
Download Code
Re^2: DBI: interpreting column_info()
by ibm1620 (Beadle) on Feb 17, 2013 at 19:53 UTC

    Where is a list of translations for the codes appearing in some fields?

    Based on my searching, they are not readily available. You probably need to go to an SQL forum and ask some experts where those can be found.

    This sounds like the best way to proceed. I've posted to http://forums.mysql.com/ unless someone can suggest a better forum.
      The SQL standard Part 3: Call-Level Interface (SQL/CLI) defines the constants in sqlcli.h and ODBC in sql.h

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://1019171]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others avoiding work at the Monastery: (6)
As of 2014-11-25 01:59 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    My preferred Perl binaries come from:














    Results (148 votes), past polls