San Juan Island Quest

After about a seven year hiatus, I competed in another adventure race this past Saturday. I saw a actually saw poster for the San Juan Island Quest while riding the ferry and thought it sounded like fun. Although they offered a 12-hour course as well, I decided that the 6-hour course was a better idea, so I signed up, loaded up my truck, and headed over to beautiful Orcas Island.

Race Headquarters

My bike getting ready for the event

The competition milling about

After a bit of delay, we got started at 12:15. First off was the mountain biking leg, which required a long, steep climb and then some fast, fun downhill. Unfortunately, it seemed like more up than down to me, but it was a beautiful area and I couldnt complain at all.

Checkpoint #2

Then it was on to the kayaking. The sun was just coming out and the water was smooth as glass. Great day to be out on the lake paddling.

Just completed the kayaking leg

After a short bike back to the HQ, it was off to the trekking portion. I am not a runner, but I used some good navigation to pick up a few places by being smart instead of fast.

Cascade Falls

Atop Sunrise Rock

After the last checkpoint, we had to canoe a couple hundred yards and then return to the finish line. I came in around 6:25pm or so, putting me just barely over the 6-hour mark. Out of 14 teams, I came 7th, which I was pretty happy with. (The guy who won runs ultra-marathons in the Alps for a hobby, so I didnt feel too bad).

Anyway, it was a great course, beautiful day, and overall fun event. Definitely planning on doing it again next year!


SQLite on WinRT bug

tl;dr SQLite on WinRT needs to have its temp directory manually specified to avoid random silent query execution failures. Skip to the code

While working on a Windows 8 app that uses a SQLite database, I was running into a very strange bug. I would construct a query that would work fine, but when I added certain columns to the ORDER BY clause, suddenly no results would be returned. I ruled out syntax issues since the same queries worked fine when ran in SqlSpy. After much debugging, I finally decided it was just a bug since I was using the Release Preview of Windows 8, beta version of VS 2012, and a pre-release version of SQLite with preliminary support for WinRT.

Fast-forward a few months and the bug cropped up again. This time, I added an additional single column to the SELECT clause and the query broke. By now, I had upgraded to the RTM version of Windows 8 and was using the official WinRT build of SQLite so I was less confident that it was just a beta bug. I decided that I needed to dig deeper.

Whenever the issue cropped up, the SQLite.Step() command would return 'CannotOpen', which the documentation simply says means: "Unable to open the database file". A call to SQLite.GetErrorMsg() returned "library routine called out of sequence" for which the documentation listed several possible causes, but none of them really applied. The database was definitely opened correctly and not closed unexpectedly. The calling code was isolated to single thread, so no wonkiness there. The issue was occuring on the Step() command, but the statement pointer was definitely prepared properly and valid.

At this point, I could only assume the issue was deep in the bowels of the sqlite3.dll and I was not sure what to do. I googled a bunch of things on hunches, but nothing was coming up. Finally, I stumbled across a posting by a guy having a semi-similar problem. He was executing statements inside of a transaction and having some work and some fail. Several folks had suggestions on things to try, but nothing worked. Finally, Joe Mistachkin replied with this nuget of wisdom:

Setting the sqlite3_temp_directory to the value contained in the "Windows.Storage.ApplicationData.Current.TemporaryFolder.Path" property should clear the issue. This can be done immediately after opening the connection using PRAGMA temp_store_directory command on the newly opened database connection.

Of course! WinRT apps cant access the full file system the same way that normal desktop apps can. In fact, by default, they can only access their own installation location. Apparently, sometimes SQLite needs to write to temporary files in order to execute queries and perhaps my changes were just enough to cause the database engine to need to use such a file. Joe's advice made sense in that SQLite for WinRT would need to be told where it was allowed to store temporary files.

A quick look at the temp_store_directory documentation made it clear that this PRAGMA was deprecated and should not be used. (For those that dont know (like I didnt), a PRAGMA statement is a SQLite-specific command that can be used to modify the operation of the SQLite library.) Now that I knew the cause of the issue, my googling was better focused and I found this advice that it was fine to use the temp_store_directory setting in this case.

So armed with a potential solution, I coded up a quick test and it did indeed solve the issue - success at last! For those using Frank A. Krueger's sqlite-net wrapper, here are the changes required (all in the SQLiteConnection class):

static bool isTempStoreSet;

public SQLiteConnection(string databasePath, bool storeDateTimeAsTicks = false)
    DatabasePath = databasePath;
    Sqlite3DatabaseHandle handle;
    var r = SQLite3.Open(DatabasePath, out handle);
    Handle = handle;
    if (r != SQLite3.Result.OK)
        throw SQLiteException.New(r, String.Format("Could not open database file: {0} ({1})", DatabasePath, r));
    _open = true;

    /* NOTE: Added to ensure that the temp directory is correctly set for WinRT apps.
     * See:
     * */
    if (!isTempStoreSet)
        Execute(String.Format("PRAGMA temp_store_directory = '{0}'", Windows.Storage.ApplicationData.Current.TemporaryFolder.Path));
        tempStoreSet = true;
    /* END NOTE */

    StoreDateTimeAsTicks = storeDateTimeAsTicks;

    BusyTimeout = TimeSpan.FromSeconds(0.1);

That was one of the longest bug-hunting expeditions I had been on for a long time, so I was glad to finally get it sorted. Thanks to the wonderful internet for being filled with folks much smarter than me for doing most of the hard work - I just wrote it up here so it was all in one place and hopefully will help somebody else out in the future.



Hope you found the peace that eluded you in life.


Splashy Fun on the Sauk

Got out kayaking on the middle section (Whitechuck to Backman Park) of the Sauk this past Saturday and it was a great time. Beautiful sunny day and fun whitewater - not the worst way to spend the day!

(You can see me flip just after 4 minutes in, and the best whitewater is about 5 minutes in if you are just looking for the good stuff).


Summer Olympic Traverse

Well, this summer's big adventure was to be a 116 mile traverse of the entire Olympic Peninsula by foot and boat. Things didnt quite go as planned, but we still had a good time anyway. Here is a glimpse of what we saw and how things turned out: