cvsuser 02/08/30 10:52:05
Modified: P5EEx/Blue/P5EEx/Blue/Repository DBI.pm
Log:
experimented with bind values on select. modified select_row() to clean up some bug
or other.
Revision Changes Path
1.19 +118 -15 p5ee/P5EEx/Blue/P5EEx/Blue/Repository/DBI.pm
Index: DBI.pm
===================================================================
RCS file: /cvs/public/p5ee/P5EEx/Blue/P5EEx/Blue/Repository/DBI.pm,v
retrieving revision 1.18
retrieving revision 1.19
diff -u -w -r1.18 -r1.19
--- DBI.pm 16 Aug 2002 16:09:14 -0000 1.18
+++ DBI.pm 30 Aug 2002 17:52:05 -0000 1.19
@@ -1,13 +1,13 @@
######################################################################
-## File: $Id: DBI.pm,v 1.18 2002/08/16 16:09:14 spadkins Exp $
+## File: $Id: DBI.pm,v 1.19 2002/08/30 17:52:05 spadkins Exp $
######################################################################
use P5EEx::Blue::P5EE;
use P5EEx::Blue::Repository;
package P5EEx::Blue::Repository::DBI;
-$VERSION = do { my @r=(q$Revision: 1.18 $=~/\d+/g); sprintf "%d."."%02d"x$#r,@r};
+$VERSION = do { my @r=(q$Revision: 1.19 $=~/\d+/g); sprintf "%d."."%02d"x$#r,@r};
@ISA = ( "P5EEx::Blue::Repository" );
@@ -1182,7 +1182,13 @@
$self->{sql} = $sql;
$dbh = $self->{dbh};
return undef if (!$dbh);
- return $dbh->selectrow_arrayref($sql);
+ my $row = $dbh->selectrow_arrayref($sql);
+ if ($P5EEx::Blue::DEBUG && $self->{context}->dbg(4)) {
+ $self->{context}->dbgprint("select_row(): $sql");
+ $self->{context}->dbgprint(" result=[", join("|",@$row), "]") if ($row);
+ $self->{context}->dbgprint(" error=[", $dbh->errstr, "]") if
($dbh->errstr);
+ }
+ return $row;
}
# NOTE: everything after the first line is optional
@@ -1193,18 +1199,106 @@
# TODO: get the $startrow/$endrow working when one/both/neither work in the SQL
portion
# TODO: rethink $startrow/$endrow vs. $numrows/$skiprows
sub select_rows {
- my $self = shift;
+ my ($self, $table, $cols, $params, $paramvalues, $ordercols, $startrow, $endrow,
+ $sortdircol, $keycolidx, $writeable, $columntype, $summarykeys) = @_;
+ my ($sql);
+
$self->{error} = "";
- my ($dbh, $table, $startrow, $endrow, $sql);
- ($table, $startrow, $endrow) = @_[(0,5,6)];
+
if ($self->{table}{$table}{rawaccess}) {
- $sql = $self->mk_select_rows_sql(@_);
+ $sql = $self->mk_select_rows_sql($table, $cols, $params, $paramvalues,
$ordercols,
+ $startrow, $endrow, $sortdircol, $keycolidx, $writeable, $columntype,
$summarykeys);
}
else {
- $sql = $self->mk_select_joined_rows_sql(@_);
+ $sql = $self->mk_select_joined_rows_sql($table, $cols, $params,
$paramvalues, $ordercols,
+ $startrow, $endrow, $sortdircol, $keycolidx, $writeable, $columntype,
$summarykeys);
+ }
+ $self->{sql} = $sql;
+ my $rows = $self->selectrange_arrayref($sql, $startrow, $endrow);
+ if ($P5EEx::Blue::DEBUG && $self->{context}->dbg(4)) {
+ $self->{context}->dbgprint("select_rows(): $sql");
+ if ($rows) {
+ foreach my $row (@$rows) {
+ $self->{context}->dbgprint(" result=[", join("|",@$row), "]") if
($row);
+ }
+ }
+ $self->{context}->dbgprint(" error=[", $self->error, "]") if
($self->error);
+ }
+ return ($rows);
+}
+
+# $row = $rep->select_row ($table, \@cols, \@params, \%paramvalues);
+
+# this is a new version that uses bind variables instead of relying on my quoting
rules
+# unfortunately, it doesn't work yet
+
+sub select_row2 {
+ my ($self, $table, $cols, $params, $paramvalues) = @_;
+ my ($dbh, $sql, $param, @params, %paramvalues, @paramvalues);
+
+ $self->{error} = "";
+
+ if (defined $params) {
+ @params = @$params;
+ }
+ else {
+ @params = (keys %$paramvalues);
+ }
+ foreach $param (@params) {
+ push(@paramvalues, $paramvalues->{$param});
+ }
+
+ if ($self->{table}{$table}{rawaccess}) {
+ $sql = $self->mk_select_rows_sql($table, $cols, \@params, \%paramvalues,
undef, 1, 1);
+ }
+ else {
+ $sql = $self->mk_select_rows_sql($table, $cols, \@params, \%paramvalues,
undef, 1, 1);
}
$self->{sql} = $sql;
- return $self->selectrange_arrayref($sql, $startrow, $endrow);
+
+ my $rows = $self->selectrange_arrayref($sql, 1, 1, undef, @paramvalues);
+ return [] if (!$rows || $#$rows == -1);
+ return ($rows->[0]);
+}
+
+# NOTE: everything after the first line is optional
+# @rows = $rep->select_rows($table, \@cols,
+# \@params, \%paramvalues, \@ordercols,
+# $startrow, $endrow,
+# \@sortdircol, \@keycolidx, \@writeable, \@columntype,
\@summarykeys);
+# TODO: get the $startrow/$endrow working when one/both/neither work in the SQL
portion
+# TODO: rethink $startrow/$endrow vs. $numrows/$skiprows
+
+# this is a new version that uses bind variables instead of relying on my quoting
rules
+# unfortunately, it doesn't work yet
+
+sub select_rows2 {
+ my ($self, $table, $cols, $params, $paramvalues, $ordercols, $startrow, $endrow,
+ $sortdircol, $keycolidx, $writeable, $columntype, $summarykeys) = @_;
+ my ($sql, $param, @params, %paramvalues, @paramvalues);
+
+ $self->{error} = "";
+
+ if (defined $params) {
+ @params = @$params;
+ }
+ else {
+ @params = (keys %$paramvalues);
+ }
+ foreach $param (@params) {
+ push(@paramvalues, $paramvalues->{$param});
+ }
+
+ if ($self->{table}{$table}{rawaccess}) {
+ $sql = $self->mk_select_rows_sql($table, $cols, \@params, \%paramvalues,
$ordercols,
+ $startrow, $endrow, $sortdircol, $keycolidx, $writeable, $columntype,
$summarykeys);
+ }
+ else {
+ $sql = $self->mk_select_rows_sql($table, $cols, \@params, \%paramvalues,
$ordercols,
+ $startrow, $endrow, $sortdircol, $keycolidx, $writeable, $columntype,
$summarykeys);
+ }
+ $self->{sql} = $sql;
+ return $self->selectrange_arrayref($sql, $startrow, $endrow, undef,
@paramvalues);
}
# $ok = $rep->insert_row ($table, \@cols, \@row);
@@ -1416,6 +1510,11 @@
if ((!defined $row->[$colnum] && defined $oldrow->[$colnum]) ||
( defined $row->[$colnum] && !defined $oldrow->[$colnum])) {
$different = 1;
+ # print "*** different colnum=$colnum column=$column row=[",
+ # defined $row->[$colnum] ? $row->[$colnum] : "undef",
+ # "] oldrow=[",
+ # defined $oldrow->[$colnum] ? $oldrow->[$colnum] : "undef",
+ # "]\n";
}
if (defined $row->[$colnum] && defined $oldrow->[$colnum]) {
@@ -1423,11 +1522,15 @@
($oldrow->[$colnum] =~ /^-?[0-9]+\.?[0-9]*$/ ||
$oldrow->[$colnum] =~ /^-?\.[0-9]+$/)) {
if ($row->[$colnum] != $oldrow->[$colnum]) {
$different = 1;
+ #print "*** different colnum=$colnum column=$column ",
+ # "row=[$row->[$colnum]] !=
oldrow=[$oldrow->[$colnum]]\n";
}
}
else {
if ($row->[$colnum] ne $oldrow->[$colnum]) {
$different = 1;
+ #print "*** different colnum=$colnum column=$column ",
+ # "row=[$row->[$colnum]] ne
oldrow=[$oldrow->[$colnum]]\n";
}
}
}
@@ -1448,8 +1551,8 @@
}
if ($P5EEx::Blue::DEBUG && $context->dbg(2)) {
- $context->dbgprint("store_row() old=[", join(",",@$oldrow), "]");
- $context->dbgprint(" new=[", join(",",@$row), "]");
+ $context->dbgprint("store_row() old=[", join("|",@$oldrow), "]");
+ $context->dbgprint(" new=[", join("|",@$row), "]");
$error = $self->error();
$sql = $error ? $self->{sql} : "";
$context->dbgprint(" diff=$different ok=$ok err=$error
$sql");
@@ -1466,7 +1569,7 @@
$ok = $self->insert_row($table,\@insert_cols,$row);
if ($P5EEx::Blue::DEBUG && $context->dbg(1)) {
- $context->dbgprint("store_row() new=[", join(",",@$row), "]");
+ $context->dbgprint("store_row() new=[", join("|",@$row), "]");
$error = $self->error();
$sql = $error ? $self->{sql} : "";
$context->dbgprint(" ok=$ok err=$error $sql");
@@ -1526,9 +1629,9 @@
$context = $self->{context};
if ($P5EEx::Blue::DEBUG && $context->dbg(1)) {
$context->dbgprint("store_rows($table, col=$cols, rows=$rows (", ($#$rows +
1), "), kidx=$keycolidx, up=$update_first, crit=$row_crit)");
- $context->dbgprint(" cols=[", join(",",@$cols), "]") if ($cols);
- $context->dbgprint(" keycolidx=[", join(",",@$keycolidx), "]") if
($keycolidx);
- $context->dbgprint(" rowcrit=[", join(",",%$row_crit),"]") if
($row_crit);
+ $context->dbgprint(" cols=[", join("|",@$cols), "]") if ($cols);
+ $context->dbgprint(" keycolidx=[", join("|",@$keycolidx), "]") if
($keycolidx);
+ $context->dbgprint(" rowcrit=[", join("|",%$row_crit),"]") if
($row_crit);
}
$self->store_begin($table);
