Unable to coonect to SQLite database file

[Sam]

Hello,
Perl newbie here. I am using SQLite3 for a project to learn DBI.
Getting the following error when trying to connect to the database
file: "unable to open database file(1) at dbdimp.c line 94 at tool.cgi
line
16."

-------------- The code -------------------

#!/usr/bin/perl -wT
use strict;
use warnings;
use CGI::Carp qw(fatalsToBrowser); #Output errors to browser
use DBI;

my $driver = 'SQLite';
my $database = '/path_to_database_file/data_v1.db3'; # db path
my $username = undef; # database username
my $password = undef; # database password

# Set up a connection string specific to this database
my $dsn = "DBI:$driver:database=$database";

# make the actual connection
my $dbh = DBI->connect($dsn, $username, $password) || die DBI->errstr;

# when you're done with the database, disconnect
$dbh->disconnect();

------------------ End Code ----------------------------

What could be the potential problem(s):

1. I made the Sqlite3 database file using SQLite SPY (
http://www.zeitungsjunge.de/delphi/sqlitespy/index.htm ) and then
uploaded it to the directory?

2. The path. The database directory is outside public_html from where
the CGI script executes?

The database file, and the directory where it exists have been chmoded
777.

Also, how can I find out what version of sqlite the DBD::SQLite module
is using?

 

[Tad McClellan]

Perl newbie here. I am using SQLite3 for a project to learn DBI.

use CGI::Carp qw(fatalsToBrowser); #Output errors to browser

You should get your CGI programs working from the command line
and move them to the CGI environment only after they have
been debugged.

my $dbh = DBI->connect($dsn, $username, $password) || die DBI->errstr;

I think you should look up the precedence of = and || in perlop.pod.

2. The path. The database directory is outside public_html from where
the CGI script executes?

If you can run it from the command line, then this one seems
a good candidate.

The database file, and the directory where it exists have been chmoded
777.

That is a really bad idea!

You should never need wide-open permissions like that.

 

[Sam]

Thanks for the hints and guidance. Really appreciate it.

You should get your CGI programs working from the command line ...

Hmm ... I'll need to get shell access which I currently don't have.

I think you should look up the precedence of = and || in perlop.pod.

I did a google search and found the doc you mentioned (
http://search.cpan.org/src/NWCLARK/perl-5.8.7/pod/perlop.pod ). Found
it quite technical, gave me a headache, but I think I figured it out
(please correct me if I am wrong) - The || operator evaluates the left
side first. The = operator the right side. The || operator has a higher
precedence than =. So I don't think there's any problem in that line of
code ...

If you can run it from the command line, then this one seems
a good candidate.

I created another directory inside public_html, copied the database
file to it, and updated the path in the perl code to point to it.
Unfortunately it still spouts the same error as before: "unable to open
database file(1) at dbdimp.c ..."

That is a really bad idea!
You should never need wide-open permissions like that.

I just did that to make sure it wasn't a permission related problem ...
but I do have a doubt - Since the directory and db file are outside
public_html, is it a security risk if we do give it such wide
permissions?

Any help to get this working is really appreciated - have been stuck on
this for the past 2 days ... it's starting to have a demotivating
effect.

 

[DJ Stunks]

(Tad, I believe, wrote

I did a google search and found the doc you mentioned (
http://search.cpan.org/src/NWCLARK/perl-5.8.7/pod/perlop.pod ). Found
it quite technical, gave me a headache, but I think I figured it out
(please correct me if I am wrong) - The || operator evaluates the left
side first. The = operator the right side. The || operator has a higher
precedence than =. So I don't think there's any problem in that line of
code ...

Please do not snip attributions!

You have read perlop and understood it, but your final reasoning is
incorrect. Perhaps the following will help you understand:

C:\>perl -MO=Deparse,-p -e "my $dbh = \
DBI->connect($dsn, $username, $password) || die DBI->errstr;"
(my $dbh = ('DBI'->connect($dsn,$username,$password) ||
die('DBI'->errstr)));

-jp

 

[Sam]

Please do not snip attributions!

I am not sure I understood what you are trying to say ... perhaps the
confusion is because I am reading and posting through Google Groups?

You have read perlop and understood it, but your final reasoning is
incorrect. Perhaps the following will help you understand:

Thanks for correcting that misperception. I get the point you and Tad
where trying to make me understand. Unfortunately, the root problem
still remains, i.e. I am unable to open the database. Trying

my $dbh = DBI->connect($dsn, $username, $password, { RaiseError => 1,
AutoCommit => 0 });

still gives the error:

DBI connect('database=/path_to_database/data_v1.db3','',...) failed:
unable to open database file(1) at dbdimp.c line 94 at tool.cgi line 18

A doubt - if the database file doesn't exist, shouldn't the DBI module
create it? I tried to do that too, but again got the same error:

DBI connect('database=/path_to_database/newdb.db3','',...) failed:
unable to open database file(1) at dbdimp.c line 94 at tool.cgi line 18

On another forum, somebody suggested that I re-upload the database file
in binary mode. Thinking that could have been the problem, I tried that
too but the problem still persists. I am totally stumped ...

-------------------------------------------

 

[Sam]

Please do not snip attributions!

I am not sure I understood what you are trying to say ... perhaps the
confusion is because I am reading and posting through Google Groups?

You have read perlop and understood it, but your final reasoning is
incorrect. Perhaps the following will help you understand:

Thanks for correcting that misperception. I get the point you and Tad
where trying to make me understand. Unfortunately, the root problem
still remains, i.e. I am unable to open the database. Trying

my $dbh = DBI->connect($dsn, $username, $password, { RaiseError => 1,
AutoCommit => 0 });

still gives the error:

DBI connect('database=/path_to_database/data_v1.db3','',...) failed:
unable to open database file(1) at dbdimp.c line 94 at tool.cgi line 18

A doubt - if the database file doesn't exist, shouldn't the DBI module
create it? I tried to do that too, but again got the same error:

DBI connect('database=/path_to_database/newdb.db3','',...) failed:
unable to open database file(1) at dbdimp.c line 94 at tool.cgi line 18

On another forum, somebody suggested that I re-upload the database file
in binary mode. Thinking that could have been the problem, I tried that
too but the problem still persists. I am totally stumped ...

-------------------------------------------

 

[John Bokma]

I am not sure I understood what you are trying to say ... perhaps the
confusion is because I am reading and posting through Google Groups?

You deleted the name of the author of "Please do not snip attributions!".
As you can see, I left your name, so it's clear who wrote the > part, but
since you snipped the name of the author you replied to, it's not clear
who wrote >>.

Also, when you qoute, delete all lines you're not replying to.

 

[Sam]

Ah, my mistake. Thanks for the clarification John. I'll ensure that
doesn't happen again.

 

[David Squire]

Ah, my mistake. Thanks for the clarification

What clarification?

John.
Who?

I'll ensure that doesn't happen again.

What doesn't?

Please observe the posting guidelines for this group. Retain
attributions. Quote context.

Read the posting guidelines. They are posted here regularly.

DS

 

[Sam]

Please observe the posting guidelines for this group. Retain
attributions. Quote context.

Thanks, I'll keep those in mind.

 

[David Squire]

Thanks, I'll keep those in mind.

And thanks to you

 

[J. Gleixner]

A doubt - if the database file doesn't exist, shouldn't the DBI module
create it? I tried to do that too, but again got the same error:

DBI connect('database=/path_to_database/newdb.db3','',...) failed:
unable to open database file(1) at dbdimp.c line 94 at tool.cgi line 18

Does it work from the command line?

On another forum, somebody suggested that I re-upload the database file
in binary mode. Thinking that could have been the problem, I tried that
too but the problem still persists. I am totally stumped ...

When you installed DBD::SQLite, did you do a "make test"?

I missed your original post. What's the value of $dsn?

 

[Sam]

Thanks for taking the time, J. Gleixner. Much appreciated. The complete
thread of this discussion with my original post that started it all can
be found on Google Group at:
http://groups.google.co.in/group/co...8cc01/64739c3f43048d04?hl=en#64739c3f43048d04

Does it work from the command line?

I do not have shell access. Moreover, I am a newbie to perl programming
and am trying to learn to use DBI through SQLite3 ...

When you installed DBD::SQLite, did you do a "make test"?

My web host installed the module on my request.

I missed your original post. What's the value of $dsn?

Originally, this was the code I was using: (derived from
http://zoic.org/training/perldb/x989.html )

----- The Code --------

#!/usr/bin/perl -T
use strict;
use warnings;
use CGI::Carp qw(fatalsToBrowser); #Output errors to browser
use DBI;


my $driver = 'SQLite';
my $database = '/path_to_database_file/data_v1.db3'; # db path
my $username = undef; # database username
my $password = undef; # database password


# Set up a connection string specific to this database
my $dsn = "DBI:$driver:database=$database";


# make the actual connection
my $dbh = DBI->connect($dsn, $username, $password) || die DBI->errstr;


# when you're done with the database, disconnect
$dbh->disconnect();

----- End Code --------

Doubting that perhaps I hadn't specified the path of the database
correctly, I removed the path and used this instead in the above code:

my $database = 'newdb.db3';

I got a "500 Internal Server Error ...", but the cgi-bin directory did
contain a database=newdb.db3 file. Seeing the 'database=' included in
the filename and browsing other source codes using "DBI:SQLite:dbname=
...." I tried that instead. This time the script did create the database
file in the path I specified, but again gave the unhelpful "500
Internal Server Error - The server encountered an internal error or
misconfiguration and was unable to complete your request."

I do feel I am getting nearer to the solution but am still a bit lost.

 

[Sam]

Aaaarg. Stupid me ...! Executing in browser without specifying the
headers ... There was print statement included in the original code
print $dbh->{'sqlite_version'};
which I had added while tweaking the code.

 

[J. Gleixner]

The DSN should be something like: "dbi:SQLite:dbname=dbfile"

You do know that you can install modules on your computer and run them
there, before trying to run them on another host that doesn't provide
shell access, right? It'll make debugging CGI-based scripts much easier
for you.. and us.

Try something from the DBD::SQLite tests.

Not tested, just simply a hack modified from the SQLite test cases, to
get you going or to help point to where the error is coming from.

use DBI;
use CGI qwstandard);
use CGI::Carp qw(fatalsToBrowser);
use strict;

print header, start_html;

print "<pre>connecting...\n";
my $dbh = DBI->connect('dbi:SQLite:dbname=foo', '', '',
{ RaiseError => 1 , AutoCommit => 1 });
print "Got connected to dbname=foo\n";

print "creating table f...\n";
$dbh->do("CREATE TABLE f (f1, f2, f3)");
print "created table f.\n";

print "query from table f..\n";
my $sth = $dbh->prepare("SELECT f.f1, f.* FROM f");
$sth->execute();
print "query from table f successful.\n";

print "results from table f...\n";
my $names = $sth->{NAME};
print join(', ', @$names), "\n";
$sth->finish;
$dbh->disconnect;

print "finished\n</pre>", end_html;