Beefy Boxes and Bandwidth Generously Provided by pair Networks
Welcome to the Monastery

Finding database version from handle?

by cosmicperl (Chaplain)
on Jul 21, 2010 at 22:44 UTC ( [id://850736]=perlquestion: print w/replies, xml ) Need Help??

cosmicperl has asked for the wisdom of the Perl Monks concerning the following question:

Hi All,
  I've got a module that will be deployed on a variety of servers and work with a variety of databases. I'm not sure what versions of the supported databases users will be running. Many of the users will not be techies, it'll be hard enough to get the DSN from them, let alone the exact version of the DB they are running. Is there any way to detect the version from the DBI Database handle? I've checked the DBI pod and googled, but can't find a way :/


Replies are listed 'Best First'.
Re: Finding database version from handle?
by ahmad (Hermit) on Jul 21, 2010 at 22:58 UTC

    In MySQL you can query the version number "SELECT version();"

    I don't know about other database drivers.

      Thanks, it's a start :) For some reason I hadn't thought there would be ways through the SQL itself.
Re: Finding database version from handle?
by mje (Curate) on Jul 22, 2010 at 07:09 UTC

    There isn't a specific DBI method for returning the db version although some time ago DBI added the get_info method which can be used to get db meta information. The get_info method is modeled on ODBC and is fully supported in DBD::ODBC like the following (stolen straight from DBD::ODBC's 20SqlServer.t test file):

    $dbms_name = $dbh->get_info(17); ok($dbms_name, "got DBMS name: $dbms_name"); $dbms_version = $dbh->get_info(18); ok($dbms_version, "got DBMS version: $dbms_version"); $m_dbmsversion = $dbms_version; $m_dbmsversion =~ s/^(\d+).*/$1/; ok($m_dbmsversion, "got DBMS major version: $m_dbmsversion"); $driver_name = $dbh->get_info(6); ok($driver_name, "got Driver Name: $driver_name");

    However, other DBDs support get_info via a rather circuitous route (see generating the get_info method) which involves running some code through DBD::ODBC and the database's ODBC driver to find the get_info values then adding the results to the DBD. As a result, sometimes the results are not as dynamic as you might like i.e., the DBD would have to update the version numbers in the get_info structure when it connects and I suspect some don't. Also, I don't believe all DBDs have added full get_info support - DBD::Oracle only just got a reworked get_info recently for instance.

    If you find a DBD which fails to support the get_info values you want then you can always rt it or even better fix it yourself and send the maintainer a patch.

    You might also look at the private attribute information for each DBD - see private_attribute_info.

    Beyond that, you are probably left with a database/DBD specific way for each database/DBD like the select you have been shown for MySQL.

Re: Finding database version from handle?
by erix (Prior) on Jul 22, 2010 at 10:05 UTC

    Querying PostgreSQL, timtowtdi:

    -- versions 7.4 thru 9: # select substring(version() from E'^PostgreSQL (\\S+)') ; substring ----------- 8.4.4 (1 row) -- versions 7.4 thru 9: # select current_setting('server_version'); current_setting ----------------- 8.4.4 (1 row) -- versions 8.2 thru 9: # select current_setting('server_version_num'); current_setting ----------------- 80404 (1 row)

    DBIx-Version looks unmaintained, but maybe it has some useful examples.

    (update) There is info in DBD::Pg attributes as well:

    perl -MDBI -e'my$dbh=DBI->connect("dbi:Pg:"); print $dbh->{pg_server_v +ersion}, "\n"'; 80404

    Then, the 2008 SQL standard prescribes a SQL_IMPLEMENTATION_INFO view which (in postgres) has this information:

    select implementation_info_name, character_value from information_schema.sql_implementation_info where implementation_info_name ~ 'DBMS'; implementation_info_name | character_value --------------------------+----------------- DBMS NAME | PostgreSQL DBMS VERSION | 08.04.0004 (2 rows)

    I seem to remember that, alas, this latter solution (although standard and therefore in principle the 'best') is not very widely available.

Log In?

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://850736]
Approved by ahmad
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others imbibing at the Monastery: (7)
As of 2024-05-21 22:50 GMT
Find Nodes?
    Voting Booth?

    No recent polls found