Re: Generic way of fetching a list of databases

What you seek is not possible, in any general sense.

The concept of "what is a database" varies too greatly between different DBMS 
products, even if you restrict yourself to SQL DBMSs.  Loosely the question is 
like asking for a "list of websites".

If what you want is to have a product or service that is DBMS-agnostic, you need 
to more precisely define the criteria for what you want a list of.

Here are some more specific definitions of a database:

- A DBMS process service endpoint or embedded library that you can connect to as 
a client.

- A file or file collection on disk that is a logical database.

- A collection of database table variables that can be addressed within a common 
database query.

- A collection of database table variables that can be collectively subject to a 
common ACID transaction, changes to the collection succeed or no-op as a whole.

- A logical namespace for database table variables.

For some DBMS engines several of the above things are one and the same, while 
with others they are not.

-- Darren Duncan

On 2018-11-15 6:09 PM, Daniel Kasak 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
>    ... 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