NAME

EZDBI - Easy interface to SQL database


SYNOPSIS

  use EZDBI 'type:database', @arguments;
  Insert 'into TABLE values', ...;
  Delete 'from TABLE where field=?, field=?', ...;
  Update 'TABLE set field=?, field=?', ...;
  @rows   = Select 'field, field from TABLE where field=?, field=?', ...;
  $n_rows = Select 'field, field from TABLE where field=?, field=?', ...;


DESCRIPTION

This file documents version 0.06 of EZDBI.

EZDBI provides a simple and convenient interface to most common SQL databases. It requires that you have installed the DBI module and the DBD module for whatever database you will be using.

This documentation assumes that you already know the basics of SQL. It is not an SQL tutorial.

use

To use EZDBI, you put the following line at the top of your program:

        use EZDBI 'type:database', ...;

The type is the kind of database you are using. Typical values are mysql, Oracle, Sybase, Pg (for PostgreSQL), Informix, DB2, and CSV (for text files). database is the name of the database. For example, if you want to connect to a MySQL database named 'accounts', use mysql:accounts.

Any additional arguments here will be passed directly to the database. This part is hard to document because every database is a little different. Typically, you supply a username and a password here if the database requires them. Consult the documentation for the DBD:: module for your database for more information.

        # For MySQL
        use EZDBI 'mysql:databasename', 'username', 'password';
        # For Postgres
        use EZDBI 'Pg:databasename', 'username', 'password';
        # Please send me sample calls for other databases

The normal use of use creates a connection to the database immediately, even before the rest of your program is compiled, and aborts the compilation unless the attempt to connect to the database is successful. Sometimes it may be more convenient to defer the connection attempt until later, after part of your program has run. To do that, use:

        use EZDBI;

and later, when your program is ready to connect, call

        Connect 'type:database', ...;

Select

Select queries the database and retrieves the records that you ask for. It returns a list of matching records.

        @records = Select 'lastname from accounts where balance < 0';

@records now contains a list of the last names of every customer with an overdrawn account.

        @Tims = Select "lastname from accounts where firstname = 'Tim'";

@Tims now contains a list of the last names of every customer whose first name is Tim.

You can use this in a loop:

        for $name (Select "lastname from accounts where firstname = 'Tim'") {
          print "Tim $name\n";
        }

It prints out Tim Cox, Tim O'Reilly, Tim Bunce, Tim Allen.

This next example prompts the user for a last name, then prints out all the people with that last name. But it has a bug:

        while (1) {
          print "Enter last name: ";
          chomp($lastname = <>);
          last unless $lastname;
          print "People named $lastname:\n"
          for (Select "firstname from accounts where lastname='$lastname'") {
            print "$_ $lastname\n";
          }
        }

The bug is that if the user enters "O'Reilly", the SQL statement will have a syntax error, because the apostrophe in O'Reilly will confuse the database.

Sometimes people go to a lot of work to try to fix this. EZDBI will fix it for you automatically. Instead of the code above, you should use this:

          for (Select "firstname from accounts where lastname=?", $lastname) {
            print "$_ $lastname\n";
          }

EZDBI will replace the ? with the value of $lastname. If $lastname contains an apostrophe or something else that would mess up the SQL, EZDBI will take care of it for you. Use ? wherever you want to insert a value. Doing this may also be much more efficient than inserting the variables into the SQL yourself.

The ?es in the SQL code are called placeholders. The Perl value undef is converted to the SQL NULL value by placeholders:

        for (Select "* from accounts where occupation=?", undef) {
          # selects records where occupation is NULL
        }

You can, of course, use

        for (Select "* from accounts where occupation is NULL") {
          # selects records where occupation is NULL
        }

In scalar context, Select returns the number of rows selected. This means you can say

        if (Select "* from accounts where balance < 0") {
          print "Someone is overdrawn.\n";
        } else {
          print "Nobody is overdrawn.\n";
        }

In list context, Select returns a list of selected records. If the selection includes only one field, you will get back a list of field values:

        # print out all last names
        for $lastname (Select "lastname from accounts") {       
          print "$lastname\n";
        }
        # Select returned ("Smith", "Jones", "O'Reilly", ...)

If the selection includes more than one field, you will get back a list of rows; each row will be an array of values:

        # print out all full names
        for $name (Select "firstname, lastname from accounts") {       
          print "$name->[1], $name->[0]\n";
        }
        # Select returned (["Will", "Smith"], ["Tom", "Jones"],
        #                       ["Tim", "O'Reilly"], ...)
        # print out everything
        for $row (Select "* from accounts") {       
          print "@$row\n";
        }
        # Select returned ([143, "Will", "Smith", 36, "Actor", 142395.37], 
        #                  [229, "Tom", "Jones", 52, "Singer", -1834.00],
        #                  [119, "Tim", "O'Reilly", 48, "Publishing Magnate",
        #                    -550.00], ...)

Delete

Delete removes records from the database.

        Delete "from accounts where id=?", $old_customer_id;

You can (and should) use ? placeholders with Delete when they are approprite.

In a numeric context, Delete returns the number of records deleted. In boolean context, Delete returns a success or failure code. Deleting zero records is considered to be success.

Update

Update modifies records that are already in the database.

        Update "accounts set balance=balance+? where id=?", 
                  $deposit, $old_customer_id;

The return value is the same as for Delete.

Insert

Insert inserts new records into the database.

        Insert "into accounts values (?, ?, ?, ?, ?, ?)", 
                  undef, "Michael", "Schwern",  26, "Slacker", 0.00;

Writing so many ?'s is inconvenient. For Insert, you may use ??L as an abbreviation for the appropriate list of placeholders:

        Insert "into accounts values ??L",
                  undef, "Michael", "Schwern",  26, "Slacker", 0.00;

If the ??L is the last thing in the SQL statement, you may omit it. You may also omit the word 'values':

        Insert "into accounts",
                  undef, "Michael", "Schwern",  26, "Slacker", 0.00;

The return value is the same as for Delete.

Error Handling

If there's an error, EZDBI prints a (hopefully explanatory) message and throws an exception. You can catch the exception with eval { ... } or let it kill your program.

Other Features

Any other features in this module should be construed as undocumented and unsupported and may go away in a future release.


BUGS

This is ALPHA software. There may be bugs. The interface may change. Do not use this for anything important.

Notice that this module has NO TEST SUITE. What does that mean to you?


THANKS

Thanks to the following people for their advice, suggestions, and support:

Terence Brannon / Jerrad Pierce / Meng Wong


SUPPORT

Send mail to mjd-perl-ezdbi+@plover.com and I will do what I can.


AUTHOR

        Mark Jason Dominus
        mjd-perl-ezdbi+@plover.com
        http://perl.plover.com/EZDBI/


COPYRIGHT

    EZDBI - Easy Perl interface to SQL databases
    Copyright (C) 2001  Mark Jason Dominus
    This program is free software; you can redistribute it and/or modify
    it under the terms of the GNU General Public License as published by
    the Free Software Foundation; either version 2 of the License, or
    (at your option) any later version.
    This program is distributed in the hope that it will be useful,
    but WITHOUT ANY WARRANTY; without even the implied warranty of
    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
    GNU General Public License for more details.
    You should have received a copy of the GNU General Public License
    along with this program; if not, write to the Free Software
    Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.

The full text of the license can be found in the COPYING file included with this module.


SEE ALSO

perl(1), DBI.