Beefy Boxes and Bandwidth Generously Provided by pair Networks
Think about Loose Coupling
 
PerlMonks  

Database problem

by jeroen_h (Initiate)
on Aug 06, 2005 at 14:42 UTC ( #481490=perlquestion: print w/ replies, xml ) Need Help??
jeroen_h has asked for the wisdom of the Perl Monks concerning the following question:

Dear Monks, I've written this little script that's behaving strangely (to me, but I'm not an expierenced programmer ;-) ) In a sub routine the script gets data from a (MySQL) database. When I call the schript from one place everything works fine and when I call it from another place (in exactly the same way) there are no results comming back from the database. When I do a print of the database Query and past that into phpmyadmin for testing, everything works fine, also with the query that does not return the data... Can anyone point me at what I'm doing wrong here? I don't know where the problem comes from so I'll post the whole script I have so far... (Sorry for the large part of code!)
#!/usr/bin/perl use Time::HiRes qw( usleep ); use DBI; open (CONFIG, "<homenet.actiontrigger.conf"); while (readline CONFIG) { if ( $_ !~ /^\s*#\s*/ ) { if ($_ =~ /\S+/ ) { if ( $_ =~ /^\s*(\S+)\s*=\s*(\S+)\s*#*(.*)$/ ) {}; $conf_key = $1; $conf_value = $2; $conf{$conf_key} = $conf_value; } } } close (CONFIG); $script_name = "action trigger"; $db_homenet = DBI->connect("DBI:mysql:$conf{DatabaseName}:$conf{Databa +seServer}",$conf{DatabaseUser},$conf{DatabaseSecret}); $X = 0; while( $X != 1 ) { $Query_lookque = "SELECT COUNT(*) FROM Input_Que"; my $lookque = $db_homenet->prepare( $Query_lookque ); $lookque->execute(); my ( $rows ); $lookque->bind_columns( undef, \$rows ); while( $lookque->fetch() ){} if ( $rows >= 1 ) { $data = ""; $Query_check_inputque = "SELECT * FROM `Input_Que` WHERE 1 +LIMIT 1"; my $check_inputque = $db_homenet->prepare( $Query_check_inp +utque ); $check_inputque->execute(); my( $nr, $timestamp, $busname, $data ); $check_inputque->bind_columns( undef, \$nr, \$timestamp, \$ +busname, \$data ); while( $check_inputque->fetch() ){ } print "$nr - $timestamp - $busname - $data"; if ( $data =~ /.*(\w{2})r(\w{2})=(\w{2})/ ) + # memory read data { $data_type = "alive_message"; $dev_id = $1; $memory_location = $2; $data = $3; print "MEMORY-DATA-READ van $dev_id op bus $busname +: Mem_loc = $memory_location, data = $data\n"; } elsif ( $data =~ /.*(\w{2})M(\w{2})=(\w{2})/ ) + # Meter reading { $data_type = "meter_reading"; $dev_id = $1; $dev_line = $2; $data = $3; print "METER-READING van $dev_id op bus $busname: D +evice_line = $dev_line, data = $data\n"; } elsif ( $data =~ /.*(\w{2})(\w{1})(\w{2})=(\w{2})/ ) + # alive message { $data_type = "alive_message"; $dev_id = $1; $short_type = $2; $version_high = $3; $version_low = $4; print "ALIVE-MESSAGE van $dev_id op bus $busname: T +ype = $short_type\n"; $query_sendcommand = "UPDATE `state-table` SET `la +stchange` = '$timestamp' WHERE `device` =('$dev_id') && `busname` =(' +$busname') LIMIT 1 ;"; $db_homenet->do( $query_sendcommand ); } elsif ( $data =~ /.*(\w{2})(\w{2})>(\w+)>/ ) + # hard command { $data_type = "hard_command"; $sender_id = $1; $receiver_id = $2; $command = $3; print "HARD-COMMAND van $sender_id naar $receiver_ +id op bus $busname: Command = $command\n"; received_command($sender_id, $busname, $receiver_i +d, $busname, $command); } elsif ( $data =~ /.*(\w+)>(\w+)>(.+)>(\w+)>/ ) + # soft command { $data_type = "soft_command"; $receiver_id = $3; $command = $4; $sender_id = "S3"; print "SOFT-COMMAND naar $receiver_id op bus $busn +ame: Command = $command\n"; received_command($sender_id, $busname, $receiver_i +d, $busname, $command); } elsif ( $data =~ /.*(\w+)\?(.+)\?/ ) + # memory read request { $data_type = "memory_read_request"; $receiver_id = $1; $memory_location = $2; print "MEMORY-READ-REQUEST naar $receiver_id op bu +s $busname: Memory Location = $memory_location\n"; } elsif ( $data =~ /.*(\w+)=(.{2})(.{2})=/ ) + # memory write request { $data_type = "memory_write_request"; $receiver_id = $1; $memory_location = $2; $write_data = $3; print "MEMORY-WRITE naar $receiver_id op bus $busn +ame: Memory Location = $memory_location, data = $write_data \n"; } else { print "data kan niet worden ontcijfert! \n"; } $Query_delete_from_que = "DELETE FROM `Input_Que` WHERE `nr +` = $nr LIMIT 1"; $db_homenet->do( $Query_delete_from_que ); print " ******************************************************* +************************ \n"; } usleep 100000 } sub received_command { print "(1)\n"; ($command_source, $sbus, $command_destination, $dbus, $data) = @_; print "@_\n"; # First: We get the device type and software version! print "(2)\n"; $cod = "$command_destination"; # $Query_gettype = "SELECT `type` , `softv_high` , `softv_low` FROM + `master_devices` WHERE naam = ( '$command_destination' ) && bus = ( +'$dbus' ) LIMIT 1"; $Query_gettype = "SELECT `type` , `softv_high` , `softv_low` FROM +`master_devices` WHERE naam = ( '$cod' ) LIMIT 1"; print "$Query_gettype\n"; my $gettype = $db_homenet->prepare( $Query_gettype ); print "(3)\n"; $gettype->execute(); print "(4)\n"; my( $type, $swh, $swl ); print "(5)\n"; $gettype->bind_columns( undef, \$type, \$swh, \$swl ); print "(6)\n"; while( $gettype->fetch() ){} print "$type\n"; print "$swh\n"; print "$swl\n"; print "(7)\n"; $devtype = "$type.$swh.$swl"; print "$type\n"; print "$swh\n"; print "$swl\n"; print "Device Type = $devtype\n"; # Then we look up the command in the command_matrix! $Query_getline = "SELECT `line`, `activate`, `deactivate`, `switch +`, `timerswitch`, `value` FROM `command_matrix` WHERE device_type = ( +'$devtype') && ( activate = ('$data') || deactivate = ('$data') || sw +itch = ('$data') || timerswitch = ('$data') || value = ('$data') ) LI +MIT 1"; my $getline = $db_homenet->prepare( $Query_getline ); # print "$Query_getline\n"; $getline->execute(); my( $line, $activate, $deactivate, $switch, $timerswitch, $value ) +; $getline->bind_columns( undef, \$line, \$activate, \$deactivate, \ +$switch, \$timerswitch, \$value ); while( $getline->fetch() ){} # print "$line, $on, $off, $switch, $timerswitch, $value\n" ; # print "$command_destination\n"; $sdev = "$command_destination.$line"; print "slave-device = $sdev\n"; # Next we see what command it is! # print "-$data-\n"; # print "-$activate-\n"; # print "-$deactivate-\n"; if ( $activate =~ $data ) { print "activate\n"; } elsif ( $deactivate =~ $data ) { print "deactivate\n"; } elsif ( $switch =~ $data ) { print "switch\n"; } elsif ( $timerswitch =~ $data ) { print "timerswitch\n"; } elsif ( $value =~ $data ) { print "value\n"; } }
The script works for the Hard Command but not for the Soft Command. Thanks a lot for any help you can give me on this!

Comment on Database problem
Download Code
Re: Database problem
by trammell (Priest) on Aug 06, 2005 at 15:30 UTC
    Sorry, I don't have the inclination to delve deeply into the code you post. But in the first handful of lines I notice:
    • you don't use strict;
    • you don't use warnings;
    • you don't check the return status of your open(), e.g.
      open(CONFIG, "<homenet.actiontrigger.conf") or die "Can't open config file: $!";
    I'd put those three things at the top of your "to do" list.
      OK, I'll try this first! I get a lot of errors on my variables now, so I'll look at these to correct them...
Re: Database problem
by dws (Chancellor) on Aug 06, 2005 at 15:51 UTC

    I've written this little script that's behaving strangely (to me, but I'm not an expierenced programmer ;-) )

    Puzzling this out will help you get more experienced. A useful technique to add to your bag of tricks is the practice of taking a problem like this and iteratively removing everything that doesn't have anything to do with the problem you're seeing. That is, boil it down to a small test case.

    Quite often, during this process you'll have a valuable "aha!", and your experience level will notch up. But if you don't get the ahah, at least you have a small test case that exhibits the problem.

    Posting small test cases will get you better answer here, for two reasons

    • It shows that you've worked on the problem, which removes you from the "lazy bastard" category.
    • It gives people something digestable to work with. What you've plopped down is too large to ingest.

    So give it a try. Make a copy of the script, and start stripping out everything that isn't part of the problem. Then post that.

      Ofcourse I would not dare to ask for all of your help without first trying really hard to solve my problems myself... But ofcourse that does not always show when asking a question. Using the suggestions of all of you, I've come a bit closer to what the problem is (I think) I've striped the script of all I think I could do without, and after changing the variables that are send to the subroutine to fixed values, everything worked from both places. So next I tried to find the problem by changing them back to variables again and I found that the $receiver_id I get out of the regular expression in the soft command section seems to cause the problem. When I do my $receiver_id = $3; it gives an error but when I say my $receiver_id = "U1"; it all works... I've also tried to change the regular expression from using (.+) to (\w+) and (\w{2}) because that's what is used in the hard-command part, but then it breaks my regular expression... Does this help you people to point me at what I'm doing wrong here? The shortened script looks like this:
      #!/usr/bin/perl use Time::HiRes qw( usleep ); use DBI; # use strict; use warnings; open (CONFIG, "<homenet.actiontrigger.conf") or die "Can't open Config + file: $!" ; while (readline CONFIG) { if ( $_ !~ /^\s*#\s*/ ) { if ($_ =~ /\S+/ ) { if ( $_ =~ /^\s*(\S+)\s*=\s*(\S+)\s*#*(.*)$/ ) {}; my $conf_key = $1; my $conf_value = $2; $conf{$conf_key} = $conf_value; } } } close (CONFIG); my $db_homenet = DBI->connect("DBI:mysql:$conf{DatabaseName}:$conf{Dat +abaseServer}",$conf{DatabaseUser},$conf{DatabaseSecret}); my $X = 0; while( $X != 1 ) { $Query_lookque = "SELECT COUNT(*) FROM Input_Que"; my $lookque = $db_homenet->prepare( $Query_lookque ); $lookque->execute(); my ( $rows ); $lookque->bind_columns( undef, \$rows ); while( $lookque->fetch() ){} if ( $rows >= 1 ) { my $data = ""; $Query_check_inputque = "SELECT * FROM `Input_Que` WHERE 1 +LIMIT 1"; my $check_inputque = $db_homenet->prepare( $Query_check_inp +utque ); $check_inputque->execute(); my( $nr, $timestamp, $busname, $data ); $check_inputque->bind_columns( undef, \$nr, \$timestamp, \$ +busname, \$data ); while( $check_inputque->fetch() ){ } print "$nr - $timestamp - $busname - $data"; if ( $data =~ /.*(\w{2})(\w{2})>(\w+)>/ ) + # hard command { my $data_type = "hard_command"; my $sender_id = $1; my $receiver_id = $2; my $command = $3; print "HARD-COMMAND van $sender_id naar $receiver_ +id op bus $busname: Command = $command\n"; received_command($sender_id, $busname, $receiver_i +d, $busname, $command); } elsif ( $data =~ /.*(\w+)>(\w+)>(.+)>(\w+)>/ ) + # soft command { my $data_type = "soft_command"; my $receiver_id = $3; my $command = $4; my $sender_id = "S3"; print "SOFT-COMMAND naar $receiver_id op bus $busn +ame: Command = $command\n"; received_command($sender_id, $busname, $receiver_i +d, $busname, $command); } else { print "data kan niet worden ontcijferd! \n"; } $Query_delete_from_que = "DELETE FROM `Input_Que` WHERE `nr +` = $nr LIMIT 1"; $db_homenet->do( $Query_delete_from_que ); } usleep 100000 } sub received_command { my $command_source = "*"; my $sbus = "*"; my $command_destination = "*"; my $dbus = "*"; my $data = "*"; ($command_source, $sbus, $command_destination, $dbus, $data) = @_; print "@_\n"; my $cod = "$command_destination"; # $Query_gettype = "SELECT `type` , `softv_high` , `softv_low` FROM + `master_devices` WHERE naam = ( '$command_destination' ) && bus = ( +'$dbus' ) LIMIT 1"; my $Query_gettype = "SELECT `type` , `softv_high` , `softv_low` FR +OM `master_devices` WHERE naam = ( '$cod' ) LIMIT 1"; print "$Query_gettype\n"; my $gettype = $db_homenet->prepare( $Query_gettype ); $gettype->execute(); my( $type, $swh, $swl ); $gettype->bind_columns( undef, \$type, \$swh, \$swl ); while( $gettype->fetch() ){} print "$type\n"; print "$swh\n"; print "$swl\n"; $devtype = "$type.$swh.$swl"; print "Device Type = $devtype\n"; }
      By the way, this is the output I get when I have the soft-command problem:
      ./hbat-test "my" variable $data masks earlier declaration in same scope at ./hbat- +test line 41. 9873 - 20050806205302 - 10.54.1.250 - 1>h>U1>h> SOFT-COMMAND naar U1 op bus 10.54.1.250: Command = h S3 10.54.1.250 U1 10.54.1.250 h SELECT `type` , `softv_high` , `softv_low` FROM `master_devices` WHERE + naam = ( 'U1' ) LIMIT 1 Use of uninitialized value in concatenation (.) or string at ./hbat-te +st line 102. Use of uninitialized value in concatenation (.) or string at ./hbat-te +st line 103. Use of uninitialized value in concatenation (.) or string at ./hbat-te +st line 104. Use of uninitialized value in concatenation (.) or string at ./hbat-te +st line 105. Use of uninitialized value in concatenation (.) or string at ./hbat-te +st line 105. Use of uninitialized value in concatenation (.) or string at ./hbat-te +st line 105. Device Type = ..
Re: Database problem
by rnahi (Curate) on Aug 06, 2005 at 16:09 UTC

    Here is one more case where reading Before asking a database related question ... could save you and the helpful monks a bunch of time.

    Please, read it and come back with a more suitable example. As the tutorial says, perhaps you may even find the solution on your own, while going through the process of posting a good question.

      Thank you for pointing me at this information... I'm sure it will be very helpfull with this and future problems!
Re: Database problem
by graff (Chancellor) on Aug 06, 2005 at 16:10 UTC
    When I call the schript from one place everything works fine and when I call it from another place (in exactly the same way) there are no results comming back from the database. When I do a print of the database Query and past that into phpmyadmin for testing, everything works fine, also with the query that does not return the data...

    In the posted code, you are not doing any error checking on the DBI->connect call, so based on what you say the problem is, I'd guess that when running it from "another place", the connection is failing.

    Try writing your connect call in this form, and see what happens:

    $db_homenet = DBI->connect( ... ) or die "DBI connect failed: $DBI::er +rstr";

    While you're at it, look at the DBI man page about the "RaiseError" attribute (which you set via the connect call), and/or consider adding error traps at each of the "prepare" and "execute" calls.

Re: Database problem
by CountZero (Bishop) on Aug 06, 2005 at 16:47 UTC

    Which one of your queries don't always work? You have more than one query in your script and as we don't have access to your database it gets mighty difficult to guess where the error is.

    BTW: print "data kan niet worden ontcijfert! \n" should really be print "data kan niet worden ontcijferd! \n"

    CountZero

    "If you have four groups working on a compiler, you'll get a 4-pass compiler." - Conway's Law

      Sorry about that, kind of slipped by... The query that goes wrong is the Query_gettype, the first one in the sub routine... It works every time that the sub routine is called from the -hard command- part and it never works when it is called from the -soft command-. And by the way, thank you for your Dutch lessons... guess I just got a little to desperate with all those debug messages :-)
Re: Database problem
by jeroen_h (Initiate) on Aug 07, 2005 at 11:08 UTC
    The problem is solved for now... or should I say I have found a way around it (hmmm... ugly). The problem was that the variable that was send to the subroutien for 'naam' did look alright to me but not to the program... I now get this variable by inserting the var I get from the string in to a new string and get it out of that again with a new regular expression (I'm sure you don't like this way of doing it, but for me it works for now...) I want to thank you all again for your time! Jeroen

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://481490]
Approved by holli
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others chanting in the Monastery: (10)
As of 2014-07-23 10:01 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    My favorite superfluous repetitious redundant duplicative phrase is:









    Results (139 votes), past polls