Closest thing that I know of is INFORMATION_SCHEMA and the INFORMATION_SCHEMA.TABLES pseudo table. INFORMATION_SCHEMA is a standard across many databases. One Database that doesn’t comply is Oracle (but it can be installed by the DBA as an Add-on) for Oracle select from ALL_TABLES (or DBA_TABLES if you have administrative privileges or “read any data dictionary” privileges). Every database that I have ever worked with has some way to list the tables as if there were a “table of tables” somewhere. If you are working with a database where cannot select from INFORMATION_SCHEMA.TABLES than just search google/bing for the name of that database and “INFORMATION_SCHEMA.TABLES” to find the equivalent for your odd-ball database. Put the whole thing in a subroutine that returns the same kind of data structure as a SELECT, adding perhaps an if/the/else structure to handle the different kinds and you should at least be able to keep all the messiness in one subroutine.
From: Scott Webster Wood via dbi-users <dbi-users@perl.org> Sent: Friday, November 16, 2018 10:26 AM To: dbi-users@perl.org; Daniel Kasak <d.j.kasak...@gmail.com> Subject: Re: Generic way of fetching a list of databases convert to 'rest'ful JSON? ---- Barack-O-phobia: The fear of politicians who think (more) government is the solution to every problem. On Thursday, November 15, 2018, 9:09:44 PM EST, Daniel Kasak <d.j.kasak...@gmail.com<mailto:d.j.kasak...@gmail.com>> wrote: Hi all. I have a project that has to support pretty much every major database around, plus a number of more esoteric "big data" platforms as well. Until now, I've had a bunch of methods, implementing fetching databases, schemas, tables, etc, per database, with database-specific queries ( eg against information_schema ). Some of the newer databases I'm trying to support have very little documentation, and in some cases no apparent way of fetching in the schema via SQL. I've had a conversation with one of the tech support people for a DB product who said that there were generic ODBC functions we can call for this kind of thing. Is this the case? I've done quite a bit of search, but can't find any docs that mention fetching *databases* - either in ODBC docs or in Perl/DBI docs. The closest I've found that *might* have worked was DBI's tables() method: https://metacpan.org/pod/DBI#tables<https://urldefense.proofpoint.com/v2/url?u=https-3A__metacpan.org_pod_DBI-23tables&d=DwMFaQ&c=ukcTAPl5KduEyRvXoL9XMA&r=0EHe68Ki46dgcWiA0JMjgV97dO_1ZKSBkQjMhiDvNg4&m=14PCzr90RUPmRHknBEdUfrQy3bTBcoDMUrPms1CH0P4&s=NjjBrUnIpZgky0dxEExjZpoYiqS29GDigmjBsTA_-As&e=> ... but: - this doesn't work in cases where there is a separation between hierarchies at the database level ( eg postgres only lists schemas and tables in the current database ) - this isn't returning *anything* for me with any of the ODBC drivers I've tried So is it possible to retrieve a list of databases in a generic way? Failing that, assuming that there *is* some ODBC call ( as suggested by one DB vendor ) that I can use, is there some way of calling it from Perl with DBD::ODBC? Thanks :) Dan The information contained in this electronic mail transmission is intended only for the use of the individual or entity named in this transmission. If you are not the intended recipient of this transmission, you are hereby notified that any disclosure, copying or distribution of the contents of this transmission is strictly prohibited and that you should delete the contents of this transmission from your system immediately. Any comments or statements contained in this transmission do not necessarily reflect the views or position of Radial or its subsidiaries and/or affiliates.
