dbo.fSQLSpecialColumns

dbo.fSQLSpecialColumns

This function retrieves column information for a specified table for the following:

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

dbo.fSQLSpecialColumns (<'database_qualifier' | null>, <'table_name' | null>, <'nullable' | null>)

Table 120

dbo.fSQLSpecialColumns Arguments

database_qualifier

Current database

Name of the database from which the details are to be obtained

(no default value)

Name of the table whose column information is required

(no default value)

Determines whether to return special columns that can have a NULL value. Must be one of the following:

•

0 = SQL_NO_NULLS—exclude special columns that can have NULL values.

•

1 = SQL_NULLABLE—return special columns even if they can have NULL values.

Returned Result Set

Table 121

dbo.fSQLSpecialColumns Returned Result Set

Scope of the rowid. Contains one of the following values:

•

0 = SQL_SCOPE_CURROW

•

1 = SQL_SCOPE_TRANSACTION

•

2 = SQL_SCOPE_SESSION

NULL is returned when IdentifierType is SQL_ROWVER.

VARCHAR not NULL

Name of the column. An empty string is returned for a column that does not have a name.

SMALLINT not NULL

ODBC SQL data type. See also Pervasive PSQL Supported Data Types.

Size of the procedure column in the database. See also Precision and Scale of Decimal Data Types.

Length in bytes of data transferred on an SQLGetData or SQLFetch operation if SQL_C_DEFAULT is specified. For numeric data, this size may be different than the size of the data stored in the database. See also Pervasive PSQL Supported Data Types.

Number of decimal digits of the procedure column in the database. NULL is returned for data types where decimal digits is not applicable. See also also Precision and Scale of Decimal Data Types.

Indicates whether the column is a pseudo-column.

•

0 = SQL_PC_UNKNOWN

Pervasive PSQL does not support pseudo-columns.

This example creates a table with two columns that uniquely identify a row and are automatically updated when a transaction updates any value in the row.

CREATE TABLE t2 (c1 IDENTITY, c2 INTEGER, c3 SMALLINT NOT NULL, c4 TIMESTAMP NOT NULL)

ALTER TABLE t2 ADD PRIMARY KEY (c1, c4)

SELECT * FROM dbo.fSQLSpecialColumns ('Demodata' ,'t2' , 'null')

Result Set (abbreviated for space considerations):

COLUMN_NAME DATA_TYPE TYPE_NAME PRECISION LENGTH

=========== ========= ========= ========= ======

c1 4 INTEGER 4 4

c4 11 DATETIME 16 16

2 rows were affected.