Re: DBI and SQLite's .read feature (updated)

Hi aplonis,

It's possible to call sqlite3 via system. However, I would strongly recommend that you do all of your SELECTing via DBI, because otherwise you're left parsing the output of the CLI tool. However, I have in the past done something similar to the following, you could change this to .read:

system('sqlite3', $DB_FILE, '.dump')==0
    or die "sqlite3 failed, \$?=$?";
[download]

Update: Added the first sentence above; also I found this in the DBD::SQLite docs:

Processing Multiple Statements At A Time

DBI's statement handle is not supposed to process multiple statements at a time. So if you pass a string that contains multiple statements (a dump) to a statement handle (via prepare or do), DBD::SQLite only processes the first statement, and discards the rest.

If you need to process multiple statements at a time, set a sqlite_allow_multiple_statements attribute of a database handle to true when you connect to a database, and do method takes care of the rest (since 1.30_01, and without creating DBI's statement handles internally since 1.47_01). If you do need to use prepare or prepare_cached (which I don't recommend in this case, because typically there's no placeholder nor reusable part in a dump), you can look at $sth->{sqlite_unprepared_statements} to retrieve what's left, though it usually contains nothing but white spaces.

Hope this helps,
-- Hauke D

Comment on Re: DBI and SQLite's .read feature (updated) Select or Download Code

Replies are listed 'Best First'.
Re^2: DBI and SQLite's .read feature (updated) by aplonis (Pilgrim) on Jan 26, 2017 at 14:33 UTC
Yes, excellent! This worked quite well indeed. `system('sqlite3', $DB_FILE, '.read filename.sql')==0 or die "sqlite3 failed, \$?=$?";` [download] The reason I wanted to do it this way is that the `.sql` in question updates one table by summarizing plural columns from a plurality of other tables, building a number of temporary tables in the process. There are four such `.sql` files for similar summaries, each one a whole page of SQL code, which itself never changes, and which I wished to keep from complicating my Perl. The final query, to be issued from Perl, pulls already-summarized rows from the now easily updatable table. Thank you so much!	[reply] [d/l] [select]

Replies are listed 'Best First'.

Re^2: DBI and SQLite's .read feature (updated)
by aplonis (Pilgrim) on Jan 26, 2017 at 14:33 UTC

Yes, excellent! This worked quite well indeed.

system('sqlite3', $DB_FILE, '.read filename.sql')==0
    or die "sqlite3 failed, \$?=$?";
[download]

The reason I wanted to do it this way is that the *.sql in question updates one table by summarizing plural columns from a plurality of other tables, building a number of temporary tables in the process. There are four such *.sql files for similar summaries, each one a whole page of SQL code, which itself never changes, and which I wished to keep from complicating my Perl.

The final query, to be issued from Perl, pulls already-summarized rows from the now easily updatable table. Thank you so much!

[reply]
[d/l]
[select]