You can use the (undocumented) ".mode off" command to get the same effect, I think.

Almost!

I should be able to put .mode off before my PRAGMA statement, and mode list after it.

But, I build a variable containing all my lines of SQL code (using a "here" document), and then pipe it to the sqlite3 command. If I do that within a bash accept, it works perfectly.

> cat test2c.sh
#! /bin/bash
cat << EOF | sqlite3 test.db
.mode off
  PRAGMA busy_timeout=5000;
.mode list
  INSERT INTO TestTable (col1, col2)
     VALUES (123, $$);
  SELECT * FROM TestTable
     WHERE col2 = $$;
EOF
> ./test2c.sh
123|3549946
>

But if I do that within a perl script, it complains.

> cat test2d.pl
#! /usr/bin/perl
my $sqlcmd = << "END_OF_SQL";
.mode off
  PRAGMA busy_timeout=5000;
.mode list
  INSERT INTO TestTable (col1, col2)
     VALUES (123, $$);
  SELECT * FROM TestTable
     WHERE col2 = $$;
END_OF_SQL
`sqlite3 test.db "$sqlcmd"`;
> ./test2d.pl
extra argument: "busy_timeout=5000;"
>

I think it is combining the .mode off and the PRAGMA statements into a single line. I tried adding a semi-colon after the .mode line, doesn't make any difference.

I will be running this on both a Linux machine and my Windows laptop, so I can't use bash (or I would).

Any ideas?

If you want to replicate piping the script into SQLite in Perl like you did in Bash, you can do something like this:

#!/usr/bin/perl
my $sqlcmd = << "END_OF_SQL";
.mode off
  PRAGMA busy_timeout=5000;
.mode list
  INSERT INTO TestTable (col1, col2)
     VALUES (123, $$);
  SELECT * FROM TestTable
     WHERE col2 = $$;
END_OF_SQL
open SQLITE, "|sqlite3 test.db > temp_output";
print SQLITE $sqlcmd;
close SQLITE;
open OUTPUT, "temp_output";
$results = <OUTPUT>;
close OUTPUT;
unlink("temp_output");

print "Results = [" . $results . "]\n";

It's possible to also pipe the output so you don't need the temporary file, but that's an exercise for you, as it's a Perl specific feature, not anything to do with SQLite. Or, I'd very much suggest using DBD::SQLite to avoid this rather fragile way of interacting with a SQLite database.

Thanks, but if I'm going to have to jump through hoops for each and every SQL command I use in my code, I'll grep out the PRAGMA output in a subroutine:

> cat test2d.pl
#! /usr/bin/perl

sub sql_cmd {
  my ($dbname, $cmd) = @_;
  $timeout = 5135; # a pseudo-unique number around 5000
  $cmd = "PRAGMA busy_timeout=$timeout;\n$cmd";
  chomp (@rows = `sqlite3 $dbname "$cmd"`);
  @rows = grep (!/^$timeout$/, @rows);
  return @rows;
}

my $sqlcmd = << "END_OF_SQL";
  INSERT INTO TestTable (col1, col2)
    VALUES (123, $$);
  SELECT * FROM TestTable;
END_OF_SQL
@lines = sql_cmd ("test.db", "$sqlcmd");

foreach $line (@lines) {
  print "$line\n";
}
> ./test2d.pl
123|3572361
>

I'll just make sure I put that subroutine in every perl script I write.

I still think it should be part of the SQLite package to suppress PRAGMA output, but if I have to go the long way around, I guess I'll have to.

(And there are complicated reasons why I'm not using DBD::SQLite, that are too long to go into here, and aren't really part of this issue. The product should work as-is, without expecting you to use a specific perl module.)

you could factor out your PRAGMA-part to a file and load that with the -init option

sqlite3 -init file_with_modeoff_pragma_modelist.sql test.db "$sqlcmd_without_the_pragmastuff"

or daisy-chain them up with -cmd options

sqlite3 -cmd '.mode off' -cmd 'PRAGMA busy_timeout=5000' -cmd '.mode list' test.db "$sqlcmd_without_the_pragmastuff"

or use the variant with multiple SQL arguments as recently (re)discovered and documented 82fc67070f9aff00 / cli extra command line arguments

sqlite3 test.db '.mode off' 'PRAGMA busy_timeout=5000' '.mode list' "$sqlcmd_without_the_pragmastuff"

I am aware that this can currently be simulated somewhat by specifying .output /dev/null if you are running on a Linux OS, or.output null if running under Windows. But in my case, my code will be running in both environments.

What if:

sqlite> .once off

was simply an alias for one of:

sqlite> .once /dev/null
sqlite> .once nul

depending on whether it's built for Windows? Would that solve your problem?

i've got a local patch for that, drh's approval to make it so, and it seems to work on Linux and Windows, but i'd like confirmation that that is functionally what you're looking for before committing it:

$ f diff
Index: src/shell.c.in
==================================================================
--- src/shell.c.in
+++ src/shell.c.in
@@ -10269,11 +10269,19 @@
           showHelp(p->out, azArg[0]);
           rc = 1;
           goto meta_command_exit;
         }
       }else if( zFile==0 && eMode==0 ){
-        zFile = sqlite3_mprintf("%s", z);
+        if( cli_strcmp(z, "off")==0 ){
+#ifdef _WIN32
+          zFile = sqlite3_mprintf("nul");
+#else
+          zFile = sqlite3_mprintf("/dev/null");
+#endif
+        }else{
+          zFile = sqlite3_mprintf("%s", z);
+        }
         if( zFile && zFile[0]=='|' ){
           while( i+1<nArg ) zFile = sqlite3_mprintf("%z %s", zFile, azArg[++i]);
           break;
         }
       }else{

That looks like it would work for me.

That looks like it would work for me.

That's now in the trunk.

though neither

.once off
PRAGMA busy_timeout=5000;
SELECT ... ;

nor

.output off
PRAGMA busy_timeout=5000;
.output
SELECT ... ;

is any better than

.mode off
PRAGMA busy_timeout=5000;
.mode list
SELECT ... ;

if the linebreak gets lost between the .xxx commands, as wdixon showed.

On the contrary, '.once' behaves strangely (also before that change ) when used with -cmd or as "one of many" argument, it changes output not only for one statement but for all of them:

sqlite3 :memory: ".once test.txt" "PRAGMA busy_timeout=5000;" "SELECT 88;" "SELECT 99;"
sqlite3 -cmd ".once test2.txt" :memory: "PRAGMA busy_timeout=5000; SELECT 88; SELECT 99;"

Both do not output anything but write 3 lines to test.txt/test2.txt :

5000
88
99