DBI SQLite

mikasue has asked for the wisdom of the Perl Monks concerning the following question:

I have a SQLite db (demo.db) that was created with the SQLite Database Browser. I have imported that .db file into my Eclipse workspace and used the following code to connect to it in my Perl code.

my $dbh = DBI->connect("dbi:SQLite:dbname=demo.db","","",{RaiseError=>
+1,AutoCommit=>1} ) or die "Can't connect: ".$DBI::errstr;
[download]

It connects fine no error. But when I try to execute this code it errors and says the tables don't exist.

my $all = $dbh->selectall_arrayref("SELECT * FROM mytable ");
[download]

How can I get my Perl program to read, access and manipulate the tables in that SQLite db? I have the proper use statements declared. Thanks!

Comment on DBI SQLite Select or Download Code

Replies are listed 'Best First'.
Re: DBI SQLite by CountZero (Bishop) on Nov 10, 2011 at 22:31 UTC
Are you sure the table "mytable" exists? CountZero A program should be light and agile, its subroutines connected like a string of pearls. The spirit and intent of the program should be retained throughout. There should be neither too little or too much, neither needless loops nor useless variables, neither lack of structure nor overwhelming rigidity." - The Tao of Programming, 4.1 - Geoffrey James	[reply]
Re^2: DBI SQLite by Anonymous Monk on Nov 10, 2011 at 22:37 UTC
Are you sure the table "mytable" exists? I second this idea, use http://search.cpan.org/perldoc/DBI#table_info instead	[reply]
Re: DBI SQLite by Plankton (Vicar) on Nov 11, 2011 at 06:10 UTC
To rule out any problems with you Perl code you could use `sqlite3` from the command-line to verify your SQLite data file. For example you could do this to see what tables are available in your SQLite database: `plankton@chumbucket:~$ sqlite3 mysqlitedb SQLite version 3.7.4 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> .dump PRAGMA foreign_keys=OFF; BEGIN TRANSACTION; CREATE TABLE tbl1 (one varchat(10), two smallint); INSERT INTO "tbl1" VALUES('hello',10); COMMIT; sqlite> .tables tbl1 sqlite> .exit` [download] You can find out more about SQLite command line shell here: http://www.sqlite.org/sqlite.html	[reply] [d/l] [select]
Re: DBI SQLite by Marshall (Canon) on Nov 11, 2011 at 11:07 UTC
When I first started using SQlite, I just installed the DBD Perl module and then I found a manager plug-n for Firefox that worked so well that I haven't even fiddled with installing any other command line tool. There are some rough edges on this thing, but its not bad. Might try it to get another view of what you have created (there's a pretty nice graphical display for that). You probably have not created what you thought that you created.	[reply]
Re: DBI SQLite by Anonymous Monk on Nov 10, 2011 at 22:27 UTC
What version of sqlite engine? Make sure your versions of sqlite match	[reply]
Re^2: DBI SQLite by Anonymous Monk on Nov 11, 2011 at 02:00 UTC
Hi, This is very good advice. The versions of SQLite that you used to create the DB, and the one that Perl is using to access it must be at least a close match. I have had exactly the same problem as you are getting when they are not. J.C.	[reply]
Re^3: DBI SQLite by Anonymous Monk on Nov 11, 2011 at 02:04 UTC
In fact I now always use Perl to create the DB so that I know I won't get this problem. J.C.	[reply]
Re^2: DBI SQLite by mikasue (Friar) on Nov 10, 2011 at 22:42 UTC
Additional Information: I was using DBD::CSV at first and everything was working beautifully. I then got the bright idea to use SQLite. After the initial connection code, is there any difference in how to execute queries? Here is how I accessed db with CSV... `$vquery = "SELECT * FROM mytable WHERE myfield = 2"; $vsth = $dbh->prepare($vquery); $vsth->execute();` [download]	[reply] [d/l]