Parsing Sqlite results

Scripting Forums AppleScript | Mac OS X
#1

I’ve just begun experimenting with accessing sqlite dbs using do shell script. I can access them no problem, can run queries, etc. My question is regarding parsing the results. Each record is returned with each field separated with the pipe character (changeable if you prefer).

The problem I’m having is separating the records. For example, let’s say I have a table of first and last names, and there are 3 entries in that table. The returned results look like this:

Steve|Woodward
Sam|Jones
Bob|Smith

What is actually separating the records? Is it a return character? I’ve played around with changing the way sqlite returns the results, but none of them provide an easy way to access the records individually. I basically need to determine the text delimiter to change to in order to break up the results.

BTW I’m staying away from Database Events…

Thanks for any advice!

#2

You can parse for a pipe. Here’s an example:

set N to paragraphs of "Steve|Woodward
Sam|Jones
Bob|Smith"
set tNames to {}

set tid to AppleScript's text item delimiters
set AppleScript's text item delimiters to "|"
repeat with P in N
	set end of tNames to text items of P
end repeat
set AppleScript's text item delimiters to tid
--> {{"Steve", "Woodward"}, {"Sam", "Jones"}, {"Bob", "Smith"}}

Easy to get from there to whatever form you had in mind.

Have you read this article? A Tutorial Introduction to SQLite3 - a Simple Database Engine. It’s got quite a few examples.

#3

Thanks Adam, that’s most helpful!

I did read that tutorial, it provided a lot of information but I still found myself unsure of how to parse the results. I’m getting closer now…

#4

If you wanted them back as lines of text, or a list of text, this would be the way:


set N to paragraphs of "Steve|Woodward
Sam|Jones
Bob|Smith"
set tNames to {}
set wNames to ""

set tid to AppleScript's text item delimiters
set AppleScript's text item delimiters to "|"
repeat with P in N
	set TI to text items of P
	set end of tNames to TI
	tell TI to set wNames to wNames & item 1 & " " & item 2 & return
end repeat
set pNames to paragraphs 1 thru -2 of wNames --- last one is just a return
set AppleScript's text item delimiters to tid

--> tNames returns {{"Steve", "Woodward"}, {"Sam", "Jones"}, {"Bob", "Smith"}}
--> wNames returns three paragraphs with a final return (quotes on next line)

"Steve Woodward
Sam Jones
Bob Smith
"
--> pNames returns {"Steve Woodward", "Sam Jones", "Bob Smith"}