www.developer.com/lang/article.php/2184681
April 10, 2003 Connection and DisconnectionThe main activity in database programming usually involves the execution of SQL statements within a database. However, to accomplish this task, a connection to a database must be established first. Furthermore, after all the work has been done, it is good manners to disconnect from the database to free up both your local machine resources and, more importantly, valuable database resources. ConnectionIn the case of simple databases, such as flat-file or Berkeley DB files, "connecting" is usually as simple as opening the files for reading or using the tie mechanism. However, in larger database systems, connecting may be considerably more complicated. A relatively simple RDBMS is mSQL, which has a simple method of connection: to connect, a program connects to a TCP/IP port on the computer running the database. This establishes a live connection within the database. However, more complex systems, such as Oracle, have a lot more internal security and housekeeping work that must be performed at connection time. They also have more data that needs to be specified by the program, such as the username and password that you wish to connect with. By looking at a broad spectrum of database systems, the information required to connect can be boiled down to:
In light of these common arguments, the syntax for connecting to databases using DBI is to use the $dbh = DBI->connect( $data_source, $username, $password, \%attr ); The final argument, This method, when invoked, returns a database handle if the connection has been successfully made to the database. Upon failure, the value To illustrate the
#!/usr/bin/perl -w
#
# ch04/connect/ex1: Connects to an Oracle database.
use DBI; # Load the DBI module
### Perform the connection using the Oracle driver
my $dbh = DBI->connect( "dbi:Oracle:archaeo", "username",
"password" )
or die "Can't connect to Oracle database: $DBI::errstr\n";
exit;
This simple example illustrates the use of the A more complicated example might be to connect twice to the same database from within the one script:
#!/usr/bin/perl -w
#
# ch04/connect/ex2: Connects to two Oracle databases
# simultaneously with identical arguments.
# This is to illustrate that all database
# handles, even if identical argument-wise,
# are completely separate from one another.
use DBI; # Load the DBI module
### Perform the connection using the Oracle driver
my $dbh1 = DBI->connect( "dbi:Oracle:archaeo", "username",
"password" )
or die "Can't make 1st database connect: $DBI::errstr\n";
my $dbh2 = DBI->connect( "dbi:Oracle:archaeo", "username",
"password" )
or die "Can't make 2nd database connect: $DBI::errstr\n";
exit;
or to connect simultaneously to two different databases. For example:
#!/usr/bin/perl -w
#
# ch04/connect/ex3: Connects to two Oracle databases
# simultaneously.
use DBI; # Load the DBI module
### Perform the connection using the Oracle driver
my $dbh1 = DBI->connect( "dbi:Oracle:archaeo", "username",
"password" )
or die "Can't connect to 1st Oracle database: $DBI::errstr\n";
my $dbh2 = DBI->connect( "dbi:Oracle:seconddb", "username",
"password" )
or die "Can't connect to 2nd Oracle database: $DBI::errstr\n";
exit;
This former example is quite interesting, because even though we have used identical arguments to A final example of using
#!/usr/bin/perl -w
#
# ch04/connect/ex4: Connects to two database, one Oracle, one mSQL
# simultaneously. The mSQL database handle has
# auto-error-reporting disabled.
use DBI; # Load the DBI module
### Perform the connection using the Oracle driver
my $dbh1 = DBI->connect( "dbi:Oracle:archaeo", "username",
"password" )
or die "Can't connect to Oracle database: $DBI::errstr\n";
my $dbh2 = DBI->connect( "dbi:mSQL:seconddb", "username",
"password" , {
PrintError => 0
} )
or die "Can't connect to mSQL database: $DBI::errstr\n";
exit;
The DisconnectionExplicit disconnection from the database is not strictly necessary if you are exiting from your program after you have performed all the work, but it is a good idea. We strongly recommend that you get into the habit of disconnecting explicitly. DBI provides a method through which programmers may disconnect a given database handle from its database. This is good practice, especially in programs in which you have performed multiple connections or will be carrying out multiple sequential connections. The method for performing disconnections is: $rc = $dbh->disconnect(); According to this definition, An example of using
#!/usr/bin/perl -w
#
# ch04/disconnect/ex1: Connects to an Oracle database
# with auto-error-reporting disabled
# then performs an explicit disconnection.
use DBI; # Load the DBI module
### Perform the connection using the Oracle driver
my $dbh = DBI->connect( "dbi:Oracle:archaeo", "username",
"password" , {
PrintError => 0
} )
or die "Can't connect to Oracle database: $DBI::errstr\n";
### Now, disconnect from the database
$dbh->disconnect
or warn "Disconnection failed: $DBI::errstr\n";
exit;
Upon successful disconnection, the return value will be true. Otherwise, it will be false. In practice, failure to disconnect usually means that the connection has already been lost for some reason. After disconnecting the database handle can't be used for anything worthwhile. What happens if you don't explicitly disconnect? Since DBI handles are references to Perl objects, Perl's own garbage collector will move in and sweep up any object trash you leave lying around. It does that by calling the object's The Database handle destroyed without explicit disconnect. A major caveat with the |
Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | Shopping | E-mail Offers | Freelance Jobs 