Using JDBC to Access SPD Server Tables

Read this section if you do not have Base SAS software on the network client, but you want to use JDBC to query SPD Server tables from any client on the network that has a browser. In addition, you must have SPD Server tables available for use somewhere on the network and SPD Server and SPD Server SNET server running on the same server as the Web server to use JDBC to access SPD Server tables.

Why 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 SPD Server

JDBC Used with SAS Versus JDBC Used with SPD Server

Example of a JDBC Query That Gets a List of Tables (Used with SPD Server)

Example of a JDBC Query That Gets Metadata about a Specific Table (Your Data File)

Why Use JDBC?

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 running 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. You have UNIX, Windows PCs, and workstations. The clients might have browser access to your corporate intranet.
Users of the information understand browsing and want point-and-click access to the information.
You want to distribute the information over the Web.
Your planned application requires the power of Java.

How Is JDBC Set Up on the Server?

JDBC is usually set up on the server at the same time the SPD Server is installed. Instructions for installing JDBC are included in the SPD Server installation package.

How Is JDBC Set Up on the Client?

The client needs a browser set up to accept Java applets. For example:

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

How Do I Use JDBC to Make a Query?

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

JDBC Code Examples and Tips

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=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 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 Server SNET Server is running.
PORT is the port number of the machine on which the SPD Server 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 Server SNET Server.
SERV='NNNN' is the port number of the Name Server.

Line 4:

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

Line 5:

sharePassword value=thepassword is the password of the user ID that is making the query.

Line 6:

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

Line 7:

This line of code sets the foreign database property on the JDBC driver. What this property indicates is that the server is not SAS, and JDBC should not create a DataBaseMetaData object. The following examples show you how to circumvent this property:

Limitations of Using JDBC with SPD Server

JDBC Used with SAS Versus JDBC Used with SPD Server

SPD Server is treated as a foreign database. SPD Server clients cannot query the JDBC metadata class for available tables or other metadata. Users must write their own queries to do perform this function.

Example of a JDBC Query That Gets a List of Tables (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 of a JDBC Query That Gets 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')