Apache2::Controller::SQL::MySQL - useful database methods for MySQL

Apache2-Controller documentation Contained in the Apache2-Controller distribution.

Index

Code Index:

NAME

Top

Apache2::Controller::SQL::MySQL - useful database methods for MySQL

VERSION

Top

Version 1.000.111

SYNOPSIS

Top

 package UFP::SFC::Controller::Tools;
 use base qw( 
     Apache2::Controller 
     Apache2::Controller::SQL::MySQL
 );
 # ...

DESCRIPTION

Top

Provides some useful methods for interacting with a MySQL database.

This isn't really standard or a necessary part of A2C, I just find it handy.

METHODS

Top

insert_hash

 insert_hash( \%hashref )

Insert data into the database. 

 # http://sfc.ufp/tools/register_crew/enterprise?captain=kirk&sci=spock&med=mccoy
 sub register_crew {
     my ($self, $ship) = @_; 
     my $crew = $self->param();
     $self->insert_hash({
         table    => "crew_$ship",
         data     => $crew,
     });
     $self->print("Warp factor 5, engage.\n");
     return Apache2::Const::HTTP_OK;
 }

Requires a database handle be assigned to $self->{dbh}. See Apache2::Controller::DBI::Connector.

Hashref argument supports these fields:

table

The SQL table to insert into.

data

The hash ref of field data to insert.

on_dup_sql

Optional string of SQL for after 'ON DUPLICATE KEY UPDATE'. Format it yourself.

on_dup_bind

Array ref of bind values for extra ? characters in on_dup_sql.

SEE ALSO

Top

Apache2::Controller::DBI::Connector

Apache2::Controller

AUTHOR

Top

Mark Hedges, hedges +(a t)- formdata.biz

COPYRIGHT AND LICENSE

Top

Copyright 2008-2010 Mark Hedges. CPAN: markle

This library is free software; you can redistribute it and/or modify it under the same terms as Perl itself.

This software is provided as-is, with no warranty and no guarantee of fitness for any particular purpose.

Apache2-Controller documentation Contained in the Apache2-Controller distribution. 
package Apache2::Controller::SQL::MySQL;


use version;
our $VERSION = version->new('1.000.111');


use strict;
use warnings FATAL => 'all';
use English '-no_match_vars';
use Apache2::Controller::X;

sub insert_hash {
    my ($self, $p) = @_;

    my ($table, $data, $on_dup_sql, $on_dup_bind) = @{$p}{qw(
         table   data   on_dup_sql   on_dup_bind
    )};

    my @bind = values %{$data};

    my $sql 
        = "INSERT INTO $table SET\n"
        . join(",\n", map {"    $_ = ".(ref $_ ? $_ : '?')} keys %{$data});

    if ($on_dup_sql) {
        $sql .= "\nON DUPLICATE KEY UPDATE\n$on_dup_sql\n";
        push @bind, @{$on_dup_bind} if $on_dup_bind;
    }

    my $dbh = $self->{dbh};
    my $id;
    eval {
        DEBUG("preparing handle for sql:\n$sql\n---\n");
        my $sth = $dbh->prepare_cached($sql);
        $sth->execute(@bind);
        ($id) = $dbh->selectrow_array(q{ SELECT LAST_INSERT_ID() });
    };
    if ($EVAL_ERROR) {
        a2cx message => "database error: $EVAL_ERROR",
            dump => { sql => $sql, bind => \@bind, };
    }
    return $id;
}


1;