Home

	Expand Search

Access Service User's Guide DirectConnect Anywhere

Chapter 12 Accessing Catalog Information with CSPs

Chapter 12

Accessing Catalog Information with CSPs

This chapter describes how to use catalog stored procedures (CSPs). It covers the following topics:

• General description

• ODBC information

• sp_column_privileges

• sp_columns

• sp_databases

• sp_datatype_info

• sp_fkeys

• sp_pkeys

• sp_server_info

• sp_special_columns

• sp_sproc_columns

• sp_statistics

• sp_stored_procedures

• sp_table_privileges

• sp_tables

General description

CSPs are specially recognized commands that return catalog information. Client applications use CSPs instead of SQL to access information contained in the system catalog of the target database. The service library implements CSPs by executing stored procedures against the target catalog.

When you invoke a CSP, the access service executes a stored procedure that returns a result set that attempts to match the results an Adaptive Server would return under the same circumstances. Because the ODBC catalog is significantly different from the Adaptive Server catalog, an exact match is impossible. To make the results match, the access service performs additional computations as the rows are returned.

Because some information is static, sp_datatype_info uses memory tables to improve the speed of operation.

Syntax

The following syntactical rules apply to CSPs:

• Arguments can be delimited with commas and identified by position, as shown by the following:

  sp_columns  parm1,parm2

• Arguments can be identified using the keyword NULL, as shown by the following:

  sp_columns  NULL, NULL, parm3

• Empty arguments can be identified using empty strings, as shown by the following:

  sp_columns ' ',' ',smith

• Arguments can be named using the syntax @name=parm, as shown by the following:

  sp_columns @table_owner=smith

The positional forms (1, 2, and 3) cannot be mixed with the named form (4).

The service library does not support the TABLE_QUALIFIER or PROCEDURE_QUALIFIER parameters. For all CSPs, leave the parameter empty or set it to NULL.

The argument syntax for most of the CSPs referenced in this chapter is contained in the Sybase Adaptive Server Reference Manual, v 2.

RPC events

You can execute CSPs using a language command or an RPC event.

When the access service processes a CSP as an RPC event, it retrieves the name and parameters from the client application using standard RPC processing techniques. However, the argument cannot be named using the following syntax:

@name=parm  form

Treatment of special characters

The access service supports only the "%" wildcard character, which can be used in parameters that allow wildcard-character search patterns. The character represents any string of zero or more characters.

The access service treats all underscore characters as literals.

ODBC information

The ODBC definition of the CSPS is the model for behavior. If the access service cannot support a particular procedure, it returns the expected column form descriptors, with no data rows.

The format and content of results returned by most CSPs are described in the Microsoft ODBC 2.0 Programmer's Reference and SDK Guide.

ODBC conformance levels

The three conformance levels for ODBC drivers are core, level one, and level two. CSPs that support the functionality in levels one and two are as follows:

• sp_columns

• sp_datatype_info

• sp_special_columns

• sp_statistics

• sp_tables

• sp_column_privileges

• sp_fkeys

• sp_pkeys

• sp_sproc_columns

• sp_stored_procedures

• sp_table_privileges

Compatibility

To maintain compatibility with Adaptive Server and previous versions of the MDI Database Gateway, all CSPs accomplish the same tasks as their counterparts in the other systems. Areas in which DirectConnect Anywhere differs are as follows:

• The result sets returned conform to ODBC requirements.

• The result sets returned confirm to ASE/CIS requirements.

When a CSP requirements conflict arises, the following precedence rules are used:

• Make the results conform to ODBC specifications.

• Make the results conform to ASE/CIS specifications.

• Make the behavior of the procedure conform to its counterpart in Adaptive Server.

• Make the behavior of the procedure conform to its counterpart in Database Gateway.

sp_column_privileges

Description

Returns column privilege information for one or more columns in a table or view.

Syntax

sp_column_privileges table_name [, table_owner[, table_qualifier [, column_name]]]

Parameters

is the name of the table. Wildcard-character search patterns are not supported.

is the name of the table owner. Wildcard-character search patterns are not supported.

is ignored. Leave blank or set to NULL.

is the name of the column for which you want privilege information. Use wildcard-character search patterns to request information about more than one column. Leave blank or set to NULL to request information about all columns in the table or tables.

Usage

