Beefy Boxes and Bandwidth Generously Provided by pair Networks
more useful options

Re: Using DBI to extract from 2 databases

by space_monk (Chaplain)
on Apr 24, 2013 at 13:35 UTC ( #1030397=note: print w/replies, xml ) Need Help??

in reply to Using DBI to extract from 2 databases

As others have suggested, look at binding your parameters - whilst you cannot bind table names, some of your code would look better if you bound the other parameters - see below.

What happens to $newstat if the $NCsevno value is not 1,2,3? Your code does not protect itself against errors sufficiently

You have funny left/right single quotes around $tableName in your original example, check you're using the proper boring single quote marks (I don't think you need quotes anyway - remove them)

You only appear to use the last $table_name to call the update method with- why?

A partially improved version of your code is below:

#!c:\perl\bin\perl.exe use lib "C:\\Perl"; use DBI; use DBD::mysql; # # # # # # # # # # # # #Variable declarations# # # # # # # # # # # # # my ($user, $pass) = ("root", "pass"); #credentials for SQL database my $table_data = q/DBI:mysql:database=information_schema;host=localhos +t;port=13308/; my @tblname; #------------------------------------------------------------- # main script # TODO: break down into smaller methods/routines. #------------------------------------------------------------ my @tblname = get_tables($table_data, $user, $pass); #Connect to the data source and get a handle for that connection my $data_source = q/DBI:mysql:database=probedb;host=localhost;port=133 +08/; #Connect to the data source and get a handle for that connection my $dbh = DBI->connect($data_source, $user, $pass) || die "Can't conne +ct to $data_source: $DBI::errstr"; foreach my $table_name (@tblname) { # TODO: the contents of this loop should probably be a method in i +tself... my $NCquery= <<EOF; SELECT id, text FROM $table_name WHERE alarmcode LIKE '%Trap%Calltouch%' EOF my $sth=$dbh->prepare($NCquery); my $updateNC= <<EOF; UPDATE $table_name SET severity = ? WHERE id = ? AND modtime > ? EOF my $udh=$dbh->prepare($updateNC); # TODO: $sth->fetchall_hashref/arrayref may be better here... $sth->execute(); while ( @row = $sth->fetchrow_array ) { my ($NCid, $NCsevno) = @row; # TODO: add default $newstat value if no match?? if ($NCsevno =~ /alarmState=(\d)/i){ $newstat = 1 if $1 == 2; $newstat = 5 if $1 == 1; $newstat = 3 if $1 == 3; #ack?# } # TODO: where is $ntime set to a value?? - please fix!! $udh->execute($newstat, $NCid, $ntime); } print "$table_name\n"; } my $query=<<EOF; SELECT name FROM managedobject WHERE displayname like '%TSEPAL%-CT%' AND type LIKE '%Windows%' EOF my $sth=$dbh->prepare($query); $sth->execute(); while ( @row = $sth->fetchrow_array ) { #update all matching alarms to newest severity update($row[0], $table_name); } $sth->finish; $dbh->disconnect; #------------------------------------------------------------- # get_tables # get list of tables from database #------------------------------------------------------------ sub get_tables { my @tblname; my $dbtbl = DBI->connect($table_data, $user, $pass) || die "Can't +connect to $data_source: $DBI::errstr"; my $tblname = <<EOF; SELECT table_name FROM `TABLES` where table_name like 'event\_%' EOF my $sthtbl=$dbtbl->prepare($tblname); $sthtbl->execute(); while ( @row = $sthtbl->fetchrow_array ) { my $table_name = $row[0]; push(@tblname, $table_name); } $sthtbl->finish; $dbtbl->disconnect; return @tblname; } #---------------------------------------------------- # update # TODO: put description here #---------------------------------------------------- sub update { my ($id, $table_name) = @_; my $alQuery=<<<EOF SELECT source, severity, ttime, text FROM $table_name WHERE source = ? AND alarmcode LIKE '%Trap%Calltouch%' EOF my $updateNC=<<EOF; UPDATE $table_name SET severity = ? WHERE source = ? AND ttime < ? and text like ? and alarmcode LIKE '%Trap%Calltouch%' EOF my $query=<<EOF; SELECT severity FROM alert WHERE source = ? ORDER BY severity ASC LIMIT 1 EOF my $updateMO=<<EOF UPDATE managedobject SET status = ? WHERE name = ? EOF my $alqH=$dbh->prepare($alQuery); my $udncH=$dbh->prepare($updateNC); my $qH=$dbh->prepare($query); my $moH=$dbh->prepare($updateMO); $alqh->execute($id); while ( @row = $alqh->fetchrow_array ) { my ($NCsource, $NCsev, $NCmodtime, $NCmmessage) = @row; # TODO: should $NCmsg be $NCmmessage as $NCmsg is not declared +....???? if ($NCmsg =~ /alarmDescription=([^\s+])/i){ $NCmmessage = $1; } $udncH->execute($NCsev, $NCsource, $NCmodtime, "%$NCmmessage%" +); } $qH->execute($id); while ( @row = $sth->fetchrow_array ) { $sev = $row[0]; } $moH->execute($sev, $id); }
If any of my proposed solutions have minor errors, it's because I don't waste my genius on trivial matters. :-P

Replies are listed 'Best First'.
Re^2: Using DBI to extract from 2 databases
by whittick (Acolyte) on Apr 25, 2013 at 08:29 UTC

    Firstly thank you for all the detailed responses. I have bitten the bullet and rewritten the script in a much neater, strict complying form. I was still getting the initial error message of fetch without an execute, however it looks as if the problem was down to the single quotes wrapping the database name in the query so thank you space_monk for suggesting I remove them - the script is now running flawlessly!

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://1030397]
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others pondering the Monastery: (3)
As of 2017-02-22 01:00 GMT
Find Nodes?
    Voting Booth?
    Before electricity was invented, what was the Electric Eel called?

    Results (322 votes). Check out past polls.