Re: How best to get a default result from sqlite3, or to use or not use first-result?

[Thomas Chust]

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 :

> 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.
>
>
>

Re: How best to get a default result from sqlite3, or to use or not use first-result?

[John Cowan]

IMO this is an excellent application for folding.

On Wed, Jun 8, 2022 at 10:54 AM Matt Welland  wrote:

> 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.
>
>
>

How best to get a default result from sqlite3, or to use or not use first-result?

[Matt Welland]

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.