French | Spanish | Portuguese | Italian | German | Japanese | Chinese | Korean | Russian | Arabic

The next two illustrate connections to an Oracle database; for these it is necessary to specify a database username (HSimpson) and password (Doh). Other database options can be set at this stage, as in the second example which sets the database to autocommit mode (each database operation is immediately made permanent).

$dbh = DBI->connect(
   "dbi:Oracle:CSCI8.CS","HSimpson", "Doh");
   #$dbh = DBI->connect("dbi:Oracle:CSCI8.CS",
 # "Hsimpson", "Doh", { AutoCommit => 1} );

In Java you would ask a java.sql.Connection object to return a statement object; you would then use this to submit strings with SQL queries. Perl is very similar. You get statement handles from your database handle, and then execute the statement. The code fragment below would attempt to return all data in Table1 of the database referenced by your $dbh handle.

$sth = $dbh->prepare("SELECT * FROM Table1");
 $sth->execute;

You could be more selective:

$sth = $dbh->prepare("SELECT * FROM Table1 WHERE GRADE > 3");
 $sth->execute;

Often you will want queries that depend on data entered by the user. You can employ interpolation when building a query string:

print "Enter Grade cutoff : ";
   $grade = <STDIN>;
   chomp($grade);
   $sth = $dbh->prepare("SELECT * FROM Table1 WHERE GRADE > $grade");
   ...
 $sth->execute;

Usually it is better to prepare statements with placeholders for arguments, rather than building a specific query each time:

# prepare the statement immediately after opening the database connection.
   $sth = $dbh->prepare("SELECT * FROM Table1 WHERE GRADE > ? ");
   # do other work
   ...
   print "Enter Grade cutoff : ";
   $grade = <STDIN>;
   chomp($grade);
   # invoke the query, with the value of $grade substituted
   # for the first (?) placeholder
 $sth->execute($grade);

The ‘prepare’ step involves some parsing of the SQL string, and this is relatively costly. So it is more efficient to prepare the statement once, and later bind values to placeholders, rather than create a new SQL query string for each request and then parse that string. A secondary reason for using prepared statements is that they help avoid messy problems with quoted strings. Suppose you need a query like the following:

$sth = $dbh->prepare("SELECT GRADE FROM Table1 WHERE NAME='Smith'");

Single string quotes are required around string data in an SQL statement, so you need NAME='Smith'. Usually, the name will be data entered:

print "Enter name : ";
   $name= <STDIN>;
   chomp($name);
 $sth = $dbh->prepare("SELECT * FROM Table1 WHERE GRADE > '$name'');

The value entered for the name is interpolated into the doubly quoted string with the SQL statement; SQL requires the value to be surrounded by single quote characters. Problems appear with customer names such as O’Brien – the single quote character in the name disrupts the pairing of quotes and results in an error from the SQL system. This problem does not occur if you use ‘?’ placeholders and bind values to the placeholders. In addition to ‘select’ statements for data retrieval, you will need ‘insert’, ‘update’, and ‘delete’ statements like the following:

$sth = $dbh->prepare("INSERT INTO TABLE1 VALUES (?, ?, ?)");
   $sth = $dbh->prepare("UPDATE TABLE SET GRADE= ? WHERE NAME = ?");
 $sth = $dbh->prepare("DELETE FROM TABLE1 WHERE NAME='Smith'");

If your SQL is faulty, the prepare operation will fail and you get undef as the result:

$sth = $dbh->prepare(...);
 unless defined $sth die "SQL problem";

Correctly formed SQL statements may fail; for example, you will get an SQL error if you attempt to insert a new record with a primary key that fails a uniqueness test. The DBI module variable $DBI::errstr contains an error report if an operation failed. This can be included in any termination message:

$sth->execute($data) || die "delete failed because $DBI::errstr";

Data retrieved by an SQL select query are accessed via the statement handle; this can be used to retrieve successive rows of the results table:

@row = $sth->fetchrow_array;

The fetchrow_array method returns undef when have all data have been processed. The typical code pattern for data retrieval is:

$sth = $dbh->prepare("select ... = ?");
   ...
   $sth->execute($data1, $data2);
   while(@row = $sth->fetchrow_array) {
   ...
 }

All database handles are closed at program termination. However, you should close database connections as soon as possible:

$dbh->close;

Database drivers often rely on environment variables. For example, a Unix version of the Oracle driver relies on environment variables to hold information identifying the directories containing Oracle components, version number and related data. You will need to consult your database documentation to find exactly what environment variables must be defined. Usually, you define and ‘export’ these variables from a ‘.profile’ file (a kind of Unix equivalent to a ‘batch’ file that is run whenever you log in). If the environment variables are not set correctly, you will not be able to connect to your database.