Thanks all for the perspectives. Clearly I misinterpreted the intent of the design of first-result. I'll use one of the provided options. Thomas, an enhancement to the sqlite3 egg would be great if you get a chance.
Matt -=- On Thu, Jun 9, 2022 at 2:34 AM Thomas Chust <thomas.ch...@gmail.com> wrote: > Hello Matt, > > it may be a bit of an oversight that no such procedure exists in the > SQLite3 egg, but you could define something like this: > > (define (one-result/default default db sql . parameters) > (call-with-temporary-statements > (lambda (stmt) > (apply bind-parameters! stmt parameters) > (if (step! stmt) > (column-data stmt 0) > default)) > db sql)) > > [untested, and leaving the usual "overloads" for statement objects vs. SQL > source as an exercise to the reader 😉] > > Ciao, > Thomas > > > Am Mi., 8. Juni 2022 um 20:10 Uhr schrieb Matt Welland <m...@kiatoa.com>: > >> The problem: retrieve one value from a query, if there is no matching >> row return a default. >> >> Method 1: use for-each-row, overwrite the default with found values >> Method 2: use first-result, on exception return the default >> Method 3: use fold-row (wasn't an option when I first wrote the code) >> >> My question is, which of these is the "right" way to do what I want? Is >> there another, better, way? Note that method #2 has problems in my >> program but it might be due to my using chicken 4.10 (for now). >> >> ==additional details== >> >> Method 1, for-each-row, was my original approach and seemed to work >> fine. >> >> Then, in the midst of a major rewrite I tried to use first-result. It >> seemed odd to me to use an exception this way but I assumed (wrongly?) >> it was a fancy software engineery way to do things since the sqlite3 >> egg did not provide any direct way to do what I want. >> >> However I hit a strange scalability issue with database locks happening >> only a few hours into a run. After a lot of fruitless debug and on a >> hunch, I replaced the first-result with a for-each-row and now am able >> to run ten of thousands of tests over many hours (still not perfect, >> work progresses). >> >> I plan to replace use of for-each-row with something like: >> >> (define (get-id db name default) >> (fold-row (lambda (row) >> (car row)) >> #f >> db "SELECT id FROM tests WHERE testname=?" name)) >> >> Thanks. >> >> >> -- -- Complexity is your enemy. Any fool can make something complicated. It is hard to keep things simple. - Richard Branson.
