db_abort_transaction
	  

Aborts all levels of a transaction. That is if this is called within several nested transactions, all of them are terminated. Use this instead of db_dml "abort" "abort transaction".

db_multirow [ -local ] [ -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.

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).

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]
}
    

You can also iterate over a multirow after it has been created - check the documentation for template::multirow

For example,

db_multirow assets assets {
  select asset_id,
    from ...
}

..

set asset_id_l [list]
multirow foreach assets {
  lappend asset_id_l $asset_id
}
          

Technically it's equivalent to using a code block on the end of your db_multirow.

db_null
	  

Returns a value which can be used in a bind variable to represent the SQL value null. See Nulls and Bind Variables above.

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 select_foo "select foo, bar from greeble" {
    doc_body_append "<li>foo=$foo; bar=$bar\n"
} if_no_rows {
    doc_body_append "<li>There are no greebles in the database.\n"
}

	  

The code block may contain break statements (which terminate the loop and flush the database handle) and continue statements (which continue to the next row of the loop).

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, setting variables to column values. Raises an error if the query does not return exactly 1 row.

Example:

db_1row select_foo "select foo, bar from greeble where greeble_id = $greeble_id"
# Bombs if there's no such greeble!
# Now $foo and $bar are set.

	  

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 and returns 1. If no rows are returned, returns 0. If more than one row is returned, throws an error.

db_nextval sequence-name
	  

Returns the next value for the sequence sequence-name (using a SQL statement like SELECT sequence-name.nextval FROM DUAL). If sequence pooling is enabled for the sequence, transparently uses a value from the pool if available to save a round-trip to the database (see Sequence Pooling).

db_register_pooled_sequence sequence-name pool-size
	  

Registers the sequence sequence-name to be pooled, with a pool size of pool-size sequence values (see Sequence Pooling).

db_string statement-name sql [ -default default ] [ -bind bind_set_id | -bind bind_value_list ]
	  

Returns the first column of the result of SQL query sql. If sql doesn't return a row, returns default (or throws an error if default is unspecified). Analogous to database_to_tcl_string and database_to_tcl_string_or_null.

db_list statement-name sql [ -bind bind_set_id | -bind bind_value_list ]
	  

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. Analogous to database_to_tcl_list.

db_list_of_lists statement-name sql [ -bind bind_set_id | -bind bind_value_list ]
	  

Returns a Tcl list, each element of which is a list of all column values in a row of the result of SQL query sql. If sql doesn't return any rows, returns an empty list. (Analogous to database_to_tcl_list_list.)

db_dml statement-name sql \
    [ -bind bind_set_id | -bind bind_value_list ] \
    [ -blobs blob_list | -clobs clob_list |
      -blob_files blob_file_list | -clob_files clob_file_list ]
	  

Performs the DML or DDL statement sql.

If a length-n list of blobs or clobs is provided, then the SQL should return n blobs or clobs into the bind variables :1, :2, ... :n. blobs or clobs, if specified, should be a list of individual BLOBs or CLOBs to insert; blob_files or clob_files, if specified, should be a list of paths to files containing the data to insert. Only one of -blobs, -clobs, -blob_files, and -clob_files may be provided.

Example:

db_dml insert_photos {
        insert photos(photo_id, image, thumbnail_image)
        values(photo_id_seq.nextval, empty_blob(), empty_blob())
        returning image, thumbnail_image into :1, :2
    }  -blob_files [list "/var/tmp/the_photo" "/var/tmp/the_thumbnail"] 

	  

This inserts a new row into the photos table, with the contents of the files /var/tmp/the_photo and /var/tmp/the_thumbnail in the image and thumbnail columns, respectively.

db_write_clob statement-name sql [ -bind bind_set_id | -bind bind_value_list ]

db_write_blob statement-name sql [ -bind bind_set_id | -bind bind_value_list ]

db_blob_get_file statement-name sql [ -bind bind_set_id | -bind bind_value_list ]
	  

Analogous to ns_ora write_clob/write_blob/blob_get_file.

	    db_release_unused_handles
	  

Releases any allocated, unused database handles.

db_transaction code_block [ on_error { code_block } ]
	  

Executes code_block transactionally. Nested transactions are supported (end transaction is transparently ns_db dml'ed when the outermost transaction completes). The db_abort_transaction command can be used to abort all levels of transactions. It is possible to specify an optional on_error code block that will be executed if some code in code_block throws an exception. The variable errmsg will be bound in that scope. If there is no on_error code, any errors will be propagated.

Example:

proc replace_the_foo { col } {
    db_transaction {
        db_dml delete {delete from foo}
        db_dml insert {insert into foo(col) values(:col)}
    }
}

proc print_the_foo {} {
    doc_body_append "foo is [db_string "select col from foo"]<br>\n"
}

replace_the_foo 8
print_the_foo ; # Writes out "foo is 8"

db_transaction {
    replace_the_foo 14
    print_the_foo ; # Writes out "foo is 14"
    db_dml insert_foo {insert into some_other_table(col) values(999)}
    ...
    db_abort_transaction
} on_error {
    doc_body_append "Error in transaction: $errmsg"
}
    

print_the_foo ; # Writes out "foo is 8"

	  

db_resultrows
	  

Returns the number of rows affected or returned by the previous statement.

db_with_handle var code_block
	  

Places a database handle into the variable var and executes code_block. This is useful when you don't want to have to use the new API (db_foreach, db_1row, etc.), but need to use database handles explicitly.

Example:

proc lookup_the_foo { foo } {
    db_with_handle db {
        return [db_string unused "select ..."]
    }
}

db_with_handle db {
    # Now there's a database handle in $db.
    set selection [ns_db select $db "select foo from bar"]
    while { [ns_db getrow $db $selection] } {
        set_variables_after_query

        lookup_the_foo $foo
    }
}

	  

db_nullify_empty_string string
	  

For true SQL purists, we provide the convenience function db_nullify_empty_string, which returns [db_null] if its string argument is the empty string and can be used to encapsulate another Oracle quirk:

set baz ""

# Clean out the foo table
#
db_dml unused {delete from foo}
db_dml unused {insert into foo(baz) values(:baz)}

set n_rows [db_string unused "select count(*) from foo where baz is null"]
#
# $n_rows is 1; in effect, the "baz is null" criterion is matching
# the empty string we just inserted (because of Oracle's coercion
# quirk)

	  

To balance out this asymmetry, you can explicitly set baz to null by writing:

db_dml foo_insert {insert into foo(baz) values(:1)} {[db_nullify_empty_string $baz]}