NAME

EZDBI - EZ (Easy) interface to SQL databases (DBI)

SYNOPSIS

use EZDBI;

Connect   'type:database', 'username', 'password', ...;
#OR
Connect   {label=>'section', ...};

Insert    'Into TABLE Values', ...;
Delete    'From TABLE Where field=?, field=?', ...;
Update    'TABLE set field=?, field=?', ...;

@rows   =  Select 'field, field From TABLE Where field=?, ...;
$n_rows = (Select 'Count(*) From TABLE Where field=?,     ...)[0];

DESCRIPTION

This file documents version 0.1 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.

`Connect`

There are two means of connecting to a database with EZDBI. To use the first you put the following line in your program:

Connect '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
Connect 'mysql:databasename', 'username', 'password';

# For Postgres
Connect 'Pg:databasename', 'username', 'password';

# Please send me sample calls for other databases

To use the second you put the following line in your program:

Connect {label=>'section', database=>'db', ini=>'file', attr=>{ ... }};

This form is especially useful if you maintain many scripts that use the same connection information, it allows you store your connection parameters in an AppConfig (Windows INI) format file, which is compatible with DBIx::Connect. Here is an example resource file.

[section]
user     = Bob
pass     = Smith
dsn      = dbi:mysql:?
attr Foo = Bar

label: Required. It indicates which section of the resource file contains the pertinent connection information.
database: Optional. If supplied it replaces the special value ? at the end of the dsn from the resource file.
ini: Optional. Specifies the resource file to read connection information from. See "ENVIRONMENT" and "FILES".
attr: Optional. Equivalent to \%attr in DBI. attr supplied to Connect take precedence over these set in the resource file.
user: Required. The username to connect with.
pass: Required. The password to connect with, be sure to protect you resource file.
dsn: Required. The dbi: is optional, though it is required for a DBIx::Connect compatibile resource file.

`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";
}

The ? will be replaced with the value of $lastname avoiding such potential embedded quoting problems. 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 ?s 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
}

If you simply require the number of rows selected do the following:

if ((Select "Count(*) From ACCOUNTS Where balance < 0")[0]) {
  print "Someone is overdrawn.\n";
} else {
  print "Nobody is overdrawn.\n";
}

That is, use the SQL Count function, and retrieve the appropriate element of the returned list. This behavior has changed since 0.07, where you would simply Select in scalar context.

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 appropriate.

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.

FMTYEWTK

Far More Than You Ever Wanted To Know. Actually, if you are reading this, probably not. These are the "advanced" features of EZDBI. They control EZDBI's behavior or bridge the gap between EZDBI's simplicity and DBI's power.

`use EZDBI maxQuery=>4`

Set the maximum number of queries to cache per database handle. The default is 10.

`Connect`

Connect returns a database handle upon connection, actually a DBI object. If no connection information is provided the current database handle is returned if one exists, otherwise an exception is thrown.

`Disconnect`

If you have a long running program that performs minimal interaction with a database you may wish to Disconnect from the database when not in use so as not to tie up a connection. Additionally it is probably not safe to assume in such a situtation that your connection is still intact. You may provide a database handle or default to the current handle.

my $dbh = Connect ...;
..;
Disconnect($dbh);
...;

`Select`

The normal manner of calling Select returns the entire recordset, this may be hazardous to your health in the limit of large recordsets. Select provides a mechanism for fetching individual records. In scalar context Select returns an object that may be repeatedly queried, fetching a row at a time until the recordset is exhausted. The object can return an arrayref or a hashref.

my $r = Select('id, name From USERS');
while( $_ = $r->([]) ){
  printf "%i\n", $_->[0];   #First column of the record
}
#OR
while( $_ = $r->({}) ){
  printf "%i\n", $_->{id};  #The record column named id
}

If you plan on using any loop control (last is the only sensible option) you will want to enclose everything in a block. It would be prudent to do this even if you aren't using last.

{
  my $r = Select('id, name From USERS');
  while( $_ = $r->([]) ){
    last if $_->[1] eq 'Tim';
    printf "%i\n", $_->[0]; #First column of the record
  }
}

`Sql`

This allows you to execute an arbitrary SQL command which is not abstracted by EZDBI such as Grant.

Sql('Drop FOO');

NOTE: Sql does not expect a result set, as DBI::do() does not, as such commands such as MySQL's Describe are not especially useful.

`Use`

Use provides the ability to manage multiple simultaneous connections. It might be compared to perl's 2-arg select where Select would be perl's readline.

my $dbha = Connect ...;
my $dbhb = Connect ...;
Select('plugh From FOO');
Use($dbha);
Select('xyzzy From BAR');

You might do this if you had Connecteed to both FOO and BAR on the same host. This is perfectly acceptable, but rather wasteful. SQL syntax allows you to do this more efficiently.

Connect ...;
Select('plugh From FOO.BARFLE');
Select('xyzzy From BAR.ZAZ');

Rather this is most appropriate when connections to databases on seperate machines need to be maintained.

ERRORS

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.

ENVIRONMENT

DBIX_CONN: If Connect is called in the AppConfig format but is not provided ini it will try the file specified by DBIX_CONN.
HOME: If DBIX_CONN is not set Connect will try the file .appconfig-dbi in HOME.

FILES

~/.appconfig-dbi: The last fall back for AppConfig style Connect as documented in "ENVIRONMENT".

CAVEATS

Select in list context: The normal manner of calling select can result in excess memory usage, see "FMTYEWTK".
Other Features: Any other features in this module should be construed as undocumented and unsupported and may go away in a future release. Inquire within.

BUGS

This is ALPHA software. There may be bugs. The interface may change.

AUTHORS

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

Jerrad Pierce
jpierce@cpan.org OR webmaster@pthbb.org
http://pthbb.org/software/perl/

THANKS

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

Terence Brannon / Meng Wong / Juerd / Ray Brizner / Gavin Estey

COPYRIGHT

EZDBI - Easy Perl interface to SQL databases
Portions Copyright (C) 2002  Jerrad Pierce
         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.

	Global
`s`	Focus search bar
`?`	Bring up this help dialog

	GitHub
`g` `p`	Go to pull requests
`g` `i`	go to github issues (only if github is preferred repository)

	POD
`g` `a`	Go to author
`g` `c`	Go to changes
`g` `i`	Go to issues
`g` `d`	Go to dist
`g` `r`	Go to repository/SCM
`g` `s`	Go to source
`g` `b`	Go to file browse

	Search terms
module: (e.g. module:Plugin)
distribution: (e.g. distribution:Dancer auth)
author: (e.g. author:SONGMU Redis)
version: (e.g. version:1.00)

NAME

SYNOPSIS

DESCRIPTION

Connect

Select

Delete

Update

Insert

FMTYEWTK

use EZDBI maxQuery=>4

Connect

Disconnect

Select

Sql

Use

ERRORS

ENVIRONMENT

FILES

CAVEATS

BUGS

AUTHORS

THANKS

COPYRIGHT

SEE ALSO

Module Install Instructions

`Connect`

`Select`

`Delete`

`Update`

`Insert`

`use EZDBI maxQuery=>4`

`Connect`

`Disconnect`

`Select`

`Sql`

`Use`