TOC PREV NEXT INDEX

The Database Toolkit


2 Using the Database Kit Macro Interfaces

This chapter documents the user interfaces for the Connect, Query, and Manipulate macros, the secondary windows associated with these macros, and the Tools interface.

These macros allow you to connect to your database, ask for information from, and send information to your database. These macros contain and use the modules discussed in the previous chapter.

This chapter discusses:

2.1 Using the Connect Interface

The Connect macro connects the user to or disconnects the user from a specified database. It provides a user interface to the DBconnect module (see the on-line reference page for DBconnect), which performs the connect or disconnect.

Connecting to a Database

This section contains the basic information required for connecting to a database. For a detailed description, see Connecting and Disconnecting on page 1-12.

A connect establishes a connection between the user and the database and initiates a session with the database. A disconnect terminates the session and breaks the connection. Both connect and disconnect are performed by the server that manages the database. The option selected from the Connection menu determines whether the server performs a connect or a disconnect.

Connection requirements

When connecting, the user must supply, at minimum, the following:

A user name is always required for a connect string. Depending on the database type, as indicated by the server type, one or more of the following may also be required:

Which elements are required and how they are assembled into a connect string are specific to the database type.

Typically, you should see your database information to obtain the correct connect string. For more information, see Connect Strings on page 1-14 or refer to your database documentation.

Disconnection Requirements

When disconnecting, the user needs to supply only the Disconnect choice on the Connect macro's Connection menu, to indicate that a disconnect is desired. (This corresponds to the value 1 for DBconnect's connect_disconnect parameter.)

The Connect Interface

The Connect interface appears whenever you instance a Connect macro in an application or start an application that contains a Connect macro:

Figure B-1

This interface contains the following components:

Menus

The menubar, located at the top of the Connect interface, contains a set of pull-down menus that provide commands or options specific to the Connect macro:

Figure B-2

The Connect menus provide the following groups of commands or options:

You use these pull-down menus in the same way that you use the pull-down menus found in many other software products.

The File Menu

The File menu provides the commands that load or save the current connection settings. The following commands are available:

DATABASE_DRIVER ORACLE
CONNECT_STRING chrise/chrise@EXPRESS
The Server Type Menu

The Server Type menu provides a list of the possible server types; for example, ORACLE. Selecting a server type inserts it into the Server Type field (see Input Fields on page 2-5). The default is the current value in the Server Type field.

This field maps to DBconnect.database_driver.

The Connection Menu

A specification of whether to connect or disconnect. The allowed values are Connect (which corresponds to 0) and Disconnect (which corresponds to 1). The default is Connect.

This field maps to DBconnect.connect_disconnect.

Input Fields

The Connect interface contains a number of input fields into which you enter connect values. If you plan to use a connect string, you should use the Connect String input field; otherwise, you must supply the elements from which to construct a connect string in the other fields.

Server Type

The server type, which indicates the type of database to which to connect; for example, ORACLE. The default is the value of the environment variable XP_DB_DRIVER, if it is set, otherwise there is no default.

This field maps to DBconnect.database_driver.

Host Name

The host name of the machine on which the server that manages the database resides. The default is the value of the environment variable XP_SERVER_HOST, if it is set. If it is not set then it is the default host for the specified database driver.

This value is required only if the user does not supply a value for Connect String and the specified server type requires a host name in the connect string. If a value is specified for Connect String, this field is ignored.

This field maps to DBconnect.server_host.

Server Name

The name of the server that manages the database. The default is the value of the environment variable XP_SERVER_NAME, if it is set, otherwise it is the default server for the specified database driver.

This value is required only if the user does not supply a value for Connect String and the specified server type requires a server name in the connect string. If a value is specified for Connect String, this field is ignored.

This field maps to DBconnect.server_name.

Database Name

The database to which to connect. The default is the value of the environment variable XP_DATABASE_NAME, if it is set, otherwise it is the default database for the specified database driver.

This value is required only if the user does not supply a value for Connect String and the specified server type requires a database name in the connect string. If a value is specified for Connect String, this field is ignored.

This field maps to DBconnect.database_name.

Connect String

The connect string that the database server will use when connecting the user to the specified database. The default is the value of the environment variable XP_CONNECT_STRING, if it is set, otherwise there is no default.

The format of a connect string is specific to the type of database (as specified with Server Type).

