Re: Generic way of fetching a list of databases

On Friday 16 November 2018 13:09:21 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

Hi! If you want to list all databases then use DBI's data_sources method:
https://metacpan.org/pod/DBI#data_sources

That method is there exactly for this purpose to list all databases in
DBI's DSN format. If you are not interested in full DSN, just extract
database... See example:

$ perl -MDBI -E 'say $_ foreach map { $_ =~ /^DBI:[^:]*:(.*)/i } DBI->data_sources("mysql")'
information_schema
mysql
performance_schema
test
test2
test3
test4