[
Table Of Contents
| Keyword Index
]
ns_db(n) 4.99 "NaviServer Built-in Commands"
ns_db - Standard database access API
TABLE OF CONTENTS
SYNOPSIS
DESCRIPTION
COMMANDS
EXAMPLES
SEE ALSO
KEYWORDS
This command provides a mechanism to access databases.
- ns_db bindrow dbhandle
-
ns_db bindrow returns an ns_set structure whose key names are the column names of the rows
returned by the SQL command previously-executed with ns_db exec. If the database is not
currently returning rows (i.e., a status other than NS_ROWS was returned by ns_db exec),
an error is thrown. The dbhandle argument is a database handle (obtained with ns_db gethandle).
- ns_db bouncepool poolname
-
Marks all database handles for the specified database pool as stale. When any database
handle currently open is put back into the pool, its connection to the database will be reset.
- ns_db cancel dbhandle
-
ns_db cancel cancels the current operation.
- ns_db close dbhandle
-
Closes the connection. Use this function only on handles that were obtained by
the ns_db open function. The server automatically close handles when the operation
is complete, so you don't normally have to call this function.
- ns_db connected dbhandle
-
ns_db connected returns a boolean value indicating whether the connection to the database pool is made.
- ns_db datasource dbhandle
-
Returns the datasource for the database pool.
- ns_db dbtype dbhandle
-
Returns the database type for the database pool.
- ns_db dml dbhandle sql
-
ns_db dml executes SQL that should be data manipulation language such as an insert or
update, or data definition language such as a create table.
- ns_db driver dbhandle
-
Returns the name of the driver of the handle.
- ns_db exception dbhandle
-
ns_db exception returns the most recent exception for the database pool.
- ns_db exec dbhandle sqlcommand
-
Executes the specified SQL command. It returns either NS_DML (if the SQL command is a DML
or DDL command) or NS_ROWS (if the SQL command returns rows, such as a SELECT). This
function can be used for ad hoc querying, where you don't know what kind of SQL command
will be executed.
- ns_db flush dbhandle
-
ns_db flush flushes the results of an SQL select so you do not need to use ns_db getrow
to get all the rows and throw them away.
- ns_db gethandle ?-timeout? poolname ?nhandles?
-
ns_db gethandle returns the specified number of handles from the specified pool. If poolname is
not specified, the Defaultpool from the configuration file is used. If nhandles is not specified,
1 handle is returned. (Note that if you specify nhandles, you must also specify a poolname.) If
not enough handles are available to fulfill the request, it waits until they are available. You
must request all the handles you will need for a specific pool with one call to ns_db gethandle.
You must release all your database handles explicitly (with ns_db releasehandle) before acquiring
more. If you request multiple handles from the database, this function returns a Tcl list of database
handles (space delimited). In this case, each handle must be released with a separate call to ns_db
releasehandle.
If a timeout is not specified or timeout is zero, ns_db gethandle will wait indefinitely
(perhaps forever) for the requested number of handles to become available. If timeout is
greater than zero, ns_db gethandle will either return with the handles within that time
period, or return "" if the time period was exceeded, or generate a Tcl error of the form
"could not allocate n handle(s) from pool 'poolname'. If timeout is less than zero, ns_db
gethandle will not block. It will either return with the handles, or generate the above Tcl
error. See the examples for ns_db gethandle, below.
- ns_db getrow dbhandle setId
-
ns_db getrow fetches the next row waiting to be retrieved after an ns_db select.
The data is dumped right into the set associated with SETID, which should be the
set returned by the ns_db select. It returns "1" if there are more rows waiting
and returns "0" otherwise. If you call ns_db getrow again after already receiving
"0" on the previous call, an error is returned.
- ns_db open driver datasource user password
-
ns_db open returns a handle at a lower level, circumventing the pools.
- ns_db poolname dbhandle
-
ns_db poolname returns the database pool that this handle came from.
- ns_db pools
-
ns_db pools returns a list of all database pools.
- ns_db releasehandle dbhandle
-
Puts the handle back in the pool. The server will automatically return any open handles to
the pool after a page has finished executing.
- ns_db 1row dbhandle sql
-
This command expects the SQL to be a select statement that returns exactly one row and
returns that row as an ns_set. An error is returned if zero or more than one row is returned.
- ns_db 0or1row dbhandle sql
-
This command expects the SQL to be a select statement that returns exactly zero or one row.
On zero rows, a null string is returned. On one row, a newly allocated ns_set is returned.
An error is thrown if more then one row is returned.
- ns_db password dbhandle
-
Returns the password of the user for the database pool.
- ns_db select dbhandle sql
-
ns_db select executes the SQL statement on the database server. It returns an ns_set
with the keys set to the column names that were selected. Use ns_db getrow to retrieve
rows. You cannot perform nested select statements. Before you start a new select statement,
you must first either retrieve all the rows from the first select or use the ns_db flush
statement to flush any rows not yet retrieved.
- ns_db setexception dbhandle code message
-
ns_db setexception returns the specified status code and message to the client.
- ns_db sp_exec dbhandle
-
ns_db sp_exec executes a stored procedure that has been initialized with ns_db sp_start
and ns_db sp_setparam. It returns "NS_DML" if the command was succsesfully executed but
did not return rows, or it returns "NS_ROWS" if the command was successfully executed and
did return rows (which can then be fetched with ns_db bindrow and ns_db getrow). It throws
an error if the command failed. This function is implemented only for the Sybase database
driver. See the Examples section, below, for an example of this function.
- ns_db sp_getparams
-
ns_db sp_getparams gets any output parameters set after executing a stored
procedure with ns_db sp_exec. It returns an ns_set or throws an error on failure.
This function is implemented only for the Sybase database driver. See the Examples
section, below, for an example of this function.
- ns_db sp_returncode dbhandle
-
ns_db sp_returncode gets the return code from a stored procedure. It must be called after
ns_db sp_exec. This function is implemented only for the Sybase database driver. See the
Examples section, below, for an example of this function.
- ns_db sp_setparam dbhandle varname vartype inout value
-
ns_db sp_setparam sets a parameter for a call to a stored procedure. The varname is the name of the variable,
for example "@name". The vartype is the data type of this parameter, for example "varchar". The inout argument
indicates whether it's an input or output parameter. It must be set to either "in" or "out". The value is the
paramter value to send. This function returns 1 on success and throws an error on failure. This function is
implemented only for the Sybase database driver. See the Examples section, below, for an example of this function.
- ns_db sp_start dbhandle procname
-
ns_db sp_start begins execution of the stored procedure called procname. It returns 0 on success and throws
an error on failure. This function is implemented only for the Sybase database driver. See the Examples section,
below, for an example of this function.
- ns_db user dbhandle
-
Returns the user (as specified for the User parameter of the configuration file) for the database pool.
- ns_db verbose dbhandle ?on | off?
-
ns_db verbose changes the verbose setting (the Verbose parameter in the configuration file) for the database pool.
These are valid uses of ns_db gethandle:
| |
ns_db gethandle
# 1 handle from default pool
ns_db gethandle -timeout 23
# 1 handle from default pool, 23 sec timeout
ns_db gethandle -timeout -1 poolname
# 1 handle from poolname, error if not available
ns_db gethandle poolname
# 1 handle from poolname
ns_db gethandle -timeout 23 poolname
# 1 handle from poolname, 23 sec timeout
ns_db gethandle poolname 5
# 5 handles from poolname
ns_db gethandle -timeout 23 poolname 5
# 5 handles from poolname, 23 sec timeout
|
| |
set db [ns_db gethandle $pool]
set ret [ns_db sp_start $db "p_TestProc"]
#
# Set the parameters for this stored procedure. The SQL definition of this
# procedure is:
#
# CREATE PROCEDURE p_TestProc(@x int, @y varchar(16) out, @z int out)
#
# The arguments to ns_db sp_setparam are like this:
#
# ns_db setparam $dbhandle $varname, $vartype, in|out, $value
#
set ret [ns_db sp_setparam $db "@x" int in 4]
set ret [ns_db sp_setparam $db "@y" varchar out "varchar val"]
set ret [ns_db sp_setparam $db "@z" int out 231]
#
# Execute the stored procedure now
#
set ret [ns_db sp_exec $db]
|
nsd
ns_buildsqldate , ns_buildsqltime , ns_buildsqltimestamp , ns_dbquotename , ns_dbquotevalue , ns_localsqltimestamp , ns_parsesqldate , ns_parsesqltime , ns_parsesqltimestamp , ns_writecsv