db_0or1row (public)

 db_0or1row [ -dbn dbn ] [ -cache_key cache_key ] \
    [ -cache_pool cache_pool ] [ -subst subst ] statement_name sql \
    [ -bind bind ] [ -column_array column_array ] \
    [ -column_set column_set ]

Defined in packages/acs-tcl/tcl/01-database-procs.tcl

Performs the specified SQL query. If a row is returned, sets variables to column values (or a set or array populated if -column_array or column_set is specified) and returns 1.

Switches:

-dbn (optional)

The database name to use. If empty_string, uses the default database.

-cache_key (optional)

Cache the result using given value as the key. Default is to not cache.

-cache_pool (optional, defaults to "db_cache_pool")

Override the default db_cache_pool

-subst (optional, defaults to "all")

Perform Tcl substitution in xql-files. Possible values: all, none, vars, commands

-bind (optional)

bind variables, passed either as an ns_set id, or via bind value list

-column_array (optional)

array to be populated with values

-column_set (optional)

ns_set to be populated with values

Parameters:

statement_name (required)

name of the SQL query

sql (required)

SQL query to be executed

Returns:

1 if variables are set, 0 if no rows are returned. If more than one row is returned, throws an error.

Testcases:

db__caching, db__0or1row, db__1row

Source code:

    # Query Dispatcher (OpenACS - ben)
    set full_statement_name [db_qd_get_fullname $statement_name]

    if { [info exists column_array] && [info exists column_set] } {
        return -code error "Can't specify both column_array and column_set"
    }

    if { [info exists column_array] } {
        upvar 1 $column_array array_val
        unset -nocomplain array_val
    }

    if { [info exists column_set] } {
        upvar 1 $column_set selection
    }

    if { [info exists cache_key] } {
        set values [ns_cache eval $cache_pool $cache_key {
            db_with_handle -dbn $dbn db {
                set selection [db_exec -subst $subst 0or1row $db $full_statement_name $sql]
            }

            set values [list]

            if { $selection ne "" } {
                set values [ns_set array $selection]
            }

            set values
        }]

        if { $values eq "" } {
            set selection ""
        } else {
            set selection [ns_set create s {*}$values]
        }
    } else {
        db_with_handle -dbn $dbn db {
            set selection [db_exec -subst $subst 0or1row $db $full_statement_name $sql]
        }
    }

    if { $selection eq "" } {
        return 0
    }

    if { [info exists column_array] } {
        array set array_val [ns_set array $selection]
    } elseif { ![info exists column_set] } {
        foreach {key value} [ns_set array $selection] {
            uplevel 1 [list set $key $value]
        }
    }

    return 1

XQL Not present:

Generic, PostgreSQL, Oracle