Passing Connection Properties on the JDBC URL

What Is the JDBC URL?

The JDBC URL is a method of supplying connection information to the data source that you are accessing. It is an alternative to providing a connection property list. The JDBC URL uses this form:

jdbc:subprotocol:subname

CAUTION:

Do not pass properties through the URL if you are creating a connection Properties object.

Properties that are passed through the URL override properties that are specified in the Properties object used in the DriverManager.getConnection(String url, Properties info) method. For example, if you use both methods of specifying properties, a libref that is specified in a URL overrides any libref that is also specified in the connection Properties object.

What Is the Subprotocol?

The subprotocol is unique to the server. The subprotocols for the SAS servers are listed in the following table.

Subprotocols

SAS Server	Subprotocol
SAS Workspace Server	sasiom
SAS/SHARE server	sharenet

What Is the Subname?

The subname uses the form //hostname:port. The host name and the port are unique to your site and should be known by your database administrator. For example, the following JDBC URL might be used for a SAS Workspace Server with the host name c123.na.abc.com using port number 5671.

jdbc:sasiom://c123.na.abc.com:5671

The JDBC URL Syntax

Here is the syntax for passing connection properties on the JDBC URL:

"url?[property=value]&[...]"

Here is an example that includes user and password properties:

"jdbc:sasiom://c123.na.abc.com:5671?user=jdoe&password=4ht8d"

Sample Code for Passing Connection Properties on the JDBC URL

The following sample code passes a libref property on the JDBC URL to a SAS Workspace Server. Here are the scenario details:

Because you are accessing a workspace server, you must use the IOM driver. The driver class name for the IOM driver is com.sas.rio.MVADriver.
The host name for the server is c123.na.abc.com, and the port number is 5671.
The librefs connection property is used to assign a SAS library, which is c:\sasdata. The first backslash is used to escape the backslash in the actual location.
Because the server is password-protected, you specify values for the user and password connection properties.

import java.sql.*;
public class AccessBase
{
  public static void main(String argv[])
  {
    Connection connection;
    int i;
    Statement statement;
    String queryString = "SELECT sup_id, sup_name " +
        "FROM mySASLib.suppliers ORDER BY sup_name";
    ResultSet result;
    double id;
    String name;

    try{
      //CONNECT TO THE SERVER BY USING A JDBC URL
      Class.forName("com.sas.rio.MVADriver");
      String user = "jdoe";
      String password = "4ht8d";
      connection = DriverManager.getConnection(
         "jdbc:sasiom://c123.na.abc.com:5671?" +
         "librefs=MySasLib 'c:\\sasdata'",
          user, password); 

      //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);
    }
  }
}