• Establishing a Sessions
• Executing Queries
• Commits and Rollbacks
• Catching Errors
• Bind Variables
• Re-Binding and Re-Executing
• Accessing Fields in the Result Set
• Cursors
• Getting Column Information

To use SQL Relay, you have to identify the connection that you intend to use.

#!/usr/bin/env perl

use DBI;

my $dbh=DBI->connect("DBI:SQLRelay:host=testhost;port=9000;socket=","testuser","testpassword");

... execute some queries ...

$dbh->disconnect;

After calling the connect(), a session is established when the first execute() is run.

For the duration of the session, the client stays connected to a database connection daemon. While one client is connected, no other client can connect. Care should be taken to minimize the length of a session.

If you're using a transactional database, ending a session has a catch. Database connection daemons can be configured to send either a commit or rollback at the end of a session if DML queries were executed during the session with no commit or rollback. Program accordingly.

Call prepare() and execute() to run a query.

#!/usr/bin/env perl

use DBI;

my $dbh=DBI->connect("DBI:SQLRelay:host=testhost;port=9000;socket=","testuser","testpassword");

my $sth=$dbh->prepare("select * from user_tables");

$sth->execute();

... process the result set ...

$dbh->disconnect;

If you need to execute a commit or rollback, you should use the commit() and rollback() methods rather than sending a "commit" or "rollback" query. There are two reasons for this. First, it's much more efficient to call the methods. Second, if you're writing code that can run on transactional or non-transactional databases, some non-transactional databases will throw errors if they receive a "commit" or "rollback" query, but by calling the commit() and rollback() methods you instruct the database connection daemon to call the commit and rollback API methods for that database rather than issuing them as queries. If the API's have no commit or rollback methods, the calls do nothing and the database throws no error. This is especially important when using SQL Relay with ODBC.

You can also turn Autocommit on or off by setting the AutoCommit attribute of the database handle.

The following command turns Autocommit on.

$dbh->{AutoCommit} = 1;

The following command turns Autocommit off.

$dbh->{AutoCommit} = 0;

When Autocommit is on, the database performs a commit after each successful DML or DDL query. When Autocommit is off, the database commits when the client instructs it to, or (by default) when a client disconnects. For databases that don't support Autocommit, setting the AutoCommit attribute has no effect.

If your calls to connect(), prepare() or execute() fail, you can catch the error in DBI->errstr.

#!/usr/bin/env perl

use DBI;

my $dbh=DBI->connect("DBI:SQLRelay:host=testhost;port=9000;socket=","testuser","testpassword")
        or die DBI->errstr;

my $sth=$dbh->prepare("select * from user_tables")
        or die DBI->errstr;

$sth->execute()
        or die DBI->errstr;

... process the result set ...

$dbh->disconnect;

Programs rarely execute fixed queries. More often than not, some part of the query is dynamically generated. The Perl DBI API provides means for using bind variables in those queries.

For a detailed discussion of binds, see this document.

Here is an example using the bind_params() function. The first parameter of the bind_params() function corresponds to the name or position of the bind variable.

#!/usr/bin/env perl

use DBI;

my $dbh=DBI->connect("DBI:SQLRelay:host=testhost;port=9000;socket=","testuser","testpassword");

my $sth=$dbh->prepare("select * from my_table where col1=:1 and col2=:2 and col3=:3");

$sth->bind_param(1,"hello");
$sth->bind_param(2,1);
$sth->bind_param(3,5.5);

$sth->execute();

... process the result set ...

$dbh->disconnect;

Here is an example using the execute() function directly. The additional parameters correspond to bind variable positions. Note that the first parameter must be "undef".

#!/usr/bin/env perl

use DBI;

my $dbh=DBI->connect("DBI:SQLRelay:host=testhost;port=9000;socket=","testuser","testpassword");

my $sth=$dbh->prepare("select * from my_table where col1=:0 and col2=:1 and col3=:2");

$sth->execute(undef,"hello",1,5.5);

... process the result set ...

$dbh->disconnect;

A feature of the prepare/bind/execute paradigm is the ability to prepare, bind and execute a query once, then re-bind and re-execute the query over and over without re-preparing it. If your backend database natively supports this paradigm, you can reap a substantial performance improvement.

#!/usr/bin/env perl

use DBI;

my $dbh=DBI->connect("DBI:SQLRelay:host=testhost;port=9000;socket=","testuser","testpassword");

my $sth=$dbh->prepare("select * from my_table where col1=:0 and col2=:1 and col3=:2");

$sth->execute(undef,"hello",1,1.1);

... process result set ...

$sth->execute(undef,"hi",2,2.2);

... process result set ...

$sth->execute(undef,"goodbye",3,3.3);

... process result set ...

$dbh->disconnect;

The fetchrow_array(), bind_columns() and fetch() functions are useful for processing result sets. fetchrow_array() returns an array of values. bind_columns() associates variables with columns which are set when fetch() is called.

Here's an example using fetchrow_array().

#!/usr/bin/env perl

use DBI;

my $dbh=DBI->connect("DBI:SQLRelay:host=testhost;port=9000;socket=","testuser","testpassword");

my $sth=$dbh->prepare("select * from user_tables");

$sth->execute();

while (@data=$sth->fetchrow_array()) {
        
        foreach $col (@data) {
                print "\"$col\",";
        }
        print "\n";
}

$dbh->disconnect;

Here's an example using bind_columns() and fetch(). Note that the first bind_columns() parameter must be "undef".

#!/usr/bin/env perl

use DBI;

my $dbh=DBI->connect("DBI:SQLRelay:host=testhost;port=9000;socket=","testuser","testpassword");

my $sth=$dbh->prepare("select * from my_table");

$sth->execute();
$sth->bind_columns(undef,\$col1,\$col2,\$col3,\$col4);

while ($sth->fetch()) {
        print "$col, $col2, $col3, $col4\n";
}

$dbh->disconnect;

Cursors make it possible to execute queries while processing the result set of another query. You can select rows from a table in one query, then iterate through it's result set, inserting rows into another table, using only 1 database connection for both operations.

For example:

#!/usr/bin/env perl

use DBI;

my $dbh=DBI->connect("DBI:SQLRelay:host=testhost;port=9000;socket=","testuser","testpassword");

my $sth1=$dbh->prepare("select * from my_first_table");

$sth1->execute();
$sth1->bind_columns(undef,\$col1,\$col2,\$col3);

while ($sth1->fetch()) {
        my $sth2=$dbh->prepare("insert into my_second_table values (:0, :1, :2, sysdate)");
        $sth2->execute(undef,$col1,$col2,$col3);
}

$dbh->disconnect;

After executing a query, the column count is stored in the NUMBER_OF_FIELDS statement property and column names are stored in the NAME statement property. They are accessible as follows:

#!/usr/bin/env perl

use DBI;

my $dbh=DBI->connect("DBI:SQLRelay:host=testhost;port=9000;socket=","testuser","testpassword");

my $sth=$dbh->prepare("select * from my_table");

$sth->execute();

for ($i=1; $i<=$sth->{NUM_OF_FIELDS}; $i++) {
       print "Column $i: $sth->{NAME}->[i-1]\n";
}

$dbh->disconnect;