Support for and Implementation of ODBC Functions

Core Functions

Function Name	Purpose	SAS ODBC Driver Implementation
SQLBindParameter	Assigns storage for a parameter in an SQL statement.	SQL_DATA_AT_EXEC is not supported because SAS does not support large data fields.
SQLCancel	Cancels an SQL statement.	This function is used only in asynchronous mode, which SAS does not support. The SAS interprocess communication library does not provide a way to interrupt a transaction in process. SQLCancel does not cause an active statement to terminate immediately, and it does not halt an operation that is already in process. This function always returns SQL_SUCCESS.
SQLColAttributes	Describes the attributes of a column in the result set.	A common reason for applications to call this function is to determine whether a column of data is a dollar amount. With SAS data sets or views, this information is inferred from the format. For more information about SAS formats, see Supported Data Types.
SQLGetCursorName	Returns the cursor name that is associated with a statement.	SAS does not support cursors, so this function returns SQL_ERROR with SQLSTATE set to IM001 (“Driver does not support this function”).
SQLPrepare	Prepares an SQL statement for execution.	This function does not check syntax; syntax is checked later in the SQLExecute function by the server.
SQLSetCursorName	Specifies a cursor name.	SAS does not support cursors, so this function returns SQL_ERROR with SQLSTATE set to IM001 (“Driver does not support this function”).
SQLTransact	Commits or rolls back a transaction.	Always returns SQL_SUCCESS for SQL_COMMIT. Returns an error for SQL_ROLLBACK because SAS does not support transactions.
SQLColumns	Returns the list of column names in specified tables.	SAS uses a specially formatted query to query the virtual table DICTIONARY.COLUMNS.
SQLDriverConnect	Connects to a specific driver by connection string or requests that the ODBC driver manager and the driver display connection dialog boxes for the user.	SAS uses the same dialog boxes that are used in configuration. If input was adequate, SAS continues with the connection, rather than saving the parameters. However, in many cases, input is not required. The connection to the host is made at this time.
SQLGetData	Returns data for a single unbound column in the current row. The application must call SQLFetch or SQLExtendedFetch before calling SQLGetData.	This function enables you to use multiple calls to retrieve data from character variables. Some applications need this functionality to access long character variables. The extension SQL_GD_BLOCK (see SQL_GETDATA_EXTENSIONS under SQLGetInfo) is not supported. This means that you must call SQLExtendedFetch with a rowset size of 1.
SQLParamData	Returns the storage value that is assigned to a parameter at execution time.	This function is used for large data fields, which SAS does not support. The function returns SQL_ERROR with SQLSTATE set to IM001 (“Driver does not support this function”).
SQLPutData	Sends part or all of a data value for a parameter.	This function is used for large data fields, which SAS does not support. The function returns SQL_ERROR with SQLSTATE set to IM001 (“Driver does not support this function”).
SQLSpecialColumns	Retrieves information about the optimal set of columns that uniquely identifies a row in a specified table, or about the columns that are automatically updated when any value in the row is updated by a transaction.	The SAS ODBC Driver issues a query to the DICTIONARY.INDEXES view to obtain this information.
SQLStatistics	Retrieves statistics about a single table and the list of indexes that are associated with the table.	The SAS ODBC Driver issues a query to the DICTIONARY.INDEXES view to obtain this information.
SQLTables	Returns the list of table names stored in a specific data source.	The SAS ODBC Driver issues a query to the DICTIONARY.TABLES and DICTIONARY.MEMBERS views to obtain this information.

ODBC Cursors and the SQLExtendedFetch Function

The SAS ODBC Driver supports the SQLExtendedFetch function to retrieve multiple rows of result data in a single operation. However, SAS does not support real cursor functionality, so the driver cannot truly support cursors.

The implementation of SQLExtendedFetch uses a forward-only cursor to support the syntax of the function. Rows in the result data cannot be skipped, so the parameter IROW (which is the number of the row to begin fetching) is ignored. You cannot retrieve an arbitrary subset of the result data. However, because a single operation can fetch multiple rows, there is less overhead compared to when you use multiple calls to the SQLFetch function.

Note: To support the SQLExtendedFetch function, the SQLSetStmtOption function allows you to set the rowset size and to select row-wise or column-wise binding.

For more information about the SQLExtendedFetch function, see ODBC Programmer's Reference at http://msdn2.microsoft.com/en-us/library/ms714177.aspx.

Catalog Functions

You might want to remove certain SAS libraries from the results of the functions such as SQLColumns, SQLStatistics, SQLSpecialColumns, and SQLTables. Many client applications (for example, Microsoft Access) use these functions to retrieve a list of available tables. Currently, the results from the functions filter out the Maps, Sashelp, and Sasuser libraries. The filtering is done primarily so unwanted tables do not appear in lists that are based on results from these functions. The filtering can improve the performance of these functions.

You can replace this filter by adding the LibWhere key to a data source entry in the system registry. The LibWhere key includes a logical expression to conditionally specify which results to retrieve from the SAS server. The current filter uses the following logical expression:

tbl.libname NE 'MAPS' AND tbl.libname NE 'SASUSER' AND 
tbl.libname NE 'SASADMIN' AND tbl.libname NE 'SASHELP'

The tbl name is important if you need to construct your own filter in these functions.

CAUTION:

When you use the registry editor, you can damage the Windows environment

if your changes are invalid or if you accidentally delete information. Do not create a filter unless it is absolutely necessary. Editing the registry can damage registered programs and make them unusable.

If many libraries are defined on a server, but only specific libraries are needed, you can edit the registry to specify which libraries to access. For information about how to edit the registry, see the discussion of catalog functions in the online Help of the SAS ODBC Driver Configuration dialog box.

ODBC Scalar Functions

The SAS ODBC Driver supports the following ODBC scalar functions:

String Functions:

ASCII, CHAR, CONCAT, LCASE, LEFT, LTRIM, REPEAT, REPLACE, RTRIM, SOUNDEX, SPACE, SUBSTRING, UCASE

Numeric Functions:

ABS, ACOS, ASIN, ATAN, CEILING, COS, EXP, FLOOR, LOG, LOG10, MOD, POWER, RAND, ROUND, SIGN, SQRT, TAN

Time, Date, and Interval Functions:

CURDATE, CURTIME, DAYNAME, DAYOFMONTH, DAYOFWEEK, DAYOFYEAR, HOUR, MINUTE, MONTH, MONTHNAME, NOW, QUARTER, SECOND, YEAR

The ODBC RAND scalar function is translated to a SAS function. For more information about the usage and parameters of ODBC scalar functions, see ODBC Programmer's Reference at http://msdn2.microsoft.com/en-us/library/ms714177.aspx.