• This procedure corresponds to the ODBC function SQLColumnPrivileges.

sp_columns

Description

Returns information about the type of data that can be stored in one or more columns.

Syntax

sp_columns table_name [, table_owner]
 [, table_qualifier] [, column_name]

Parameters

is the table name or view. Use the wildcard character to request information about more than one table.

is the owner of the table or view. Use the wildcard character to request information about tables owned by more than one user.

is ignored. Leave blank or set to NULL.

is the name of the column for which you want information. Use the wildcard character to request information about more than one column.

Usage

• This procedure returns the Adaptive Server datatype that most clearly matches the native datatype of the target, regardless of the current datatype properties.

• This procedure corresponds to the ODBC function SQLColumns.

This procedure returns one row containing a description of each column in a table.

• There are three columns in the result set that describe each columns data type; TYPE_NAME, DATA_TYPE, and REMOTE_DATA_TYPE.

• Table 12-1 describes the values returned in the TYPE_NAME and DATA_TYPE columns of the result set. TYPE_NAME contains the ODBC data type name and DATA_TYPE contains the ODBC integer identifier.

• The REMOTE_DATA_TYPE column contains a 32-bit or 4 byte composite user datatype UDT specifically identifying the remote data type. See Table 12-2 .

Table 12-1 also describes the identifiers returned in the TYPE_NAME and DATA_TYPE columns in the result set for sp_special_columns.

This procedure allows transmission of column datatypes using a target-specific type ID. The REMOTE_DATA_TYPE column contains a 32-bit composite datatype defined by the access service.

The following table describes the datatype value.

sp_databases

Description

Returns a list of databases on a target DBMS.

Syntax

sp_databases

Parameters

Usage

Not defined

sp_datatype_info

Description

Returns information about a particular datatype or all supported datatypes.

Syntax

sp_datatype_info [data_type]

Parameters

is the ODBC code number for the specified datatype for which sp_datatype_info returns information. See Table 12-1 for a description of these codes.

Usage

• The data_type parameter specifies the ODBC datatype for which information is requested. If this parameter is not provided, sp_datatype_info returns information about all supported datatypes.

• This procedure corresponds to the ODBC function SQLGetTypeInfo.

• The DatatypeInfo property specifies whether information is returned for Transact SQL datatypes or target database datatypes. For configuration information, see "DatatypeInfo" . If the value for data_type equals:

  • target the sp_datatype_info returns all target datatypes and their associated ODBC datatypes. A specific ODBC datatype may be used to represent multiple target datatypes.

  • transact the sp_datatype_info returns the TSQL datatype that best matches each ODBC datatype that the target represents.

sp_datatype_info returns a list of datatypes with information about each. Results are ordered by the following columns:

• DATA_TYPE

• TYPE_NAME

The lengths for varchar columns shown in the result set tables are maximums; the actual lengths depend on the target database.

The following table shows the result set.

sp_fkeys

Description

Returns primary and foreign key information for the specified table or tables. Foreign keys must be declared through the ANSI integrity constraint mechanism.

Syntax

sp_fkeys pktable_name [, pktable_owner]
 [, pktable_qualifier] [, fktable_name] 
 [, fktable_owner] [, fktable_qualifier]

Parameters

is the name of the table containing the primary key. Wildcard-character search patterns are not supported. You must specify this parameter, the fktable_name parameter, or both.

is the owner of the table containing the primary key. Wildcard-character search patterns are not supported.

is ignored. Leave blank or set to NULL.

is the name of the table containing the foreign key. Wildcard-character search patterns are not supported. You must specify this parameter, the pktable_name parameter, or both.

is the owner of the table containing the foreign key. Wildcard-character search patterns are not supported.

is ignored. Leave blank or set to NULL.

Usage

This procedure corresponds to the ODBC function SQLForeignKeys.

sp_pkeys

Description

Returns primary key information for a single table. Primary keys must be declared through the ANSI integrity constraint mechanism.

Syntax

sp_pkeys table_name [, table_owner]
 [, table_qualifier]

Parameters

is the name of the table. Wildcard-character search patterns are not supported.

is the owner of the table. Wildcard-character search patterns are not supported.

is ignored. Leave blank or set to NULL.

Usage

This procedure corresponds to the ODBC function SQLPrimaryKeys.

sp_server_info

Description

Returns target server metadata containing a list of attribute names and matching values for the target.

