Sql - MySql interface
use App::Framework '+Sql' ;
Provides a simplified interface to MySQL via DBI.
DOCUMENTATION TO BE COMPLETED
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):
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)
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.
EXAMPLES
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) ;
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)
Use a pre-prepared named sql query to return results. Return all results in array.
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
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
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
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 cpan.org>
<sdprice at cpan.org>
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.
To install App::Framework, copy and paste the appropriate command in to your terminal.
cpanm
cpanm App::Framework
CPAN shell
perl -MCPAN -e shell install App::Framework
For more information on module installation, please visit the detailed CPAN module installation guide.