Switches:

-dbn (optional)

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

Parameters:

table_name
column_name

Returns:

'numeric' for number type columns, 'text' otherwise Throws an error if no such column exists.

Author:

Yon Feldman <yon@arsdigita.com>

db_0or1row statement-name sql [ -bind bind_set_id | -bind bind_value_list ] [ -column_array array_name | -column_set set_name ]

Performs the SQL query sql. 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. If no rows are returned, returns 0.

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 (defaults to "db_cache_pool") (optional)

Override the default db_cache_pool

Parameters:

statement_name
sql

Returns:

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

db_1row statement-name sql [ -bind bind_set_id | -bind bind_value_list ] [ -column_array array_name | -column_set set_name ]

Performs the SQL query sql. If a row is returned, sets variables to column values (or a set or array populated if -column_array or column_set is specified). If no rows are returned, throws an error.

Returns:

1 if variables are set.

Switches:

-dbn (optional)

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

Switches:

-dbn (optional)

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

Parameters:

statement_name
sql

Switches:

-dbn (optional)

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

TODO: This proc should probably be changed to take a final file argument, only, rather than the current args variable length argument list. Currently, it is called only 4 places in OpenACS, and each place args, if used at all, is always "-file $file". However, such a change might break custom code... I'm not sure. --atp@piskorski.com, 2003/04/09 11:39 EDT

Parameters:

statement_name
sql

Parameters:

bool

Returns:

t or f

Switches:

-dbn (optional)

The database name to use. Uses the default database if not supplied.

Returns:

Call ns_db bouncepool on all pools for the named database.

Switches:

-dbn (optional)

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

Parameters:

table_name
column_name

Returns:

1 if the row exists in the table, 0 if not.

Author:

Lars Pind <lars@pinds.com>

Switches:

-dbn (optional)

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

Parameters:

table_name
column_name

Returns:

the Oracle Data Type for the specified column.
-1 if the table or column doesn't exist.

Author:

Yon Feldman <yon@arsdigita.com>

Changelog:

10 July, 2000: changed to return error if column name doesn't exist (mdettinger@arsdigita.com)

11 July, 2000: changed to return lower case data types (yon@arsdigita.com)

11 July, 2000: changed to return error using the db_string default clause (yon@arsdigita.com)

Switches:

-dbn (optional)

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

Parameters:

table_name

Returns:

a Tcl list of all the columns in the table with the given name.

Author:

Lars Pind <lars@pinds.com>

Changelog:

yon@arsdigita.com 20000711 changed to return lower case column names

Parameters:

db_type

Returns:

1 if the given db_type is compatible with the current RDBMS.

Returns:

the current rdbms type and version.

args can be one of: -clobs, -blobs, -clob_files or -blob_files. See the db-api doc referenced below for more information.

Switches:

-dbn (optional)

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

Parameters:

statement_name
sql

See Also:

• /doc/db-api-detailed.html

PostgreSQL: Performs a pl/pgsql function or procedure call. The caller must perform a select query that returns the value of the function.

Examples:

    # Oracle:
    db_exec_plsql delete_note {
        begin  note.del(:note_id);  end;
    }

    # PostgreSQL:
    db_exec_plsql delete_note {
        select note__delete(:note_id);
    }
    

If you need the return value, then do something like this:

    # Oracle:
    set new_note_id [db_exec_plsql create_note {
        begin
        :1 := note.new(
          owner_id => :user_id,
          title    => :title,
          body     => :body,
          creation_user => :user_id,
          creation_ip   => :peeraddr,
          context_id    => :package_id
        );
        end;
    }]

    # PostgreSQL:
    set new_note_id [db_exec_plsql create_note {
        select note__new(
                         null,
                         :user_id,
                         :title,
                         :body,
                         'note',
                         now(),
                         :user_id,
                         :peeraddr,
                         :package_id
                         );
    }]
    

