Author: spadkins
Date: Fri Jan 16 07:30:15 2009
New Revision: 12449
Modified:
p5ee/trunk/App-Repository/lib/App/Repository/DBI.pm
Log:
allow a blank for insert into integer column (interpreted as null)
Modified: p5ee/trunk/App-Repository/lib/App/Repository/DBI.pm
==============================================================================
--- p5ee/trunk/App-Repository/lib/App/Repository/DBI.pm (original)
+++ p5ee/trunk/App-Repository/lib/App/Repository/DBI.pm Fri Jan 16 07:30:15 2009
@@ -2037,13 +2037,20 @@
$self->explain_sql($sql);
}
if (defined $dbh) {
- my ($column, %serializer, $serializer_name, $serializer, $sqltype);
+ my ($column, %serializer, $serializer_name, $serializer, $type,
$sqltype);
my $column_defs = $self->{table}{$table}{column};
+ if ($debug_sql) {
+ $timer = $self->_get_timer();
+ print $App::DEBUG_FILE "DEBUG_SQL: insert()\n";
+ print $App::DEBUG_FILE "DEBUG_SQL: bind vars [", join("|",map {
defined $_ ? $_ : "undef" } @$row), "]\n";
+ print $App::DEBUG_FILE $sql;
+ }
eval {
my $insert_sth = $dbh->prepare($sql);
for (my $i = 0; $i <= $#$cols; $i++) {
$column = $cols->[$i];
$sqltype = $column_defs->{$column}{sqltype};
+ $type = $column_defs->{$column}{type};
$serializer_name = $column_defs->{$column}{serializer};
if ($serializer_name) {
$serializer = $serializer{$serializer_name};
@@ -2055,7 +2062,12 @@
$row->[$i] = $serializer->serialize($row->[$i]);
}
}
- $insert_sth->bind_param($i+1, $row->[$i], $sqltype ? {TYPE =>
$sqltype} : () );
+ if (defined $row->[$i] && $row->[$i] eq "" && ($type eq
"integer" || $type eq "float")) {
+ $insert_sth->bind_param($i+1, undef, $sqltype ? {TYPE =>
$sqltype} : () );
+ }
+ else {
+ $insert_sth->bind_param($i+1, $row->[$i], $sqltype ? {TYPE
=> $sqltype} : () );
+ }
#print STDERR "insert_sth->bind_param(", $i+1, ", $row->[$i], $sqltype)
[$column]\n";
}
#print STDERR "2. lii=[$last_inserted_id]
liirfi=[$is_last_inserted_id_returned_from_insert]
liic=[$last_inserted_id_column]\n";
@@ -2065,12 +2077,6 @@
$insert_sth->bind_param_inout($#$cols+2, \$last_inserted_id,
20, $sqltype ? {TYPE => $sqltype} : () );
#print STDERR "insert_sth->bind_param_inout(", $#$cols+2, ",
\$last_inserted_id, 20, $sqltype) [$last_inserted_id_column] AFTER\n";
}
- if ($debug_sql) {
- $timer = $self->_get_timer();
- print $App::DEBUG_FILE "DEBUG_SQL: insert()\n";
- print $App::DEBUG_FILE "DEBUG_SQL: bind vars [", join("|",map
{ defined $_ ? $_ : "undef" } @$row), "]\n";
- print $App::DEBUG_FILE $sql;
- }
#print STDERR "BEFORE execute: last_inserted_id=[$last_inserted_id]\n";
$retval = $insert_sth->execute;
#print STDERR "AFTER execute: last_inserted_id=[$last_inserted_id]\n";
@@ -2144,11 +2150,16 @@
if (ref($rows) eq "ARRAY") {
$sql = $self->_mk_insert_row_sql($table, $cols);
foreach $row (@$rows) {
+ if ($debug_sql) {
+ print $App::DEBUG_FILE "DEBUG_SQL: _insert_rows()\n";
+ print $App::DEBUG_FILE "DEBUG_SQL: bind vars [", join("|",map
{ defined $_ ? $_ : "undef" } @$row), "]\n";
+ print $App::DEBUG_FILE $sql;
+ }
if ($explain_sql) {
$self->explain_sql($sql);
}
if (defined $dbh) {
- my ($column, %serializer, $serializer_name, $serializer,
$sqltype);
+ my ($column, %serializer, $serializer_name, $serializer,
$type, $sqltype);
my $column_defs = $self->{table}{$table}{column};
eval {
### TODO: make this work with regex for retry
@@ -2156,6 +2167,7 @@
for (my $i = 0; $i <= $#$cols; $i++) {
$column = $cols->[$i];
$sqltype = $column_defs->{$column}{sqltype};
+ $type = $column_defs->{$column}{type};
$serializer_name = $column_defs->{$column}{serializer};
if ($serializer_name) {
$serializer = $serializer{$serializer_name};
@@ -2168,11 +2180,9 @@
}
}
$insert_sth->bind_param($i+1, undef, $sqltype ? {TYPE
=> $sqltype} : () );
- }
- if ($debug_sql) {
- print $App::DEBUG_FILE "DEBUG_SQL: _insert_rows()\n";
- print $App::DEBUG_FILE "DEBUG_SQL: bind vars [",
join("|",map { defined $_ ? $_ : "undef" } @$row), "]\n";
- print $App::DEBUG_FILE $sql;
+ if (defined $row->[$i] && $row->[$i] eq "" && ($type
eq "integer" || $type eq "float")) {
+ $row->[$i] = undef;
+ }
}
#$retval = $dbh->do($sql, undef, @$row);
$retval = $insert_sth->execute(@$row);
