Database Query

Summary

Utilize the results of a database query as a template data source.

Method

query name structure sql -db dbhandle 
                             -startrow n 
                             -maxrows n
                             -bind (set|list)
                             -eval { code }

Perform a query and store the results in a local variable.

Examples

set db [ns_db gethandle]

# this will set a scalar named current_time
template::query current_time onevalue "select sysdate from dual" -db $db

# this will set a single array named user_info with a key for each column
template::query user_info onerow "select * from users 
                                  where user_id = 86" -db $db

# this will set an array for each row returned by the query
# the arrays will be named user_info:1, user_info:2, etc.
# the variable user_info:rowcount will be set with the total number of rows.
template::query user_info multirow "select * from users" -db $db

# this will set a list named emails
template::query emails onelist "select email from users" -db $db

# this will set a list of lists named user_info
template::query user_info multilist "select * from users" -db $db

# this will create a nested list of lists in the form
# { California { Berkeley { { Ralph Smith } { Doug Jones } } } \
#   Minnestota { Minneapolis { { Ina Jaffe } { Silvia Pojoli } } } }
template::query persons nestedlist "
  select state, city, first_name, last_name from users" \
  -db $db -groupby { state city }

Note(s)

• Valid values for structure are onevalue, onerow, multirow, onelist, nestedlist and multilist.
• sql may be any valid SQL statement whose result set has the appropriate dimensions for the desired structure.
• The db parameter is optional. If no parameter is supplied, a handle will be requested to perform the query and then released immediately.
• The startrow and maxrows parameters are valid only for multirow queries. They may be specified to limit the rows from the query result that are included in the data source.
• The eval parameter takes a block of Tcl code to perform on each row of a multirow query as it is fetched from the database. The code may refer to the row array to get and set column values.
• The bind option is valid only when using Oracle.