Issues while trying to insert into database in PERL using DBD::SQLite module

Question

I am new to Databse. I installed SQLite on my system and tried running PERL program to insert into databse

SQLite commands to create database and table

In command prompt, I iterated to SQlite directory and given commands as

sqlite3 one.db;
create table table1 (a int, b string);

It created a database and table

I executed the followed PERL program

use strict;

use warnings;
use DBI;

use DBD::SQLite;

my $dbh = DBI->connect("dbi:SQLite:dbname=one.db","","") or die "cannot 

connect";

my $sth = $dbh->prepare("INSERT INTO 'table1'
                       (Income, LAST_NAME )
                        values
                       ('1000', 'poul')");
$sth->execute() or die $DBI::errstr;
$sth->finish();
$dbh->commit or die $DBI::errstr;

I got the following error

DBD::SQLite::st execute failed: no such table: table1 at DBI.txt line 14.
no such table: table1 at DBI.txt line 14.

After getting comments from IKEGAMI, I changed the DBI->connect as

my $dbh = DBI->connect

("dbi:SQLite:dbname=C:/Users/nitkumar/Downloads/sqllite/one.db","","

") or die "cannot connect";

I started getting error message as

DBI connect('dbname=C:/Users/nitkumar/Downloads/sqllite/one.db','',...) failed:
 database disk image is malformed at DBI.txt line 8
cannot connect at DBI.txt line 8.

After getting more comments i got the version by using

use DBD::SQLite;
warn $DBD::SQLite::VERSION;
warn $DBD::SQLite::sqlite_version;

I got the output as

1.37 at version.txt line 2.
3.7.12.1 at version.txt line 3.

I am using sqlite3 . I guess there are no issues with version

If this is the reason then it would have printed error msg saying "cannot connect". But it has not given any such error. Correct me if I am wrong — Sumit
– Sumit, Commented Jan 27, 2014 at 14:16
Ok. How to give path of the database??. Suppose I have create database in c://sqlite folder — Sumit
– Sumit, Commented Jan 27, 2014 at 14:19
The name of the language is "Perl", not "PERL". It's not an acronym. — ikegami
– ikegami, Commented Jan 27, 2014 at 18:42
A separate issue - you don't need the use DBD::SQLite line. The DBI modules loads the correct DBD automatically. — Dave Cross
– Dave Cross, Commented Jan 28, 2014 at 10:39

ikegami · Accepted Answer · 2014-01-27 14:22:42Z

3

one.db is probably not the path to the database you created from the command line.

If you ask to open a non-existent database, it creates it. Just like the command line tool does.

For example, if the path to your database is c:\sqlite\one.db, you can use

"dbi:SQLite:dbname=c:\\sqlite\\one.db"

or probably

"dbi:SQLite:dbname=c:/sqlite/one.db"

answered Jan 27, 2014 at 14:22

ikegami

391k17 gold badges291 silver badges555 bronze badges

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

3 Comments

Sumit Over a year ago

I tried but it is giving another message saying DBI connect('dbname=C:/Users/nitkumar/Downloads/sqllite/one.db','',...) failed: database disk image is malformed at DBI.txt line 8 cannot connect at DBI.txt line 8.

ysth Over a year ago

Nitesh: does deleting and recreating the table help?

ikegami Over a year ago

That's an entirely separate issue/question. Corrupted by ASCII transfer of binary file??? Version incompatibility??? What's $dbh->{sqlite_version} and the version of your SQLite used by your client?

bluefeet · Accepted Answer · 2014-01-27 14:28:38Z

I expect the one.db that you created is not the same one.db that your Perl script is trying to access. Make sure the one.db that you created is in the same directory where you are running your Perl script from.

DBD::SQLite will create the database file if it does not exist, so what is likely happening is you created one.db in one directory, and then are executing your Perl script from another directory, creating a second one.db in that directory.

I typically do my SQLite table creations within my Perl scripts so that there is no guesswork and I can easily blow my db file away at a later point and everything will just automagically work:

$dbh->do(q[
    CREATE TABLE IF NOT EXISTS table1 (
        a int,
        b string
    )
]);

Also, you will find things work better if you specify an absolute path for your db file. You could create it relative to the $ENV{HOME} directory or the script's directory. I would recommend the latter:

use FindBin qw( $Bin );
my $dbh = DBI->connect("dbi:SQLite:dbname=$Bin/one.db","","")

Finally, use RaiseError! Writing boilerplate to check if there was an error is a waste of your time, and you're always going to forget something. So, do this:

my $dbh = DBI->connect("dbi:SQLite:dbname=$Bin/one.db","","", {RaiseError => 1});

Then you can stop doing all those or die ... checks.

Collectives™ on Stack Overflow

Issues while trying to insert into database in PERL using DBD::SQLite module

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related