If a value for Connect String is not supplied, the user must supply the elements from which to construct a connect string. The values required for constructing a connect string are also database specific.

For a discussion of connect string format and the values required for constructing a connect string, see either Connect Strings on page 1-14 or your database documentation.

This field maps to DBconnect.connect_string.

User ID

The user's ID on the host system. The default is the value of the environment variable XP_USER_ID, if it is set, otherwise there is no default.

This value is required only if the user does not supply a value for Connect String. If a value is specified for Connect String, this field is ignored.

This field maps to DBconnect.user_id.

User Password

The user's password on the host system. The default is the value of the environment variable XP_USER_PASSWORD, if it is set, otherwise there is no default.

This value is required only if the user does not supply a value for Connect String and the specified server type requires a user password in the connect string. If a value is specified for Connect String, this field is ignored.

This field maps to DBconnect.user_password.

Output Fields

The Connect interface contains a number of output fields into which connection information is returned.

Message

An message that describes the status of the connect or disconnect. For a list of possible values, see Chapter 4, Database Kit Messages and Codes.

This field maps to DBconnect.message.

Status 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 Chapter 4, Database Kit Messages and Codes.

This field maps to DBconnect.return_code.

2.2 Using the Query Interface

The Query interface queries a database, that is, it selects data from a table or view in the current database based on user specifications in a SQL SELECT statement and returns the data as an array of columns, one for each column specified by the SELECT statement. It provides a user interface to the DBquery module that performs the query and some additional modules that perform support tasks.

Note: You must be connected to a database before querying it; see Connecting to a Database on page 2-2. If you are not connected to a database and you try to query a database, the query is not processed and the "Not connected" message remains in the Message field.

Querying a Database

You use the Query macro to query a database as follows:

1. Construct a SQL SELECT statement.
2. Specify values for the parameters that control query processing (optional- you can use the defaults).
3. Submit the statement for execution.
4. View and process the query output.

During this process, you can view various types of additional information as necessary.

Note: Query requires a SELECT statement. If you attempt to process an invalid statement, the message "DBerror: Invalid statement" is returned to the Message field and an error window containing additional information opens. For details on constructing SQL statements, see Constructing SQL Statements on page 1-10.

The next section briefly describes how the Database Kit stores and processes output query. Subsequent sections explain the query process and some related topics.

Data columns and Data Rows

Before querying a database via the Query interface, it is useful to understand how the Database Kit stores and processes data.

The Database Kit stores the data returned by a query in columns. The columns of data are made available to the application in an array of type DBcolumn. DBcolumn is a Database Kit group that consists of the following:

Although the Database Kit stores data as columns, it is frequently spoken of as occurring in rows, where a row consists of the same-indexed entries in a set of column arrays. For example, the third row of a set of data stored as a set of column arrays would consist of the following:

.
.
.

Keep this definition of "row" in mind when using this manual.

For a more on this same topic, see Querying a Database on page 1-16.

The Query Operation

The Query interface provides a number of parameters that you can set to control how your SELECT statement is processed. Before you can set these parameters effectively, you must understand how a query operates.

A query is executed as a select operation followed by one or more fetch operations. The select operation involves sending the SELECT statement to the database server, which returns the data. The fetch operation moves the data into an array of columns, where it is accessible to an application.

A fetch operation is executed as follows:

1. DBquery fetches rows of data into the fetch buffers until either it reaches the number of rows specified by the rows per fetch parameter (described later in this section) or it has returned all the rows.
IMPORTANT: Some database servers stop fetching rows prior to reaching the limit if they encounter a row with an error, but resume fetching rows during the next fetch operation. See your database documentation for a description of how your database handles fetch operations.
2. The contents of the fetch buffers are copied into a set of user buffers that are available to the application. There is one user buffer per column returned.

If there are more rows to be returned and auto-fetch mode is on (see The Auto Fetch Parameter on page 2-33), Query repeats the fetch operation until either it reaches the number of rows specified by the User buf row limit parameter (described later in this section) or it has returned all the rows. When the user buffers are full, DBquery either flushes the buffers and begins a new fetch operation or ends the query, as determined by the status of the Flush when full parameter (described later in this section).

The data in the user buffers is returned to the application as an array of columns.

Processing, Canceling, or Resetting a Query

By design, Query does not execute a fetch operation as soon as an SQL SELECT statement has been entered. This ensures that you have time to set query-processing parameter values before the query begins. When you finish entering your SELECT statement and setting query-processing parameters, you must process the query; that is, trigger its execution.

Two related operations also affect the state of a query's execution:

The cancellation operation attempts to cancel any select or fetch operation currently running on the server and prohibits further fetches for the current query. (Additional fetches occur when there is more data to be fetched and auto-fetch is on.) In addition, it releases all memory allocated for the query by the Database Kit or the database software.

You process, cancel, or reset queries using either commands on the Statement menu or the Process, Cancel, or Reset options; see The Statement Menu on page 2-13 or Tools on page 2-15, respectively.

Viewing and Processing Output

You can view your query output in either of two ways:

The Query Interface

The Query interface appears whenever you instance a Query macro in an application or start an application that contains a Query macro:

Figure B-1

This interface contains the following components:

A number of the Query commands and tools open additional windows. These windows, called secondary windows in this manual, provide access to query-processing parameters, display query output and other information, and, in certain circumstances, provide special error information.

The remainder of this section describes the interface components and provides a quick reference to their use. The secondary windows are described in Using the Secondary Windows on page 2-30.

The Statement Window and the Clear Button

The statement window, located in the middle of the Query interface, is where you construct a SQL SELECT statement. The following figure illustrates the statement window with the construction of a SELECT statement in process:

Figure B-2

You can enter text into the statement window in three ways:

When you finish entering a statement, you initiate its execution with one of the following:

Be sure that you have entered the complete statement and, optionally, set any query-processing parameters before processing the select statement.

Note: If you attempt to process an invalid SQL statement, the Query interface will display an Error window containing information that marks the location of the error. For more information, see The Error Window on page 2-42.

To clear the current statement from the window and buffer and start again, press the Clear button just above the upper left corner of the statement window.

Menus

The menubar, located at the top of the Query interface, contains a set of pull-down menus that provide commands specific to the Query macro:

Figure B-3

The File Menu

The File menu provides commands for loading or saving SELECT statements. The following commands are available:

Each command provides a file selection window in which you make your selection.

The statement being saved or loaded does not need to be a complete SELECT statement. It can be a partial statement that you want to use in constructing a complete statement.

The Save Statement command uses the DBsave module. The Load Statement command uses the DBload module.

The Statement Menu

The Statement menu provides commands that allow you to start or stop the query specified by the current SELECT statement. The following commands are available:

You can also use the Process, Cancel, or Reset icons on the toolbar. (see Tools on page 2-15).

The Process command maps to DBquery.process_statement. The Cancel statement maps to DBcancel. The Reset statement maps to DBquery.new_statement.

The Parameters Menu

The Parameters menu contains commands that allow you to view and change the values of query-processing parameters. The following commands are available:

For details and an illustration, see The Query Control Window on page 2-30.

For details and an illustration, see The Query Output Types Window on page 2-34.

You can also open these windows with the Query Control and Query Output Types tools (see Tools on page 2-15).

The Display Menu

The Display menu contains commands that allow you to control which windows are visible and whether the upper toolbar is visible. The following commands are available:

You can also open these windows with the Query Statistics, Output Window, and Connection Information icons (see Tools on page 2-15).

The List Modes Menu

The List Modes menu contains commands that provide table, view, and column information about the current database and additional user-defined information for use in constructing SELECT statements. The following commands are available:

The file should contain a list of SQL elements to be used in constructing a SQL statement. There must be only one entry per line in the file, and each line can have a maximum of 80 characters.

Selecting an entry in any of these windows inserts the entry into the statement window at the most recent cursor position. Depending on how your platform handles buffer input, you may need to signify completion of the current line in the statement window (as noted in the message under the window) before selecting the entry; check your user documentation for specifics.

You can also open these windows with the Table List, Column List, View List, and User-defined List icons (see Tools on page 2-15).

For descriptions of the above windows, see The List Windows on page 2-41.

Tools

The toolbars contain a set of query tools. The upper toolbar, located beneath the menubar, is open by default. You can close and open it with the Display->Toolbar command. The lower toolbar, located beneath the statement window, is always open.

The upper toolbar contains the following icons:

Figure B-4

The lower toolbar contains the following icons:

Figure B-5

Each of the Graph tools is linked to a Graph output port in the Query macro. The Graph ports are typically connected to other UIwindows; for example, Viewer3D. Selecting a Graph tool opens the corresponding UIwindow. (For information on the Graph ports, see the on-line reference for Query.) Each of the other tools performs the same action as a Query command on the menubar.

The following table describes each tool briefly and notes the corresponding Query command. See the description of that command for a detailed description of the operation performed.

Table B-1
Tool(s)
Action
Corresponding command
Graph
Opens the window connected to the associated output port
(None)
Table List
Opens the Table List window
List Modes->User Tables
Column List
Opens the Column List window
List Modes->
Column Names
View List
Opens the View List window
List Modes->User Views
User-defined List
Opens the User-defined List window
List Modes->
User-defined
Process
Processes the specified SELECT statement
Statement->Process
Reset
Restarts the current SELECT statement from the beginning
Statement->Reset
Cancel
Cancels the current query
Statement->Cancel
Connection Information
Opens the Connection Information window
Display->
Connection Information
Output Window
Opens the Output window
Display->
Output Window
Query Statistics
Opens the Query Statistics window
Display->Statistics
Query Output Types
Opens the Query Output Types window
Parameters->
Output Types
Query Control
Opens the Query Control window
Parameters-> Control

Output Fields

The Query interface contains three output fields into which connection and status information are returned.

Connection Status

An message that describes the status of the current connection, either Connected or Disconnected.

This field maps to DBquery.connect_information.connected.

Message

A message that describes the status of the query. For a list of possible values, see Chapter 4, Database Kit Messages and Codes.

This field maps to DBquery.message.

Status

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 Chapter 4, Database Kit Messages and Codes.

This field maps to DBquery.return_code.

Query Components: Quick Reference

The following table provides a quick reference to the tasks you can perform with the Query interface. For descriptions of these components, see the previous sections.

Table B-2
Task
Component
Constructing SQL SELECT statements
The statement window
The Clear button
Commands:
     File->Save Statement
     File->Load Statement
     List Modes->User Tables
     List Modes->User Views
     List Modes->User-defined
     List Modes->Column Names
Tools:
     Table List
     Column List
     View List
     User-defined List
Processing, canceling, or resetting SQL SELECT statements
Commands:
     Statement->Process
     Statement->Cancel
     Statement->Reset
Tools:
     Process
     Reset
     Cancel
Setting query-processing parameters
Commands:
     Parameters->Control
     Parameters->Output Types
Tools:
     Query Control
     Query Output Types
Displaying output
Commands:
     Display->Output Window
Tools:
     Graph
     Output Window
Displaying information
Commands:
     Display->Statistics
     Display->Connection Information
Tools:
     Query Statistics
     Connection Information
Output fields:
     Connection Status
     Message
     Status
Opening or closing the Toolbar
Display->Toolbar

2.3 Using the Manipulate Interface

The Manipulate macro performs SQL operations other than queries on a database. It provides a user interface to the DBdelete, DBinsert, DBupdate, DBmiscStatement, DBcommit, and DBrollback modules that perform the operation and some additional modules that perform support tasks.

Note: You must be connected to a database before performing a SQL operation on it. If you are not connected to a database, the SQL operation is not processed and the value in the Message field is "Not connected." If you are using the Manipulate macro to perform the operation, you will typically use the Connect macro to connect to the database. See Connecting to a Database on page 2-2.

Performing an SQL Operation

You use the Manipulate macro to perform a non-query SQL operation other than a commit or rollback by constructing the appropriate SQL statement and submitting it for execution.

You use Manipulate to perform a commit or roll back by selecting a Manipulate command or tool from its interface.

At any time, you can view connection and other status information.

Note: Manipulate will process any SQL statement except CONNECT, SELECT, COMMIT, or ROLLBACK. If you attempt to process an invalid statement, the message "DBerror: Invalid statement" is returned to the Message field and an error window containing additional information opens. For details on constructing SQL statements, see Constructing SQL Statements on page 1-10.

The following subsections describe important aspects of these tasks.

Processing Operations

By design, Manipulate does not execute a SQL statement as soon as it has been entered. When you finish entering the statement, you must process the statement; that is, trigger its execution.

You process an operation using either commands on the Statement menu or the Process tool; see The Statement Menu on page 2-25 or Tools on page 2-26, respectively.

