I have a database with several tables in it, and each table contains data on
lots of people. From one run of my program to the next, the tables could
change, so my code needs to be fairly general.
I want to pull the data for each person from each table, and create an XML
report - one report for each person.
The problem I'm having is that my code is very inefficient. Although I've read
that putting prepare inside a loop is slow, I have failed to make anything else
work at all (I tried putting the connections into an array, but either I did
something wrong or that is not supposed to work).
I would be very grateful if someone were able to tell me how to rewrite this in
order to make if more efficient.
use DBI;
use IO::File;
use XML::Writer;
my $dbh = DBI->connect( 'DBI:SQLite:myDB' )
or die "Couldn't connect to database: ". DBI->errstr;
my $sth = $dbh->prepare( 'SELECT id FROM demography' )
or die "Couldn't prepare statement: " . $dbh->errstr;
my $id;
my @tables = $dbh->tables();
# Get the IDs
$sth->execute();
while( $id = $sth->fetchrow_array() ){
# Open report for writing and add top-matter here:
# code not shown
for (0..$#tables){
$theTable = $tables[$_];
$qid = $dbh->quote( $id );
# Do stuff to make the next line produce the correct SQL:
# code not shown
$sql = "SELECT * FROM $theTable WHERE usubjid = $id";
# Next lines are inefficient. Please help!
$cnx = $dbh->prepare($sql);
$cnx->execute()
or die "Failed execution";
# Code omitted here
} # Close while
# Tidy up and close down here
Many thanks,
Harry
--------------------------------------------------------------------------
AstraZeneca UK Limited is a company incorporated in England and Wales with
registered number: 03674842 and a registered office at 15 Stanhope Gate, London
W1K 1LN.
Confidentiality Notice: This message is private and may contain confidential,
proprietary and legally privileged information. If you have received this
message in error, please notify us and remove it from your system and note that
you must not copy, distribute or take any action in reliance on it. Any
unauthorised use or disclosure of the contents of this message is not permitted
and may be unlawful.
Disclaimer: Email messages may be subject to delays, interception, non-delivery
and unauthorised alterations. Therefore, information expressed in this message
is not given or endorsed by AstraZeneca UK Limited unless otherwise notified by
an authorised representative independent of this message. No contractual
relationship is created by this message by any person unless specifically
indicated by agreement in writing other than email.
Monitoring: AstraZeneca UK Limited may monitor email traffic data and content
for the purposes of the prevention and detection of crime, ensuring the
security of our computer systems and checking Compliance with our Code of
Conduct and Policies.
--
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
http://learn.perl.org/
