|
package SL::DBUtils;
|
|
|
|
require Exporter;
|
|
@ISA = qw(Exporter);
|
|
|
|
@EXPORT = qw(conv_i conv_date conv_dateq do_query selectrow_query do_statement
|
|
dump_query quote_db_date
|
|
selectfirst_hashref_query selectfirst_array_query
|
|
selectall_hashref_query selectall_array_query
|
|
prepare_execute_query prepare_query);
|
|
|
|
sub conv_i {
|
|
my ($value, $default) = @_;
|
|
return (defined($value) && "$value" ne "") ? $value * 1 : $default;
|
|
}
|
|
|
|
sub conv_date {
|
|
my ($value) = @_;
|
|
return (defined($value) && "$value" ne "") ? $value : undef;
|
|
}
|
|
|
|
sub conv_dateq {
|
|
my ($value) = @_;
|
|
if (defined($value) && "$value" ne "") {
|
|
$value =~ s/\'/\'\'/g;
|
|
return "'$value'";
|
|
}
|
|
return "NULL";
|
|
}
|
|
|
|
sub do_query {
|
|
my ($form, $dbh, $query) = splice(@_, 0, 3);
|
|
|
|
dump_query(LXDebug::QUERY, '', $query, @_);
|
|
|
|
if (0 == scalar(@_)) {
|
|
$dbh->do($query) || $form->dberror($query);
|
|
} else {
|
|
$dbh->do($query, undef, @_) ||
|
|
$form->dberror($query . " (" . join(", ", @_) . ")");
|
|
}
|
|
}
|
|
|
|
sub selectrow_query { &selectfirst_array_query }
|
|
|
|
sub do_statement {
|
|
my ($form, $sth, $query) = splice(@_, 0, 3);
|
|
|
|
dump_query(LXDebug::QUERY, '', $query, @_);
|
|
|
|
if (0 == scalar(@_)) {
|
|
$sth->execute() || $form->dberror($query);
|
|
} else {
|
|
$sth->execute(@_) ||
|
|
$form->dberror($query . " (" . join(", ", @_) . ")");
|
|
}
|
|
}
|
|
|
|
sub dump_query {
|
|
my ($level, $msg, $query) = splice(@_, 0, 3);
|
|
|
|
while ($query =~ /\?/) {
|
|
my $value = shift(@_);
|
|
$value =~ s/\'/\\\'/g;
|
|
$value = "'${value}'";
|
|
$query =~ s/\?/$value/;
|
|
}
|
|
|
|
$query =~ s/[\n\s]+/ /g;
|
|
|
|
$msg .= " " if ($msg);
|
|
|
|
$main::lxdebug->message($level, $msg . $query);
|
|
}
|
|
|
|
sub quote_db_date {
|
|
my ($str) = @_;
|
|
|
|
return "NULL" unless defined $str;
|
|
return "current_date" if $str =~ /current_date/;
|
|
|
|
$str =~ s/'/''/g;
|
|
return "'$str'";
|
|
}
|
|
|
|
sub prepare_query {
|
|
my ($form, $dbh, $query) = splice(@_, 0, 3);
|
|
|
|
dump_query(LXDebug::QUERY, '', $query, @_);
|
|
|
|
my $sth = $dbh->prepare($query) || $form->dberror($query);
|
|
return $sth;
|
|
}
|
|
|
|
sub prepare_execute_query {
|
|
my ($form, $dbh, $query) = splice(@_, 0, 3);
|
|
|
|
dump_query(LXDebug::QUERY, '', $query, @_);
|
|
|
|
my $sth = $dbh->prepare($query) || $form->dberror($query);
|
|
if (scalar(@_) != 0) {
|
|
$sth->execute(@_) || $form->dberror($query . " (" . join(", ", @_) . ")");
|
|
} else {
|
|
$sth->execute() || $form->dberror($query);
|
|
}
|
|
|
|
return $sth;
|
|
}
|
|
|
|
sub selectall_hashref_query {
|
|
my ($form, $dbh, $query) = splice(@_, 0, 3);
|
|
|
|
my $sth = prepare_execute_query($form, $dbh, $query, @_);
|
|
my $result = [];
|
|
while (my $ref = $sth->fetchrow_hashref()) {
|
|
push(@{ $result }, $ref);
|
|
}
|
|
$sth->finish();
|
|
|
|
return $result;
|
|
}
|
|
|
|
sub selectall_array_query {
|
|
my ($form, $dbh, $query) = splice(@_, 0, 3);
|
|
|
|
my $sth = prepare_execute_query($form, $dbh, $query, @_);
|
|
my @result;
|
|
while (my ($value) = $sth->fetchrow_array()) {
|
|
push(@result, $value);
|
|
}
|
|
$sth->finish();
|
|
|
|
return @result;
|
|
}
|
|
|
|
sub selectfirst_hashref_query {
|
|
my ($form, $dbh, $query) = splice(@_, 0, 3);
|
|
|
|
my $sth = prepare_execute_query($form, $dbh, $query, @_);
|
|
my $ref = $sth->fetchrow_hashref();
|
|
$sth->finish();
|
|
|
|
return $ref;
|
|
}
|
|
|
|
sub selectfirst_array_query {
|
|
my ($form, $dbh, $query) = splice(@_, 0, 3);
|
|
|
|
my $sth = prepare_execute_query($form, $dbh, $query, @_);
|
|
my @ret = $sth->fetchrow_array();
|
|
$sth->finish();
|
|
|
|
return @ret;
|
|
}
|
|
|
|
1;
|
|
|
|
|
|
__END__
|
|
|
|
=head1 NAME
|
|
|
|
SL::DBUTils.pm: All about Databaseconections in Lx
|
|
|
|
=head1 SYNOPSIS
|
|
|
|
use DBUtils;
|
|
|
|
conv_i
|
|
conv_date
|
|
conv_dateq
|
|
quote_db_date($str)
|
|
|
|
do_query($form, $dbh, $query)
|
|
do_statement($form, $sth, $query)
|
|
|
|
dump_query($level, $msg, $query)
|
|
prepare_execute_query($form, $dbh, $query)
|
|
|
|
my $all_results_ref = selectall_hashref_query($form, $dbh, $query)
|
|
my $first_result_hash_ref = selectfirst_hashref_query($form, $dbh, $query);
|
|
|
|
my @first_result = selectfirst_array_query($form, $dbh, $query); # ==
|
|
my @first_result = selectrow_query($form, $dbh, $query);
|
|
|
|
|
|
=head1 DESCRIPTION
|
|
|
|
=head1 FUNCTIONS
|
|
|
|
=over 4
|
|
|
|
=item conv_i
|
|
|
|
=item conv_date
|
|
|
|
=item conv_dateq
|
|
|
|
=item quote_db_date($str)
|
|
|
|
=item do_query($form, $dbh, $query)
|
|
|
|
=item do_statement($form, $sth, $query)
|
|
|
|
=item dump_query($level, $msg, $query)
|
|
|
|
=item prepare_execute_query($form, $dbh, $query)
|
|
|
|
=item selectall_hashref_query($form, $dbh, $query)
|
|
|
|
=item selectfirst_hashref_query($form, $dbh, $query);
|
|
|
|
=item selectfirst_array_query($form, $dbh, $query); # ==
|
|
|
|
=item selectrow_query($form, $dbh, $query);
|
|
|
|
=back
|
|
|
|
=head1 EXAMPLE
|
|
|
|
=head1 SEE ALSO
|
|
|
|
=head1 MODULE AUTHORS
|
|
|
|
Moritz Bunkus E<lt>m.bunkus@linet-services.de<gt>
|
|
Sven Schoeling E<lt>s.schoeling@linet-services.de<gt>
|
|
|
|
=head1 DOCUMENTATION AUTHORS
|
|
|
|
Udo Spallek E<lt>udono@gmx.netE<gt>
|
|
|
|
=head1 COPYRIGHT AND LICENSE
|
|
|
|
Copyright 2007 by Lx-Office Community
|
|
|
|
This program is free software; you can redistribute it and/or modify
|
|
it under the terms of the GNU General Public License as published by
|
|
the Free Software Foundation; either version 2 of the License, or
|
|
(at your option) any later version.
|
|
|
|
This program is distributed in the hope that it will be useful,
|
|
but WITHOUT ANY WARRANTY; without even the implied warranty of
|
|
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
|
|
GNU General Public License for more details.
|
|
You should have received a copy of the GNU General Public License
|
|
along with this program; if not, write to the Free Software
|
|
Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
|
|
=cut
|