Syntax

sp_server_info [attribute_id]

Parameters

is the integer ID of the attribute.

Usage

This procedure generates an extensible result set. It can be expanded, depending upon the needs of the specific access service library.

sp_special_columns

Description

Retrieves the following information about columns within a specified table or view:

• The optimal set of columns that uniquely identifies a row in the table or view

• The columns that are automatically updated when any value in the row is updated by a transaction

Syntax

sp_special_columns table_name [, table_owner]
 [, table_qualifier] [, col_type]

Parameters

is the name of the table. Wildcard-character search patterns are not supported.

is the owner of the table. Wildcard-character search patterns are not supported.

is ignored. Leave blank or set to NULL.

is a value that requests information about columns of a specific type as follows:

"R" returns information about columns with values that uniquely identify any row in the table.

"V" returns information about columns with values that are automatically generated by a target each time a row is inserted or updated.

Usage

• This procedure corresponds to the ODBC function SQLSpecialColumns.

• See Table 12-1 for ODBC datatypes and matching ODBC integer identifiers returned in the TYPE_NAME and DATA_TYPE columns of the result set.

sp_sproc_columns

Description

Returns information about stored procedure input and return parameters.

Syntax

DirectConnect supports SQL-Server version 10 and 11 syntax.

sp_sproc_columns sp_name [, sp_owner]
 [, sp_qualifier] [, column_name]

or

sp_sproc_columns procedure_name [, procedure_owner]
 [, procedure_qualifier] [, column_name]

Parameters

is the name of the stored procedure. Wildcard-character search patterns are not supported.

is the owner of the stored procedure. Wildcard-character search patterns are not supported.

is ignored. Leave blank or set to NULL.

is the name of the parameter about which you want information. If you do not supply a parameter name, this procedure returns information about all input parameters.

Usage

This procedure corresponds to the ODBC function SQLProcedureColumns.

In DirectConnect Anywhere, sp_sproc_columns returns extra, unsolicited columns.

sp_statistics

Description

Returns a list of indexes in a single table.

Syntax

sp_statistics table_name [, table_owner]
 [, table_qualifier] [, index_name] [, is_unique]

Parameters

is name of the table. Wildcard-character search patterns are not supported.

is the owner of the table.

is ignored. Leave blank or set to NULL.

is one of the following values:

"Y" if unique indexes are to be returned

"N" if unique indexes are not to be returned

is the name of the index. Wildcard-character search patterns are not supported.

Usage

With all platforms in DirectConnect Anywhere, the index_name parameter is ignored, regardless of the value. This applies even if you set the value to a nonexistent name or to NULL.

This procedure corresponds to the ODBC function SQLStatistics.

sp_stored_procedures

Description

Returns a list of available procedures.

Syntax

sp_stored_procedures [sp_name] [, sp_owner]
 [, sp_qualifier]

Parameters

is the stored procedure name. Use the wildcard character to request information about more than one stored procedure.

is the owner of the stored procedure. Use the wildcard character to request information about procedures owned by more than one user.

is the name of the database. Acceptable values are the current database or NULL.

Usage

This procedure corresponds to the ODBC function SQLProcedures.

sp_table_privileges

Description

Returns privilege information for all columns in a table or view.

Syntax

sp_table_privileges table_name [, table_owner[, table_qualifier]]

Parameters

is the name of the table. Wildcard-character search patterns are not supported.

is the name of the table owner. Wildcard-character search patterns are not supported.

is ignored. Leave blank or set to NULL.

Usage

This procedure corresponds to the ODBC function SQLTablePrivileges.

sp_tables

Description

Returns a list of objects that can appear in a from clause.

Syntax

sp_tables [table_name] [, table_owner]
 [, table_qualifier] [, table_type]

Parameters

is the name of the table. Use the wildcard character to request information about more than one table.

is the owner of the table. Use the wildcard character to request information about tables owned by more than one user.

is the name of the database. Acceptable values are the current database or NULL.

is a list of values, separated by commas, that gives information about all tables of the types specified, including the following:

"'TABLE', 'SYSTEM TABLE', 'VIEW', 'SYNONYM'"

Usage

• Enclose each table type with single quotation marks and enclose the entire parameter with double quotation marks. Enter table types in uppercase.

• This procedure corresponds to the ODBC function SQLTables.