Performing a Commit or Rollback

Unlike other SQL operations in the Database Kit, you do not perform a commit or rollback by entering a COMMIT or ROLLBACK statement in the statement window and processing it. Instead, you use the appropriate menu command or tool, described later in this section, as follows:

These commands and tools are described in The Com/Roll menu on page 2-26 and Tools on page 2-26, respectively.

The Manipulate Interface

The Manipulate interface appears whenever you instance a Manipulate macro in an application or start up an application that contains a Manipulate macro. Note that it is extremely similar- but NOT identical- to the Query interface.

This interface contains the following components:

A number of the Manipulate commands and tools open additional windows. These windows, called secondary windows in this manual, provide access to additional information, including, in certain circumstances, special error information.

The remainder of this section describes the interface components and provides a quick reference to their use. The secondary windows are described in Using the Secondary Windows on page 2-30.

The Statement Window and the Clear Button

The statement window, located in the middle of the Manipulate interface, is where you construct a SQL statement. The following figure illustrates the statement window with the construction of a DELETE statement in process:

Figure B-1

You can enter text into the statement window in three ways:

When you finish entering a statement, you initiate its execution with one of the following:

Be sure before you process the SQL statement that you have entered the complete statement.

Note: If you attempt to process an invalid SQL statement, the Manipulate interface will display an Error window containing information that marks the location of the error. For more information, see The Error Window on page 2-42.

To clear the current statement from the window and buffer and start again, press the Clear button just above the upper left corner of the statement window.

Menus

The menubar, located at the top of the Manipulate interface, contains a set of pull-down menus that provide commands specific to the Manipulate macro:

Figure B-2

The Manipulate menus provide the following groups of commands:

You use these pull-down menus in the same way that you use the pull-down menus found in many other software products.

The File Menu

The File menu operates exactly as the Query interface File statement does. See The File Menu on page 2-13.

The Statement Menu

The Statement menu provides the Process command, which allows you to start the SQL operation specified by the current SELECT statement. You can also process statements with the Process tool (see Tools on page 2-26).

The Process command maps to DBdelete.process_statement, DBinsert.process_statement or DBupdate.process_statement.

The Display Menu

The Display menu operates exactly as the Display Menu for the Query Interface. For info see The Display Menu on page 2-14

The List Modes Menu

The List Modes menu operates exactly as the List Modes menu for the Query Interface. For info see The List Modes Menu on page 2-15.

You can also open these windows with the Table List, Column List, View List, and User-defined List tools (see Tools on page 2-26).

For descriptions of these windows, see The List Windows on page 2-41.

The Com/Roll menu

The Com/Roll menu contains commands that end (commit or roll back) the current transaction. (The current transaction includes all commands executed on the current connection, not just those executed through the current Manipulate interface.) The following commands are available:

You can also commit or roll back a transaction with the Commit and Rollback tools (see Tools on page 2-26).

Tools

The toolbars contain a set of manipulate-related tools. The upper toolbar, located beneath the menubar, is open by default. You can close and open it with the Display->Toolbar command. The lower toolbar, located beneath the statement window, is always open.

The upper toolbar contains the following tools:

Figure B-3

The lower toolbar contains the following tool:

Figure B-4

Each of the tools performs the same action as its associated Manipulate command on the menus. The following table describes each tool briefly and notes the corresponding Manipulate command. See the description of that command for a detailed description of the operation performed.

Table B-1
Tool(s)
Action
Corresponding command
User-defined List
Opens the User-defined List window
List Modes->
User-defined
View List
Opens the View List window
List Modes->User Views
Column List
Opens the Column List window
List Modes->
Column Names
Table List
Opens the Table List window
List Modes->User Tables
Commit
Commits the current transaction
Com/Roll->Commit
Rollback
Rolls back the current transaction
Com/Roll->Rollback
Process
Processes the specified SQL statement
Statement->Process
Connection Information
Opens the Connection Information window
Display->
Connection Information

Output Fields

The Manipulate interface contains three output fields into which connection and status information are returned.

Connection Status

A message that describes the status of the current connection, either Connected or Disconnected.

This field maps to the connect_information.connected field of DBdelete, DBinsert, DBupdate, or DBmiscStatement.

Message

A message that describes the status of the SQL operation. For a list of possible values, see Chapter 4, Database Kit Messages and Codes.

