The London Perl and Raku Workshop takes place on 26th Oct 2024. If your company depends on Perl, please consider sponsoring and/or attending.
 / 
Bio-EnsEMBL-113.0.0
River stage zero No dependents
/ Bio::EnsEMBL::DBSQL::DBConnection

LICENSE

Copyright [1999-2015] Wellcome Trust Sanger Institute and the EMBL-European Bioinformatics Institute Copyright [2016-2024] EMBL-European Bioinformatics Institute

Licensed under the Apache License, Version 2.0 (the "License"); you may not use this file except in compliance with the License. You may obtain a copy of the License at

http://www.apache.org/licenses/LICENSE-2.0

Unless required by applicable law or agreed to in writing, software distributed under the License is distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the specific language governing permissions and limitations under the License.

CONTACT

Please email comments or questions to the public Ensembl
developers list at <http://lists.ensembl.org/mailman/listinfo/dev>.

Questions may also be sent to the Ensembl help desk at
<http://www.ensembl.org/Help/Contact>.

NAME

Bio::EnsEMBL::DBSQL::DBConnection

SYNOPSIS

$dbc = Bio::EnsEMBL::DBSQL::DBConnection->new(
  -user   => 'anonymous',
  -dbname => 'homo_sapiens_core_20_34c',
  -host   => 'ensembldb.ensembl.org',
  -driver => 'mysql',
);

# SQL statements should be created/executed through this modules
# prepare() and do() methods.

$sth = $dbc->prepare("SELECT something FROM yourtable");

$sth->execute();

# do something with rows returned ...

$sth->finish();

DESCRIPTION

This class is a wrapper around DBIs datbase handle. It provides some additional functionality such as the ability to automatically disconnect when inactive and reconnect when needed.

Generally this class will be used through one of the object adaptors or the Bio::EnsEMBL::Registry and will not be instantiated directly.

METHODS

new

Arg [DBNAME] : (optional) string
               The name of the database to connect to.
Arg [HOST] : (optional) string
             The domain name of the database host to connect to.
             'localhost' by default.
Arg [USER] : string
             The name of the database user to connect with
Arg [PASS] : (optional) string
             The password to be used to connect to the database
Arg [PORT] : (optional) int
             The port to use when connecting to the database
             3306 by default if the driver is mysql.
Arg [DRIVER] : (optional) string
               The type of database driver to use to connect to the DB
               mysql by default.
Arg [DBCONN] : (optional)
               Open another handle to the same database as another connection
               If this argument is specified, no other arguments should be
               specified.
Arg [DISCONNECT_WHEN_INACTIVE]: (optional) boolean
               If set to true, the database connection will be disconnected
               everytime there are no active statement handles. This is
               useful when running a lot of jobs on a compute farm
               which would otherwise keep open a lot of connections to the
               database.  Database connections are automatically reopened
               when required.Do not use this option together with RECONNECT_WHEN_CONNECTION_LOST.
Arg [WAIT_TIMEOUT]: (optional) integer
               Time in seconds for the wait timeout to happen. Time after which
               the connection is deleted if not used. By default this is 28800 (8 hours)
               on most systems.
               So set this to greater than this if your connection are getting deleted.
               Only set this if you are having problems and know what you are doing.
Arg [RECONNECT_WHEN_CONNECTION_LOST]: (optional) boolean
               In case you're reusing the same database connection, i.e. DISCONNECT_WHEN_INACTIVE is
               set to false and running a job which takes a long time to process (over 8hrs),
               which means that the db connection may be lost, set this option to true.
               On each prepare or do statement the db handle will be pinged and the database
               connection will be reconnected if it's lost.

Example    : $dbc = Bio::EnsEMBL::DBSQL::DBConnection->new
                (-user   => 'anonymous',
                 -dbname => 'homo_sapiens_core_20_34c',
                 -host   => 'ensembldb.ensembl.org',
                 -driver => 'mysql');

Description: Constructor for a Database Connection. Any adaptors that require
             database connectivity should inherit from this class.
Returntype : Bio::EnsEMBL::DBSQL::DBConnection
Exceptions : thrown if USER or DBNAME are not specified, or if the database
             cannot be connected to.
Caller     : Bio::EnsEMBL::Utils::ConfigRegistry ( for newer code using the registry)
             Bio::EnsEMBL::DBSQL::DBAdaptor        ( for old style code)
Status     : Stable

connect

Example    : $dbcon->connect()
Description: Connects to the database using the connection attribute
             information.
Returntype : none
Exceptions : none
Caller     : new, db_handle
Status     : Stable

connected

Example    : $dbcon->connected()
Description: Boolean which tells if DBConnection is connected or not.
             State is set internally, and external processes should not alter state.
Returntype : undef or 1
Exceptions : none
Caller     : db_handle, connect, disconnect_if_idle, user processes
Status     : Stable

equals

Example    : warn 'Same!' if($dbc->equals($other_dbc));
Description: Equality checker for DBConnection objects
Returntype : boolean
Exceptions : none
Caller     : new
Status     : Stable

