NAME
   SQL::Exec - Simple thread and fork safe database access with functionnal
   and OO interface

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. Just do :

     use SQL::Exec ':all';

   at the beginning of your file to get all the power of "SQL::Exec" with
   an overhead as small as possible.

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 is an "SQL::Exec"
   object and may then be used as any other handles through the OO
   interface, but it will still be used by the functionnal interface of
   this library.

 get_dbh
     my $dbh = get_dbh();
     my $dbh = $h->get_dbh();

   Returns the internal "DBI" handle to your database. This handle may be
   used in conjonction with other libraries which can accept a connected
   handle.

   Note that, because of the use of "DBIx::Connector", this handle may
   change during the life of your program. If possible, you should rather
   use the "get_conn" method (see below) to get a persistant handle.

 get_conn
     my $conn = get_conn();
     my $conn = $h->get_conn();

   Returns the internal "DBIx::Connector" handle to your database. This
   handle may be used in conjonction with other libraries which can accept
   such a handle (e.g. "DBIx::Lite"). This handle will not change while you
   do not close your connection to your database.

 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);

   This option (which default to *true*) specify if an error condition
   abort the execution of your program or not. If so, the "croak" function
   will be called (and you may trap the error with "eval"). If not, the
   function call will still abort and return "undef" or an empty list
   (depending on the context). When this may be a valid result for the
   function, you may call the "errstr" function/method to get the last
   error message or "undef" if the last call was succesful.

  print_error
     set_options(print_error => val);
     print_error(val);

   This option (which default to *true*) control whether the errors are
   printed or not (this does not depend on the setting of the
   "die_on_error" option). If the supplied value is *true* the errors are
   printed to "STDERR", otherwise nothing is printed.

  print_warning
     set_options(print_warning => val);
     print_warning(val);

   This option (which default to *true*) control whether the warning are
   printed or not. If the supplied value is *true* the warnings are printed
   to "STDERR", otherwise nothing is printed.

  print_query
     set_options(print_query => FH);
     print_query(FH);

   This option (which default to "undef") control whether the queries are
   printed before being executed. Unless the previous option, to set it,
   you must pass it an open *file handle*. The queries will then be printed
   to this handle.

  strict
     set_options(strict => val);
     strict(val);

   This option (which default to *true*) control the so-called "strict"
   mode of the library. It has 3 possible settings. If set to a *true*
   value, some condition are checked to ensure that the operations of the
   library are as safe as possible (the exact condition are described in
   the documentation of the function to which they apply). When the
   condition are not met, an error is thrown (what happens exactly depends
   on the "die_on_error" and "print_error" options).

   If this option is set to a *defined* *false* value (such as '0'), then
   the strict conditions are still tested, but only result in a warning
   when they are not met.

   Finally, if this option is set to "undef" then the nothing happens when
   a strict condition is not met (and the tests will altogether be omitted
   if they are potentially costly).

  replace
     set_option(replace => \&code);
     replace(\&code);
     replace($obj);
     replace(HASH);
     replace(undef);

   This option allows to set up a procedure which get the possibility to
   modify an SQL query before it is executed (e.g. to replace generic
   parameter by specific name). The default (when the option is "undef") is
   that nothing is done.

   If this option is a *CODE* reference (or an anonymous sub-function),
   then this function is called each time you supply an SQL query to this
   library with the query in the $_ variable. The function may modify this
   variable and the resulting value of $_ is executed. The call to this
   function takes place before the spliting of the SQL query (if
   "auto_split" is *true*).

   You may also pass to this option a *HASH* reference. In that case, the
   hash describes a series of replacement to be performed on the SQL query
   (see the example below). Internally, this requires the "String::Replace"
   library. The function will croak if you call it with a *HASH* and you do
   not have this library installed. When using the "replace" function
   (rather than the "set_options" function) you may give a list descibing a
   *HASH*, rather than a *HASH* reference.

   Finally, you may also give to this function any object which have a
   "replace" method (e.g. an already built "String::Replace" object). This
   method will then be called with your SQL queries (using arguments and
   return values, and not the $_ variable).

   Here is an example (which will work with an SQLite database):

     replace(String::Replace->new(table_name => 't'));
     execute('create table table_name (a)');
     replace(table_name => 't');
     execute('insert into table_name values (1)');
     query_one_value('select * from table_name', { replace => sub { s/table_name/t/g } }) == 1

  connect_options
   Do not use this option...

  auto_split
   This option (which default to *true*) controls whether the queries are
   split in atomic statement before being sent to the database. If it is
   not set, your queries will be sent *as-is* to the database, with their
   ending terminator (if any), this may result in error with some database
   driver which do not allow for multi-statement queries. You should not
   set this option to a *false* value unless you know what you are doing.

   The spliting facility is provided by the "SQL::SplitStatement" package.

  auto_transaction
     set_options(auto_transaction => val);
     auto_transaction(val);

   This option (which default to *true*) controls whether the "execute" and
   "execute_multiple" functions automatically start a transaction whenever
   they execute more than one statement.

  use_connector
   Do not use this option...

  stop_on_error
     set_options(stop_on_error => val);
     stop_on_error(val);

   This option is only usefull when the "die_on_error" and "strict_error"
   options are false and will control if the execution is interupted when
   an error occurs during a multi-statement query. Its default value is
   *true*.

   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). If the "auto_transaction"
   option is false, each of your statement will be executed atomically and
   all modification will be recorded immediately.

   Optionnaly, you may also provide a reference to an array of SQL queries
   instead of a single SQL query. In that case, each query will be split
   independently (if "auto_split" is true) and all the resulting queries
   will be executed in order inside one single transaction (if
   "auto_transaction" is true). Note that you may not pass a list of SQL
   query, but only a reference to such a list (for compatibility with a
   future version of the library).

   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.

   Here are examples of valid call to the "execute" function:

     execute('insert into t values (1)');
     execute('insert into t values (1);insert into t values (1)');
     execute(['insert into t values (1)', 'insert into t values (1)']);

 execute_multiple
     execute_multiple(SQL, PARAM_LIST);
     $c->execute_multiple(SQL, PARAM_LIST);

   This function executes one or multiple time an SQL query with the
   provided parameters. The SQL query may be only a single statement
   (although this condition is not tested if "auto_split" is false, but
   then there is no garantee on what will happen).

   The SQL query can contain placeholder ('?' characters) in place of SQL
   values. These placeholder will be replaced during the execution by the
   parameters that you provide. You should provide a list of parameters
   with the same number of parameters than the number of placeholder in the
   statement. You may provide this list as an array or an array reference.

   You may also provide a list of array reference or a reference to an
   array of array reference. In that case, the query will be executed once
   for each element of this array (the external one), with the placeholders
   taking the values given in the sub-arrays.

   As a special case, if there is only a single placeholder in your query,
   you may provide a simple list of parameters to execute the query
   multiple time (each with one of the parameter).

   If the "auto_transaction" option is true, then all the executions of
   your query will be performed atomically inside a single transaction.
   This is usefull for example to performs many insertions in a table in an
   efficient manner.

   Here are three pairs of equivalent call to "execute_multiple":

     execute_multiple('insert into t values (?, ?)', 1, 2);
     execute_multiple('insert into t values (?, ?)', [1, 2]);

     execute_multiple('insert into t values (?, ?)', [1, 2], [3, 4]);
     execute_multiple('insert into t values (?, ?)', [[1, 2], [3, 4]]);

     execute_multiple('insert into t values (?)', 1, 2, 3);
     execute_multiple('insert into t values (?)', [[1], [2], [3]]);

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

 query_to_file
     query_to_file(SQL, file_name, separator, new_line);
     my $v = $h->query_one_value(SQL, file_name, separator, new_line);
     query_to_file(SQL, FH, separator, new_line);

   This function execute an SQL query and send its output to a file or file
   handle.

   The first argument is the query to execute (which may contain only a
   single statement).

   The second argument is the destination of th data. You may pass either a
   file name or a reference to an *IO* or *GLOB*. If it is omitted the data
   will go to "STDOUT". If you pass a filename, you may prefix it with
   "<<'">'>>> to append to the file (rather that to erase it).

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.

   ########################################################################
   ########
   ########################################################################
   ######## ## ## ## HIGH LEVEL HELPER FUNCTIONS ## ## ##
   ########################################################################
   ########
   ########################################################################
   ########

   push @EXPORT_OK, ('split_query');

   # TODO : décider de la sémantique (renvoie des statements vides ?) sub
   split_query { my ($str) = @_; return grep {
   $sql_split_grepper->split($_) } $sql_splitter->split($str); }

SUB-CLASSING
   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.

 Sub-classes
   The sub-classes currently existing are the following ones:

   *   SQLite: the in-file or in memory database with DBD::SQLite;

   *   Oracle: access to Oracle database server with DBD::Oracle;

   *   ODBC: access to any ODBC enabled DBMS through 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).

   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.

 How to
   ...

EXAMPLES
   Examples would be good.

CAVEATS
   There is currently no support for prepared statements and placeholders
   bayond what is exposed in the "execute_multiple" function.

BUGS
   Please report any bugs or feature requests to
   "[email protected]", or through the web interface at
   <http://rt.cpan.org/NoAuth/ReportBug.html?Queue=SQL-Exec>.

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, DBIx::Lite, and SQL::Translator.

AUTHOR
   Mathias Kende ([email protected])

VERSION
   Version 0.08 (March 2013)

COPYRIGHT & LICENSE
   Copyright 2013 © 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.

You are viewing proxied material from ftp.icm.edu.pl. The copyright of proxied material belongs to its original authors. Any comments or complaints in relation to proxied material should be directed to the original authors of the content concerned. Please see the disclaimer for more details.