Beefy Boxes and Bandwidth Generously Provided by pair Networks
"be consistent"

perl DBI & SQLite

by spx2 (Deacon)
on Jun 04, 2007 at 09:03 UTC ( #619077=perlquestion: print w/replies, xml ) Need Help??

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

im totally confused about SQLite.
i mean on windows i downloaded this small
exe and i could work with it and it stored,
files for every db i used.
on linux i just used cpan and made
"install DBI"
"install DBD::SQLite"
as root(seems otherwise i could not)
and now im trying to figgure out where does
SQLite store my data ?
for example lets take a certain module
ive tried
its just an honest question i cannot answer
also id like to read somewhere some tutorial
or guide on using DBI(wich ive read that should
be an universal interface to all databases,
including SQLite.
does SQLite have any daemon or server ?
because the usual first step in trying to do anything
would be to connect to the database like so
$dbh = DBI->connect("DBI:mysql:db_name:dbserver:2134",
"dbuser", "dbpass");
and i imagine there has to be some kind of
daemon application to provide login possibilities.
on windows i cant see how that could have been
possible as i have downloaded only 1 .exe and that
was all SQLite.
also there's like allot of modules on cpan on SQLite,
i dont know wich one to use?
so what im asking where do i start
to understand sqlite and where do i learn to
program sqlite with perl.
id appreciate if any of you monks who already
used sqlite with perl would give me some advice.
ok,i guess ive posed too much weird questions for one

Replies are listed 'Best First'.
Re: perl DBI & SQLite
by Corion (Pope) on Jun 04, 2007 at 09:12 UTC


    SQLite is a small C library that implements a self-contained, embeddable, zero-configuration SQL database engine.

    So SQLite does not have any server component. It stores the complete database in a file.

    To use SQLite from Perl, you only need to install DBI and DBD::SQLite. All other modules are fancy stuff that provides mappings from objects to database rows or other fluff that is of little interest to you if you're just starting out with databases and Perl.

Re: perl DBI & SQLite
by shmem (Chancellor) on Jun 04, 2007 at 09:39 UTC
    use DBI; my $dbfile = 'database.db'; # your database file my $dbh = DBI->connect( # connect to your database, create if + needed "dbi:SQLite:dbname=$dbfile", # DSN: dbi, driver, database file "", # no user "", # no password { RaiseError => 1 }, # complain if something goes wrong ) or die $DBI::errstr; my $table = 'test'; my @rows = qw(id time data); $dbh->do("create table $table (".join(',',@rows).')') or die $DBI::errstr;

    Read the documentation of DBD::SQLite. It helps.


    _($_=" "x(1<<5)."?\n".q·/)Oo.  G°\        /
                                  /\_¯/(q    /
    ----------------------------  \__(m.====·.(_("always off the crowd"))."·
    ");sub _{s./.($e="'Itrs `mnsgdq Gdbj O`qkdq")=~y/"-y/#-z/;$e.e && print}
Re: perl DBI & SQLite
by bart (Canon) on Jun 04, 2007 at 11:19 UTC
    now im trying to figgure out where does SQLite store my data ?
    In a file. You name it (best using the absolute path) when you connect to the database, in the DBI->connect statement. If a file of that name doesn't exist in that location, it'll be created.
    does SQLite have any daemon or server ?
    The "server" is embedded in the DBD driver. In other words: the DBD module directly accesses/manipulates the data in that file.
    also there's like allot of modules on cpan on SQLite, i dont know wich one to use?
    Just plain DBD::SQLite. On Windows, if you're using ActivePerl and you don't have a compatible C compiler (it'll work with MinGW too), use PPM to install it.

    Oh, I also wholeheartedly recommend getting and using SQLite browser, a free GUI app on SourceForge.

    HTH. HAND.

Re: perl DBI & SQLite
by derby (Abbot) on Jun 04, 2007 at 12:36 UTC

    Check out the sqlite tutorial. The perl specific part is small and near the end but well done.

Re: perl DBI & SQLite
by Anonymous Monk on Oct 15, 2007 at 23:22 UTC
    Hello monks, I'm a newbie to Perl and I've been trying to write a script that will import a data file (XXX.out) into a table (XXX) in a SQLite databse, and
    I would be very appreciative if someone could enlighten me please!
    Everything was going great until I used

    $db->do(".import XXX.out XXX);

    which gave me this error:

    DBD::SQLite::db do failed: near ".": syntax error(1) at dbdimp.c line 271 at ./ line 13.

    I tried using single quotes too, and backslashing the period, but nothing worked. The

    $db->do("create table XXX (@colnames)");

    line earlier in the code worked just fine. It also worked when rather than
    using the .import function I instead used the INSERT function to just insert a few lines.
    But somehow the .import function won't seem to work. (In fact
    the .separator function also gave me the same problem, but I changed the data file so that I wouldn't have to use .separator). In fact, when I just go to sqlite myself and try .import XXX.out XXX it imports the XXX.out file into the XXX table just fine!
    So the problem can't be with the XXX.out data file or the XXX table. But
    somehow I just can't get it to work in my Perl script.
    Can someone please shed some light on this? Thank you!
      Please post this as a top-level question, not as a response to a really old question.
        OK. Sorry and thanks!

Log In?

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

How do I use this? | Other CB clients
Other Users?
Others contemplating the Monastery: (3)
As of 2019-05-26 00:55 GMT
Find Nodes?
    Voting Booth?
    Do you enjoy 3D movies?

    Results (152 votes). Check out past polls.

    • (Sep 10, 2018 at 22:53 UTC) Welcome new users!