Re: [sqlite] pragma for headers ( pipe or redirect a series of commands)

Bill Hernandez Thu, 18 Jan 2007 10:30:25 -0800

On Jan 18, 2007, at 8:01 AM, [EMAIL PROTECTED] wrote:

I don't know what capability Applescript has. The easy way to doit with ascript in the various Linux shell languages is with a "HERE"document, wherethe input to the command is redirected from the block of lineswhich follows
the command.  In your case, you'd do something like:

    sqlite3 filename.db <<'EOF'
    .headers on
    .mode column customers
    select * from customers ;
    EOF
If Applcscript doesn't support HERE documents (it's unlikely itdoes), you canaccomplish something similar with redirecting from a separatefile. I wouldhope it has that capability. You'd then do something like this tocreate a
"commands" file:

    echo '.headers on' > commands
    echo '.mode column customers' >> commands
    echo 'select * from customers;' >> commands

and then run sqlite using that command file for input:

    sqlite3 filename.db < commands

Hope that helps.

Derrell


Derrell,

Your solution solved my problem to a tee. Using a command file toissue multiple commands to sqlite when calling it from Applescriptworked perfect. I knew that I probably could not mix and match "dotcommands" with "sql commands", but I thought perhaps I could do sousing "pragma & sql" commands.

Thanks for taking the time to help me, especially so quickly. I shutdown my computer about 2:30AM last night, and this morning when Iwoke up around 8:30AM I checked the email, just in case, and Iimmediately saw the light when I read your message...


THANKS A MILLION FOR YOUR HELP...

Best regards,

Bill Hernandez
Plano, Texas

The term AppleScript comes in two flavors (both which are free andcome with the operating system):

( 1 ) AppleScript which allows you to access (set/get) info fromalmost all applications, including the OS itself. It providesincredible flexibility, and power to an average user. Most allcommercial software is scriptable. The interface is not a strongpoint, there are dialogs to display as well as get user input, italso provides choice lists that you can build on the fly, and havethe user pick one or more items, it does a lot more, but there areGUI limitations.

( 2 ) Applescript Studio is full blown rapid application buildenvironment that allows a user to build complete GUI applicationsusing anywhere from a few lines to a few hundred lines of applescriptcode, instead of having to use thousands of lines of Objective Ccode. Not only that but but the xCode environment provides all theinterface items one could ever hope for, along with all code that hasalready been in production to build the OS X operating system itself.

The OSX xCode development environment provides all the interfacetools, and allows the user to use any number of languages to createan application. Once completed you cannot tell what language it waswritten in. So you could write a massive amount of Objective C code,or a few lines of AppleScript code, and the end result once compiledwill be identical.

Anything you build using xCode is automatically scriptable. If Iattach a data source containing the results of a query, to a GUI"Table View" object, anytime the user clicks on any of the columnheaders, the object handles the sorting of the records (up/down)without any code on my part whatsoever, there is no need to query/fetch again from the database just to get the sorted records.

I have been doing structured programming on the Mac for about 19years, and always hated the AppleScript syntax, but a few months agoI decided to really try to learn it in earnest, and I now realizewhat an incredible environment it truly is, it's like the iPod ofdevelopment environments with built in GUI objects, debuggers, etc.Talk about minimal amount of code...

OSX comes with sqlite already installed. A guy named "Adam Bell"wrote a sample AppleScript that showed how to access sqlite, create,search, modify, etc from within AppleScript issuing the commandsdirectly to any one of the Unix Shells. Basically I can haveAppleScript open a shell, issue a numbe of commands, retrieve theresults, massage them, etc, and close the shell when done, or I canrun shell commands in the background so the user never sees the shellitself, which is what I will be doing.

One of the basic handlers (functions) within the script that "AdamBell" wrote, and I used to create the DB and enter a few records :

-- +---------+---------+---------+---------+---------+---------+
on SQLite_CreateDatabase()
   set loc to space & "~/desktop/TestDB.db" & space
   set head to "sqlite3" & loc & quote
   set tail to quote

-- "head" tells SQLite where to put our db if it doesn't exist,identifies it if it does.-- "head" is the opening statement of every future command to ourdb.

   -- "tail" ends every query started with "head".

-- Next, we set up a table and give the columns labels (there canbe several).-- Note the space between the semicolon (which ends every line)and the quote.

   set tblName to "customers"

set newTbl to "create table " & tblName & "(firstname, lastname,country); "


   -- Now we set up the data to be entered in the table
   set d1 to "insert into customers values('Ray', 'Barber', 'USA'); "

set d2 to "insert into customers values('Sancho', 'Panza','Spain'); "

   set d3 to "insert into customers values('Adam', 'Bell', 'Canada'); "

set d4 to "insert into customers values('Bruce', 'Phillips','USA'); "

   set d5 to "insert into customers values('Kim', 'Hunter', 'USA'); "

set d6 to "insert into customers values('Kevin', 'Bradley','USA'); "


   -- And finally, build the SQLite query and execute it
   do shell script head & newTbl & d1 & d2 & d3 & d4 & d5 & d6 & tail
   -- a new db called TestDB.db should appear on your desktop!
end SQLite_CreateDatabase
-- +---------+---------+---------+---------+---------+---------+

Here's the working test script I wrote to get me started.

on run
   -- Create a list to populate the popup menu

set aSearchCriteria to {"begins with", "contains", "is equal to","ends with", "-", "does not begin with", "does not contain", "is notequal to", "does not end with", "-", "is greater than", "is lessthan", "-", "is greater than or equal to", "is less than or equal to"}

-- Build a simple record to use when building commands, this willgrow considerably laterset aQuery to {db_select:"SELECT", db_from:"FROM",db_where:"WHERE", db_order:"ORDER BY", db_howMany:"LIMIT",db_offset:"OFFSET", db_ending:";"}


   -- FOR TESTING PURPOSES SIMULATE SOME USER INPUT.

-- THE USER WILL PROVIDE THESE VALUES VIA THE CHOICES THEY MAKEIN THE SEARCH DIALOG


   set thisTable to "customers"
   set searchField to "lastname"
   set searchValue to ""
   set sortField to "firstname"
   set sortDir to "asc"

   -- SIMULATE SEVERAL REQUESTS FROM THE USER VIA THEIR SEARCH DIALOG

   set searchValue to "Be"
   set whichChoice to "begins with"