This field maps to the message field of DBdelete, DBinsert, DBupdate, or DBmiscStatement.

Status

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 Chapter 4, Database Kit Messages and Codes.

This field maps to the return_code field of DBdelete, DBinsert, DBupdate, or DBmiscStatement.

Manipulate Components: Quick Reference

The following table provides a quick reference to the tasks you can perform with the Manipulate interface. For descriptions of these components, see the previous sections.

Table B-2
Task
Component
Constructing SQL statements
The statement window
The Clear button
Commands:
     File->Save Statement
     File->Load Statement
     List Modes->User Tables
     List Modes->User Views
     List Modes->User-defined
     List Modes->Column Names
Tools:
     Table List
     Column List
     View List
     User-defined List
Committing or rolling back transactions
Commands:
     Com/Roll->Commit
     Com/Roll->Rollback
Tools:
     Commit
     Rollback
Processing SQL statements
Commands:
     Statement->Process
Tools:
     Process
Displaying information
Commands:
     Display->Connection Information
Tools:
     Connection Information
Output fields:
     Connection Status
     Message
     Status
Opening or closing the Toolbar
Display->Toolbar

2.4 Using the Secondary Windows

Using various Query and Manipulate commands and tools opens additional UIwindows that either provide access to parameters or display output or other information. Not all secondary windows are used by both Query and Manipulate interfaces. The following table lists the secondary windows (in one case, a group of related windows) and notes which of the interfaces use them:

Table B-1
Window(s)
Interfaces in which they are used
Query Control
Query
Query Output Types
Query
Query Statistics
Query
Output
Query
Connection Information
Query, Manipulate
The List windows:
     Table List
     View List
     Column List
     User-defined List
Query, Manipulate

The Query Control Window

The Query Control window is used only by the Query interface. It displays the current values of parameters that control query processing and allows you to reset those values. The following figure illustrates this window with the default values displayed:

Figure B-1

The parameters whose values are displayed in this window can be grouped as follows:

The Null Numeric Field Indicator Parameters

When you display query output, you must be able to represent null-value fields in a recognizable way. This is not an issue for string fields, which use a zero-length null-terminated string. It is an issue for numeric fields (byte, signed short integer, signed integer, unsigned integer, float, or double float), because all possible entries in a numeric field have value, including 0.

The Query interface provides a set of null field indicator parameters that specify how a null numeric or string field is represented:

Null value short
Null value int
Null value float
Null value uint
Null value double
Null value byte

For each parameter, you provide an integer value that will be inserted into null fields of that datatype. The defaults are -127 for Null value byte and -999 for the others.

The Fetch-Processing Parameters

The Query interface provides three parameters that you can use to control the size of the fetch and user buffers used during fetch processing: Rows per fetch, User buf realloc interval, and User buf row limit.

These values interact as follows:

You should select values for these parameters carefully because your choices will affect how memory is used during the fetch processing used to return the results of a query. You should be especially careful when setting these values if your system has limited memory available.

Working with Limited Memory

One way to use less memory is to set a small value for Rows per fetch. Decreasing the size of the fetch buffer will result in more frequent fetch operations but smaller memory usage. In particular, do not set the value of Rows per fetch near or equal to the value of User buf row limit unless you have specified that the user buffers will be flushed when full (see Flushing the User Buffers on page 1-20) and are deliberately working with small user buffers.

Also, you should not initially allocate the entire number of rows required for the user buffers; that is, you should not set the value of User buf realloc interval near or equal to the value of User buf row limit. Instead, set User buf realloc interval to a low number to ensure that memory is used in small quantities as it is needed.

A number of memory usage status fields that allow you to gauge memory usage for a query are available to you in the Query Statistics window (see The Query Statistics Window on page 2-36). You should be aware that these fields include only the memory allocated explicitly by the Database Kit; they do not include the memory allocated by the database-native routines.

The Auto Fetch Parameter

By default, fetch processing operates in a mode called auto-fetch; that is, it loops continuously until it returns the number of rows specified by User buf row limit to the application or there are no more rows to return. The Query interface provides a parameter called Auto fetch that turns auto-fetch on or off. When you turn auto-fetch off, fetch processing pauses between fetch operations. This is useful for viewing partial results. With auto-fetch turned off, you must reprocess the SQL statement after every pause to continue fetch processing. See Processing, Canceling, or Resetting a Query on page 2-10.)