You can call several pl/sql statements at once, like this:

    # Oracle:
    db_exec_plsql delete_note {
        begin
        note.del(:note_id);
        note.del(:another_note_id);
        note.del(:yet_another_note_id);
        end;
    }

    # PostgreSQL:
    db_exec_plsql delete_note {
        select note__delete(:note_id);
        select note__delete(:another_note_id);
        select note__delete(:yet_another_note_id);
    }
    

yourfilename.tcl:

    db_exec_plsql delete_note { }

yourfilename-oracle.xql:

    <fullquery name="delete_note">      
      <querytext>
        begin
        note.del(:note_id);
        end;
      </querytext>
    </fullquery>

yourfilename-postgresql.xql:

    <fullquery name="delete_note">      
      <querytext>
        select note__delete(:note_id);
      </querytext>
    </fullquery>

Switches:

-dbn (optional)

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

Parameters:

statement_name
sql

See Also:

• /doc/db-api-detailed.html

Switches:

-cache_key_pattern (defaults to "*") (optional)

The "string match" pattern used to flush keys (default is to flush all entries)

-cache_pool (defaults to "db_cache_pool") (optional)

The pool to flush (default is to flush db_cache_pool)

Author:

Don Baccus <dhogasa@pacifier.com>

db_foreach statement-name sql [ -bind bind_set_id | -bind bind_value_list ] [ -column_array array_name | -column_set set_name ] code_block [ if_no_rows if_no_rows_block ]

Performs the SQL query sql, executing code_block once for each row with variables set to column values (or a set or array populated if -column_array or column_set is specified). If the query returns no rows, executes if_no_rows_block (if provided).

Example:

db_foreach greeble_query "select foo, bar from greeble" {
        ns_write "<li>foo=$foo; bar=$bar\n"
    } if_no_rows {
        # This block is optional.
        ns_write "<li>No greebles!\n"
    }

Switches:

-dbn (optional)

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

Parameters:

statement_name
sql

Switches:

-dbn (optional)

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

Returns:

the database name from the first database pool. It assumes the datasource is properly formatted since we've already verified that we can connect to the pool.

Switches:

-dbn (optional)

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

Returns:

the name of the database host from the first database pool. It assumes the datasource is properly formatted since we've already verified that we can connect to the pool.

Switches:

-dbn (optional)

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

Returns:

the password parameter from the driver section of the first database pool for the dbn.

Switches:

-dbn (optional)

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

Returns:

the pgbin parameter from the driver section of the first database pool.

Switches:

-dbn (optional)

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

Returns:

the port number from the first database pool. It assumes the datasource is properly formatted since we've already verified that we can connect to the pool. It returns an empty string for an empty port value.

Switches:

-dbn (optional)

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

Returns:

a valid Oracle user@database/password string to access a database through sqlplus.

This proc may well work for databases other than Oracle, but its return value won't really be of any use.

Switches:

-dbn (optional)

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

Returns:

the username parameter from the driver section of the first database pool for the dbn.

Returns:

a list of three-element lists describing the database engines known to OpenACS. Each sublist contains the internal database name (used in file paths, etc), the driver name, and a "pretty name" to be used in selection forms displayed to the user. The nsv containing the list is initialized by the bootstrap script and should never be referenced directly by user code.

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 (defaults to "db_cache_pool") (optional)

Override the default db_cache_pool

Parameters:

statement_name
sql

Returns:

a Tcl list of the values in the first column of the result of SQL query sql. If sql doesn't return any rows, returns an empty list.

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 (defaults to "db_cache_pool") (optional)

Override the default db_cache_pool

Parameters:

statement_name
sql

Returns:

a Tcl list, each element of which is a list of all column values in a row of the result of the SQL querysql. If sql doesn't return any rows, returns an empty list. It checks if the element is I18N and replaces it, thereby reducing the need to do this with every single package

Switches:

-dbn (optional)

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

Parameters:

statement_name - The name of the query.
sql - The SQL to be executed.

Returns:

a list of ns_sets with the values of each column of each row returned by the sql query specified.
list of ns_sets, one per each row return by the SQL query

Switches:

