DB_SpecialColumns Function

Action

Retrieves the optimal set of columns, which uniquely identifies a row in the specified table, and the columns that are automatically updated when any value in the row is updated by a transaction.

Syntax

hstmnt = DB_SpecialColumns (hdbc, type, catalog-name, schema-name, table-name, scope, nullable)
Variable Description
hstmnt

The returned handle to the executed SQL statement. This is an input parameter for other DBTester functions, for example DB_FetchNext. HSQL.

hdbc

The handle to a database as returned by DB_Connect. HDATABASE.

type
The type of the column to return.
  • "1" for SQL_BEST_ROWID
  • "2" for SQL_ROWVER
STRING.
catalog-name

Catalog name for the table. STRING.

schema-name

Schema name for the table. STRING.

table-name

Table name. STRING.

scope
Minimum required scope of the rowid.
  • "0" for SQL_SCOPE_CURROW
  • "1" for SQL_SCOPE_TRANSACTION
  • "2" for SQL_SCOPE_SESSION
STRING.
nullable
Determines whether or not to return special columns that can have a NULL value.
  • "0" for SQL_NONULLS
  • "1" for SQL_NULLABLE
STRING.

Notes

  • DB_SpecialColumns corresponds to SQLSpecialColumns. For additional information about SQLSpecialColumns, see SQLSpecialColumns Function.
  • The type argument can have one of the following values.
    Value Description
    "1" SQL_BEST_ROWID returns one ore more optimal columns that allow any row in the specified table to be uniquely identified.
    "2" SQL_ROWVER returns the columns that are automatically updated by the data source when any value in the row is updated by any transaction.
  • The table-name argument is required. It cannot be an empty string or contain a wildcard.

  • The following wildcard characters are supported for arguments:
    Wildcard character Matches
    % The percent sign matches any character sequence.
    _ The underscore matches any single character.
  • To omit a variable argument, which is not a required argument, specify the argument as an empty string ("").

  • The scope argument can have one of the following values:
    Value Meaning Description
    "0" SQL_SCOPE_CURROW The rowid is guaranteed to be valid while positioned on the current row.
    "1" SQL_SCOPE_TRANSACTION The rowid is guaranteed to be valid for the duration of the current transaction.
    "2" SQL_SCOPE_SESSION The rowid is guaranteed to be valid for the duration of the session, across transaction boundaries.
  • The nullable argument can have one of the following values.
    Value Meaning Description
    "0" SQL_NONULLS Exclude special columns that can have NULL values.
    "1" SQL_NULLABLE Return special columns even if they can have NULL values.
  • When you receive a valid statement handle, you can call DB_FetchNext or DB_FetchPrevious to manipulate the information.

Example

[ ] STRING cat, sch, name, ignore
[ ] INTEGER I 
[ ] 
[ ] hstmnt = DB_SpecialColumns (hdbc, "1", cat, sch, " ", "1", "1")
[ ] // retrieve columns and data types; ignore the rest.
[-] while (DB_FetchNext (hstmt, ignore, name, I) == TRUE) {
	[ ] //print columns and data types ...
[-] }