Note: If auto-fetch is turned off and you reset any query parameters other than Flush when full between loops, the select resumes execution from the start rather than resuming with next rows of returned data.

Note: If auto-fetch is turned on, you should not turn on the Flush when full parameter. If you turn on Flush when full while in auto-fetch mode, the user buffers will be flushed automatically every time they fill until the end of fetch processing, and only the last user-buffer's worth of data will be available for processing. (Flush when full is described in the next section.)

The Flush When Full Parameter

As noted previously, Query executes fetch operations until either the number of rows specified by User buf row limit is reached or there are no more rows to be returned. The Query interface provides a parameter called Flush when full that determines how to proceed when the user buffers are full.

Note: You should select Flush when full only if fetch processing is not being performed in auto-fetch mode (see the previous section).

Note: Some database servers stop fetching rows prior to reaching the limit set by Rows per fetch if they encounter a row with an error, but resume fetching rows during the next fetch operation. As a result, the user buffer may not be completely full when it is flushed. See your database documentation for a description of how your database handles fetch operations.

Note: Space is not automatically reallocated for the user buffer when a flush occurs. The memory allocation stays at the maximum for subsequent fetch operations until the current query is canceled or released or a new query is issued.

The Query Timeout Parameter

The Query timeout parameter specifies, in seconds, how long the Query macro will wait for a response from the database server once a SQL command is submitted for processing.

The Query Output Types Window

The Query Output Types window is used only by the Query interface. It is provided by the OutputUI macro, which is available for use in other applications; see the on-line help.

The buttons in this window allow you to specify how numerical values are returned from a query. The following figure illustrates this window with the default settings displayed:

Figure B-2

A database typically supports different datatypes than does the Database Kit. Therefore, the Database Kit maps the data returned by a query into its own datatypes. (See Mapping Data to Supported Database Kit Datatypes on page 1-22, for details.) This default mapping is typically the one used; however, you can force the mapping of numeric fields to other types.

In general, the database-independent layer maps numeric types as follows:

For information on your database's supported datatypes and for supported limits for scale, precision and number of digits in the result, see your database documentation.

You can use the numeric datatype parameters provided by this window- Force output, Float Type, and Fixed Type- to override the database-independent layer's default mapping of numeric fields. You must exercise care when using these parameters to override the database-independent layer's mapping. If the mapping you select causes an overflow condition, cancellation of the current query will occur due to fetch errors.

The three override parameters are used as follows:

Table B-2
Value
Meaning
Default
Uses the database-independent layer's mapping
Float Type
Treats fixed types as floating-point types and maps them as specified by the Float Type parameter
Fixed Type
Treats fixed types as fixed types and maps them as specified by the Fixed Type parameter

In the context of this parameter, "fixed" means non-floating-point types with a SCALE of 0. By default, the database-independent layer maps these values to signed byte, signed short integer, or signed integer, depending on the maximum value of the column as defined by the database engine.

This parameter is useful in circumstances such as the following: some databases return a SCALE of 0 for all columns that result from the use of operators such as UNION, UNION ALL, and INTERSECT. Therefore, all of the columns, including floating-point types, are classified as fixed types and may result in an overflow error. (To determine whether your database does this, see Chapter 3, Database-Specific Usage,or your database documentation.)

Selecting Float Type allows such a query to return all numeric values, both floating-point and fixed, into double or float fields, avoiding the overflow error.

Table B-3
Value
Meaning
Float
Maps floating-point types to float
Double
(the default)
Maps floating-point types to double float

This parameter allows you to reduce float types from double to float. Do not do this unless you are certain that the maximum values returned by the query (including the returned values of functions such as AVG) can be contained in the float datatype without resulting in an overflow condition. (An overflow condition will result in the display of the Database Kit error DB ERROR: Recoverable database error.)

Note: Regardless of how a database represents floating-point types internally, the data values actually present may be restricted by factors such as application-level editing rules or host-variable datatypes. For example, the data values for a column could always be generated from a C-language float variable. For more information, see your database documentation.

Table B-4
Value
Meaning
Default
Uses the database-independent layer's default mapping
Integer (the default)
Maps fixed types to signed integer
Short Integer
Maps fixed types to signed short integer
Byte
Maps fixed types to signed byte
Unsigned Integer
Maps maps fixed types to unsigned integer

