I recently ran into an issue I have not seen before.
Scenario:
I am running a simple script on a Raspberry Pi which reads some data from some sensors located on an Arduino which come across serial via USB in the form of JSON to the RPi, The script simply uses Device::SerialPort::Arduino to read the data, decode the JSON, and pump the resultant values into my SQLite3 database. I am switching from an RPi2 to an RPI3 and have moved everything from one to the other. Oddly enough, my Perl script is the only thing that is failing. The environment has been thoroughly checked including the apache2 setup, file locations, permissions, mods enabled, Perl packages installed, etc... .
The Specific Issue
My script reaches the line where the execute on a query is to take place and I get an error which says "DBD::SQLite::st execute failed: unable to open database file at ./input_handler.pl line 68.". Line 68 is the execute portion of the SQLite interaction. All code (not much) is included below. What I don't get and have not seen before is where the opening of the database takes place just fine, but the execute directive fails with this kind of error.
Any input appreciated... Thanks in advance
Updated to correct a couple of typos/misquotes
#!/usr/bin/perl -w use Modern::Perl; use Device::SerialPort::Arduino; use DBI; #use Proc::Daemon; use DateTime; use JSON; my $dbfile = "/var/www/data/weather_data.sqlt"; #use Data::Dumper; #my $dt = DateTime->now(); my ($key,$val, $q, $sth, $rec_cnt, $char, $json); my $debug = 0; #set to 1 if debug print statements +are to be displayed my $delay = 29; #must be set to less than in the ar +duino rpt_ms/1000 ###################################################################### +############ # Set up the serial port if ($debug) { say "Setting up serial port\n"; } my $dev = Device::SerialPort->new("/dev/ttyACM0") || warn "Can't o +pen /dev/ttyACM0 $!"; my $profile = 0; # set to '1' when running NYTProf +ile. Limits run length... $dev->baudrate(115200); # you may change this value $dev->databits(8); # but not this and the two followi +ng $dev->parity("none"); $dev->stopbits(1); $dev->read_char_time( 0 ); # don't wait for each character $dev->read_const_time( 1000 ); # 1 second per unfulfilled "read" +call $dev->read_const_time( 1500 ); # 1.5 seconds per unfulfilled "rea +d" call ###################################################################### +############### while (1) { # Poll to see if any data is coming in if ($debug) { say "Entering polling...." } my $char = $dev->lookfor(); if ($char) { if ($debug == 1) { say "Raw input is -> $char"; } $rec_cnt ++; next if $rec_cnt == 1; # The first line incoming tends to b +e two lines combined, skip it. my $line = $char; chomp($line); $json = decode_json($line); process_data(\$json); # now process the competed inc +oming json object, inserting it into DB } if($debug == 1) { # give up its process resources u +ntil we need it. Change here if changed in Ardduino!! say "return after $delay seconds of sleeping\n"; } sleep($delay); # the arduino sends data eve +ry 30 second (currently). Let the Perl script } ###################################################################### +################# sub process_data { if ($debug) { say "Processing polled data...." } my $json_line = shift(@_); my $dt = DateTime->now(); $dt->set_time_zone( 'America/Chicago' ); my $sth; my $dbh = DBI->connect("dbi:SQLite:dbname=$dbfile" ,"",""); if ( (exists($json->{'bbl'})) and (not exists($json->{'ra'}))) { $q = "insert into weather_data values (?, ?, ?, ?, ?, ?, ?, ?, + ?, ?, ?, ?, ?, ?, ?, ?, ?)"; $sth = $dbh->prepare($q); $sth->execute(undef, $dt->datetime(), $dt->ymd(), $dt->hms(), +$json->{'wd'}, $json->{'wv'}, $json->{'tF'}, $json->{'tC'}, $json->{' +bp'}, $json->{'rh'}, $json->{'li'}, $json->{'ov'}, $json->{'lux'}, $j +son->{'bbl'}, $json->{'irl'}, 'null', 'null' ); } elsif ( (exists($json->{'ra'})) and (exists($json->{'bbl'}))) { + $q = "insert into weather_data values (?, ?, ?, ?, ?, ?, ?, ?, + ?, ?, ?, ?, ?, ?, ?, ?, ?)"; $sth = $dbh->prepare($q); $sth->execute(undef,$dt->datetime(), $dt->ymd(), $dt->hms(), $ +json->{'wd'}, $json->{'wv'}, $json->{'tF'}, $json->{'tC'}, $json->{'b +p'}, $json->{'rh'}, $json->{'li'}, $json->{'ov'}, $json->{'lux'}, $js +on->{'bbl'}, $json->{'irl'}, $json->{'ra'}, $json->{'rr'} ); } elsif ( (exists($json->{'ra'})) and (not exists($json->{'bbl'})) +) { $q = "insert into weather_data values (?, ?, ?, ?, ?, ?, ?, ?, + ?, ?, ?, ?, ?, ?, ?, ?, ?)"; $sth = $dbh->prepare($q); $sth->execute(undef,$dt->datetime(), $dt->ymd(), $dt->hms(), $ +json->{'wd'}, $json->{'wv'}, $json->{'tF'}, $json->{'tC'}, $json->{'b +p'}, $json->{'rh'}, $json->{'li'}, $json->{'ov'}, 'null', 'null', 'nu +ll', $json->{'ra'}, $json->{'rr'} ); } elsif ( (not exists($json->{'ra'})) and (not exists($json->{'bbl +'}))) { $q = "insert into weather_data values (?, ?, ?, ?, ?, ?, ?, ?, + ?, ?, ?, ?, ?, ?, ?, ?, ?)"; $sth = $dbh->prepare($q); $sth->execute(undef,$dt->datetime(), $dt->ymd(), $dt->hms(), $ +json->{'wd'}, $json->{'wv'}, $json->{'tF'}, $json->{'tC'}, $json->{'b +p'}, $json->{'rh'}, $json->{'li'}, $json->{'ov'}, 'null', 'null', 'nu +ll', 'null', 'null' ); } $sth->finish; $dbh->disconnect; }
...the majority is always wrong, and always the last to know about it...
A solution is nothing more than a clearly stated problem...
In reply to DBD::SQLite - Can't Connect to file - Solved by wjw
| For: | Use: | ||
| & | & | ||
| < | < | ||
| > | > | ||
| [ | [ | ||
| ] | ] |