EZDBI - EZ (Easy) interface to SQL databases (DBI)
use EZDBI; Connect 'type:database', 'username', 'password', ...; Connect {label=>'section', ...}; Delete 'From TABLE Where field=?, field=?', ...; Insert 'Into TABLE', \%values; Insert 'Into TABLE Values', ...; @rows = Select 'field, field From TABLE Where field=?, ...; $n_rows = (Select 'Count(*) From TABLE Where field=?, ...)[0]; Update 'TABLE Set', \%values, ...; Update 'TABLE Set field=?, field=?', ...;
This file documents version 0.120 of EZDBI. It assumes that you already know the basics of SQL. It is not a(n) SQL tutorial.
EZDBI provides a simple and convenient interface to most common SQL databases. It requires that you have installed the DBI module and the database driver (DBD module) for whatever database you will be using.
All of the EZDBI commands support placeholders (?), assuming the DBD you are using does as well. You should always use placeholders where possible as they increase performance and prevent some potential mishaps. For example, the following code would fail due to an imbalanced number of single quotes if $name=q(O'Reilly).
?
$name=q(O'Reilly)
Select "firstname From ACCOUNTS Where lastname='$lastname'"
Instead do
Select "firstname From ACCOUNTS Where lastname='?'", $lastname
Also note that the Perl value undef is converted to the SQL NULL value by placeholders:
undef
NULL
Select '* From ACCOUNTS Where occupation=?', undef # selects records where occupation is NULL
Connect
Creates a connection to the database. There are two means of Connecting to a database with EZDBI. The first is:
Connect 'type:database', ...;
The type is the DBD you are using eg; mysql, Oracle, Pg (for PostgreSQL), 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.
type
mysql
Oracle
Pg
CSV
database
mysql:accounts
Any additional arguments will be passed directly to the database. This is difficult 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 of your DBD for more information.
The second way to connect to a database 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.
Connect { label=>'section', database=>'db', ini=>'file', attr=>{ ... } };
Required. It indicates which section of the resource file contains the pertinent connection information.
Optional. If supplied it replaces the special value ? at the end of the dsn from the resource file.
Optional. Specifies the resource file to read connection information from. See "ENVIRONMENT" and "FILES".
Optional. Equivalent to \%attr in DBI. attr supplied to Connect take precedence over those set in the resource file.
Here is an example resource file. [section] user = Bob pass = Smith dsn = dbi:mysql:? attr Foo = Bar
Note that section names, properties and values are all case-sensitive.
The username to connect with.
The password to connect with, be sure to protect your resource file e.g; chmod go-rw to prevent others from reading it.
chmod go-rw
Required. The dbi: is optional, though it is required for a DBIx::Connect compatibile resource file.
dbi:
Delete
Delete removes records from the database.
Delete 'From ACCOUNTS Where id=?', $old_customer_id;
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.
Insert
Insert inserts new records into the database. The return value is the same as for Delete.
Insert 'Into ACCOUNTS', { id=>undef, age=>26, firstname=>'Michael', lastname=>'Schwern', occupation=>'Slacker', balance=>0.00 };
Or equivalently:
Insert 'Into ACCOUNTS '. '(id, firstname, lastname, age, occupation, balance) '. 'Values(?, ?, ?, ?, ?, ?)', undef, 'Michael', 'Schwern', 26, 'Slacker', 0.00;
While you may explicitly include each placeholder, it is inconvenient. Insert allows the use of ??L as an abbreviation for the appropriate list of placeholders. And so we have the equivalent:
??L
Insert 'Into ACCOUNTS '. '(id, firstname, lastname, age, occupation, balance) '. 'Values ??L', undef, 'Michael', 'Schwern', 26, 'Slacker', 0.00;
If the ??L is the last thing in the SQL statement you may omit it, as well as the word 'Values'. And so we have the equivalent:
'Values'
Insert 'Into ACCOUNTS '. '(id, firstname, lastname, age, occupation, balance) ', undef, 'Michael', 'Schwern', 26, 'Slacker', 0.00;
Select
Select queries the database and retrieves the records that you ask for. 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 @lastname = Select 'lastname From ACCOUNTS'; for $lastname (@lastname) { 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"], ...)
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.
Count
Update
Update modifies records that are already in the database. The return value is the same as for Delete.
Update 'ACCOUNTS Set', {balance=>$balance}, 'Where id=?', $old_customer_id;
Update 'ACCOUNTS Set balance=balance+? Where id=?', $deposit, $old_customer_id;
For the first form, if the Set is the last thing in the first clause of the SQL statement, you may omit it. Likewise the second clause of the SQL statement, 'Where id=?' in the example above, is optional. And so Robin Hood might:
Set
'Where id=?'
Update 'ACCOUNTS', {balance=>1_000_000};
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 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); ...;
The normal manner of calling Select returns the entire recordset at once, 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 "ID: %i\n", $_->[0]; #First column of the record } #OR while( $_ = $r->({}) ){ printf "ID: %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.
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.
Grant
Sql('Drop FOO');
NOTE: Sql does not return a recordest, as it is implemented with DBI::do(). As such Sql is not especially useful for commands like MySQL's Describe.
DBI::do()
Use
Use provides the ability to manage multiple simultaneous connections. It might be compared to perl's 1-arg select where Select would be perl's readline.
select
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.
Connecte
Connect ...; Select('plugh From FOO.BARFLE'); Select('xyzzy From BAR.ZAZ');
Rather this is most appropriate when connections to different database servers are required.
If you need to bind your parameters to a specific data type, perhaps because Microsoft ODBC is misinterpeting your binary data as VARCHAR and storing your data as UCS-2LE (a.k.a extra nulls), EZDBI can handle this as well. Simply import the data type constants ("DBI Constants" in DBI), and pass any values you wish to be bound/cast as tuples like so:
use EZDBI sql_types=>1; ... Update('Foo SET Baz=?, Fred=?', ['Qux'=>SQL_LONGVARBINARY], 'Flintstone');
Note: It is senseless to change the binding type of a column between invocations, and doing so may be hazardous to your health.
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.
eval { ... }
Set $EZDBI::DEBUG to a true value to display the actual SQL statements EZDBI is passing to DBI.
If Connect is called in the AppConfig format but ini is not provided it will try the file specified by DBIX_CONN.
If DBIX_CONN is not set Connect will try the file .appconfig-dbi in HOME.
The last fall back for AppConfig style Connect as documented in "ENVIRONMENT".
The normal manner of calling select can result in excess memory usage, see "FMTYEWTK".
Any other features in this module should be construed as undocumented and unsupported and may go away in a future release. Inquire within.
There may be bugs. The interface may change.
Jerrad Pierce jpierce@cpan.org OR webmaster@pthbb.org http://pthbb.org/software/perl/ Mark Jason Dominus mjd-perl-ezdbi+@plover.com http://perl.plover.com/EZDBI/
Bug reports and feature requests preferred via http://rt.cpan.org/
Thanks to the following people for their advice, suggestions, and support:
Coruscate / Terence Brannon / Meng Wong / Juerd / Ray Brizner / Gavin Estey
EZDBI - Easy Perl interface to SQL databases Portions Copyright (C) 2016, 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.
perl(1), DBI, DBIx::Connect.
To install EZDBI, copy and paste the appropriate command in to your terminal.
cpanm
cpanm EZDBI
CPAN shell
perl -MCPAN -e shell install EZDBI
For more information on module installation, please visit the detailed CPAN module installation guide.