sql query works on command line, fails in th1

(1) By matt w. (maphew) on 2021-08-31 09:30:56 [source]

The following snippet works in fossil sql but fails in /admin_th1 web ui. Is it a known limitation that max() doesn't work in th1 or do I have a syntax error?

In /admin_th1 the below yields "no such variable: rid"

query {
    select max(rid),value from tagxref where value like 'trunk'
    } {
    puts "$rid, $value\n"
    }

But is no problem in fossil sql:

$ fossil sql                                                        
SQLite version 3.36.0 2021-06-18 18:36:39                      
Enter ".help" for usage hints.                                 
sqlite> select max(rid),value from tagxref where value like 'trunk';
446,'trunk'                                                    
sqlite>

Additionally: in th1 ending an sql code sequence with ; creates an endless loop, both in /admin_th1 web ui and command line fossil test-th-eval ...

(2) By matt w. (maphew) on 2021-08-31 09:36:16 in reply to 1 [link] [source]

Hah! figured it out. Th1 is creating a variable name that includes the name of the sql function.

fails: puts "$rid, $value\n"
works: puts "$max(rid), $value\n"

Interestingly the max() function still works: only one record is returned.

(3) By Andreas Kupries (aku) on 2021-08-31 09:43:19 in reply to 2 [link] [source]

In that case how about trying to use AS syntax. I.e:

select max(rid) AS themax, value from tagxref where value like 'trunk'

You should then get a variable named themax.

(4) By matt w. (maphew) on 2021-08-31 10:10:38 in reply to 3 [link] [source]

that's cleaner and works, thank you! Though I'm using max(rid) as rid to keep the number of variable names I'm creating and remembering down.