CODEWITHSUNDEEP
linuxperlsqlite
hijaked79
hijaked79

Reputation: 55

Having issues with SQLite DB executing within a Perl script

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

Upvotes: 2

Views: 2465

Answers (1)

Borodin
Borodin

Reputation: 126772

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;

Upvotes: 5

Related Questions