DBquery

Synopsis

DBquery selects data from a table in the current database based on user specifications and returns it to the user.

Input Ports

dbvisual_connect structure

statement string

process_statement int

Parameters

loop int

new_statement int

query_timeout_secs int

cancel_button int

fetch_cancelled int

fetch_complete int

flush_when_full int

rows_per_fetch int

userbuf_row_limit int

userbuf_realloc_interval int

use_floating_type_only int

floating_type int

fixed_type int

null_value_byte int

null_value_short int

null_value_int int

null_value_uint int

null_value_float float

null_value_double double

output_statistics group

connect_information DBconnectInformation group

previous ptr

dbvisual_descriptor ptr to a structure

dbinput_descriptor ptr to a structure

dboutput_descriptor ptr to a structure

row_specific_error_cnt int

statement_error_offset int

db_specific_error_message string

db_specific_error_code int

message string

return_code int

processing int

num_columns int

Output Ports

column_array[] DBcolumn group

Description

DBquery selects data from a table in the current database based on user specifications and returns it to the user. The data is selected based on the SQL SELECT statement that is entered into the statement input port. It is returned as an array of columns, one for each column specified by the SELECT statement. The columns in the array are of type DBcolumn (see DBcolumn ), which is itself an array.

Regardless of how returned data is stored, a number of the parameters used to control query processing refer to "rows" of data; for example, userbuf_row_limit. A data row is a logical structure that concatenates the same-indexed entries in an array of columns. For example, the third "row" of a set of columns would consist of the third item in the first column concatenated with the third item in the second column, and so forth. When a parameter specifies a number of rows, it is, in fact, referring to the number of items in a column.

DBquery executes as a select operation followed by a fetch operation. The select operation involves sending the SELECT statement to the database server, which returns the data. The fetch operation moves the data into column_array[], where it is accessible to the user.

A fetch operation executes as follows:

1. DBquery fetches the amount of data specified by rows_per_fetch a column at a time and inserts it into a set of fetch buffers (one fetch buffer per column).

2. The contents of the fetch buffers are copied into a set of user buffers a column at a time, where it is available to the user. There is one user buffer per column returned.

If there are more rows to be returned and auto-fetch mode is on (see next paragraph), DBquery repeats the fetch operation as specified by the DBquery parameters until either it reaches the number of rows specified by userbuf_row_limit or it has returned all the rows.

DBquery auto-fetches by default. When auto-fetching, it performs the additional fetch operations without pausing for user action between them. You can turn auto-fetch off with the loop parameter. With auto-fetch off, DBquery pauses for user action between fetch loops, and the user must reset process_statement to 1 to resume fetch processing.

Note: If auto-fetch is turned off and the user resets any fetch parameters between loops, the select resumes execution from the start rather than resuming with the fetch operation.

You can choose to restart execution of the SELECT statement from the beginning at any time with the new_statement parameter.

Input

dbvisual_connect_descriptor

A pointer to the connection descriptor of the session in which DBquery is to execute. This is the output of the DBconnect module that initiated the session.

CAUTION: Do not modify this value. A fatal error will result.

statement

A SQL SELECT statement. This statement must be a complete, literal, SQL statement that includes all required keywords and all necessary input data values. It must NOT end with a semicolon. The maximum length allowed is 32K bytes.

process_statement

A trigger that initiates execution of the SELECT statement. When this value changes to a value greater than 0, the statement is executed.

Parameters

loop

A switch that turns auto-fetching on or off. A value of 1 (the default) causes DBquery to turn auto-fetch on. It executes fetch operations until it has returned the number of rows specified by userbuf_row_limit or there are no more rows to return. A value of 0 turns auto-fetch off. It pauses between fetch operations. If auto-fetch is turned off, the value of process_statement must be reset to 1 to continue fetch processing.

Note: If auto-fetch is turned off and any fetch parameters are reset between fetch operations, the select re-executes from the start rather than resuming with the next rows of returned data.

new_statement

A trigger that restarts execution of the SELECT statement from the beginning. A value of 1 executes the statement. A value of 1 executes the statement; a value of 0 does not execute the statement.

query_timeout_secs

The time, in seconds, that DBquery waits for a response from the database server before terminating. The default is 0.

The total number of rows to be returned by DBquery. It must be greater than or equal to, and an integral multiple of, rows_per_fetch. The default is 1000.

cancel_button

A switch that specifies whether the current query should be canceled. A value of 0 (the default) indicates that the query should not be canceled; a value of 1 indicates that the query should be canceled. You can use this value as a trigger to execute a cancellation using the DBcancel module.

