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

Hello monks, I'm new 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 ./new.pl line 13.

I tried using single quotes too, and backslashing the period, together and separately, but it didn't work. The

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

line earlier in the code worked just fine. Also, when I instead used the INSERT function
(i.e., $db->do("INSERT INTO XXX VALUES (@somevalues)");
to just insert a few lines, rather than using .import to import the file, it worked. But somehow the .import function won't seem to work. (I would just use INSERT, but looping through the file to insert line by line takes much longer than .import.)
(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).

Furthermore, when I just go to sqlite myself and type .import XXX.out XXX
it imports the XXX.out file into the XXX table just fine! So the problem seems not to lie 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!

Replies are listed 'Best First'.
Re: SQLite function .import not working
by ikegami (Patriarch) on Oct 16, 2007 at 03:33 UTC
[reply]
[d/l]
[select]
Re: SQLite function .import not working
by Illuminatus (Curate) on Oct 16, 2007 at 13:57 UTC
    First of all, just to be sure, the example you provided had an opening double-quote, but not a closing one. I am assuming that this is a typo. Did you RTFC :)? (I understand that having only one eye can make this kind of hard). I looked at dbdimp.c line 271. It looks to me like the DBD api for SQLite may not use the dot notation for commands. Did you try it without the leading dot? Also, from the code, it looks like it is possible to turn on message tracing. This might help you out as well. Illuminatus
[reply]
      You monks are indeed wise. Thank you very much. I have it now.
      .import is not something the database understands. Therefore, I just tried

      system "sqlite3 databasename \".import filename tablename\"";

      and it worked! I think the same would work for all the other sqlite3 functions that begins with a dot (".").
[reply]
        By the way, you can avoid escaping double-quotes by using the qq operator, as follows: 
        system qq{sqlite3 databasename ".import filename tablename"};

        [download]
[reply]
[d/l]
        But... I need to do
        sqlite3 dbname
.separator \t
.import filename tablename

        [download]
        How can I do that? (put 2 sqlite3 commands on the command line)?
[reply]
[d/l]
      Yes, you are correct, it was just a typo in my post. I did have a closing quote in my code.
[reply]
Re: SQLite function .import not working
by Anonymous Monk on Feb 13, 2010 at 01:41 UTC
    I was wondering if there is a way to use the .commands via DBI:: rather then resorting to a system call etc?
[reply]
      I believe it's possible to add driver-specific methods to DBI database handles, but no one has ported sqlite's functions.
[reply]
[d/l]
        They're not part of sqlite, they're part of the sqlite shell program
[reply]
      No. Those are special commands for the sqlite shell. Yes, you could rewrite the program using perl with DBI.
[reply]

Back to Seekers of Perl Wisdom