Accessing DBMS and SPD Server Data

Requirements

Your Java application must meet the following requirements:

Use the standard JDBC data-access calls.
Specify the appropriate JDBC driver class name.
Specify the appropriate host server user ID and host server password.
Specify the subprotocol and subname for the driver that you are using.
Use the appropriate dbms and dbmsOptions connection properties to specify the connection information.
Use SQL that is specific to the underlying DBMS or the SPD Server.

What Are SAS Engines?

An engine is a component of SAS software that is used to read from or write to a source of data. There are several types of SAS engines, including engines that SAS/ACCESS software uses to connect to a variety of data sources other than Base SAS. Specifically, this recipe discusses how to use SAS/ACCESS engines to access data that is stored in a third-party DBMS or on an SPD Server.

In order to access the DBMS or SPD Server, the JDBC driver uses the dbms connection property. The dbms connection property specifies the name of the engine that is used to connect directly to the underlying DBMS or the SPD Server. Connecting through an engine enables you to use the SQL syntax of the underlying DBMS or SPD Server.

Valid dbms values include oracle and db2 for use with the IOM driver and spds for use with the SAS/SHARE driver.

After you use the dbms property to specify a DBMS engine or the SPD Server engine, you use the dbmsOptions property to specify the engine-specific options. Valid dbmsOptions include user, path, and dsn for DBMS engines, and host and serv for the SPD Server engine.

Note: The JDBC drivers do not process the dbms and dbmsOptions values. The drivers simply pass the values down to the server.

More About Engine-Specific Options

For a DBMS, engine-specific options are usually the same as the database-connection-arguments for the CONNECT statement, which is part of the SAS/ACCESS syntax for the Pass-Through Facility for relational databases. For more information, see “DBMS-Specific Reference” in SAS/ACCESS for Relational Databases: Reference.

For information about SPD Server options, see the SAS Scalable Performance Data Server: User's Guide.

Assigning Librefs to a DBMS or SPD Server

You can also assign a libref to a DBMS or the SPD Server. However, if you use a libref, then the connection is implicit. An implicit connection uses SAS SQL syntax instead of the underlying SQL syntax. In addition, a connection that is made by using a libref does not support all of the SQL procedure functionality.

Sample Code for Accessing an Oracle DBMS on a SAS Workspace Server

In this example, you are accessing an Oracle DBMS that is hosted on a SAS Workspace Server. Here are the scenario details:

Because you are accessing a SAS Workspace Server, you must use the IOM driver. The driver class name for the IOM driver is com.sas.rio.MVADriver.
Because the workspace server is password-protected, you specify values for the user and password connection properties. These credentials are authenticated to SAS, as opposed to credentials authenticated to the DBMS.
You identify the Oracle DBMS by specifying the appropriate values for the dbms and the dbmsOptions connection properties. The dbmsOptions property is the object to use for identifying the user and password credentials authenticated by the DBMS.
The host name for the workspace server is c123.na.abc.com, and the port number is 8591.

import java.sql.*;
import java.util.Properties;

public class iomAccessDbms
{
  public static void main(String argv[])
  {
    Connection connection;
    Properties props;
    int i;
    Statement statement;
    String queryString = "SELECT sup_id, sup_name FROM suppliers ORDER BY sup_name";
    ResultSet result;
    double id;
    String name;

    try {
      //CONNECT TO THE SERVER BY USING A CONNECTION PROPERTY LIST
      Class.forName("com.sas.rio.MVADriver");
      props = new Properties();
      props.setProperty("user","oracleUserJdoe");  /* user acct for SAS */
      props.setProperty("password","oracle2ytr8"); /* passwd for SAS acct */
      props.setProperty("dbms", "ORACLE");      
      props.setProperty("dbmsOptions","user='oracleAdminRsmith' " +
         "password='admin3yr8' path='oraclev7'");

      connection = DriverManager.getConnection(
          "jdbc:sasiom://c123.na.abc.com:8591",props);

      //ACCESS DATA
      statement = connection.createStatement();
      result = statement.executeQuery(queryString);
      while (result.next()){
        id = result.getDouble(1);
        name = result.getString(2);
        System.out.println(id + " " + name);
      }
      statement.close();
      connection.close();
    }
    catch(Exception e){
      System.out.println("error " + e);
    }
  }
}

You can also use the JDBC URL to specify connection properties as shown in the following example. Note that the credentials oracleUserJdoe and oracle2ytr8 are authenticated by SAS. The credentials in the dbmsOptions property are authenticated by the database.

connection = DriverManager.getConnection(
  "jdbc:sasiom://c123.na.abc.com:8591?user=oracleUserJdoe&password=oracle2ytr8" +
  "&dbms=ORACLE&dbmsOptions="user='oracleAdminRsmith' password='admin3yr8' " +
  "path='oraclev7'"

Sample Code for Accessing SQL Server and DB2 Data on a SAS Workspace Server

The following table contains sample connection code for using the IOM driver to access other third-party relational DBMS data. User-supplied values such as passwords and user IDs are italicized. Note that SAS/ACCESS must be configured on the server that receives the request.

Sample Connection Code for Using the IOM Driver to Access DBMS Data

Data Source	Sample Connection Code
OLE DB Connection to Microsoft SQL Server	properties.setProperty("dbms","oledb"); properties.setProperty("dbmsOptions","provider='sqloledb' user='dbadmin' password='password1' datasource='sql1'");
DB2	properties.setProperty("dbms","DB2"); properties.setProperty("dbmsOptions","user='dbadmin' password='password1' dsn='myDSN'");

Data Source

Sample Connection Code

OLE DB Connection to Microsoft SQL Server

properties.setProperty("dbms","oledb");
properties.setProperty("dbmsOptions","provider='sqloledb' user='dbadmin'
password='password1' datasource='sql1'");

DB2

properties.setProperty("dbms","DB2");
properties.setProperty("dbmsOptions","user='dbadmin'
password='password1' dsn='myDSN'");

Sample Code for Accessing SPD Server Data on a SAS/SHARE Server

Only the SAS/SHARE driver for JDBC supports access to SPD Server data via the dbms and dbmsOptions connection properties. The driver class name for the SAS/SHARE driver for JDBC is com.sas.net.sharenet.ShareNetDriver. You can use the following sample connection code:

properties.setProperty("dbms", "SPDS");
properties.setProperty("dbmsOptions", "DBQ='qabig1' HOST='xyz.abc.def.com' " +
    "SERV='5190'");