driver

Arg [1]    : (optional) string $arg
             the name of the driver to use to connect to the database
Example    : $driver = $db_connection->driver()
Description: Getter / Setter for the driver this connection uses.
             Right now there is no point to setting this value after a
             connection has already been established in the constructor.
Returntype : string
Exceptions : none
Caller     : new
Status     : Stable

port

Arg [1]    : (optional) int $arg
             the TCP or UDP port to use to connect to the database
Example    : $port = $db_connection->port();
Description: Getter / Setter for the port this connection uses to communicate
             to the database daemon.  There currently is no point in
             setting this value after the connection has already been
             established by the constructor.
Returntype : string
Exceptions : none
Caller     : new
Status     : Stable

dbname

Arg [1]    : (optional) string $arg
             The new value of the database name used by this connection.
Example    : $dbname = $db_connection->dbname()
Description: Getter/Setter for the name of the database used by this
             connection.  There is currently no point in setting this value
             after the connection has already been established by the
             constructor.
Returntype : string
Exceptions : none
Caller     : new
Status     : Stable

username

Arg [1]    : (optional) string $arg
             The new value of the username used by this connection.
Example    : $username = $db_connection->username()
Description: Getter/Setter for the username used by this
             connection.  There is currently no point in setting this value
             after the connection has already been established by the
             constructor.
Returntype : string
Exceptions : none
Caller     : new
Status     : Stable

user

Arg [1]    : (optional) string $arg
             The new value of the username used by this connection.
Example    : $user = $db_connection->user()
Description: Convenience alias for the username method
Returntype : String

host

Arg [1]    : (optional) string $arg
             The new value of the host used by this connection.
Example    : $host = $db_connection->host()
Description: Getter/Setter for the domain name of the database host use by
             this connection.  There is currently no point in setting
             this value after the connection has already been established
             by the constructor.
Returntype : string
Exceptions : none
Caller     : new
Status     : Stable

hostname

Arg [1]    : (optional) string $arg
             The new value of the host used by this connection.
Example    : $hostname = $db_connection->hostname()
Description: Convenience alias for the host method
Returntype : String

password

Arg [1]    : (optional) string $arg
             The new value of the password used by this connection.
Example    : $host = $db_connection->password()
Description: Getter/Setter for the password of to use for this
             connection.  There is currently no point in setting
             this value after the connection has already been
             established by the constructor.
Returntype : string
Exceptions : none
Caller     : new
Status     : Stable

pass

Arg [1]    : (optional) string $arg
             The new value of the password used by this connection.
Example    : $pass = $db_connection->pass()
Description: Convenience alias for the password method
Returntype : String

disconnect_when_inactive

Arg [1]    : (optional) boolean $newval
Example    : $db->disconnect_when_inactive(1);
Description: Getter/Setter for the disconnect_when_inactive flag.  If set
             to true this DBConnection will continually disconnect itself
             when there are no active statement handles and reconnect as
             necessary.  Useful for farm environments when there can be
             many (often inactive) open connections to a database at once.
Returntype : boolean
Exceptions : none
Caller     : Pipeline
Status     : Stable

reconnect_when_lost

Arg [1]    : (optional) boolean $newval
Example    : $db->reconnect_when_lost(1);
Description: Getter/Setter for the reconnect_when_lost flag.  If set
             to true the db handle will be pinged on each prepare or do statement
             and the connection will be reestablished in case it's lost.
             Useful for long running jobs (over 8hrs), which means that the db
             connection may be lost.
Returntype : boolean
Exceptions : none
Caller     : Pipeline
Status     : Stable

locator

Arg [1]    : none
Example    : $locator = $dbc->locator;
Description: Constructs a locator string for this database connection
             that can, for example, be used by the DBLoader module
Returntype : string
Exceptions : none
Caller     : general
Status     : Stable

db_handle

Arg [1]    : DBI Database Handle $value
Example    : $dbh = $db_connection->db_handle()
Description: Getter / Setter for the Database handle used by this
             database connection.
Returntype : DBI Database Handle
Exceptions : none
Caller     : new, DESTROY
Status     : Stable

prepare

Arg [1]    : string $string
             the SQL statement to prepare
Example    : $sth = $db_connection->prepare("SELECT column FROM table");
Description: Prepares a SQL statement using the internal DBI database handle
             and returns the DBI statement handle.
Returntype : DBI statement handle
Exceptions : thrown if the SQL statement is empty, or if the internal
             database handle is not present
Caller     : Adaptor modules
Status     : Stable

prepare_cached

Arg [1]    : string $string
             the SQL statement to prepare
Example    : $sth = $db_connection->prepare_cached("SELECT column FROM table");
Description: Prepares a SQL statement using the internal DBI database handle
             and returns the DBI statement handle. The prepared statement is
             cached so that it does not have to be prepared again.

             If only a subset of rows are required, finish() should be called
             on the object to free up the statement handle.

             For further information please consult https://metacpan.org/pod/DBI#prepare_cached
