SQL::Exec - Simple thread and fork safe database access with functionnal and OO interface
use SQL::Exec ':all'; connect('dbi:SQLite:dbname=db_file'); execute(SQL); my $val = query_one_value(SQL); my @line = query_one_line(SQL); my @table = query_all_line(SQL);
SQL::Exec is (another) interface to the DBI which strive for simplicity. Its main functionalities are:
DBMS independent. The module offers specific support for some DB server but can work with any DBD driver;
Extremely simple, a query is always only one function or method call;
Everything is as (in)efficient: you choose the function to call based only on the data that you want to get back, not on some supposed performance benefit;
Supports both OO and functional paradigm with the same interface and functionalities;
Hides away all DBIism, you do not need to set any options, they are handled by the library with nice defaults;
Safe: SQL::Exec verify that what happens is what you meant;
Not an ORM, nor a query generator: you are controling your SQL;
Easy to extends to offer functionalities specific to one DB server;
Handles transparently network failure, fork, thread, etc;
Safely handle multi statement query and automatic transaction.
All this means that SQL::Exec is extremely beginners friendly, it can be used with no advanced knowledge of Perl and code using it can be easily read by people with no knowledge of Perl at all, which is interesting in a mixed environment.
Also, the fact that SQL::Exec does not try to write SQL for the programmer (this is a feature, not a bug), ease the migration to other tools or languages if a big part of the application logic is written in SQL.
Thus SQL::Exec is optimal for fast prototyping, for small applications which do not need a full fledged ORM, for migrating SQL code from/to an other environment, etc. It is usable (thanks to DBIx::Connector) in a CGI scripts, in a mod_perl program or in any web framework as the database access layer.
DBIx::Connector
The SQL::Exec library is mostly database agnostic. However there is some support (limited at the moment) for specific database which will extends the functionnalities of the library for those database.
SQL::Exec
If there is a sub-classe of SQL::Exec for your prefered RDBMS you should use it (for both the OO and the functionnal interface of the library) rather than using directly SQL::Exec. These sub-classes will provide tuned functions and method for your RDBMS, additionnal functionnalities, will set specific database parameters correctly and will assist you to connect to your desired database.
You will find in "Sub-classes" a list of the supported RDBMS and a link to the documentation of their specific modules. If your prefered database is not listed there, you can still use SQL::Exec directly and get most of its benefits.
Do not hesitate to ask for (or propose) a module for your database of choice.
Each function of this library (that is everything described below except new and new_no_connect which are only package method) may be exported on request.
new
new_no_connect
There is also a ':all' tag to get everything at once.
':all'
If you want to use this library in an object oriented way (or if you want to use multiple database connection at once) you will need to create SQL::Exec object using the constructors described here. If you want to use this library in a purely functionnal way then you will want to take a look at the "connect" function described below which will allow you to connect the library without using a single object.
my $h = SQL::Exec->new($dsn, $user, $password, %opts);
Create a new SQL::Exec object and connect-it to the database defined by the $dsn argument, with the supplied $user and $password if necessary.
$dsn
$user
$password
The syntax of the $dsn argument is described in the manual of your DBD driver. However, you will probably want to use one of the existing sub-classes of this module to assist you in connecting to some specific database.
DBD
The %opts argument is optionnal and may be given as a hash or as a hash reference. If the argument is given it set accordingly the option of the object being created. See the "set_options" method for a description of the available options.
%opts
If your DB has a specific support in a sub-classe you must use its specific constructor to get the additionnal benefits it will offer.
my $h = SQL::Exec->new_no_connect(%opts);
This constructor creates a SQL::Exec object without connecting it to any database. You will need to call the "connect" option on the handle to connect it to a database.
The %opts argument is optionnal and is the same as for the new constructor.
Whenever you have finished working with a database connection you may close it (see the "disconnect" function) or you may just let go of the database handle. There is a DESTROY method in this package which will take care of closing the database connection correctly whenever your handle is garbage collected.
DESTROY
The functions and method described below are related to knowing and manipulating the state of a database connection and of its options. The main function to set the options of a database connection is the set_options functions. However, you can pass a hash reference as the last argument to any function of this library with the same syntax as for the set_options function and the options that it describes will be in effect for the duration of the function or method call.
set_options
Any invalid option given in this way to a function/method will result in a 'no such option' error. If you do not die on error but are in strict mode, then the called function will not be executed.
'no such option'
connect($dsn, $user, $password, %opts); $h->connect($dsn, $user, $password, %opts);
This function/method permits to connect a handle which is not currently connected to a database (either because it was created with new_no_connect or because disconnect has been called on it). It also enable to connect to library to a database in a purely functionnal way (without using objects). In that case you can maintain only a single connection to a database. This is the connection that will be used by all the function of this library when not called as an object method. This connection will be refered to as the default handle in this documentation. Its the handle that all other function will use when not applied to an object.
disconnect
You can perfectly mix together the two styles (OO and functionnal): that is, have the library connected in a functionnal style to a database and have multiple other connections openned through the OO interface (with new).
As stated above, this function accepts an optional hash reference as its last argument. Note, however, that the option in this hash will be in effect only for the duration of the connect call, while options passed as the last argument of the constructors (new and new_no_connect) remain in effect until they are modified. This is true even if connect is called to create a default connection for the library. You should use set_options to set options permanently for the default database handle (or any other handle after its creation).
connect
This function will return a true value if the connection succeed and will die or return undef otherwise (depending on the die_on_error option). Not that in strict mode it is an error to try to connect a handle which is already connected to a database.
undef
die_on_error
disconnect();
This function disconnect the default handle of the library from its current connection. You can later on reconnect the library to an other database (or to the same) with the connect function.
$h->disconnect();
This function disconnect the handle it is applied on from its database. Note that the handle itself is not destroyed and can be reused later on with the connect method.
my $v = is_connected(); my $v = $h->is_connected();
This call returns whether the default handle of the library and/or a given handle is currently connected to a database.
This function does not actually check the connection to the database. So it is possible that this call returns true but that a later call to a function which does access the database will fail if, e.g., you have lost your network connection.
my $h = get_default_handle();
Return the default handle of the library (the one used by all function when not applied on an object). The returned handle may then be used as any other handle through the OO interface, but it will still be used by the functionnal interface of this library.
my $e = errstr(); my $e = $c->errstr;
This function returns an error string associated with the last call to the library made with a given handle (or with the default handle). This function will return undef if the last call did not raise an error.
my $e = warnstr(); my $e = $c->warnstr;
This function returns a warning string associated with the last call to the library made with a given handle (or with the default handle). This function will return undef if the last call did not raise a warning.
Note that a single call way raise multiple warning. In that case, only the last one will we stored in this variable.
set_options(HASH); $c->set_options(HASH);
This function sets the option of the given connection handle (or of the default handle). The HASH describing the option may be given as a list of <option = value>> or as a reference to a hash.
HASH
<option =
The function returns a hash with the previous value of all modified options. As a special case, if the function is called without argument, it will returns a hash with the value of all the options. In both cases, this hash is returned as a list in list context and as a hash reference in scalar context.
If an error happen (e.g. use of an invalid value for an option) the function returns undef or an empty list and nothing is modified. In strict mode it is also an error to try to set an nonexistant option.
strict
If the options that you are setting include the strict option, the value of the strict mode is not defined during the execution of this function (that is, it may either be true or false).
See below for a list of the available options.
You will find below a list of the currently available options. Each of these options may be accessed through its dedicated function or with either of the set_option/set_options functions.
set_option
set_options(die_on_error => val); die_on_error(val);
set_options(print_error => val); print_error(val);
set_options(print_warning => val); print_warning(val);
set_options(print_query => FH); print_query(FH);
set_options(strict => val); strict(val);
set_option(replace => \&code); strict(\&code);
Do not use this option...
This option control whether the queries are split in atomic statement before being sent to the database. This option default to true. If it is not set, your queries will be sent as-is to the database, with their ending terminator (if any), etc. You should not set this option to some false value unless you know what you are doing.
The spliting facility is provided by the SQL::SplitStatement package.
SQL::SplitStatement
set_options(auto_transaction => val); auto_transaction(val);
set_options(stop_on_error => val); stop_on_error(val);
sub set_option { my $c = &get_handle;
return $c->set_options({$_[0] => $_[1]}) if @_ == 2; $c->error("Bad number of arguments in %s::set_option", ref $c); return; }
execute(SQL); $c->execute(SQL);
This function execute the SQL code contained in its argument. The SQL is first split at the boundary of each statement that it contains (except if the auto_split option is false) and is then executed statement by statement in a single transaction (meaning that if one of the statement fails, nothing is changed in your database), except if the auto_transaction option is false.
auto_split
auto_transaction
The function will return a defined value if everything succeeded, and undef if an error happen (and it is ignored, otherwise, the function will croak).
defined
croak
The returned value may or may not be the total number of lines modified by your query.
my $v = query_one_value(SQL); my $v = $h->query_one_value(SQL);
This function return one scalar value corresponding to the result of the SQL query provided. This query must be a data returning query (e.g. SELECT).
SELECT
The function will raise an error if nothing is returned by your query (even if the SQL code itself is valid) and, if in strict mode, the function will also fail if your query returns more than one line or one column (but note that the query is still executed).
In case of an error (and if die_on_error is not set) the function will return undef. You must not that this value may also be returned if your query returns a NULL value. In that case to check if an error happened you must check the errstr function which will return undef if there was no errors.
NULL
errstr
Also, if auto_split is activated, the SQL query provided to this function may not contains more than one statement (otherwise an error is thrown). If the option is not set, this condition will not be tested and there is no guarantee on what will happens if you try to execute more than one statement with this function.
my @l = query_one_line(SQL); my @l = $h->query_one_line(SQL); my $l = query_one_line(SQL); my $l = $h->query_one_line(SQL);
This function returns a list corresponding to one line of result of the provided SQL query. If called in scalar context, the function will return a reference to an array rather than a list. You may safely store this array which will not be reused by the library.
In list context, the function will return an empty list in case of an error. You may distinguish this from a query returning no columns with the errstr function. In scalar context, the function will return undef in case of error or a reference to an empty array for query returning no columns.
An error will happen if the query returns no rows at all and, if you are in strict mode, an error will also happen if the query returns more than one rows.
The same limitation applies to this function as for the query_one_line about the number of statement in your query.
query_one_line
my @a = query_all_lines(SQL); my @a = $h->query_all_lines(SQL); my $a = query_all_lines(SQL); my $a = $h->query_all_lines(SQL);
This function executes the given SQL and returns all the returned data from this query. In list context, the fonction returns a list of all the lines. Each lines is a reference to an array, even if there is only one column per lines. In scalar context, the function returns a reference to an array containing each of the array reference for each lines.
In case of errors, if die_on_error is not set, the function will return undef in scalar context and an empty list in list context. This could also be the correct result of a query returning no rows, use the errstr function to distinguish between these two cases.
If there is an error during the fetching of the data and that die_on_error is not set and you are not in strict mode, then all the data already fetched will be returned but no tentatives will be done to try to fetch any more data.
my @l = query_one_column(SQL); my @l = $h->query_one_column(SQL); my $l = query_one_column(SQL); my $l = $h->query_one_column(SQL);
This function returns a list corresponding to one column of result of the provided SQL query. If called in scalar context, the function will return a reference to an array rather than a list. You may safely store this array which will not be reused by the library.
In list context, the function will return an empty list in case of an error. You may distinguish this from a query returning no lines with the errstr function. In scalar context, the function will return undef in case of error or a reference to an empty array for query returning no lines.
An error will happen if the query returns no columns at all and, if you are in strict mode, an error will also happen if the query returns more than one columns.
query_to_file(SQL, file_name, separator, new_line); my $v = $h->query_one_value(SQL, file_name, separator, new_line);
This function...
These functions (or method) provide higher level interface to the database. The implemetations provided here try to be generic and portable but they may not work with any database driver. If necessary, these functions will be overidden in the database specific sub-classes. Be sure to check the documentation for the sub-classe that you are using (if any) because the arguments of these function may differ from their base version.
my $n = count_lines(SQL); my $n = $c->count_lines(SQL);
This function takes an SQL query (SELECT-like), executes it and return the number of lines that the query would have returned (with, e.g., the query_all_lines functions).
query_all_lines
my $b = table_exists(table_name); my $b = $c->table_exists(table_name);
This function returns a boolean value indicating if there is a table with name table_name. The default implementation may erroneously returns false if the table exists but you do not have enough rights to access it.
table_name
This function might also returns true when there is an object with the correct name looking like a table (e.g. a view) in the database.
The implementation of this library is as generic as possible. However some specific functions can be better written for some specific database server and some helper function can be easier to use if they are tuned for a single database server.
This specific support is provided through sub-classse which extend both the OO and the functionnal interface of this library. As stated above, if there is a sub-classe for your specific database, you should use it instead of this module, otherwise.
The sub-classes currently existing are the following ones:
SQLite: the in-file or in memory database with DBD::SQLite;
DBD::SQLite
Oracle: access to Oracle database server with DBD::Oracle;
DBD::Oracle
ODBC: access to any ODBC enabled DBMS through DBD::ODBC;
DBD::ODBC
Teradata: access to a Teradata database with the ODBC driver (there is a DBD::Teradata DBI driver using the native driver for this database (CLI), but its latest version is not on CPAN, so I recommend using the ODBC interface).
ODBC
DBD::Teradata
DBI
CLI
If your database of choice is not yet supported, let me know it and I will do my best to add a module for it (if the DBMS is freely available) or help you add this support (if I cannot have access to an instance of this database server).
In the meantime, SQL::Exec should just work with your database. If that is not the case, you should report this as a bug.
...
Examples would be good.
There is currently no support for placeholders (named or positional) in queries. Mostly because I have not yet found a simple way to expose this functionnality.
Please report any bugs or feature requests to bug-sql-exec@rt.cpan.org, or through the web interface at http://rt.cpan.org/NoAuth/ReportBug.html?Queue=SQL-Exec.
bug-sql-exec@rt.cpan.org
At some point or another you will want to look at the DBI documentation, mother of all database manipulation in Perl. You may also want to look at the DBIx::Connector and SQL::SplitStatement modules upon which SQL::Exec is based.
There is several CPAN module similar to SQL::Exec, I list here only the closest (e.g. which does not impose OO upon your code), you should have a look at them before deciding to use SQL::Exec: DBI::Simple, DBIx::Simple, DBIx::DWIW, DBIx::Wrapper, DBIx::SimpleGoBetween, DBIx::Sunny, SQL::Executor.
DBI::Simple
DBIx::Simple
DBIx::DWIW
DBIx::Wrapper
DBIx::SimpleGoBetween
DBIx::Sunny
SQL::Executor
Also, SQL::Exec will try its best to enable you to run your SQL code in a simple and efficiant way but it will not boil your coffee. You may be interested in other packages which may be used to go beyond SQL::Exec functionnalities, like SQL::Abstract and SQL::Transformer.
SQL::Abstract
SQL::Transformer
Mathias Kende (mathias@cpan.org)
Version 0.02 (January 2013)
Copyright 2012 © Mathias Kende. All rights reserved.
This program is free software; you can redistribute it and/or modify it under the same terms as Perl itself.
To install SQL::Exec, copy and paste the appropriate command in to your terminal.
cpanm
cpanm SQL::Exec
CPAN shell
perl -MCPAN -e shell install SQL::Exec
For more information on module installation, please visit the detailed CPAN module installation guide.