3 Database-Specific Usage

This chapter details database-specific usage of the Database Kit. The sections include:

For information on current release versions of databases and their compatibility with the Database Kit and AVS/Express see the book Installing AVS/Express or our web site at http://www.avs.com/. Any critical information about database changes will be found in the release notes.

This section provides database-specific usage notes for developing and using Database Kit applications that access Oracle's standard databases, Spatial Data Option, and the Spatial Data Cartridge.

Oracle connects are either local or remote. In either case, a SQL*Net component is involved. By default, your Database Kit is linked with the SQL*Net protocols noted in Section 1.7 Database Kit Requirements of the Installing AVS/Express manual.

When connecting to a database, you must supply either a connect string or the elements from which to construct a connect string. The format of a connect string is database specific. In addition, connect strings can vary depending on how the database driver has been installed, what networking component(s) it uses, where the associated server resides, and so forth.

Generally, you should see your database administrator to obtain a valid connect string. However, this section provides some general information on Oracle connect string format. For more information, see your database documentation.

An Oracle connect string always requires a user name. In addition, it may require a user password and/or the name or alias of a database, depending on how the user's database administrator has configured the server and set up the user's account. For example, the user account may not require a password, or the user may be allowed to connect only to one database.

If SQL*Plus is available on your machine, you can use it to verify that a connect string is valid. To do this, try to connect to the tool using the desired connect string. If the connect is successful, the connect string is valid and can be used with AVS/Express. If SQL*Plus is not available on your machine, you should locate a machine on which it is installed and test the validity of the connect string there.

In some circumstances, a connect string will work with SQL*Plus but not with the Database Kit. This may occur because different network drivers are linked to the Database Kit than to the SQL*Plus; there may also be other site-specific reasons for this problem. Ask your database administrator for assistance.

In order to resolve this problem, your database administrator may need to know how the underlying layers of the Database Kit handle a connect attempt. The Database Kit uses an Oracle Call Interface (OCI) call named olog to connect to a database. Here is the format of the olog call:

if (olog(lda_ptr, hda_ptr, (text *) uid_connect, -1,
(text *) 0, -1, (text *) 0, -1, OCI_LM_DEF))

Recall that you can supply either a connect string or the elements from which to construct a connect string. In either case, the connect string is passed to the Oracle server as the uid parameter of the olog call. This field contains the connect string in whatever format it is supplied. (See the previous section or your database documentation for more information on connect string formats.)

Note: The Database Kit does not use the pswd parameter of the olog call; this field is null.

Oracle maintains data in the data dictionary as a set of internal datatypes. The following table lists these datatypes and their specifications, and notes the C-language datatype to which the Database Kit maps them. (The list of internal datatypes and specifications are derived from the Oracle documentation.)

Table C-1


Oracle internal type	Database Kit C-language type
varchar2	char
number	varies (see below)
long	unsupported
rowid	char
date	char
raw	string
long raw	unsupported
char	char
mlslabel	char

Note: As of version 7.1x, Oracle returns a SCALE of 0 for all columns that result from the use of UNION, UNION ALL and INTERSECT operators. The database-independent layer relies completely on the "describe" feature of the Oracle Call-level Interface (OCI) when deciding on datatypes and will therefore map them to a fixed type. This will generate a runtime Oracle NUMERIC OVERFLOW error, which in turn will generate the Database Kit error DB ERROR: Recoverable database error. To work around the problem, use the use_floating_type_only parameter to specify that all data is mapped to a floating type (see Mapping Data to Supported Database Kit Datatypes on page 1-22).

Oracle stores all columns defined for a table as one of these internal datatypes, regardless of the exact mode of creation. For example, Oracle supports the ANSI datatypes of float, int, and so forth in the CREATE TABLE statement, but stores both the float and int data as the "number" internal datatype. When the Database Kit queries the database server about a datatype, the original, Oracle user-specified datatype - for example, "float" - is lost and the appropriate Oracle internal type - "number" - is returned along with the scale and precision as maintained internally by Oracle. The Database Kit supports only C-language datatypes, and when a nonzero scale is indicated, it stores the "float" data as C-language float or double float data.

For Oracle databases, the fetchret, fetchind, and fetchsize buffers are copied directly from the arrays used by the OCI and are not modified by the Database Kit.

The Oracle database-specific module is used to access both the standard and the Spatial Data Option databases. The OCI was used to build the Oracle database-specific module. (The version of the OCI varies by platform.) The following C-language calls are used:

Table C-2


C-language call	Purpose
olog	connect
oparse	parse SQL statement
oexec	execute SQL statement
ofen	fetch data into column arrays
ocom	commit
orol	rollback
odescr	describe SQL statement
oopen	open cursor
oclose	close cursor
ologof	disconnect
obreak	break query processing
odefin	bind output arrays
oerhms	get error message

AVS/Express supports the Open Database Connectivity (ODBC) interface. This section describes issues associated with developing and using AVS/Express applications that use the ODBC interface to access databases on a PC.

Note: The ODBC interface is a Windows-only interface; it is not supported on UNIX platforms.

AVS/Express uses the ODBC product for connecting to an ODBC compliant database server. The Database Kit includes object code from ODBC libraries that are normally found in the directory C:\MSDEV\LIB. The ODBC library is called odbc32.lib.

The ODBC setup tool for Windows NT is located in the Control Panel. This setup tool maintains the ODBC data sources and drivers for connecting to a database server. This tool helps you add drivers, determine what drivers are already installed on your PC, and set up the correct connection information to the database of choice. When using the setup tool, you specify the information that is required when connecting to the database via AVS/Express; for example, the elements to establish a physical connection to the database server.

The information that follows is general connection information. The required information is database specific, so you should see your database administrator or your database documentation to obtain valid connection information.

The table below defines the connect elements and their meaning:


name	A user ID (user name); for example, SCOTT
password	The user's password; for example, TIGER.
db	A data source name as specified in ODBC tool; for example, EXPRESS.

ODBC maintains data in the data library as a set of internal datatypes. The following table lists these datatypes and maps them to their equivalent C-language datatype.

Note: ODBC stores all columns defined for a table as one of these internal datatypes, regardless of the exact mode of creation. The Database Kit supports only C-language datatypes, and when a non-zero scale is indicated, it stores the "float" data using C-language float or double float format.


ODBC internal type	Database Kit C-language type
SQL_TINYINT	byte
SQL_CHAR	char
SQL_VARCHAR	char[n]
SQL_DATE	char[]
SQL_TIME	char[]
SQL_TIMESTAMP	char[]
SQL_DECIMAL	double
SQL_NUMERIC	double
SQL_LONGVARCHAR	unsupported
SQL_BIT	unsupported
SQL_SMALLINT	short
SQL_INTEGER	int
SQL_BIGINT	unsupported
SQL_REAL	float
SQL_FLOAT	float
SQL_DOUBLE	double
SQL_BINARY	unsupported
SQL_VARBINARY	unsupported
SQL_LONGVARBINARY	unsupported

The Database Toolkit

3 Database-Specific Usage