Sql - MySql interface


  use App::Framework '+Sql' ;


Provides a simplified interface to MySQL via DBI.



The following fields should be defined either in the call to 'new()', as part of a 'set()' call, or called by their accessor method (which is the same name as the field):

host - MySql host [default=localhost]
database - Database name (required)
table - Table name
user - User name
password - Password
trace - Sql debug trace level [default=0]
trace_file - If specified, output trace information to file (default=STDOUT)
sql_vars - Default HASH used to store 'prepare' values
prepare - Create one or more queries



Create a new Sql object.

The %args are specified as they would be in the set method, for example:

        'mmap_handler' => $mmap_handler

The full list of possible arguments are :

        'fields'        => Either ARRAY list of valid field names, or HASH of field names with default values 



Initialises the Sql object class variables.



Set one or more settable parameter.

The %args are specified as a hash, for example

        set('mmap_handler' => $mmap_handler)

Sets field values. Field values are expressed as part of the HASH (i.e. normal field => value pairs).



Returns the sql object. If %args are specified they are used to set the "FIELDS"


Alias to "sql"


Use HASH ref to create 1 or more STHs


Change trace level


Change trace file


Connects to database. Either uses pre-set values for user/password/database, or can use optionally specified args


Disconnect from database (if connected)

sth_create($name, $spec)

Prepare a named SQL query & store it for later execution by query_sth()

Name is saved as $name. Certain names are 'special':

 ins*   - Create an 'insert' type command
 upd*   - Create an 'update' type command
 sel*   - Create a 'select' type command
 check* - Create a 'select' type command

The $spec is either a SCALAR or HASH ref

If $spec is a SCALAR then it is in the form of sql. Note, when the query is executed the values (if required) must be specified.

If $spec is a HASH ref then it can contain the following fields:

        'cmd'   => Command type: 'insert', 'update', 'select'
        'vars'  => ARRAY ref list of variable names (used for 'insert', 'update')
        'vals'  => Provides values to be used in the query (no extra values need to be specified). HASH ref or ARRAY ref. 
                   HASH ref - the hash is used to look up the values using the 'vars' names
                   ARRAY ref - list of values (or refs to values)
                   NOTE: If insufficient values are provided for the query, then the remaining values must be specified in the query call
        'sql'   => Sql string.
                           NOTE: Depending on the command type, if the command is not specified then a default will be prepended to this string.
        'table' => Overrides the object table setting for this query
        'limit' => Sets the limit on the number of results
        'group' => Specify group by string
        'where' => Where clause. String or HASH ref.
                           String - specify sql for where clause (can omit 'WHERE' prefix)
                           HASH ref - specify where clause as HASH:  
                                        'sql' => Used to specify more complicated where clauses (e.g. '`pid`=? AND `channel`=?')
                                        'vars'  => ARRAY ref list of variable names (used for 'where'). If no 'sql' is specified, then the where clause
                                                           is created by ANDing the vars together (e.g. [qw/pid channel/] gives '`pid`=? AND `channel`=?')
                                        'vals'  => Provides values to be used in the query (no extra values need to be specified). HASH ref or ARRAY ref.


The following are all (almost) equivalent:

        $sql->sth_create('check',  {
                                        'table' => '$table',
                                        'limit' => 1,
                                        'where' => {
                                                'sql' => '`pid`=? AND `channel`=?',
                                                'vars'  => [qw/pid channel/],
                                                'vals'  => \%sql_vars
                                        }) ;

        $sql->sth_create('check2',  {
                                        'table' => '$table',
                                        'limit' => 1,
                                        'where' => '`pid`=? AND `channel`=?',# need to pass in extra params to query method
                                        }}) ;

        $sql->sth_create('check3',  "SELECT * FROM `$table` WHERE `pid`=? AND `channel`=? LIMIT 1") ;
        $sql->sth_create('select',  "WHERE `pid`=? AND `channel`=? LIMIT 1") ;

They are then used as:

        $sql->sth_query('check') ; # already given it's parameters
        $sql->sth_query('check2', $pid, $channel) ;
        $sql->sth_query('check3', $pid, $channel) ;
        $sql->sth_query('select', $pid, $channel) ;
sth_query($name, [@vals])

Use a pre-prepared named sql query to return results. If the query has already been given a set of values, then use them; otherwise use the values specified in this call (or append the values to an insufficient list of values given when the sth was created)

sth_query_all($name, [@vals])

Use a pre-prepared named sql query to return results. Return all results in array.

query($query [, @vals])

Query database


Query database - return array of complete results, each entry is a hash ref


Do sql command


Process the SQL text, split it into one or more SQL command, then execute each of them


Returns hash ref to next row (as a result of query). Uses prepared STH name $name (as created by sth_create method), or default name (as created by query method)


Returns list of tables for this database


Convert standard date string (d-MMM-YYYY) or (d/M/YY) to SQL based date (YYYY-MM-DD)


Convert SQL based date (YYYY-MM-DD) to standard date string (d-MMM-YYYY)


Convert SQL based date (YYYY-MM-DD) to a date string suitable for Date::Manip (d/M/YYYY)


NOTE: Only works when feature is registered with an application

Execute the (possible sequence of) command(s) stored in a named __DATA__ area in the application.


Convert $name into a sql command if possible

_sql_setvars($context, $spec, $vars_href)

Set/add variables into the $vars_href HASH driven by the specification $spec (which may be a sql string or a HASH specification). Creates the variables in the namespace defined by the $context string (which is usually the lookup string into the %CMD_SQL table)


Expand all the variables in the HASH ref


Expand all the array variables in the HASH ref

_sql_expand_array($arr, $vars_href)

Expand the named array


Returns the saved sth information looked up from $name; returns undef otherwise


Returns the saved sth looked up from $name; returns undef otherwise

_set_trace($dbh, $trace, $trace_file)

Update trace level


Setting the debug flag to level 1 prints out (to STDOUT) some debug messages, setting it to level 2 prints out more verbose messages.


Steve Price <sdprice at>


None that I know of!

NOTE: To avoid the common "Mysql server gone away" problem, everywhere that I get the database connection handle, I actually call the connect() method to ensure the connection is working.