NAME
SQL::Exec - Functionnal and OO interface to the DBI and DBIx::Connector
SYNOPSIS
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);Main functionnalities
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.
DESCRIPTION
Support of specific DB
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.
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.
Exported symbols
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.
There is also a ':all' tag to get everything at once.
CONSTRUCTORS/DESTRUCTORS
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.
new
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.
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.
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.
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.
new_no_connect
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.
destructor
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.
GETTER/SETTER AND OPTIONS
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.
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.
connect
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.
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).
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.
disconnect
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.
is_connected
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.
get_default_handle
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.
errstr
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.
warnstr
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
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.
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.
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.
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.
die_on_error
set_options(die_on_error => val);
die_on_error(val);print_error
set_options(print_error => val);
print_error(val);print_warning
set_options(print_warning => val);
print_warning(val);print_query
set_options(print_query => FH);
print_query(FH);strict
replace
connect_options
auto_split
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.
auto_transaction
use_connector
Do not use this option...
stop_on_error
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;
}STANDARD QUERY FUNCTIONS
execute
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.
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).
The returned value may or may not be the total number of lines modified by your query.
query_one_value
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).
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.
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.
query_one_line
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_all_lines
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.
The same limitation applies to this function as for the query_one_line about the number of statement in your query.
query_one_column
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.
The same limitation applies to this function as for the query_one_line about the number of statement in your query.
query_to_file
query_to_file(SQL, file_name, separator, new_line);
my $v = $h->query_one_value(SQL, file_name, separator, new_line);This function...
HIGH LEVEL QUERY FUNCTIONS
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.
count_lines
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).
table_exists
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.
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.
SUB-CLASSING
TODO: howto...
SUB-CLASSES
SQLite, Oracle, ODBC, and Teradata...
EXAMPLE
CAVEATS
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.
BUGS
Please report any bugs or feature requests to bug-dbix-puresql@rt.cpan.org, or through the web interface at http://rt.cpan.org/NoAuth/ReportBug.html?Queue=DBIx-PureSQL.
SEE ALSO
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.
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.
AUTHOR
Mathias Kende (mathias@cpan.org)
VERSION
Version 0.01 (January 2013)
COPYRIGHT & LICENSE
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.