Re: Generic way of fetching a list of databases

2018-11-16 Thread Darren Duncan

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


RE: Generic way of fetching a list of databases

2018-11-16 Thread Fennell, Brian
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 
Sent: Friday, November 16, 2018 10:26 AM
To: dbi-users@perl.org; Daniel Kasak 
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 
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
  ... 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.




Re: Generic way of fetching a list of databases

2018-11-16 Thread Scott Webster Wood via dbi-users
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 
 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  

Re: Generic way of fetching a list of databases

2018-11-16 Thread pali
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