33060 - How to Create a JDBC Connection Using the Java Connection Factory

Sample 33060: How to Create a JDBC Connection Using the Java Connection Factory

Overview

This sample demonstrates how to use the Java Connection Factory to create a JDBC connection. Specifically, the sample explains how to perform these tasks:

connect to a SAS Workspace Server using the Java Connection Factory.
retrieve the ILanguageService to submit SAS code.
retrieve the SAS log and print it out.
use the connection to create a JDBC connection, submit a SQL query, and print out some results.

It is possible to use the com.sas.rio.MVADriver to connect directly to a workspace server, as explained in Sample 26049. However, in some situations, connecting using the Java Connection Factory might provide these additional benefits:

The ability to assign a libref to a DBMS server at run time. With the MVADriver, it is only possible to submit Base SAS LIBNAME statements. This sample shows how to connect to a SAS/SHARE^® Server library, but it is possible to submit any valid SAS LIBNAME statement.
The ability to submit SAS code prior to using the JDBC connection.
Connection pooling using the Java Connection Factory.

Notes

The instructions on the Full Code tab are for SAS AppDev Studio users. If you do not use SAS AppDev Studio, then you must manually configure your build environment in order to run the Java code. This will require the Foundation Services JAR files.

This sample does not demonstrate how to use connection pooling, but uses a simple direct connection as an example. The Java Connection Factory documentation provides details on the options for pooling.

Additional Documentation

For information about developing applications with SAS Integration Technologies, see the SAS Integration Technologies Developer's Guide. Specifically, see Using the Java Connection Factory.
For a list of samples for common SAS AppDev Studio tasks, see SAS Note 32218.
The SAS AppDev Studio Developer's Site contains detailed information that will assist you when developing Web applications with SAS AppDev Studio, including an API reference and the custom tag reference for the sas tagset.

These sample files and code examples are provided by SAS Institute Inc. "as is" without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability and fitness for a particular purpose. Recipients acknowledge and agree that SAS Institute shall not be liable for any damages whatsoever arising out of their use of this material. In addition, SAS Institute will provide no support for the materials contained herein.

Instructions

Build a new SAS Java Project in SAS AppDev Studio. No template is required for this sample.
Create a new Java class called JDBCThroughJavaConnectionFactory using samples as the package.
Run the class as a SAS Java Application.

Tip: For help with building a SAS Java Project, see SAS Note 32218.

Code for JDBCThroughJavaConnectionFactory

Required changes

Edit the values for host, port, user, and password to match your environment.

String host = "server";
int port = 8591;
String user = "sasdemo";
String password = "password";


package samples;

import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

import com.sas.iom.SAS.ILanguageService;
import com.sas.iom.SAS.IWorkspace;
import com.sas.iom.SAS.IWorkspaceHelper;
import com.sas.iom.SAS.ILanguageServicePackage.CarriageControlSeqHolder;
import com.sas.iom.SAS.ILanguageServicePackage.LineTypeSeqHolder;
import com.sas.iom.SASIOMDefs.GenericError;
import com.sas.iom.SASIOMDefs.StringSeqHolder;
import com.sas.rio.MVAConnection;
import com.sas.services.connection.BridgeServer;
import com.sas.services.connection.ConnectionFactoryConfiguration;
import com.sas.services.connection.ConnectionFactoryException;
import com.sas.services.connection.ConnectionFactoryInterface;
import com.sas.services.connection.ConnectionFactoryManager;
import com.sas.services.connection.ConnectionInterface;
import com.sas.services.connection.ManualConnectionFactoryConfiguration;
import com.sas.services.connection.Server;

public class JDBCThroughJavaConnectionFactory {

	public static void main(String[] args) {
		
		String host = "server";
		int port = 8591;
		String user = "sasdemo";
		String password = "password";
		
		MVAConnection jdbcConnection = null;
		Statement stmt = null;
		IWorkspace sasWorkspace = null;
		
		try {
			String classID = Server.CLSID_SAS;

			// Connect to the server and retrieve an IWorkSpace
			Server server = new BridgeServer(classID, host, port);
			ConnectionFactoryConfiguration cxfConfig = new ManualConnectionFactoryConfiguration(
					server);
			ConnectionFactoryManager cxfManager = new ConnectionFactoryManager();
			ConnectionFactoryInterface cxf = cxfManager.getFactory(cxfConfig);			
			ConnectionInterface cx = cxf.getConnection(user, password);
			org.omg.CORBA.Object obj = cx.getObject();
			sasWorkspace = IWorkspaceHelper.narrow(obj);
			
			// Retrieve the ILanguageService.  This will allow you to submit any SAS code
			// We will use it to assign a libname
			ILanguageService sasLanguage = sasWorkspace.LanguageService();
			//sasLanguage.Submit("libname appdev 'c:\\data\\tables';");
			sasLanguage.Submit("libname shrtest hostname=servername server=__8551 user=" + user + " password=" + password + ";");
			// Retrieve the SAS log and print it out
			CarriageControlSeqHolder logCarriageControlHldr = new CarriageControlSeqHolder();
			LineTypeSeqHolder logLineTypeHldr = new LineTypeSeqHolder();
			StringSeqHolder logHldr = new StringSeqHolder();
			sasLanguage.FlushLogLines(Integer.MAX_VALUE,
					logCarriageControlHldr, logLineTypeHldr, logHldr);
			String[] logLines = logHldr.value;
			for (int i = 0; i < logLines.length; i++) {
				System.out.println(logLines[i]);
			}

			// Create a new jdbc connection using the Iworkspace 
			Properties jdbcProps = new Properties();
			jdbcConnection = new MVAConnection(sasWorkspace, jdbcProps);

			stmt = jdbcConnection.createStatement();
			ResultSet rs = stmt.executeQuery("select * from shrtest.class");
			
			// Just print out the column names, to verify the connection works
			ResultSetMetaData rsmeta = rs.getMetaData();
			for( int i=1; i< rsmeta.getColumnCount();i++ ){
				System.out.println( "Column Label " + i + ": " + rsmeta.getColumnName(i));
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (ConnectionFactoryException e) {
			e.printStackTrace();
		} catch (GenericError e) {
			e.printStackTrace();
		} finally {
			try {
				if (null != stmt) {
					stmt.close();
				}
				if (null != jdbcConnection) {
					jdbcConnection.close();
					sasWorkspace.Close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (GenericError e) {
				e.printStackTrace();
			}

		}
	}

}