[SOLVED] Perl connect to SQLite database

totaluser · 11-09-2011, 04:36 PM

Hello everybody,
I created a small SQLite database that I want to use in my Perl script

Code:

#!/usr/bin/perl -w

 use strict;
 use warnings;
 use DBI;
 use DBD::SQLite;
 use POSIX 'strftime';

 my $now = strftime "%d%m%Y_%H%M%S", localtime;

 my $db_handle=DBI->connect("dbi:SQLite:dbname=myDB.db") == 0  or die "$DBI::errstr\n";


 $db_handle->do( "DROP TABLE routers" ) or die "$DBI::errstr\n";
 $db_handle->do( "DROP TABLE modules" ) or die "$DBI::errstr\n";

 $db_handle->do( "CREATE TABLE routers(NAME VARCHAR(20) PRIMARY KEY, IOS VARCHAR(200), RAM INTEGER, FLASH INTEGER, WLC VARCHAR(20), AIM VARCHAR(20))" ) or die "$DBI::errstr\n";
 $db_handle->do( "CREATE TABLE modules(NAME VARCHAR(200), DESCR VARCHAR(200), PID VARCHAR(200), VID VARCHAR(3), SN VARCHAR(11) PRIMARY KEY, ROUTER VARCHAR(20), FOREIGN KEY(ROUTER) REFERENCES routers(NAME))" ) or die "$DBI::errstr\n";



 system("sqlite3", "myDB.db", ".import A512_routers.table routers") == 0 or die "Import of routers table failed";
 system("sqlite3", "myDB.db", ".import A512_modules.table modules") == 0 or die "Import of modules table failed";

 $db_handle->do( "CREATE TABLE routers_$now AS SELECT * FROM routers" ) or die "$DBI::errstr\n";
 $db_handle->do( "CREATE TABLE modules_$now AS SELECT * FROM modules" ) or die "$DBI::errstr\n";
 $db_handle->disconnect;

if I run it like this, the script fails at this line

Code:

 my $db_handle=DBI->connect("dbi:SQLite:dbname=myDB.db") == 0  or die "$DBI::errstr\n";

telling me
Use of uninitialized value in concatenation (.) or string at ./test.pl line 12.

line 12 is the line I am showing you

on the other hand if I remove the die part so it looks like this

Code:

my $db_handle=DBI->connect("dbi:SQLite:dbname=myDB.db");

the program runs fine and the data is indeed imported and new tables are created

I am interested in your opinions why this is happening and what is causing it

thank you very much for your replies in advance

Cedrik · 11-09-2011, 04:52 PM

Try removing the quotes around "$DBI::errstr\n";

PHP Code:



my $db_handle=DBI->connect("dbi:SQLite:dbname=myDB.db") 
    or die $DBI::errstr . "\n";

totaluser · 11-09-2011, 05:04 PM

after removing quotes like you suggested it still displays the same message as shown in 1st post

if I modify it to

Code:

my $db_handle=DBI->connect("dbi:SQLite:dbname=myDB.db") or die $DBI::errstr ;

then the message changes to
Use of uninitialized value in die at ./test.pl line 12.
Died at ./test.pl line 12.

Cedrik · 11-09-2011, 05:08 PM

Uh ? Maybe this would work ?
(I noticed the script name, bad karma...)

PHP Code:



my $db_handle=DBI->connect("dbi:SQLite:dbname=myDB.db") or die DBI->errstr ;

totaluser · 11-09-2011, 05:16 PM

sorry for inconsistent script name

even after removing that $ sign and . "\n" it still is the same
Use of uninitialized value in die at ./test.pl line 12.
Died at ./test.pl line 12.

and with . "\n"
Use of uninitialized value in concatenation (.) or string at ./test.pl line 12.

Cedrik · 11-09-2011, 05:29 PM

Are you sure it works with "dbi:SQLite:dbname=myDB.db" ? (I notice the ':', dunno if it shouldn't be a ';' instead)

PHP Code:



my $db_handle=DBI->connect("dbi:SQLite;dbname=myDB.db") or die DBI->errstr;

[edit]
No, it should be "dbi:SQLite:dbname=myDB.db" :/
http://search.cpan.org/~msergeant/DB.../DBD/SQLite.pm

Maybe complete the extra arguments for the connect method

PHP Code:



my $db_handle=DBI->connect("dbi:SQLite:dbname=myDB.db", "", "") or die DBI->errstr;

[edit]
After a few test, this method call works:

PHP Code:



my $db_handle=DBI->connect("dbi:SQLite:dbname=myDB.db") 
    or die DBI->errstr . "\n";

However I can reproduce your error with:

PHP Code:



my $db_handle=DBI->connect("dbi:SQLite:dbname=myDB.db") == 0 
    or die DBI->errstr . "\n";

totaluser · 11-10-2011, 12:59 AM

Thank you very for the time you spent solving my problem

now it works fine on my testing server (Perl 5.8.8, Perld-DBI 1.607, Perl-DBD-SQLite 1.14) and hopefully it will work on a production server as well once appropriate modules are installed.

Thanks again