Re: Reading SQLite results directly into a list
Re: Reading SQLite results directly into a list
- Subject: Re: Reading SQLite results directly into a list
- From: Bruce Robertson <email@hidden>
- Date: Fri, 30 May 2008 19:04:13 -0700
> Let me rephrase in a new thread, for clarity:
>
> We can get results from an SQLite database, which by default looks like this:
>
> Name|Company|URL
> Tom|BareFeet|http://www.tandb.com.au
> Steve|Apple|http://www.apple.com
>
> In order to make that an AppleScript list, we have to slowly parse through the
> column separators and row separators to result in something like:
>
> {
> {"Name", "Company", "URL"}
> {"Tom", "BareFeet", "http://www.tandb.com.au"}
> {"Steve", "Apple", "http://www.apple.com"}
> }
>
> It would be much faster and easier if we could somehow create or manipulate
> the output from SQLite in a way that could be simply read into a list by
> AppleScript's "read" OSAX command:
>
> read POSIX file "Database Search Results" as list
>
> The question is, how can we change the output of the SQLite select statement
> to be in the format needed for AppleScript to read it in as a native list?
>
> My preliminary tests show that an AppleScript list in a file contains
> something like:
>
> list....
> list....utxt....Nameutxt....Companyutxt....URL
> list....utxt....Tomutxt....BareFeetutxt....http://www.tandb.com.au
> list....utxt....Steveutxt....Appleutxt....http://www.apple.com
>
> where the tabs and newlines are just shown for clarity. The ... four byte
> sequences are the length of the subsequent list (number of items) or text
> (number of characters). But I'm a bit hazy on the actual format of a list in a
> file.
>
> You might think it's just a simple matter of a search and replace of the "|"
> column separators or the newline row separators after the SQLite output but
> the data itself may contain those characters. We can replace the column
> separator using the "-separator" option in the sqlite3 command line tool. I
> don't know of any way to replace the row separator other than to add a column
> to the output.
>
> Here is a fully self contained script that demonstrates this:
>
> property sqlite3Path : "/usr/bin/sqlite3"
> property columnSeparator : "<cSep>"
> property rowSeparator : "<rSep>"
>
> on run
> set databasePath to "MyTestDatabase.sqlite"
> set createData to "
> create table if not exists Contacts(Id integer primary key, Name text, Company
> text, URL text);
> delete from Contacts;
> insert into Contacts(Name, Company, URL) values('Tom', 'BareFeet',
> 'http://www.tandb.com.au');
> insert into Contacts(Name, Company, URL) values('Steve', 'Apple',
> 'http://www.apple.com');
> "
> SQLExecute(databasePath, createData)
> set myQuery to "select Name, Company from Contacts where URL not null"
> SQLExecuteSelect(databasePath, myQuery)
> end run
>
> on SQLExecuteSelect(databasePath, sqlCommand)
> -- 2008 BareFeet http://www.tandb.com.au
> set sqlCommandWithSeparators to "select *, '" & rowSeparator & "' as
> rowSeparator from (" & sqlCommand & ");"
> set shellCommand to "echo " & (quoted form of sqlCommandWithSeparators) & " |
> " & sqlite3Path & space & "-header -separator " & (quoted form of
> columnSeparator) & space & quoted form of databasePath
> set recordsText to do shell script shellCommand
> return recordsText
> end SQLExecuteSelect
>
> on SQLExecute(databasePath, sqlCommand)
> set shellCommand to "echo " & (quoted form of sqlCommand) & " | " &
> sqlite3Path & space & quoted form of databasePath
> set resultText to do shell script shellCommand
> return resultText
> end SQLExecute
>
> The output appears as:
>
> "Name<cSep>Company<cSep>rowSeparator
> Tom<cSep>BareFeet<cSep><rSep>
> Steve<cSep>Apple<cSep><rSep>"
>
> So, how could we change each <cSep> and <rSep> to be the appropriate 8 byte
> code (utxt.... and list.... respectively, but with the right values of ....)
> that the AppleScript "read file as list" OSAX expects?
>
> Thanks,
> Tom
> BareFeet
The problem appears to be the statement "create table if not exists
Contacts"
If the "if not exists" part is removed, it works.
_______________________________________________
Do not post admin requests to the list. They will be ignored.
AppleScript-Users mailing list (email@hidden)
Help/Unsubscribe/Update your Subscription:
Archives: http://lists.apple.com/archives/applescript-users
This email sent to email@hidden