Forum OpenACS Development: Re: db_1row default return

Collapse
3: Re: db_1row default return (response to 1)
Posted by Gustaf Neumann on 05/19/19 11:34 AM

Probably, the original designer left out a -default flag intentionally for these commands, since these do not return a single value, but some potentially a version dependent set of output variables, which are bound on success (e.g. "select * from ...").

On queries returning an unknown amount of tuples, use db_foreach, db_list_of_lists, etc. If you want to get a list of Tcl dicts, one can convert ns_sets with little effort into dicts:

set db_list_of_dicts [lmap s [db_list_of_ns_sets _ {
   select * from acs_objects limit 5
}] {ns_set array $s}]

all the best
-gn

Collapse
4: Re: db_1row default return (response to 3)
Posted by Iuri Sampaio on 05/20/19 12:26 AM
Yes, that's right. it returns a set of output variables.

I'm not suggesting to change that. My suggestion is in case of no results were returned in the recordset. Then instead a throwing errors, a null array or a simple null value must be returned.

One would avoid to use [catch {...} errmsg] all the time db_1row is used.

Does it make sense?

p.s. I'm curious to understand 100% of your last post!!
but your code didn't run. It returned error.

ERROR:
wrong # args: should be "lmap list proc_name"
while executing
"lmap s [db_list_of_ns_sets _ {
select * from acs_objects limit 5
}] {ns_set array $s}"
("uplevel" body line 1)
invoked from within
"uplevel 1 [string map {"\\\r\n" " "} $script]"

I amended it, chaging _ to get_obj and added square brackets to {ns_set array $s} as in

I. wiithin curly braquets {}, ns_set command is not recognized.

set db_list_of_dicts [lmap s [db_list_of_ns_sets get_objs {
select * from acs_objects limit 5
}] [{ns_set array $s}]]]

ERROR:
invalid command name "ns_set array $s"
while executing
"{ns_set array $s}"
("uplevel" body line 3)
invoked from within
"uplevel 1 [string map {"\\\r\n" " "} $script]"

II. and if I remove curly braquets {}, I get another error.

set db_list_of_dicts [lmap s [db_list_of_ns_sets get_objs {
select * from acs_objects limit 5
}] [ns_set array $s]]]

ERROR:
can't read "s": no such variable
while executing
"ns_set array $s"
("uplevel" body line 4)
invoked from within
"uplevel 1 [string map {"\\\r\n" " "} $script]"

Collapse
5: Re: db_1row default return (response to 4)
Posted by Gustaf Neumann on 05/20/19 10:36 AM

you wrote: "avoid catch ?"

Yes, avoiding catch is in general a good strategy. One thing is replace the all-exceptions-swallowing catch by an ad_try, at least in situations, where meaningful error codes are used (which is not the case for current db_* commands).

But notice that this exception is only raised by by db_1row, the function db_0or1row returns exactly such a value expressing success or failure. Follow these guidelines:

  • SQL query has to return exactly 1 tuple: db_1row - otherwise something is broken
  • SQL query may return at most 1 tuple: db_0or1row - both outcomes are permissible
  • all other cases see previous post in this thread

Concerning error with lmap: Until about tree years ago, OpenACS hat an ad_proc named lmap, which was nowhere used, but conflicts with the Tcl 8.6 built in with the same name (released 2012) [1]. Therefore, the proc was move to the acs-outdated package. Your error indicates that you are using such an old version or you are requiring acs-outdated. You should consider updating.

Below is a simple version of lmap implemented by Richard Suchenwirth. If you overload the lmap in your system with this version, the error will go away.

proc lmap {_var list body} {
    upvar 1 $_var var
    set res {}
    foreach var $list {lappend res [uplevel 1 $body]}
    set res
}

[1] https://www.tcl.tk/man/tcl/TclCmd/lmap.htm