NAME

MySperqlOO - OO Module to simplify DBI MySQL statements

SYNOPSIS

use DBIx::MySperqlOO;
  
my $mysperql = DBIx::MySperqlOO->new( $parameters );

$return = $mysperql->sqlexec($sql, $type, $parms);
@return = $mysperql->sqlexec($sql, $type, $parms);

$sth = $mysperql->sqlexecute($sql, $parms);
$sth = $mysperql->sqlparse($test, $parms);
$ref = $mysperql->sqlfetch($sth, $type);

DESCRIPTION

MySperqlOO enables one line sql statements when using perl with DBI and MySQL. It supports single or multiple connections to both local and remote databases.

Using the module requires that you understand: 1) how to write the SQL statments you need 2) perl lists ('@') and references ('\')

The second concept is necessary to both understand the "$type" parameter and handle the results. The types are strings that represent the data type expected to be returned. They logically match the data structure returned: '@' is a one-row list, '\@' is a one row reference to a list, and '\@@' is a (potentially) multi-row reference. See below for included examples of each type.

This module was originally created as a database library in 2001 by Roger Hall (Little Rock, AR) and Eric Goldbrenner (San Francisco, CA), and was upgraded to Class::Std style OO in 2008 by Roger Hall and Michael Bauer.

Methods

new() / connect()
```
my $mysperql = DBIx::MySperqlOO->new({ db => $db, host => $host, user => $user, pass => $pass });
```
The parameters hash should include the following minimum keys for DBI connections: db, host, user, pass. The database must exist on the host, and the user must have permissions using the pass. Note that the hash is referenced.

The connect() method is called within new(). You should not use the connect() method directly. It is included in the title to help you understand what new() does.

sqlexec()

my $dataref = $mysperql->sqlexec( $sql, $type, $parms );

This is the workhorse statement, and for most applications, the only "sqlX" function you will use.

A typical statement might be:

my $dataref = $mysperql->sqlexec( $sql, '\@@' );

with other examples below.

Parm: $sql    a sql statement string
      $type   a character string that determines 
              the data type of the return:
              \@@   reference to an array of arrays (all 
                    rows fetch)
              \@    reference to an array (one row fetch)
              @     an array (one row fetch)
              \%    reference to a hash with field names
                    as keys (one row fetch)
              $sth  statement handle
              other scalar value
      $parms  database specific parameters
Note: Remember to single-quote the $type character
      string (because "$sth" != '$sth')!

_sqlexecute()
```
my $sth = $mysperql->_sqlexecute( $sql, $parms);
```
Handles the parsing, binding and execution of a sql statement.

_sqlfetch()

my $dataref = $mysperql->_sqlfetch( $sth, $type );

Fetches rows based on $type specified.

Parm: $sth    a valid dbi statement handle
      $type   a string that determines the type of return
              \@@   reference to an array of arrays (all 
                    rows fetch)
              \@    reference to an array (one row fetch)
              @     an array (one row fetch)
              \%    reference to a hash with field names
                    as keys (one row fetch)
              $sth  statement handle
              other scalar value

_sqlparse()

my $sth = $mysperql->_sqlparse( $test, $parms );

Encapsulates DBI prepare() function.

Parm: $test is either a(n) sql statement with zero
      or more named parameters or an active 
      statement handle
      $parms is a hash reference with parameter names 
      as keys and parameter values as values. 
      Only the word part of the names should be 
      used as keys (ie leave off the leading ':').
Note: 1. _sqlparse is responsible for making sure an 
      active global database handle ($dbh) exists
      2. _sqlparse prepare()s the statement to check 
      for parsing errors.

get_row_count()

my $count = $mysperql->get_row_count( $dataref );

Returns the row count of a record object.

Parm: $dataref    a reference to a table (array of arrays)
      $count      the number of rows
Note: Handles Scalars, Arrays, and all references.

get_column_count()

my $count = $mysperql->get_column_count( $dataref );

Returns the column count of a record object.

Parm: $dataref    a reference to a table (array of arrays)
      $count      the number of columns
Note: Handles Scalars, Arrays, and all references.

get_field_names()

my @fields = $mysperql->get_field_names( $sth );

Returns the names of the table columns.

Parm: $sth    a valid dbi statement handle
      @fields the returned list of field names

_is_handle()

my $boolean = $mysperql->_is_handle( $test );

Tests if an object is a statement handle. Used to tell handles and sql strings apart.

 if ( $self->_is_handle( $test ) ) { ... }

Parm: $test   the handle or string

EXAMPLES

Top-Of-The-File Requirements

  use DBIx::MySperqlOO;
  
  my $mysperql = DBIx::MySperqlOO->new({ db   => 'mydb', 
                                         host => 'localhost', 
					 user => 'myuser', 
					 pass => 'mypass' });

Insert a record

# Insert record
$mysperql->sqlexec( "insert into table(field1, field2) values ('$field1', '$field2')" );

Update a record

# Update record
$mysperql->sqlexec( "update table set field1 = '$field1' where id = $id" );

Delete a record

# Delete record
$mysperql->sqlexec( "delete from table where id = $id" );

Select single record

# Get record
my $sql = "select * from table where id = $id";
my ($id, $field1, $field2) = $mysperql->sqlexec( $sql, '@' );

Select table with fields-on-the-fly

# Create handle
my $sql = "select * from table where id = $id";
$mysperql->sqlexec( $sql, '$sth' );

# Or optionally get the handle
my $sth = $mysperql->sqlexec( $sql, '$sth' );

# Get field names
my @fields = $mysperql->get_field_names();

# Get records for a saved statement handle
my $dataref = $mysperql->sqlexec( $sth, '@' );

Select multiple records

# Get table
my $sql = "select * from table";
my $rows = $mysperql->sqlexec( $sql, '\@@' );
foreach $row (@$rows) {
	my ($id, $field1, $field2) = @$row;

	print "($id) $field1 = $field2\n";
}

Using multiple MySQL connections

# Open the first connection
my $myperql1 = DBIx::MySperql->new( $parameters1 );

# Make statements on db 1
my $sql = "...
$mysperql1->sqlexec( $sql ...);

# Open an additional connection
my $myperql2 = DBIx::MySperql->new( $parameters2 );

# Make statements on db 2
$sql = "...
$mysperql2->sqlexec( $sql ...);

# Make statements on db 1
$sql = "...
$mysperql1->sqlexec( $sql ...);

...

EXPORT

None.

ABSTRACT

MySperqlOO enables one line sql statements with DBI and MySQL.

AUTHOR

Roger A Hall <roger@iosea.com>

COPYRIGHT AND LICENSE

This library is free software; you can redistribute it and/or modify it under the same terms as Perl itself, either Perl version 5.8.5 or, at your option, any later version of Perl 5 you may have available.

To install DBIx::MySperqlOO, copy and paste the appropriate command in to your terminal.

cpanm

cpanm DBIx::MySperqlOO

CPAN shell

perl -MCPAN -e shell
install DBIx::MySperqlOO

For more information on module installation, please visit the detailed CPAN module installation guide.

	Global
`s`	Focus search bar
`?`	Bring up this help dialog

	GitHub
`g` `p`	Go to pull requests
`g` `i`	Go to GitHub issues (only if GitHub is preferred repository)

	POD
`g` `a`	Go to author
`g` `c`	Go to changes
`g` `i`	Go to issues
`g` `d`	Go to dist
`g` `r`	Go to repository/SCM
`g` `s`	Go to source
`g` `b`	Go to file browse

Search terms
module: (e.g. module:Plugin)
distribution: (e.g. distribution:Dancer auth)
author: (e.g. author:SONGMU Redis)
version: (e.g. version:1.00)