You mostly worked it out before the time I finished writing this out. Be sure to use quoted form of for all your variable command line arguments (like if you plan on using different separators; even that one with ampersands should still need it since ampersands are special to the shell).
I assume you want to do a query after setting the separator. You might want to try one of these methods:
set thePath to "/Users/me/Library/Application Support/Snippit/snippitDB.db"
set theQuery to ".separator '&|&'"
set thePath to "/tmp/foo.db"
set theSeparator to "&|&"
set theQuery to "select * from bar;"
-- Use -separator to set the separator.
set a to do shell script ("sqlite3 -separator " & quoted form of theSeparator & space & quoted form of thePath & space & quoted form of theQuery)
-- Use echo and create a multiple lines of input for the sqlite3 program.
set b to do shell script "(echo .separator " & quoted form of my quoteForDotSeparator(theSeparator) & ";echo " & quoted form of theQuery & ")|sqlite3 " & quoted form of thePath
set theCmdFilePOSIXPath to "/tmp/foo.sql"
set myLF to ASCII character 10 -- can use linefeed in OS 10.5
-- Put .separator in init file. Do query on command line.
".separator " & my quoteForDotSeparator(theSeparator) & myLF
my createFile(theCmdFilePOSIXPath, result)
set c to do shell script "sqlite3 -init " & quoted form of theCmdFilePOSIXPath & space & quoted form of thePath & space & quoted form of theQuery
-- Put .separator and query in init file.
".separator " & my quoteForDotSeparator(theSeparator) & myLF & theQuery & myLF
my createFile(theCmdFilePOSIXPath, result)
set d to do shell script "sqlite3 -init " & quoted form of theCmdFilePOSIXPath & space & quoted form of thePath
{a = b, a = c, a = d, a, b, c, d} -- test that results are identical
to replace(s, orig, new)
-- Adapted From: http://bbs.applescript.net/viewtopic.php?pid=41257#p41257
-- By: kai, Nigel Garvey
set {otid, text item delimiters} to {text item delimiters, {orig}}
set s to text items of s
set text item delimiters to {new}
tell s to set s to beginning & ({""} & rest)
set text item delimiters to otid
s
end replace
to quoteForDotSeparator(s)
-- "quoted form of" is not sufficient for this purpose
my replace(s, "\"", "\\\"")
my replace(result, "'", "\\'")
end quoteForDotSeparator
to createFile(pp, s)
set f to POSIX file pp
set r to open for access f with write permission
set eof of r to 0
write s to r starting at 0 as «class utf8» -- At least it is not UTF-16.
close access f
end createFile
As far as I know, the separator is only set per SQLite process, not per database. As soon as the sqlite3 process exits (do shell script won’t have much to return until the sqlite3 process exits), the setting is “lost”. The .separator command is not an SQL command/query, but an SQLite-specific meta-command (that is why it starts with a dot).
It seems that one can only issue one “command” via the sqlite3 command-line. Since the SQLite meta-commands cannot be strung together with other commands via semicolons it prevents one from appending an actual query in addition to setting the separator. Above I show four different ways of setting the separator and running a query (-separator and command-line SQL, .separator and query on stdin (via echo), .separator in init file and query on command line, .separator and query in init file). There are other permutations available, too (.separator in init file and query on command line), but those should give you an idea.
I think I prefer the first for simplicity.
Model: iBook G4 933
AppleScript: 1.10.7
Browser: Safari 3.0.4 (523.12)
Operating System: Mac OS X (10.4)