-dbn (optional)

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

-callback (defaults to "apm_ns_write_callback") (optional)

Parameters:

file

Switches:

-local (boolean) (optional)

-append (boolean) (optional)

-upvar_level (defaults to "1") (optional)

-unclobber (boolean) (optional)

If set, will cause the proc to not overwrite local variables. Actually, what happens is that the local variables will be overwritten, so you can access them within the code block. However, if you specify -unclobber, we will revert them to their original state after execution of this proc. Usage:

db_multirow [ -local ] [ -upvar_level n_levels_up ] [ -append ] [ -extend column_list ] var-name statement-name sql [ -bind bind_set_id | -bind bind_value_list ] code_block [ if_no_rows if_no_rows_block ]

Performs the SQL query sql, saving results in variables of the form var_name:1, var_name:2, etc, setting var_name:rowcount to the total number of rows, and setting var_name:columns to a list of column names.

If "cache_key" is set, cache the array that results from the query *and* any code block for future use. When this result is returned from cache, THE CODE BLOCK IS NOT EXECUTED. Therefore any values calculated by the code block that aren't listed as arguments to "extend" will not be created. In practice this impacts relatively few queries, but do take care.

You can not simultaneously append to and cache a non-empty multirow.

Each row also has a column, rownum, automatically added and set to the row number, starting with 1. Note that this will override any column in the SQL statement named 'rownum', also if you're using the Oracle rownum pseudo-column.

