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 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; * Handles prepared statements and bound parameters. 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 "