Creating a SQL Server 2000 Data Source with OracleAS 10g OC4J using Microsoft JDBC Drivers
By Jason Bennett
The following steps demonstrate how to create server side data source for connecting to SQL Server from an OracleAS 10g OC4J instance.
Step 1. Download SQL Server JDBC drivers from Microsoft (http://www.microsoft.com/downloads/details.aspx?FamilyID=9f1874b6-f8e1-4bd6-947c-0fc5bf05bf71&;DisplayLang=en#filelist).
Step 2. After installing the drivers, copy .JAR files from <Microsoft SQL Server 2000 Driver for JDBC install directory>lib directory to the <OracleAS 10g HOME>j2ee<OC4J INSTANCE HOME>applib directory.
Step 3. Log into Oracle Enterprise Manager 10g and create a data source under the OC4J instance of your choice with following values:
Name: SQLServerDS
Description: Datasource used for MS SQL Server connection.
Data Source Class: com.microsoft.jdbcx.sqlserver.SQLServerDataSource
Schema: <not required>
Username: <db user>
Password: <password for db user >
JDBC_URL: jdbc:microsoft:sqlserver://<;host>:1433
JDBC Driver: com.microsoft.jdbc.sqlserver.SQLServerDriver
JNDI LOCATIONS SECTION
Location: jdbc/SQLServerDS
Transactional (XA) Location: <optional>
EJB Location: <optional>
CONNECTION ATTRIBUTES SECTION
Connection Retry Interval (secs): 30
Max Connection Attempts: 5
Maximum Open Connections: 30 <depends on load and application>
Minimum Open Connections: 0 <This will actually open 5 connections>
Wait for Free Connection Timeout (secs): 30
PROPERTIES SECTION (Add the following properties)
Name: databaseName Value: <database name i.e. NorthWinds>
Name: loginTimeout Value: <value in seconds>
Name: selectMethod Value: cursor
Name: serverName Value: <name of DB host or IP address>
data-sources.xml Method
The data source can also be created manually by adding it to a data-sources.xml and placing the file in <OracleAS 10g HOME>j2ee<OC4J INSTANCE HOME>config directory. The following is an example of a data-sources.xml file:
<?xml version = '1.0' standalone = 'yes'?>
<!DOCTYPE data-sources PUBLIC "Orion data-sources" "http://xmlns.oracle.com/ias/dtds/data-sources-9_04.dtd">
<data-sources>
<data-source location="jdbc/SQLServerDS"
class="com.microsoft.jdbcx.sqlserver.SQLServerDataSource"
password="yourpassword"
max-connect-attempts="10"
connection-driver="com.microsoft.jdbc.sqlserver.SQLServerDriver"
username="jbennett"
min-connections="0"
max-connections="50"
url="jdbc:microsoft:sqlserver://localhost:1433" name="SQLServerDS">
<property name="databaseName" value="JasonTestDB"/>
<property name="loginTimeout" value="3000"/>
<property name="selectMethod" value="cursor"/>
<property name="serverName" value="tst-jason"/>
</data-source>
</data-sources>
Some Sample Code to Get you Started
The following code sample demonstrates how to obtain a JNDI reference to your server side SQL Server JDBC data source.
package test.sqlserver.connect;
import javax.sql.*;
import java.sql.*;
import com.microsoft.jdbcx.sqlserver.SQLServerDataSource;
import com.microsoft.jdbc.sqlserver.SQLServerConnection;
import javax.naming.*;
public class ConnectionTest
{
private DataSource ds = null;
protected Connection conn = null;
protected CallableStatement cs = null;
protected Statement stmt = null;
protected ResultSet rs = null;
private InitialContext context = null;
public ConnectionTest()
{
try
{
context = new InitialContext();
ds = (DataSource)context.lookup("jdbc/SQLServerDS");
}catch(Exception e)
{
System.out.println("Error getting SQLServer connection: "+e.getMessage());
}
}
public String getNames() throws SQLException
{
String v_return = "no data";
try
{
conn = ds.getConnection();
stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("select <somecolumn> from <user>.person where <criteria>");
while(rs.next()){
v_return = rs.getString(1);
}
return v_return;
}catch(Exception e)
{
System.out.println("SQL error: "+e.getMessage());
}finally
{
conn.close();
}
return v_return;
}
}
7:58:22 PM 
|