If the -local is passed, the variables defined by db_multirow will be set locally (useful if you're compiling dynamic templates in a function or similar situations). Use the -upvar_level switch to specify how many levels up the variable should be set.

You may supply a code block, which will be executed for each row in the loop. This is very useful if you need to make computations that are better done in Tcl than in SQL, for example using ns_urlencode or ad_quotehtml, etc. When the Tcl code is executed, all the columns from the SQL query will be set as local variables in that code. Any changes made to these local variables will be copied back into the multirow.

You may also add additional, computed columns to the multirow, using the -extend { col_1 col_2 ... } switch. This is useful for things like constructing a URL for the object retrieved by the query.

If you're constructing your multirow through multiple queries with the same set of columns, but with different rows, you can use the -append switch. This causes the rows returned by this query to be appended to the rows already in the multirow, instead of starting a clean multirow, as is the normal behavior. The columns must match the columns in the original multirow, or an error will be thrown.

Your code block may call continue in order to skip a row and not include it in the multirow. Or you can call break to skip this row and quit looping.

Notice the nonstandard numbering (everything else in Tcl starts at 0); the reason is that the graphics designer, a non programmer, may wish to work with row numbers.

Example:

db_multirow -extend { user_url } users users_query {
    select user_id first_names, last_name, email from cc_users
} {
    set user_url [acs_community_member_url -user_id $user_id]
}

-extend (optional)

-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 (defaults to "db_cache_pool") (optional)

Override the default db_cache_pool

Parameters:

var_name
statement_name
sql

See Also:

• template::multirow

This is useful when you want to build up a multirow for a master/slave table pair, where you only want one row per row in the master table, but you want to include data from the slave table in a column of the multirow.

Here's an example:

    # Initialize the lines variable to hold a list of order line summaries
    set lines [list]

    # Start building the multirow. We add the dynamic column 'lines_pretty', which will
    # contain the pretty summary of the order lines.
    db_multirow -extend { lines_pretty } orders select_orders_and_lines { 
        select o.order_id, 
               o.customer_name,
               l.item_name,
               l.quantity
        from   orders o,
               order_lines l
        where  l.order_id = o.order_id
        order  by o.order_id, l.item_name
    } {
        lappend lines "$quantity $item_name"
        if { [db_multirow_group_last_row_p -column order_id] } {
            # Last row of this order, prepare the pretty version of the order lines
            set lines_pretty [join $lines ", "]

            # Reset the lines list, so we start from a fresh with the next row
            set lines [list]
        } else {
            # There are yet more order lines to come for this order,
            # continue until we've collected all the order lines
            # The 'continue' keyword means this line will not be added to the resulting multirow
            continue
        }
    }

Switches:

-column (required)

The name of the column defining the groups.

Returns:

1 if this is the last row before the column value changes, 0 otherwise.

Author:

Lars Pind <lars@collaboraid.biz>

Switches:

-dbn (optional)

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

Returns:

the name of the database as reported by the driver.

     set new_object_id [db_nextval acs_object_id_seq]
     

Switches:

-dbn (optional)

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

Parameters:

sequence - the name of an sql sequence

Returns:

the next value for a sequence. This can utilize a pool of sequence values.

See Also:

• /doc/db-api-detailed.html

Switches:

-dbn (optional)

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

Parameters:

n

Returns:

the name of the pool used for the nth-nested selection (0-relative).

Returns:

an empty string, which Oracle thinks is null. This routine was invented to provide an RDBMS-specific null value but doesn't actually work. I (DRB) left it in to speed porting - we should really clean up the code an pull out the calls instead, though.

Parameters:

string

Parameters:

db_type_list

Returns:

1 if db_type_list contains the current RDMBS type. A package intended to run with a given RDBMS must note this in it's package info file regardless of whether or not it actually uses the database.

See Also:

• apm_package_supports_rdbms_p

Parameters:

string

Switches:

-dbn (optional)

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

Switches:

-dbn (optional)

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

Returns:

the number of rows affected by the last DML command.

Switches:

-dbn (optional)

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

-callback (defaults to "apm_ns_write_callback") (optional)

Parameters:

file

Sources a SQLJ file using loadjava.

Switches:

-dbn (optional)

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

-callback (defaults to "apm_ns_write_callback") (optional)

Parameters:

file

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 (defaults to "db_cache_pool") (optional)

Override the default db_cache_pool

Parameters:

statement_name
sql

Returns:

the first column of the result of the SQL query sql. If the query doesn't return a row, returns default or raises an error if no default is provided.

Switches:

-dbn (optional)

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

Parameters:

table_name

Returns:

1 if a table with the specified name exists in the database, otherwise 0.

Authors:

Don Baccus <dhogaza@pacifier.com>

Lars Pind <lars@pinds.com>

Switches:

-pattern (optional)

Will be used as LIKE 'pattern%' to limit the number of tables returned.

-dbn (optional)

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

Returns:

a Tcl list of all the tables owned by the connected user.

Authors:

Don Baccus <dhogaza@pacifier.com>

Lars Pind <lars@pinds.com>

Changelog:

yon@arsdigita.com 20000711 changed to return lower case table names

To handle errors, use db_transaction {transaction_code} on_error {error_code_block}. Any error generated in transaction_code will be caught automatically and process control will transfer to error_code_block with a variable errmsg set. The error_code block can then clean up after the error, such as presenting a usable error message to the user. Following the execution of error_code_block the transaction will be aborted. If you want to explicity abort the transaction, call db_abort_transaction from within the transaction_code block or the error_code block.

Example 1:
In this example, db_dml triggers an error, so control passes to the on_error block which prints a readable error.

    db_transaction {
        db_dml test "nonsense"
    } on_error {
        ad_return_error "Error in blah/foo/bar" "The error was: $errmsg"
    }
    

    db_transaction {
        db_dml test {insert into footest values(1)}
        nonsense
        db_dml test {insert into footest values(2)}
    } 
    

Switches:

-dbn (optional)

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

Parameters:

transaction_code

Returns:

the RDBMS type (i.e. oracle, postgresql) this OpenACS installation is using. The nsv ad_database_type is set up during the bootstrap process.

Returns:

the RDBMS version (i.e. 8.1.6 is a recent Oracle version; 7.1 a recent PostgreSQL version.

Switches:

-dbn (optional)

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

Parameters:

db
code_block

Switches:

-dbn (optional)

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

Parameters:

statement_name
sql

Switches:

-dbn (optional)

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

Parameters:

statement_name
sql