(Ovid) Re(3): How do you get Perl::DBI to do a desc table

Replies are listed 'Best First'.
Re: (Ovid) Re(3): How do you get Perl::DBI to do a desc table_name? by mpeppler (Vicar) on Mar 27, 2002 at 20:56 UTC
Just execute a query with a false where clause: `select * from ... where 1=0` [download] This will return an empty result set, but you'll still get all the column headers, names, etc. Michael	[reply] [d/l]
Re: (Ovid) Re(3): How do you get Perl::DBI to do a desc table_name? by kleinbiker7 (Sexton) on Mar 27, 2002 at 18:49 UTC
OK. I think I see now. Another question: How do you get the types, like varchar2(2000), number(3), date, etc? Basically I want to get all the results from a DESC operation into my Perl script. Thanks! Robert	[reply]
Re: Re: (Ovid) Re(3): How do you get Perl::DBI to do a desc table_name? by lestrrat (Deacon) on Mar 27, 2002 at 19:07 UTC
I think this is soooo system dependent, and that it would be faster consulting your manual. Just as an example, in PostgreSQL you would need to either use "\d $tablename", or you would need to go into system tables such as pg_class, pg_attribute, and pg_type. I know how to do this in Postgres, but this certainly doesn't apply to any other databases. I'm sure there is a way, but I think you're asking in the wrong place. You should find your manual and look for this information, or ask an oracle users group...	[reply]
Re: Re: Re: (Ovid) Re(3): How do you get Perl::DBI to do a desc table_name? by rob_au (Abbot) on Mar 27, 2002 at 22:43 UTC
You're right, this is fairly system dependent - However, basic database metadata can be determined through the metadata attributes of DBI, specifically the `NAME`, `TYPE`, `PRECISION` and `NULLABLE` field attributes. The standard values for common SQL data field types returned by the `TYPE` attribute are as follows: `SQL_CHAR 1 SQL_NUMERIC 2 SQL_DECIMAL 3 SQL_INTEGER 4 SQL_SMALLINT 5 SQL_FLOAT 6 SQL_REAL 7 SQL_DOUBLE 8 SQL_DATE 9 SQL_TIME 10 SQL_TIMESTAMP 11 SQL_VARCHAR 12 SQL_LONGVARCHAR -1 SQL_BINARY -2 SQL_VARBINARY -3 SQL_LONGVARBINARY -4 SQL_BIGINT -5 SQL_TINYINT -6 SQL_BIT -7 SQL_WCHAR -8 SQL_WVARCHAR -9 SQL_WLONGVARCHAR -10` [download] This information can be retrieved from a DBI database handle similar that shown in one of my first SOPW questions to the monastery back here. While these attributes allow information about the most standard of SQL field types to be determined, there may be some non-standard field types returned by databases with extended field types. From my own experience I find its usually better for just this reason to gleam an understanding of the database structure directly and incorporate this understanding into my code, rather than trying to have the code determine this information by itself.	[reply] [d/l] [select]
Re: (Ovid) Re(3): How do you get Perl::DBI to do a desc table_name? by Rhose (Priest) on Mar 27, 2002 at 20:21 UTC
While I am not familiar with the TOP syntax, I bet ROWNUM can be used to do the same. ROWNUM is a the number of a row as it was retrieved from the table (this differs from the display order) -- to limit results to 10 rows, you can use WHERE ROWNUM <= 10. (Please note: this takes place before sorting, so it is no good for getting the "top 10" and such.) `SELECT * FROM scott.emp WHERE ROWNUM = 1;` [download]	[reply] [d/l]

(Ovid) Re(3): How do you get Perl::DBI to do a desc table_name?