fetch_cancelled

A return value that specifies the status of the cancellation of the current fetch operation. A value of 1 indicates that the fetch operation was canceled successfully; a value of 0 indicates failure.

This return value is relevant only if cancel_button is 1.

fetch_complete

A return value that specifies the status of the current fetch operation. A value of 1 indicates that the fetch operation was successful; a value of 0 indicates failure.

This return value is relevant only if cancel_button is 0.

flush_when_full

A switch that specifies the behavior of DBquery when the user buffer fills up. A value is 0 (the default) specifies that the query ends; a value of 1 specifies that the buffer is flushed and the next fetch operation begins to fill it.

rows_per_fetch

The number of rows to be returned by each fetch operation. The default is 10.

userbuf_row_limit

userbuf_realloc_interval

The number of rows of memory to be allocated at one time. It must be greater than or equal to rows_per_fetch and less than or equal to userbuf_row_limit. The default is 2.

use_floating_type_only

A value that specifies the mapping used for numerical data returned by DBquery. A value of 0 (the default) uses the database-independent layer's mapping; a value of 1 specifies that all numerical data is returned as C-language double float data.

floating_type

A value used to specify a mapping for floating-point types:

0 (the default): the database-independent layer's default mapping

1: double float

2: float

For a discussion of the database independent layer data-mapping process and an explanation of when to use this parameter, see The Toolikts Book .

fixed_type

A value used to specify a mapping for fixed types:

0 (the default): the database-independent layer's default mapping

1: integer

2: short integer

3: byte

4: unsigned integer

The default is 1.

For a discussion of the database independent layer data-mapping process and an explanation of when to use this parameter, see The Toolkits Book .

null_value_byte

An integer value that specifies how a null value in a byte field is represented in the user buffer. The default is -127.

null_value_short

An integer value that specifies how a null value in a short int field is represented in the user buffer. The default is -999.

null_value_int

An integer value that specifies how a null value in an int field is represented in the user buffer. The default is -999.

null_value_uint

An integer value that specifies how a null value in an unsigned int field is represented in the user buffer. The default is -999.

null_value_float

An integer value that specifies how a null value in a float field is represented in the user buffer. The default is -999.

null_value_double

An integer value that specifies how a null value in a double float field is represented in the user buffer. The default is -999.

output_statistics

A set of statistics about the most recent fetch operation, in the following subobjects:

num_fetches_for_query

rows_processed

num_output_columns

userbuf_rows_allocated

userbuf_rows_used

userbuf_bytes_allocated

connect_information

Information about the current connection, in the following subobjects:

database_driver

server_host

server_name

database_name

user_id

connect_string

connected: the connection status--1 for connected or 0 for disconnected

This parameter is of type DBconnectInformation. For more information, see DBconnectInformation .

previous

For internal use only.

CAUTION: Do not modify this value. A fatal error will result.

dbvisual_descriptor

For internal use only.

CAUTION: Do not modify this value. A fatal error will result.

dbinput_descriptor

For internal use only.

CAUTION: Do not modify this value. A fatal error will result.

dboutput_descriptor

For internal use only.

CAUTION: Do not modify this value. A fatal error will result.

row_specific_error_cnt

The number of rows returned during the most recent fetch operation that contained errors.

statement_error_offset

A message that describes the status of the query. For a list of possible values, see The Toolkits Book .

none.

A returned value indicating the position in the SELECT statement where an error occurred. This value is returned only if the database server returns an error message indicating that the SELECT statement is invalid.

This information is useful for constructing an error message; see DBerrorString .

db_specific_error_message

An error message returned by the database server when an error occurs during its processing of the query. For more information, see your database documentation.

db_specific_error_code

An error code associated with the error message returned by the database server when an error occurs during its processing of the query. For more information, see your database documentation. A value of 0 indicates success; a nonzero value indicates failure.

return_code

The status code associated with the message returned in the message parameter. A value of 0 indicates success; a nonzero value indicates failure. For a list of possible values, see The Toolkits Book .

processing

A return value that indicates the processing status of the query. This value is set to 1 while the query is being processed and is cleared to 0 to indicate that the query has completed.

num_columns

A return value that indicates the number of columns in the data rows returned by the query.

Output Port

column_array[]

The data returned by a SELECT statement. Each element in this array is a DBcolumn group that consists of an array containing a column of returned data, an integer value that indicates whether null data values are present, the value that is to be used to represent a null data value, and the name of the column.
For more information on DBcolumn, see DBcolumn

Example

Not available.

File

v/db.v

See also related modules