DBI->trace() is your friend and so is $sth->errstr.
First up is $sth->errstr who you really should be using after all your prepare and execute statements like this:
$sth->execute($foo,$bar)
or die "Couldn't execute query:" . $sth->errstr;
DBI->trace() is more a development tool, it has multiple levels of tracing for very effecient debugging.
LEVEL | EFFECT
--------------------------------------------
0 Disable trace
1 Execution trace (values and errors)
2 L1 + method and params
3 L2 + driver level (very detailed)
4 An insane amount of information
The DBI->trace(2, 'tracefile') takes one or two parameters, the first is the trace level as illustrated above, the second is an optional file to dump the debugging log into.
I'm recalling this out of Programming the Perl DBI which is an excellent book, but it's on loan to a friend so I hope the table's correct. I was/am preparing a more detailed post given some of your other code (Oracle DBA here, so this stuff is fun for me) but I'm pretty busy so I thought I'd just share this until I get a few moments
Update: Added the $sth->errstr() stuff then noticed it had already been mentioned by andye
Update 2: Have a few more moments, I'd really like to go over your general query structure but that would take more time than I currently have so let's stick to some little things (BTW brovnick covered a really good point, personally I see $count as very perplexing given you can just iterate over the cursor):
# The main Select
$sth_main = $dbh->prepare(<<'__EOSQL__');
SELECT DISTINCT t.account_no,
t.telephony_id,
n.sim,
n.mobile_number,
s.user_name,
s.contract_status,
s.dealer,
to_char(t.eff_start_date,'DD.MM.YYYY'),
to_char(s.signed_date,'DD.MM.YYYY'),
to_char(s.subscr_change_upd,'DD.MM.YYYY'),
lpad(s.s_id,7,'0'),
to_char(t.eff_start_date,'HH24:MI:SS')
FROM new_sim n, subscription s, tele_period t
WHERE (t.account_no = n.account_no) AND
(t.telephony_id = n.mobile_number) AND
(t.account_no = s.account_no) AND
(t.telephony_id = s.mobile_number)
ORDER BY t.account_no, t.telephony_id
__EOSQL__
Notice the single quotes around __EOSQL__? By default here docs are interpolated, you don't want perl to spend the time checking and you certainly don't want it interpolating in this case. So explicitly state it as shown. Also, if you want, you can use table aliases. I handle a lot of queries a day and it's so much faster to type and easier to read when they're used (check the FROM clause to see how it's done).
# using an array ref instead
$rec = $sth_main->fetchrow_arrayref('NAME_lc')
$sth_imei->execute( $rec->[1], $rec->[0] )
or die $sth_imei->errstr;
# using a hash ref
$rec = $sth_main->fetchrow_hashref('NAME_lc');
$sth_imei->execute( $rec->{telephony_id}, $rec->{account_no})
or die $sth_imei->errstr;
Using a fetched reference instead of an array is much faster and uses less memory, b/c each time your cursor moves (ie. iterate to the next record) DBI uses the same memory space instead of creating a new array. This would necesitate some major changes to your structure, but if you want more speed you'll probably be doing them anyways.
The hash reference is a bit slower on lookup simply b/c it is a hash, however, the benefits of it should be obvious. Now $rec actually means something instead of being just a number.
P.S. Consider joining, it's painless: It's also much nicer talking to a name than Anonymous Monk |