| Subject: | ad_import into existing table doesn't preserve old data and fails if repeated |
I'm trying to use XML with DBD::AnyData. What I've found is that
ad_import into a single table from multiple XML sources doesn't merge
the new rows, it just replaces all old data in the table (contrary to
the examples in the documentation). In addition, ad_import fails if you
try to import into any existing table more than once in the same DBI
session.
When you try to ad_import data into an existing table, the ad_import
function creates a temporary 'temp__' table to preserve the existing
table's records. Unfortunately, that table is never cleaned up, so a
subsequent ad_import into an existing table fails with:
Execution ERROR: Table 'temp__' already exists. called from (eval 144) at 1.
The attached test script attempts to import 3 XML records into the same
'test' table, printing the rowcount after each ad_import. Expected output:
rows: 1
rows: 2
rows: 3
Observed output:
rows: 1
rows: 1
Execution ERROR: Table 'temp__' already exists. called from
anydata_ad_import_test.pl at 14.
I'm using DBD::AnyData v0.110 in Perl 5.12.3 built for
darwin-multi-2level (Macports in OSX Snow Leopard). The package was
installed through CPAN along with all of its dependencies.
| Subject: | anydata_ad_import_test.pl |
use DBI;
$dbh = DBI->connect('dbi:AnyData(RaiseError=>1):');
# Creates 'test' table
$dbh->func('test', 'XML', ['<tests><test>example</test></tests>'], {}, 'ad_import');
print "rows: " . $dbh->selectcol_arrayref('select count(*) from test')->[0] . "\n";
# Table 'test' already exists, so 'temp__' table created in ad_import
# Rows do not accumulate, Table 'test' still has only 1 record after this import, now 'example2'
$dbh->func('test', 'XML', ['<tests><test>example2</test></tests>'], {}, 'ad_import');
print "rows: " . $dbh->selectcol_arrayref('select count(*) from test')->[0] . "\n";
# Boom! "Table 'temp__' already exists."
$dbh->func('test', 'XML', ['<tests><test>example3</test></tests>'], {}, 'ad_import');
print "rows: " . $dbh->selectcol_arrayref('select count(*) from test')->[0] . "\n";