Perl - DBI Programming:

Handles:
Driver Handles:
The actual instantiation of driver handles happens ''under the hood'' of DBI, typically when DBI->connect() is called.
Statement Handles:

Database Handles:
Database handles are the first step towards actually doing work with the database, in that they encapsulate a single connection to a particular database.


Connecting to DB:

$dbh = DBI->connect( $data_source, $username, $password, \%attr );
The final argument, \%attr, is optional and may be omitted


Ex:

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;


Note:
We can have any number of Database Handles for same Database.


Disconnecting:

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 DESTROY method when there are no longer any references to the object held by your script, or when Perl is exiting.


Piece of Code :

### Prepare a SQL statement for execution
my $sth = $dbh->prepare( "SELECT * FROM megaliths" )
or die "Can't prepare SQL statement: ", $dbh->errstr(), "\n";

### Execute the statement in the database
$sth->execute
or die "Can't execute SQL statement: ", $sth->errstr(), "\n";

### Retrieve the returned rows of data
while ( my @row = $sth->fetchrow_array() ) {
print "Row: @row\n";
}
warn "Problem in fetchrow_array(): ", $sth->errstr(), "\n"

if $sth->err();
### Disconnect from the database
$dbh->disconnect




Fetching Columns:

while ( @row = $sth->fetchrow_array ) {
### Print out a wee message
print "Megalith site $row[0] is a $row[1]\n";
}



Quick way to Ftech & Print:

$sth = $dbh->prepare( "
SELECT name, mapref, location
FROM megaliths
" );
$sth->execute( );
$rows = $sth->dump_results( );

which would display the following results:

'Balbirnie', 'NO 285 029', 'Balbirnie Park, Markinch, Fife'
'Castlerigg', 'NY 291 236', 'Near Keswick, Cumbria, England'
'Sunhoney', 'NJ 716 058', 'Near Insch, Aberdeenshire'
'Avebury', 'SU 103 700', 'Avebury, Wiltshire, England'



Dump the formatted results to the file:

### Dump the formatted results to the file
$rows = $sth->dump_results( 80, '\n', ':', \*FILE );


do vs Prepare:




do method used with database handle..

$dhb->do(insert into ....); internally uses prepare,execute ...

### Setup the statement for repeated execution

$sth = $dbh->prepare( "INSERT INTO megaliths ( name ) VALUES ( ? )" );

### Iterate through the various bits of data...
foreach $name ( qw( Stonehenge Avebury Castlerigg Sunhoney ) ) {
### ... and insert them into the table

$sth->execute( $name );


Fetching Only 1 Row:

selectrow_array( ) and selectrow_arrayref( ) .

>> Like fetchrow_array,fetchrow_arrayref..
* Used with database Handle..


$dbh->selectrow_array( "SELECT name, mapref
FROM megaliths" ); // No need of prepare,execute..