Beefy Boxes and Bandwidth Generously Provided by pair Networks
laziness, impatience, and hubris
 
PerlMonks  

DBI SQLite

by mikasue (Friar)
on Nov 10, 2011 at 22:09 UTC ( #937469=perlquestion: print w/ replies, xml ) Need Help??
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;

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 ");

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
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

      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();

      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.

        In fact I now always use Perl to create the DB so that I know I won't get this problem.

        J.C.

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

Re: DBI SQLite
by Plankton (Priest) 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
    You can find out more about SQLite command line shell here: http://www.sqlite.org/sqlite.html
Re: DBI SQLite
by Marshall (Prior) 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.

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://937469]
Approved by ww
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others scrutinizing the Monastery: (6)
As of 2014-07-13 14:15 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    When choosing user names for websites, I prefer to use:








    Results (249 votes), past polls