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
SQL tables. 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'
is case sensitive
and is required to distinguish special queries from regular queries.
is case sensitive
and is the specific API that is being called.
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.
-
An 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.
- 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.
The following example
connects to a Microsoft Excel workbook. The example does the following:
-
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
PC Files Server must
be running on the server specified for this example to work.
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;