Note: Regardless of how a database represents fixed types internally, the data values actually present may be restricted by factors such as application-level editing rules or host-variable datatypes. For more information, see your database documentation.

The Query Statistics Window

The Query Statistics window is used only by the Query interface. It contains statistics about the most recent fetch operation of the current query:

For example:

Figure B-3

The values in this window depend on parameter settings specified in the query-processing windows (see The Parameters Menu on page 2-14) and the amount of processing completed. They are obtained from the various fields of the DBquery.output_statistics structure.

You can open this window with either the Display->Statistics command or the corresponding tool. See The Display Menu on page 2-14 and Tools on page 2-15 for details.

The Query Statistics window is provided by the StatisticsUI macro. This macro is available for use in other applications; see the on-line reference for StatisticsUI.

The Output Window

The Output window is used only by the Query interface. The following figure illustrates an Output window containing some data returned by a simple query (the contents of a given Output window will vary depending on the query specifications).

Figure B-4

The Output window, unlike other secondary windows, has its own menubar, containing menus and commands that save the output or specify how it is displayed. Five Output window menus are available:

For more information on the conversion, see Viewing and Processing Output on page 2-10.

The Output window is provided by the OutputWindowUI macro. This macro is available for use in other applications; see the on-line reference for OutputWindowUI.

The Connection Information Window

The Connection Information window is used by both the Query and Manipulate interfaces. It contains the following information about the current connection:

For example:

Figure B-5

The values in this window are obtained from the various fields of the connect_information parameter of the relevant underlying Database Kit module.

You can open this window with either the Display->Connection Information command or the corresponding tool. See The Display Menu on page 2-14 and Tools on page 2-15 for details.

The Connection Information window is provided by the ConnectionUI macro. This macro is available for use in other applications; see the on-line reference for ConnectionUI.

The List Windows

The List windows display information about tables, views, and columns in the current database. You can use them to construct SELECT statements, as described in The Statement Window and the Clear Button on page 2-12. The following windows are available:

You can open these windows with either the List Modes menu or the corresponding tools. See The List Modes Menu on page 2-15 and Tools on page 2-15 for details.

The Table List, View List, and Column List Windows

The Table List window contains a list of the tables in the current database; for example:

Figure B-6

The View List and Column List windows, which display lists of views in the current database and columns in a specified table, have a similar appearance.

The User-Defined List Window

The User-Defined List window contains the contents of a user-specified file; for example:

Figure B-7

The file should contain a list of SQL elements to be used in constructing a SQL statement. There must be only one entry per line in the file, and each line can have a maximum of 80 characters.

The Error Window

A certain amount of error information is available in fields in the Connect, Query, and Manipulate interfaces. In addition, an error resulting from an invalid SQL statement displays a special Error window that identifies the location of the error.

Suppose, for example, that you construct the following simple DELETE statement in the statement window of the Manipulate interface:

DELETE * FROM MNIUTES

In this DELETE statement, the table name MINUTES is misspelled as MNIUTES. Because the table MNIUTES does not exist, the database server returns an SQL error message when you process this statement, and the following Error window is displayed:

Figure B-8

The System error and Error code fields show the SQL error message and return code, respectively. The Statement error location consists of the invalid statement with the marker ----->> inserted at the point of error.

For error handling information, including a description of how this error message is constructed, see Handling Errors on page 1-52.

The Error window is provided by the ErrorUI macro. This macro is available for use in other applications; see the on-line reference for ErrorUI.

2.5 Using the Tools Interface

The Tools macro provides access to the user interfaces of the Connect, Query, and Manipulate macros. The Tools interface appears whenever you instance a Tools macro:

Figure B-1

This interface contains three tools.

The Tools macro is intended only as a convenience for launching these three interfaces when you are using the Database Kit in the AVS/Express development environment. It is not intended for use in an application that you develop from AVS/Express.

Note: You must be connected to a database before performing a query or other SQL operation on it. If you are using the Query or Manipulate macro to perform the SQL operation, you will typically use the Connect macro to connect to the database. See Connecting to a Database on page 2-2.

TOC PREV NEXT INDEX

Copyright © 2001 Advanced Visual Systems Inc.
All rights reserved.