Using JDBC (Java) to Access SPD Server Tables

Read this information if you do not have Base SAS software on the network client, but you want to use the power of the Java programming language to query SPD Server tables from any client on the network that has a browser. 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 in order to use JDBC to access SPD Server tables.

Why Would I Want to Use JDBC?

How Is JDBC Set Up on the Server?

How Is JDBC Set Up on the Client?

How Do I Use JDBC to Make a Query?

JDBC Code Examples and Tips

Limitations of Using JDBC with the SPD Server

JDBC Used with SAS Versus JDBC Used with the SPD Server

Example JDBC Query for Getting a List of Tables

Example JDBC Query for Getting Metadata about a Specific Table

Why Would I Want to Use JDBC?

You might want to use JDBC if you have SPD Server tables available on your network and one or more of the following is 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 boxes, Windows PCs, and workstations. One thing they might have in common is browser access to your intranet.
The audience for the information understands Web browsing and wants point-and-click access to the information.
You want to distribute the information over the World Wide Web.
Your planned application requires the power of the Java programming language.

How Is JDBC Set Up 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.

How Is JDBC Set Up on the Client?

The client needs a browser set up to accept Java applets, such as

Netscape Navigator, Release 3.0 or later
Microsoft Internet Explorer, Release 3.02 or later

JDBC Set Up on an SPD Server Client

How Do I Use JDBC to Make a Query?

Log on to the World Wide Web and enter the URL for the Web page that contains the JDBC code.
Click on the desired information.
JDBC handles the request, formats the information, and returns the result to the Web page.

JDBC Code Examples and Tips

The following lines must be a 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="spdsuser" 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 set up 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=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:

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

Line 5:

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

Line 6:

shareRelease value=V9 is the version of the driver you are using. This must not be altered.

Line 7:

Sets the foreign database property on the JDBC driver. This means that the server is not SAS and JDBC should not create a DataBaseMetaData object. See the examples below for how to get around this.

Limitations of Using JDBC with the SPD Server

JDBC Used with SAS Versus JDBC Used with the SPD Server

SPD Server is treated as a foreign database. SPD Server clients can't query the JDBC metadata class for available tables and other metadata. Users must write their own queries to do this.

Example JDBC Query for Getting a List of Tables

(JDBC Used with the 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

(Your data file)

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')