ODBC Driver for Workday

Build 23.0.8839

Discovering Schemas

The following sections show how to obtain schema information in the ODBC API. See Data Model to obtain the driver metadata by querying the available system tables.

List Tables and Views

You can use the SQLTables function to list all available tables or views. The TableType parameter can be 'TABLE', 'VIEW', or both, and it is used to decide if tables or views are listed. After calling SQLTables, you can use SQLFetch and SQLBindCol to read the table listing. This is similar to reading columns from any SQL query, described in Querying Data.

SQLTables(hstmt, 0, 0, 0, 0, 0, 0, (SQLCHAR*)"'TABLE','VIEW'", SQL_NTS);

List Table Columns

You can use the SQLColumns function to list all columns of a specified table. After calling SQLColumns, you can use SQLFetch and SQLBindCol to read the column listing. This is similar to reading columns from any SQL query, described in Querying Data.

SQLColumns(hstmt, 0, 0, 0, 0, (SQLCHAR*)"DemoTable", SQL_NTS, 0, 0);

Get Column Information

You can use the SQLColAttribute function to get information about a column. The ColumnNumber parameter is the column index starting at 1. The FieldIdentifier parameter can be SQL_COLUMN_NAME, SQL_COLUMN_LENGTH, SQL_DESC_TYPE etc.

SQLCHAR columnName[30];
SQLSMALLINT cbColumnName;
SQLColAttribute(hstmt, 1, SQL_COLUMN_NAME, columnName, 30, &cbColumnName, NULL);

You can also use the SQLDescribeCol function to get the column name, type, size, decimal digits, and nullability of a column. The ColumnNumber parameter is the column index starting at 1.

SQLCHAR columnName[256];
SQLSMALLINT cbColumnName;
SQLSMALLINT dataType;
SQLULEN columnSize;
SQLSMALLINT decimalDigits;
SQLSMALLINT nullable;
SQLDescribeCol(hstmt, 1, columnName, 256, &cbColumnName, &dataType, &columnSize, &decimalDigits, &nullable);

List Primary Key Columns

You can use the SQLPrimaryKeys function to get the column name and the sequence number for the primary-key columns of a table. After calling SQLPrimaryKeys, use SQLFetch and SQLBindCol to read the table listing. This is similar to reading columns from any SQL query and is described in Querying Data.

SQLPrimaryKeys(hstmt, NULL, SQL_NTS, NULL, SQL_NTS, (SQLCHAR*)"DemoTable", SQL_NTS);

List Procedures

You can use the SQLProcedures function to list all available procedures. After calling SQLProcedures, you can use SQLFetch and SQLBindCol to read the table listing. This is similar to reading columns from any SQL query, described in Querying Data.

SQLProcedures(hstmt, NULL, SQL_NTS, NULL, SQL_NTS, NULL, SQL_NTS);

List Procedure Columns

You can use the SQLProcedureColumns function to list all columns of a specified procedure. After calling SQLProcedureColumns, you can use SQLFetch and SQLBindCol to read the table listing. This is similar to reading columns from any SQL query, described in Querying Data.

SQLProcedureColumns(hstmt, NULL, SQL_NTS, NULL, SQL_NTS, (SQLCHAR*)"DemoProcedure", SQL_NTS, NULL, SQL_NTS);

List Supported ODBC Functions

You can use the SQLGetFunctions function to determine whether a specific ODBC function is supported in the ODBC driver. The FunctionId (second parameter) can be SQL_API_ALL_FUNCTIONS or SQL_API_ODBC3_ALL_FUNCTIONS. The SupportedPtr (third parameter) should be a SQLUSMALLINT array of 100 elements. The call will set the SQLUSMALLINT element to true if the ODBC function is supported by the driver, and false otherwise.

SQLUSMALLINT functions[100];
SQLGetFunctions(hdbc, SQL_API_ALL_FUNCTIONS, functions);

Get Type Information

You can use the SQLGetTypeInfo function to return the information of the specified data type or all types. After calling SQLGetTypeInfo, use SQLFetch and SQLBindCol to read the information; see Querying Data for a code example.

SQLGetTypeInfo(hstmt, SQL_ALL_TYPES);

Get Number of Parameters

You can use the SQLNumParams function to get the parameter count of a query.

SQLSMALLINT paramCount;
SQLNumParams(hstmt, &paramCount);

Get Number of Columns

You can use the SQLNumResultCols function to get the column count of a table.

SQLSMALLINT columnCount;
SQLNumResultCols(hstmt, &columnCount);

Get Number of Rows

You can use the SQLRowCount function to get the row count of a table.

SQLLEN rowCount;
SQLRowCount(hstmt, &rowCount);

List Special Information for Columns

You can use the SQLSpecialColumns function to list all columns of a table with special information. The IdentifierType (second parameter) can be SQL_BEST_ROWID or SQL_ROWVER. The Scope (ninth parameter) can be SQL_SCOPE_CURROW, SQL_SCOPE_TRANSACTION or SQL_SCOPE_SESSION. After calling SQLSpecialColumns, use SQLFetch and SQLBindCol to read the column listing. This is similar to reading columns from any SQL query, described in Querying Data.

SQLSpecialColumns(hstmt, SQL_BEST_ROWID, NULL, 0, NULL,0, (SQLCHAR *)"DemoProcedure", SQL_NTS, SQL_SCOPE_SESSION, SQL_NULLABLE);

List Table Statistics Information

You can use the SQLStatistics function to list statistics and indices associated with a table. The IndexType (eighth parameter) can be SQL_INDEX_UNIQUE or SQL_INDEX_ALL. After calling SQLStatistics, you can use SQLFetch and SQLBindCol to read the table listing. This is similar to reading columns from any SQL query, described in Querying Data.

SQLStatistics(hstmt, NULL, 0, NULL, 0, (SQLCHAR*)"DemoProcedure", SQL_NTS, SQL_INDEX_UNIQUE, SQL_QUICK);

Copyright (c) 2024 CData Software, Inc. - All rights reserved.
Build 23.0.8839