Pass-Through Facility: PC Files on Linux, UNIX, and 64-Bit Microsoft Windows

Special PC Files Queries

SAS/ACCESS Interface to PC Files on UNIX supports special queries. Many databases provide or use system tables that allow queries to return the list of available tables, columns, procedures, and other useful information. In PC files, much of this functionality is provided through special APIs (application programming interfaces). This is done in order to accommodate databases that are not structured as an SQL table. You can use these special queries on non-SQL and SQL databases. The general format of special queries is as follows:

PCFILES::SQLAPI 'parameter 1',...'parameter n'

PCFILES::

is case-sensitive and is required to distinguish special queries from regular queries.

SQLAPI

is case-sensitive and is the specific API that is being called.

' parameter n'

is a series of quoted strings delimited by commas. Within the quoted string, two characters are universally recognized: the percent sign (%) and the underscore (_).

The percent sign matches any sequence of zero or more characters
The underscore represents any single character.

Each driver also has an escape character that can be used to place characters within the string. Consult the driver's documentation to determine the valid escape character.

The values for the special query arguments are DBMS specific.

For example, you supply the fully qualified table name for a "Catalog" argument. In dBASE, the value of "Catalog" might be c:\dbase\tst.dbf and in SQL Server, the value might be test.customer .

Depending on the DBMS that you are using, valid values for the "Schema" argument might be a user ID, a database name, or a library. All arguments are optional. If you specify some but not all arguments within a parameter, use a comma to indicate the omitted parameters. If you do not specify any parameters, commas are not necessary.

Note: These special queries might not be available for all PCFILES drivers. [cautionend]

The following special queries are supported:

PCFILES::SQLTables <"Catalog", "Schema", "Table-name", "Type">: returns a list of all tables that match the specified arguments. If no arguments are specified, all accessible table names and information are returned.
PCFILES::SQLColumns <"Catalog", "Schema", "Table-name", "Column-name">: returns a list of all columns that match the specified arguments. If no arguments are specified, all accessible column names and information are returned.
PCFILES::SQLColumnPrivileges <"Catalog", "Schema", "Table-name", "Column-name">: returns a list of all column privileges that match the specified arguments. If no arguments are specified, all accessible column names and privilege information are returned.
PCFILES::SQLForeignKeys <"PK-catalog", "PK-schema", "PK-table-name", "FK-catalog", "FKschema", "FKtable-name">: returns a list of all columns that comprise foreign keys that match the specified arguments. If no arguments are specified, all accessible foreign key columns and information are returned.
PCFILES::SQLPrimaryKeys <"Catalog", "Schema", "Table-name">: returns a list of all columns that compose the primary key that matches the specified table. A primary key can be composed of one or more columns. If no table name is specified, this special query fails.
PCFILES::SQLProcedureColumns <"Catalog", "Schema", "procedure-name", "Column-name">: returns a list of all procedure columns that match the specified arguments. If no arguments are specified, all accessible procedure columns are returned.
PCFILES::SQLProcedures <"Catalog", "Schema", "procedure-name">: returns a list of all procedures that match the specified arguments. If no arguments are specified, all accessible procedures are returned.
PCFILES::SQLSpecialColumns <"Identifier-type", "Catalog-name", "Schema-name", "Table-name", "Scope", "Nullable">: returns a list of the optimal set of columns that uniquely identify a row in the specified table.
PCFILES::SQLStatistics <"Catalog", "Schema", "Table-name">: returns a list of the statistics for the specified table name, with options of SQL_INDEX_ALL and SQL_ENSURE set in the SQLStatistics API call. If the table name argument is not specified, this special query fails.
PCFILES::SQLTablePrivileges <"Catalog", "Schema", "Table-name">: returns a list of all tables and associated privileges that match the specified arguments. If no arguments are specified, all accessible table names and associated privileges are returned.
PCFILES::SQLGetTypeInfo: returns information about the data types that are supported in the data source.

Example: Connect to Microsoft Excel using PC Files Server

This example connects to a Microsoft EXCEL workbook.

lists the columns of Sheet1$
lists the available type info
creates a SAS data set with the list of tables (sheets) in the Microsoft EXCEL workbook sheet
the PRINT procedure prints the created data set.

Note: PC Files Server must be running on the server specified for this example to work. [cautionend]

PROC SQL; 
   CONNECT TO PCFILES AS DB (SERVER=d1234 PATH='c:\xl.xls' );
   SELECT * FROM CONNECTION TO DB (PCFILES::SQLColumns "","","Sheet1$",""); 
   SELECT * FROM CONNECTION TO DB (PCFILES::SQLLGetTypeInfo); 
   CREATE TABLE work AS SELECT * FROM CONNECTION TO DB (PCFILES::SQLTables "","","","");
QUIT;
PROC PRINT DATA=work;
RUN;

Top of Page