Using JDBC (Java) to Access SPD Server Tables

Requirements and Considerations for Using JDBC

Set Up JDBC on the Server

Set Up JDBC on the Client

Make a Query with JDBC

HTML File Requirement for JDBC

Limitations of Using JDBC with SPD Server

Using JDBC with SAS Versus Using JDBC with SPD Server

Example JDBC Query for Getting a List of Tables

Example JDBC Query for Getting Metadata about a Specific Table

Requirements and Considerations for Using JDBC

To use JDBC to access SPD Server tables, you must have SPD Server tables on the network, and SPD Server and SPD SNET servers running on the same server as the Web server. If you are working in such an environment, you might want to use JDBC if one or more of the following criterion are true:

You do not have Base SAS software on the network client to process the data sets.
You want to distribute the information across your corporate intranet through a Web page.
The clients on your network are varied: UNIX machines, Windows PCs, and workstations.
The audience for the information wants point-and-click access to it.
You want to distribute the information over the Internet.
Your planned application requires the power of the Java programming language.

Set Up JDBC on the Server

JDBC is usually set up on the server at the time the SPD Server is installed. The process is covered in the SPD Server installation manual.

Set Up JDBC on the Client

To set up JDBC on the client, the client needs a browser that can accept Java applets.

JDBC Set Up on an SPD Server Client

Make a Query with JDBC

To use JDBC to make a query:

Open a browser and enter the URL for the Web page that contains the JDBC code.
Click on the information that you are interested in. JDBC handles the request, formats the information, and returns the result to the Web page.

HTML File Requirement for JDBC

The following lines must be part of the HTML file for JDBC:

<applet code="CLASSPATH.*.class codebase="../ width=600 height=425>
<param name=url value="jdbc:sharenet://spdssnet_node:PORT">
<param name="dbms_options" value=DBQ='LIBNAME' HOST='host_node' SERV='NNNN'>
<param name="shareUser" value="userid">
<param name="sharePassword" value="thepassword">
<param name="shareRelease" value="V9">
<param name="dbms" value="spds">
</applet>

Line 1

CLASSPATH points to the class path where the JDBC driver is installed.

*.class is the name of the Java class that consumes all of the <param name...> lines.

Line 2

spdssnet_node is the node name of the machine on which the SPD SNET server is running.

PORT is the port number of the machine on which the SPD SNET server is running.

Line 3

value=DBQ='LIBNAME' is the LIBNAME domain of the SPD Server.

HOST='host_node' is the location of the SPD SNET server.

SERV='NNNN' is the port number of the name server.

Line 4

value=”userid” is the user ID that queries the SPD Server table.

Line 5

value=”thepassword” is the password of the user ID that will make the query.

Line 6

value=”V9” is the version of the driver you are using. Do not modify this value.

Line 7

Sets the foreign database property on the JDBC driver. If you specify a foreign database in line 7, the foreign database server will not need JDBC to create a SAS DataBaseMetaData object.

Limitations of Using JDBC with SPD Server

Using JDBC with SAS Versus Using JDBC with SPD Server

SPD Server is treated as a foreign database. SPD Server clients cannot query the JDBC metadata class for available tables and other metadata. Users must write their own queries for these actions.

Example JDBC Query for Getting a List of Tables

The following example shows JDBC used with SPD Server:

SELECT '' AS qual,
LIBNAME AS owner,
MEMNAME AS name,
MEMTYPE AS type,
MEMNAME AS remarks FROM dictionary.tables AS tbl
WHERE ( memtype = 'DATA' OR memtype = 'VIEW' OR memtype = 'SYSTEM TABLE' OR
        memtype = 'ALIAS' OR memtype = 'SYNONYM')
AND (tbl.LIBNAME NE 'MAPS' AND tbl.LIBNAME NE 'SASUSER' AND tbl.LIBNAME NE 'SASHELP')
ORDER BY type, qual, owner, name

Example JDBC Query for Getting Metadata about a Specific Table

The following example shows using JDBC to query your data file for metadata:

SELECT '' AS qual,
LIBNAME AS owner,
MEMNAME AS tname, name,
length AS datatype,
type || ' ',
length AS prec,length,
length AS scale, length AS radix, length AS nullable,label,
FORMAT FROM dictionary.columns AS tbl
WHERE memname = 'your data file'
AND (tbl.LIBNAME NE 'MAPS'
     AND tbl.LIBNAME NE 'SASUSER'
     AND tbl.LIBNAME NE 'SASHELP')