NaviServer - programmable web server
4.99  5.0

[ Main Table Of Contents | Table Of Contents | Keyword Index ]

ns_db(n) 5.0.0a nsdb "NaviServer Module Commands"

Name

ns_db - Database access API

Table Of Contents

Synopsis

Description

These commands provides a mechanism to access databases.

COMMANDS

ns_db 1row handle 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 handle sql

This command expects the provided SQL command 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 than one row is returned.

ns_db bindrow handle

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 handle 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 handle

ns_db cancel cancels the current operation.

ns_db close handle

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 handle

Returns a boolean value indicating whether the connection to the database pool is made.

ns_db currenthandles

Return information about handles currently allocated to the thread performing this call. The result contains a dict with the pools, from which handles were allocated, the names of the handles and their "active" state (a handle is active between a "ns_db select" and the last "ns_db getrow" statement). If no handles are allocated by this thread, empty is returned.

 # [ns_db currenthandles]
 main {nsdb0 1} subquery {nsdb1 0}

in the example above the result indicates that the current thread has allocated handles from pool main and subquery, but the handle from pool main (i.e. nsdb0) is currently active (e.g., during a select).

ns_db datasource handle

Returns the datasource for the database pool.

ns_db dbtype handle

Returns the database type for the database pool.

ns_db dml handle sql

ns_db dml executes the provided SQL DML statement that should be data manipulation language such as an insert or update, or data definition language such as a create table.

ns_db driver handle

Returns the name of the driver of the handle.

ns_db exception handle

ns_db exception returns the most recent exception for the database pool.

ns_db exec handle 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 handle

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 t? 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 -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. See the examples for ns_db gethandle, below. The argument t can be specified in the form secs?:microsecs?, or secs.fraction, or as a number with the time units ms, s, m, d.

ns_db getrow handle 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 logminduration pool ?duration?

Query or set a threshold for logging of SQL statements. Log only statements in the error.log, when SQL logging is turned on in general (via "ns_logctl severity Debug(sql) ...") and the statement took at least the specified duration. The duration can be specified in the form secs?:microsecs?, or secs.fraction, or as a number with the time units ms, s, m, d.

ns_db open driver datasource user password

ns_db open returns a handle at a lower level, circumventing the pools.

ns_db password handle

Returns the password of the user for the database pool.

ns_db poolname handle

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 handle

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 rowcount handle

Returns number of processed records by the last SQL statements, this is useful with INSERT/UPDATE/DELETE statements to know how many records updated.

ns_db select handle 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 handle code message

ns_db setexception returns the specified status code and message to the client.

ns_db sp_exec handle

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 successfully 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 handle

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 handle varname vartype direction value

ns_db sp_setparam sets a parameter for a call to a stored procedure. varname is the name of the variable, for example "@name". vartype is the data type of this parameter, for example "varchar". The direction argument indicates whether it is an input or output parameter. It must be set to either "in" or "out". value denotes the parameter 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 handle 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 stats

Returns statistics in form of a Tcl dict from all pools in form of a dict. The statistics contain the number of SQL statements executed, the number of get-handle operations, the number of currently connected database connections, the total and the used handles from the pool, and the aggregated wait time for handles from this pool (including the connection setup time to the database server).

ns_db user handle

Returns the user (as specified for the User parameter of the configuration file) for the database pool.

ns_db verbose handle ?enabled?

Change the verbose setting (the Verbose parameter in the configuration file) for the given pool.

ns_dberrorcode handle

Return the database error code for the specified database handle.

ns_dberrormsg handle

Return the database error message for the specified database handle.

ns_dbconfigpath

Return the database section name file for the nsdb module from the configuration file.

ns_pooldescription poolname

Return the description of the provided database pool name from the [ns/db/pools] section in the configuration file.

EXAMPLES

These are valid uses of ns_db gethandle:

 ns_db gethandle
 # 1 handle from default pool
 
 ns_db gethandle -timeout 2.5s
 # 1 handle from default pool, 2.5 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 2.0s poolname 5
 # 5 handles from poolname, 2.0 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 $handle $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]

CONFIGURATION

The nsdb module is typically loaded per-server, where every server might use multiple database pools. Below is a sample configuration for PostgreSQL using the nsdbpg driver, using two database pools where pool1 is the default pool.

 ns_section "ns/db/drivers" {
   ns_param	postgres        ${bindir}/nsdbpg.so
 }
 
 ns_section ns/server/${server}/modules {
   ns_param	nsdb		${bindir}/nsdb.so
 }
 
 ns_section ns/server/${server}/db {
   ns_param	pools           pool1,pool2
   ns_param	defaultpool     pool1
 }
 
 ns_section ns/db/pools {
   ns_param	pool1           "Pool 1"
   ns_param	pool2           "Pool 2"
 }
 
 ns_section ns/db/pool/pool1 {
   ns_param	...
 }

For every pool the following configuration options might be used:

CheckInterval

Check after this time interval for stale connections. Default: 5m.

Connections

Integer: The maximum number of connections that can be established at any one time. The server automatically makes connections as needed up to this maximum number. If additional connections are requested during processing, the requests must wait. Default: 2

Datasource

Name specific to the database management system that might be necessary for opening database connections. For PostgreSQL, datasource has the form "${db_host}:${db_port}:${db_name}". See also: https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-PARAMKEYWORDS

Driver

Name of the driver for this pool as specified in ns/db/drivers.

LogMinDuration

Time interval for logging SQL statements. When SQL logging is turned on, log only SQL queries that take longer than the specified duration (similar to log_min_duration_statement in PostgreSQL). Setting LogMinDuration to 0 causes all SQL statements to be logged.

LogSQLErrors

Boolean value: If set to On, SQL errors are written to the server log along with the offending SQL statement(s).

MaxIdle

The maximum time interval that a database connection within this pool can remain open and idle. The default setting of "10m" causes connections that are idle for 10 minutes to be closed. Note that MaxIdle will not have an effect if it is equal to MaxOpen. Setting MaxIdle to 0 deactivates the automatic closing feature on idle connections. Default: 5m

MaxOpen

The maximum time interval that a database connection within this pool can remain open. The default setting of "60m" causes connections to keep open for one hour regardless of activity level. Database connections will then be opened again as needed up to the value of the Connections parameter. It is recommended that MaxOpen be a multiple of MaxIdle; otherwise, MaxOpen may be off by as much as (MaxOpen mod MaxIdle). Setting MaxOpen to 0 deactivates the automatic closing feature. Default: 60m

Password

Password to log in to the database.

User

Username to log in to the database.

All time intervals can be specified with and without a time unit suffix. Valid time units are "ms", "s", "m", "h", "d". If no time unit suffix is specified, seconds are assumed.

Below is a sample configuration for two pools. The referenced Tcl variables have to be set earlier to appropriate values.

 ns_section ns/db/pool/pool1 {
   ns_param	Connections        15
   ns_param      LogMinDuration     10ms
   ns_param	LogSQLerrors       $debug
   ns_param	driver             postgres
   ns_param	DataSource         ${db_host}:${db_port}:${db_name}
   ns_param	user               $db_user
   ns_param	password           ""
 }
 ns_section ns/db/pool/pool2 {
   ns_param	Connections        5
   ns_param      LogMinDuration     10ms
   ns_param	LogSQLerrors       $debug
   ns_param	driver             postgres
   ns_param	DataSource         ${db_host}:${db_port}:${db_name}
   ns_param	user               $db_user
   ns_param	password           ""
 }

See Also

ns_buildsqldate, ns_dbquotevalues

Keywords

SQL, database, module, nsdb, pools, server built-in