execute(reset qrw=0) by sasspds; /* Another way to disable */
6. Select ODBC Databases.
3. The services file contains four columns. The rows of information may be sorted in port number order. Find the closest port number to the SPD Server port number, which you obtained from the network administrator (see "Preparing for Installation"). This is where you insert the new information.
4. Add an entry to the Services file, on its own line, in proper numeric order, using the following syntax:
column1 <service name> column2 <port number & protocol> column3 <aliases> column4 <comment> spdssnet spdssnet=name assigned to server nnnn/tcp nnnn=port number protocol is always /tcp not
required requirednot
Table 6.1: How to Add Service Name and Port Number to the Services File
Remember: The service name, spdssnet must match the server name that you used in step 6 of Configuring an ODBC Data Source for SPD SNET. The port number must match the port number on which the SPD SNET server is running.
Creating a Query Using an ODBC-Compliant Program
The following instructions create a query using Microsoft Access. 1. Start the SPD SNET server.
2. Start Microsoft Access.
3. From the Microsoft Access main menu, select
File Get External Table.
4. Select Link Table.
5. Select Files of Type.
6. Select ODBC Databases.
7. Select the data source.
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 SPD Server
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.
● 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.
Figure 6.3: JDBC Set Up on an SPD Server Client
How Do I Use JDBC to Make a Query?
1. Log on to the World Wide Web and enter the URL for the Web page that contains the JDBC code.
2. Click on the desired information.
3. 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="userid" is the user ID that queries the SPD Server table.
Line 5:
● "sharePassword" value="thepassword" 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 SPD Server ● Example JDBC Query for Getting a List of Tables
● Example JDBC Query for Getting Metadata about a Specific Table
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 109
WHERE memname = 'your data file' AND (tbl.libname NE 'MAPS'
AND tbl.libname NE 'SASUSER' AND tbl.libname NE 'SASHELP')
Using htmSQL 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 the point-and-click convenience of a Web page to query SPD Server tables from any browser-enabled client on the network. You must have SPD Server tables available for use, htmSQL loaded and configured on a UNIX or Windows operating system, and Scalable Performance Data Servers and SPD SNET servers running.
● Why Would I Want to Use htmSQL? ● How Is htmSQL Set Up on the Server? ● How Is htmSQL Set Up on The Client? ● How Do I Use htmSQL to Make a Query? ● Examples of Setting Up an htmSQL Web Page
Why Would I Want to Use htmSQL?
You may want to use htmSQL 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 data.
● You would like to use the JDBC option to extract the information but cannot permit Java applets to run on your network browsers.
● You want to distribute the information over the World Wide Web.
● Your developers are familiar with SQL and HTML.
How Is htmSQL Set Up on the Server?
● htmSQL 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.
● htmSQL must be installed on the Web server and you need the name of a data source that points to the SPD SNET server and to the specific LIBNAME domain that contains the SPD Server data you are interested in.
How Is htmSQL Set Up on the Client?
HtmSQL requires nothing more than a browser on the network or Web client.
Figure 6.4: htmSQL Configured on an SPD Server Client
How Do I Use htmSQL to Make a Query?
1. Log on to the World Wide Web and enter the URL for the Web page that contains the htmSQL code.
2. Click on the desired information.
3. htmSQL handles the request, formats the information, and returns the result to the Web page.
Examples of Setting Up an htmSQL Web Page
SAS Institute maintains a Web site that explains the technical details of setting up htmSQL Web pages. In some cases, there are references to the SAS/SHARE product. The rules for setting up htmSQL for either the SPD Server or SAS/SHARE are virtually the same.
The SAS Institute Web page for htmSQL is
http://support.sas.com/rnd/web/intrnet/htmSQL/index.html
Using SQL C API to Access SPD Server Tables
Read this section if you do not have Base SAS software on the network client but you want to provide your network client machines with the capability of accessing SPD Server tables, using SQL query methods. You must have SPD Server tables available for use, SPD Servers and SPD SNET servers running, and Network client machines capable of running C/C++ programs.
Why Would I Want to Use SQL C API?
You have SPD Server tables available on your network and one or more of the following may be true: ● You do not have Base SAS software on the network client to process the data sets.
● You wish to distribute the information across your corporate intranet.
● The clients on your network are varied: UNIX boxes, Windows PCs, workstations. One thing they might have in common is the ability to run C/ C++ programs.
● Your developers are familiar with SQL and C/C++.
The chapter "SPD Server SQL Access Library API Reference" in the SPD Server User's Guide contains additional information on SQL C API.