Section 2.3. Running Perl DBI

2.3 Running Perl DBI

Would this book be complete without a "Hello World" example? Of course not, so here goes! Our very simple Perl DBI script (in Example 2-1) will simply connect to the orcl Oracle database as the scott user , run through a straightforward SQL cursor on the DUAL table via a prepared statement, and then print out the result before logging off. We'll run the same script on both Unix and Win32 to demonstrate Perl's operating system independence:

Example 2-1. Our first Perl DBI script, HelloWorld.pl

  #!perl -w  use strict;  use DBI;  # Connect to Oracle database, making sure AutoCommit is # turned off and potential errors are raised.    my $dbh = DBI->connect(  'dbi:Oracle:orcl', 'scott', 'tiger',  { RaiseError => 1, AutoCommit => 0 } ); # Create the SQL.    my $sql = qq{ SELECT 'Hello World' FROM DUAL };    # Prepare the SQL and execute.    my $sth = $dbh->prepare( $sql ); $sth->execute(  );    # Fetch output rows into array, plus prepare a  # print formatter for the results.    while ( my($helloWorldString) = $sth->fetchrow_array) {       # Print out the result.       print $helloWorldString, "\n"; } $dbh->disconnect(  );  # Disconnect 

Example 2-1 may look a little scary, but after reading Appendix B, you'll quickly be able to reduce it to the following:

 #!perl -w    use strict; use DBI;    my $dbh = DBI->connect( 'dbi:Oracle:orcl', 'scott', 'tiger'  ,  { RaiseError => 1, AutoCommit => 0 } );    print $dbh->selectrow_array(qq{ SELECT 'Hello World' FROM DUAL });    $dbh->disconnect(  ); 

Note the following about Example 2-1:

• If you know some Perl already, you may notice how we've only imported the DBI module, within the script, via Perl's use command. The Perl DBI package takes care of picking up DBD::Oracle , for us when we run the DBI->connect call. (The dbi:Oracle:orcl parameter string cleverly indicates that we want to use DBD::Oracle rather than any other database driver.)

• You may also wish to change the orcl , scott, and tiger information strings to something more appropriate for your own target database before running the program.

Before we run this script under either Unix or Win32, we need to do the following:

1. Make sure ORACLE_HOME is set within the command shell environment, to ensure that the DBD::Oracle driver can locate the OCI libraries.

2. Make sure the target database is up and ready.

3. Check that the relevant database listener is up and running; you can do this by trying to connect to the target database via a remote SQL*Plus session.

We should now be ready to run the HelloWorld.pl Perl script under both Unix and Win32.

2.3.1 Running a Perl Script on Unix

Follow these steps to run a Perl script on a Unix system:

1. Log in to Unix as the Oracle user. If necessary with your particular version of Unix, make sure that the LD_LIBRARY_PATH environment variable is set correctly by adding $ORACLE_HOME/lib to it.

2. Make sure the Perl executable program is within your PATH:

    $ type perl 

   This should return something similar to the following:

    perl is hashed (/usr/bin/perl) 

3. Ensure that you're in the directory where you've written the HelloWorld.pl script.

4. Now simply type:

    $ perl HelloWorld.pl 

   (You may want to use the optional -w flag, as in perl -w HelloWorld.pl , to make explicit any warnings.)

   If you make the HelloWorld.pl file executable, you can also use the shebang syntax on line 1, #!perl -w , to run the program directly:

    $ chmod +x HelloWorld.pl $ ./HelloWorld.pl 

5. The resulting output should be:

    Hello World 

   OK, so it's lacking in wild inspiration, but from little acorns grow. You can see our output in Figure 2-3, along with the corresponding Win32 version.

2.3.2 Running a Perl Script on Win32

Follow these steps to run a Perl script on a Win32 system:

1. Go to the directory where you've created the HelloWorld.pl script (or copied it) from your Unix system.

    C:\> cd Perl\eg 

2. Enter the following:

    C:\Perl\eg>  perl  HelloWorld.pl 

   (Some Win32 systems associate the .pl suffix with the Perl interpreter, which means that you may be able to drop the use of the explicit perl command.)

3. You should now see the following output generated:

    Hello World 

   Again, this is demonstrated in Figure 2-3.

Now we can break out the bubbly!

2.3.3 DBI by Proxy

One of DBD::Oracle 's major limitations is its reliance on the presence of at least Oracle client libraries for successful compilation. Indeed, here's what the Version 1.12 DBD::Oracle README file has to say:

Install enough Oracle software to enable DBD::Oracle to build. That usually includes Pro*C. That's not very specific because it varies so much between Oracle releases.

Figure 2-3. HelloWorld.pl running under Win32 and Unix

If you have an Oracle server but no client machines possessing on-board Oracle software, this is a problem. It's also a problem if you have a client firewall that DBD::Oracle fails to break through. Fear not, for there is a potential solution at hand ” Jochen Wiedmann's amazing DBI::ProxyServer and DBD::Proxy module set, which comes automatically within the DBI tarball. For DBI 1.20, you can read about both modules here:

http://search.cpan.org/doc/TIMB/DBI-1.20/lib/DBI/ProxyServer.pm

http://search.cpan.org/doc/TIMB/DBI-1.20/lib/DBD/Proxy.pm

(Try searching on http://search.cpan.org if these version-specific documents have been superseded.)

The idea is to set up a proxy server daemon, dbiproxy , that runs on your Oracle server machine. On your remote clients , you use DBD::Proxy instead of DBD::Oracle . This module connects across the network to the dbiproxy daemon, which passes through the SQL requests to a server-configured DBD::Oracle driver, thereby allowing proxy access to the Oracle database. This setup is displayed in Figure 2-4.

In order to use the ProxyServer system over a network, we need to install several Perl packages:

• Storable (as used by the following PlRPC packages); you can find this at http://www.cpan.org/authors/id/KWILLIAMS.

• Net::Daemon .

• PlRPC (which contains the RPC::PlServer and RPC::PlClient subpackages).

You can find these at http://www.cpan.org/authors/id/JWIED.

Figure 2-4. DBI:ProxyServer and DBD::Proxy architecture