Returntype : DBI statement handle
Exceptions : thrown if the SQL statement is empty, or if the internal
             database handle is not present
Caller     : Adaptor modules
Status     : Experimental

reconnect

Example    : $dbcon->reconnect()
Description: Reconnects to the database using the connection attribute
             information if db_handle no longer pingable.
Returntype : none
Exceptions : none
Caller     : new, db_handle
Status     : Stable

do

Arg [1]    : string $string
             the SQL statement to prepare
Example    : $sth = $db_connection->do("SELECT column FROM table");
Description: Executes a SQL statement using the internal DBI database handle.
Returntype : Result of DBI dbh do() method
Exceptions : thrown if the SQL statement is empty, or if the internal
             database handle is not present.
Caller     : Adaptor modules
Status     : Stable

work_with_db_handle

Arg [1]    : CodeRef $callback
Example    : my $q_t = $dbc->work_with_db_handle(sub { my ($dbh) = @_; return $dbh->quote_identifier('table'); });
Description: Gives access to the DBI handle to execute methods not normally
             provided by the DBConnection interface
Returntype : Any from callback
Exceptions : If the callback paramater is not a CodeRef; all other
             errors are re-thrown after cleanup.
Caller     : Adaptor modules
Status     : Stable

prevent_disconnect

Arg[1]      : CodeRef $callback
Example     : $dbc->prevent_disconnect(sub { $dbc->do('do something'); $dbc->do('something else')});
Description : A wrapper method which prevents database disconnection for the
              duration of the callback. This is very useful if you need
              to make multiple database calls avoiding excessive database
              connection creation/destruction but still want the API
              to disconnect after the body of work.

              The value of C<disconnect_when_inactive()> is set to 0 no
              matter what the original value was & after $callback has
              been executed. If C<disconnect_when_inactive()> was
              already set to 0 then this method will be an effective no-op.
Returntype  : None
Exceptions  : Raised if there are issues with reverting the connection to its
              default state.
Caller      : DBConnection methods
Status      : Beta

quote_identifier

Arg [n]    : scalar/ArrayRef
Example    : $q = $dbc->quote_identifier('table', 'other');
             $q = $dbc->quote_identifier([undef, 'my_db', 'table'], [undef, 'my_db', 'other']);
Description: Executes the DBI C<quote_identifier> method which will quote
             any given string using the database driver's quote character.
Returntype : ArrayRef
Exceptions : None
Caller     : General
Status     : Stable

disconnect_if_idle

Arg [1]    : none
Example    : $dbc->disconnect_if_idle();
Description: Disconnects from the database if there are no currently active
             statement handles.
             It is called automatically by the DESTROY method of the
             Bio::EnsEMBL::DBSQL::SQL::StatementHandle if the
             disconect_when_inactive flag is set.
             Users may call it whenever they want to disconnect. Connection will
             reestablish on next access to db_handle()
Returntype : 1 or 0
             1=problem trying to disconnect while a statement handle was still active
Exceptions : none
Caller     : Bio::EnsEMBL::DBSQL::SQL::StatementHandle::DESTROY
             Bio::EnsEMBL::DBSQL::DBConnection::do
Status     : Stable

add_limit_clause

Arg [1]    : string $sql
Arg [2]    : int $max_number
Example    : my $new_sql = $dbc->add_limit_clause($sql,$max_number);
Description: Giving an SQL statement, it adds a limit clause, dependent on the database
             (in MySQL, should add a LIMIT at the end, MSSQL uses a TOP clause)
Returntype : String containing the new valid SQL statement
Exceptions : none
Caller     : general
Status     : at risk

from_date_to_seconds

Arg [1]    : date $date
Example    : my $string = $dbc->from_date_to_seconds($date);
Description: Giving a string representing a column of type date
              applies the database function to convert to the number of seconds from 01-01-1970
Returntype : string
Exceptions : none
Caller     : general
Status     : at risk

from_seconds_to_date

Arg [1]    : int $seconds
Example    : my $string = $dbc->from_seconds_to_date($seconds);
Description: Giving an int representing number of seconds
              applies the database function to convert to a date
Returntype : string
Exceptions : none
Caller     : general
Status     : at risk

sql_helper

Example    : my $h = $dbc->sql_helper();
Description: Lazy generated instance of L<Bio::EnsEMBL::Utils::SqlHelper>
             which provides useful wrapper methods for interacting with a
             DBConnection instance.
Returntype : Bio::EnsEMBL::Utils::SqlHelper
Exceptions : none
Caller     : general
Status     : Stable

to_hash

Example    : my $hash = $dbc->to_hash();
             my $new_dbc = $dbc->new(%{$hash});
Description: Provides a hash which is compatible with the
             parameters for DBConnection's new() method. This can be
             useful during serialisation
Returntype : Hash
Exceptions : none
Caller     : general
Status     : New

_driver_object