SQL Pass-Through Facility Specifics for JDBC

Key Information

For general information about this feature, see SQL Pass-Through Facility.

Here are the SQL pass-through facility specifics for the JDBC interface.

• The dbms-name is JDBC.
• The CONNECT statement is required.
• PROC SQL supports multiple connections to JDBC. If you use multiple simultaneous connections, you must use the alias argument to identify the different connections. If you do not specify an alias, the default JDBC alias is used. The functionality of multiple connections to the same JDBC data source might be limited by the particular data source driver.
• The CONNECT statement database-connection-arguments are identical to its LIBNAME connection-options.
• On some DBMSs, the DBMS-SQL-query argument can be a DBMS-specific SQL EXECUTE statement that executes a DBMS stored procedure. However, if the stored procedure contains more than one query, only the first query is processed.

CONNECT Statement Examples

These examples use JDBC to connect to a PostgreSQL data source. The first example uses the connection method that is guaranteed to be present at the lowest level of JDBC conformance.

proc sql;
connect to jdbc as C (driverclass="org.postgresql.Driver" 
   URL="jdbc:postgresql://<PostgreSQL-server>:<port>/<PostgreSQL-database>" 
   user=user1 password="myPwd" classpath="C:\Progam Files\SASHome\AccessClients\9.4\DataDrivers\jdbc\postgresql");  
select * from connection to c (select * from class);   
quit;

If a LIBNAME statement has been specified, you can use the libref to connect to the JDBC data source.

libname x jdbc driverclass="org.postgresql.Driver" 
   URL="jdbc:postgresql://<PostgreSQL-server>:<port>/<PostgreSQL-database>" 
   user=user1 password="myPwd" classpath="C:\Progam Files\SASHome\AccessClients\9.4\DataDrivers\jdbc\postgresql";

proc sql; 
connect using x; 
select * from connection to x (select * from class);     
quit;

proc sql;
connect using x;
execute (create table test12(c char(10))) by x;
quit;