CodeSamplesPerl#!/usr/bin/perl #------------------------------------------------------------------------------- # The DBI module is the interface to the database, and the strict module # forces us to declare variables so we don't get burned by a global variable # with the same name. #------------------------------------------------------------------------------- #require DBI; use DBI; use strict; #------------------------------------------------------------------------------- # declare local variables #------------------------------------------------------------------------------- my $dbh; my $sth; my $data_source; my $username; my $password; my %dbOptions; my $rc; my $rowCount; my $empno; my $ename; my @resultRow; #------------------------------------------------------------------------------- # declare variables with database connection information #------------------------------------------------------------------------------- $data_source = "dbi:ODBC:ORA8I_SOLARIS"; $username ='scott'; $password = 'tiger'; %dbOptions = {PrintError => 1, AutoCommit => 2}; #------------------------------------------------------------------------------- # connect to the database #------------------------------------------------------------------------------- $dbh = DBI->connect("$data_source", '', '') or die "$DBI::errstr\n"; $dbh->{PrintError} = 0; $dbh->{AutoCommit} = 0; $dbh->{LongReadLen} = 64000; if ( defined($DBI::err) ) { if ( $DBI::err != 0 ) { printf( STDERR "$0: Connect to Oracle server failed with the following error:\n"); printf( STDERR " %s\n\n", $DBI::errstr); exit(1); } } #------------------------------------------------------------------------------- # set up the SQL statement to be sent #------------------------------------------------------------------------------- $rc = $dbh->do("create table foo (id int)"); if ( !defined($rc) ) { printf( STDERR "$0: Create table failed with the following error:\n"); printf( STDERR " %s\n\n", $DBI::errstr); } else { printf( STDERR "$0: Create table succeeded.\n"); } #------------------------------------------------------------------------------- # commit #------------------------------------------------------------------------------- $rc = $dbh->commit; #------------------------------------------------------------------------------- # set up the SQL statement to be sent #------------------------------------------------------------------------------- $sth = $dbh->prepare("select empno, ename from emp order by empno"); if ( $DBI::err != 0 ) { printf(STDERR "Error parsing SQL:\n"); printf(STDERR "%s\n\n", $DBI::errstr); exit(-1); } #------------------------------------------------------------------------------- # execute the SQL statement #------------------------------------------------------------------------------- $rc = $sth->execute; # rc is useless for select statements if ( defined($DBI::err) ) { if ( $DBI::err != 0 ) { printf( STDERR "$0: Select from emp table failed with the following error:\n"); printf( STDERR " %s\n\n", $DBI::errstr); } } #------------------------------------------------------------------------------- # fetch each row of data from the database and process it #------------------------------------------------------------------------------- for ( @resultRow = $sth->fetchrow_array; @resultRow != (); @resultRow = $sth->fetchrow_array ) { ($empno, $ename) = @resultRow; printf("%8d %1s\n", $empno, $ename); } #------------------------------------------------------------------------------- # free resources from the select cursor #------------------------------------------------------------------------------- $rc = $sth->finish; #------------------------------------------------------------------------------- # Get the row count (THIS ONLY WORKS IF YOU FETCH ALL OF THE ROWS) #------------------------------------------------------------------------------- $rowCount = $sth->rows; printf("\n%d rows processed.\n\n", $rowCount); #------------------------------------------------------------------------------- # disconnect from the database #------------------------------------------------------------------------------- $rc = $dbh->disconnect; #------------------------------------------------------------------------------- # return success to the OS #------------------------------------------------------------------------------- exit(0);