It should be possible to reformulate the while-loop like this:
while (1) {
my $row_ref = $walk_sth->fetchrow_arrayref();
if (not defined $row_ref) {
if ($walk_sth->err()) {
warn "found invalid record\n";
# implicit 'next;'
} else { # no more records
last;
}
}
$isrt_sth->execute( @$row_ref )
}
Robert
----
Brian H. Oak schrieb:
I'm working with an MS SQL Server database residing on a disk that
suffered a crash. There are a couple of very important tables in
there, and I'm trying to recover as much data as possible. One of the
tables in question holds ~2M records and, while I can still get
success with a 'select count(*)' query, any type of 'select *' query
chokes about 700K records in.
Here's my thinking on this: unlike all-or-nothing tools that error out
when they hit a corrupted record, I believe I can use DBI's
cursor-based, iterative methods (e.g. 'fetchrow_arrayref') to at least
distill all remaining good records from a corrupt table, skipping over
the corrupted "problem" records.
I have built a tool (using DBI, of course) that replicates the table's
structure to a second, intact database. It then uses the metadata
that it has already gleaned to formulate a correct 'insert' statement
(i.e. the right number of placeholders).
This works like a charm on good source tables, but I haven't even
tried it on a corrupted table yet because I can see that it doesn't
have a chance of working. I'm trying to get my head around how to
compose my code so that I can get my 'fetchrow_arrayref' into an
'eval{...}' block so that I can catch individual record errors and
record them (I need to know how many bad records there are), but still
keep working my way through the table to find subsequent good records.
Here's a snippet of what I have so far:
========================================
croak( "No columns found in specified source table $stable!" ) unless
$col_count >= 1;
my $placers = $col_count > 1 ? "?, " x ( $col_count - 1 ) . "?" :
"?";
my $walk_sth = $sdbh->prepare( "select * from $stable" );
$walk_sth->execute();
my $isrt_sth = $tdbh->prepare( "insert into $ttable values (
$placers )" );
while ( my $row_ref = $walk_sth->fetchrow_arrayref()) {
$isrt_sth->execute( @$row_ref );
}
========================================
Any sugggestions you might make about how I can deploy 'eval{...}'
blocks into this to make it able to slog through the entire table --
instead of stopping at the first corrupt record -- would certainly be
welcome.
Thank you,
-Brian
_________________________
Brian H. Oak CISSP CISA
Acorn Networks & Security
<http://acornnetsec.com/>
