DB_Columns Function

Action

Returns the list of column names in specified tables as a result set on the statement handle.

Syntax

hstmnt = DB_Columns (hdbc, catalog-name, schema-name, table-name, column-name)
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.

catalog-name

Catalog name. STRING.

schema-name

String search pattern for schema names. STRING.

table-name

String search pattern for table names. STRING.

column-name

String search pattern for column names. STRING.

Notes

  • DB_Columns corresponds to SQLColumns. For additional information about SQLColumns, see SQLColumns Function.

  • 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, specify it as NULL.

  • When you receive a valid statement handle, you can call DB_FetchNext or DB_FetchPrevious to manipulate the information.

Example

[+] testcase DBColumnsExample () appstate none 
[ ] // Retrieve all columns beginning with the letter 'I' (or 'I') for all tables
[ ] // beginning with the letter 'p' (or 'P').
[ ] // Assume that hdbc is a valid database handle returned from DB_Connect(). 
[ ] STRING cat, sch, tbl, col, ignore, table, column, dtype 
[ ]
[ ] tbl = "p%"
[ ] cat = NULL
[ ] sch = NULL 
[ ] col = "I%"
[ ]
[ ] HDATABASE hdbc
[ ] HSQL hstmnt 
[ ]
[ ] hdbc = DB_Connect("DSN=<DSNNAME>")
[ ]
[ ] hstmnt = DB_Columns (hdbc, cat, sch, tbl, col) 
[ ] // retrieve and print only column name and type; ignore the rest.
[+] while(DB_FetchNext (hstmnt, ignore, ignore, table, column, ignore, dtype)== TRUE)
[ ] Print ("({dtype}) {table}.{column}") 
[ ] DB_FinishSql (hstmnt) 
[ ]  
[ ] DB_Disconnect (hdbc) 
[ ]