Long time visitor at this site but I think this maybe my first question posted. The information found here has solved every problem I’ve encountered with my, very limited knowledge of, AppleScript until now.
Scenario:
I am building an Applescript Studio application that stores broadband usage stats, by date, in an sqlite3 database. (As an aside I have developed a widget that shows broadband usage stats and am expanding the information being made available to users of the widget - but the widget has nothing to do with the problem).
The stats being stored in the db are: Date, Download, Upload, Free. I create the fields in the database as date, and integer float.
The source data comes from an XML file retrieved, indirectly, from Australia’s largest ISP - called Bigpond.
I am aware that dates in sqlite must be in the format “yyyy-mm-dd hh:mm:ss” although it appears, maybe, that declaring an sqlite field as date one can use a “yyyy-mm-dd” format
The source dates are in the form “04 Feb 2008” which I convert to a string “2008-02-04” and insert this into the date field of the sqlite database … apparently successfully.
I am not using Database Events - everything is done using “do shell script”
The Problem:
Queries against the database by a date or date range are not working - the query either returns nothing or every row. These queries are either in Applescript or using MesaSQLite.
As the date based queries I’ve tried both these:
set tblQuery to "SELECT pkid, date(date), total, download, upload, free from daily_totals where date >= " & quote & sDate & quote & " and date is <= " & quote & eDate & quote & " ORDER BY date DESC;" & space
and
set tblQuery to "SELECT pkid, date(date), total, download, upload, free from daily_totals where date >= " & sDate & " and date is <= " & eDate & " ORDER BY date DESC;" & space
The create and and insert routines snippets are:
set newTbl to "create table " & tblName & "(pkid INTEGER PRIMARY KEY AUTOINCREMENT, date DATE, total INTEGER FLOAT, download INTEGER FLOAT, upload INTEGER FLOAT, free INTEGER FLOAT, note TEXT); "
and …
set d1 to "insert into daily_totals (date, total, download, upload, free) values('" & theDate & "'," & thisTotal & "," & thisDownload & "," & thisUpload & "," & thisFree & "); "
and date conversion snippet for the inserted field “theDate”:
set eachWord to every word of theDate as list
set aDay to item 1 of eachWord as text
set aMonth to item 2 of eachWord as text (somewhere else I add a leading zero if month is less than 10)
set aYear to item 3 of eachWord as text
set theDate to quote & aYear & "-" & aMonthTxt & "-" & aDay & quote as string
I’ve also tried
set theDate to aYear & "-" & aMonthTxt & "-" & aDay as string
All to no avail. “Where” based queries against the database do not work.
I am suspicious of the format of the date data I am inserting into the database.
Any suggestions or advice?
Cheers
MacP
www.macpomme.com