Having issues with SQLite DB executing within a Perl script

Question

So I'm new to Perl along with being new to SQLite. I have SQL experience, but this new Perl syntax is throwing me a bit.

So I have an issue where I'm trying to create a DB from a IPtable log using a Perl script to parse the data that the table has and send notifications out to people. The script also sends notifications to the users, but I don't believe that has anything to do with this problem.

This is the error I'm receiving.

DBD::SQLite::db prepare failed: no such table: syslog_decom_notif at ./send_notification_syslog.pl line 251. Can't call method "execute" on an undefined value at ./send_notification_syslog.pl line 252.

Below is the code where I'm receiving the error from:

2 sub select_contacts {
233         my @contact_info;
234         my $dbh = DBI->connect( DECOM_NOTIFICATION_DB ,"","");
235
236         my ( $where_clause, @exec_params ) = build_where_clause();
237
238         my $SQL = <<SQL;
239 select
240                 contact
241         ,       status
242         ,       contact_mngr
243         ,       hostname
244         ,       contact_type
245         ,       syslog_server
246 from
247         syslog_decom_notif
248 $where_clause
249 SQL
250         debug  ( __LINE__ . " Excuting SQL = \n[ $SQL ]\n" );
251         my $sth = $dbh->prepare( $SQL );
252         $sth->execute( @exec_params );
253         if ( $debug_mode ) {
254                 my @cols = @{$sth->{NAME}};
255                 print join '|', @cols;
256                 print "\n";
257         }
258         while (my @res = $sth->fetchrow) {
259                 for ( my $i=0; $i<@res; $i++ ) { $res[$i] = 'Null' if ! defined $res[$i]; }
260                 my $row = join '|', @res;
261                 debug "$row\n";
262                 push @contact_info, $row;
263         }
264         $sth->finish();
265         return @contact_info;
266 }

I've searched around and I can't seem to find anything that could really help with this problem.

I appreciate any and all ideas.

Best Regards

It says no such table: syslog_decom_notif. Is there such a table? Try executing the query ($SQL, you're already outputting it) in a command line db client. Also it says you're creating a table and storing data, but your code only shows a SELECT statement. That's a bit odd. ;-) — simbabque
– simbabque, Commented Jun 13, 2012 at 16:36
Thanks for you input! I edited my post, I meant to say that the data is parsed in the DB and then notifications through email are sent out. I didn't mean to say the data is parsed then stored. :-) — hijaked79
– hijaked79, Commented Jun 13, 2012 at 16:43

Borodin · Accepted Answer · 2012-06-13 17:38:28Z

5

Well the simple truth is that, as it says, DBI cannot find a table called syslog_decom_notif in the database you have opened. After the prepare has failed nothing else is going to work.

I presume you have the table name correct, so I wonder about the value of DECOM_NOTIFICATION_DB. Where has this come from? Is it perhaps a string defined with use constant?

In the connect call for SQLite, DBI expects a DSN that looks like dbi:SQLite:dbname=my_db_file. If the file doesn't exist it will be created, so perhaps you have this set up wrongly and you are working with an empty database?

Update

To see what is in the database you have connected to, add this right after the DBI->connect call. It will print a list of tables in the connected database. Make sure the END terminator appears at the start of the line and has no whitespace before or after it.

my $tables = $dbh->selectcol_arrayref(<<END);
SELECT name FROM sqlite_master
WHERE type='table'
ORDER BY name
END

print "$_\n" for @$tables;

edited Jun 13, 2012 at 17:38

answered Jun 13, 2012 at 16:45

Borodin

127k9 gold badges72 silver badges146 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

hijaked79 Over a year ago

The DECOM_NOTIFICATION_DB is set as a use constant early in the script. As for the connect you are speaking of, I'm still looking into it, but I'll get back to you.

Borodin Over a year ago

I have added to my answer to show some code that will list the tables in the SQLite database you have connected to.

hijaked79 Over a year ago

I found out the error. It was the use of a constant somewhere was a redundant when a package was inserted into the code. I thank you for the help!

Collectives™ on Stack Overflow

Having issues with SQLite DB executing within a Perl script

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related