ReportCaster Connecting to SQL Server 2000 Using Pure JDBC on Microsoft Windows 2000 and UNIX
By Mark Nesson
So you want to expand the repositories for ReportCaster, but this time you want to use be SQL Server 2000 with pure JDBC.
Cross-platform JavaTM Database Connectivity JDBC implementations are commonly used to connect Java programs to any of the major relational databases available on the market today. I’ll focus on pure JDBC here and point out one of the key advantages: There is no
client-side software installation required. More specifically, we do not need the Microsoft SQL Server client-side installation. JDBC works just over your network.
This works of course not only because of JDBC, but also because we use Java. Besides the Java Development Kit, as always required for Java programs, we need the Microsoft implementation of the JDBC specification, the so-called Microsoft SQL Server 2000 JDBC Driver, available at msdn.microsoft.com/downloads.
The Microsoft SQL Server 2000 Driver for JDBC is a Type 4 JDBC driver that provides highly scalable and reliable connectivity for the enterprise Java environment. This driver provides JDBC access to SQL Server 2000 through any Java-enabled applet, application, or application server. Note:
Microsoft does not support prior versions of SQL Server using the pure JDBC access.
If you want to enable a UNIX operating system with ReportCaster, and want to use SQL Server as the repository, follow these steps:
To install this on UNIX, copy the mssqlserver.tar to a temporary directory. In my case, I created a directory under /home/mktmjn/mssql. I then "FTPed" the tar file there and issued this: tar xvf mssqlserver.tar.
This created the following five files: EULA.txt, install.ksh, msjdbc.tar, mssqlserver.tar and read.me.
I then ran this command: [mktmjn@brolinux mssql]$ ./install.ksh. This followed:
Press <enter> to view License Agreement.
Do you agree to the terms of the License
Agreement? <y/n> (y)
y
Installation proceeding...
./install.ksh: break: only meaningful in a
`for', `while', or `until' loop
Please enter the installation directory:
(/opt/msSQLjdbc)
I overrode the default to the following:
/home/mktmjn/mssql/msSQLjdbc
Installing SQLServer 2000 Driver for
JDBC....
Installation is Complete.
SQL Server 2000 driver for JDBC is
installed in the following location:
/home/mktmjn/mssql/msSQLjdbc
[mktmjn@brolinux mssql]$
A Small Test Program
I found the following sample program, test.java, on the Web. It shows how to establish a connection to Microsoft SQL Server. Don’t forget to import the java.sql packages to get access to Driver Manager and many other related classes and methods.
import java.sql.*;
/**
* Microsoft SQL Server JDBC test program
*/
public class Test {
public Test() throws Exception {
// Get connection
DriverManager.registerDriver(new
com.microsoft.jdbc.sqlserver.SQLServerDrive
r());
Connection connection =
DriverManager.getConnection(
"jdbc:microsoft:sqlserver://broath:1433","B
RKID","BRKID");
if (connection != null) {
System.out.println();
System.out.println("Successfully
connected");
System.out.println();
// Meta data
DatabaseMetaData meta =
connection.getMetaData();
System.out.println("\nDriver Information");
System.out.println("Driver Name: "
+ meta.getDriverName());
System.out.println("Driver Version: "
+ meta.getDriverVersion());
System.out.println("\nDatabase
Information ");
System.out.println("Database Name: "
+ meta.getDatabaseProductName());
System.out.println("Database
Version: "+
meta.getDatabaseProductVersion());
}
} // Test
public static void main (String args[])
throws Exception {
Test test = new Test();
Compile the Java Source
Your next step is to compile the Java Source: Test.java (all in one line):
$ javac -classpath
".;./lib/mssqlserver.jar;./lib/msbase.jar;.
/lib/msutil.jar" Test.java
Be aware that you need access to a JavaC program on your computer or media to do this. If not, simply specify the full path ahead of JavaC. The above command is good for Java 2. If you are using Java 1.1.8 for instance, add your JDK’s classes.zip to the classpath. On UNIX systems, replace the semicolons (;) with colons (:). The forward slashes (/) work on both platforms; it's not a must to use backslashes (\) on Windows.
Run It
Similar to the compilation, you may run it like this (again, all in one line):
$ java -classpath
".;./lib/mssqlserver.jar;./lib/msbase.jar;.
/lib/msutil.jar" Test
The output looks something like this:
Successfully connected
Driver Information
Driver Name: SQLServer
Driver Version: 2.2.0022
Database Information
Database Name: Microsoft SQL Server
Database Version: Microsoft SQL Server
2000 - 8.00.534 (Intel X86)
Nov 19 2001 13:23:50
Copyright (c) 1988-2000 Microsoft
Corporation
Enterprise Edition on Windows NT
5.0 (Build 2195: Service Pack 2)
Now it’s time for a little debugging, which is not unusual. In this case, I believe four issues may arise:
When the SQL Server is Down
Exception in thread "main" java.sql. SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]Error establishing socket. at com.microsoft.jdbc.base.Base Exceptions.create Exception (Unknown Source)
When the Database Name Does Not Exist
Exception in thread "main" java.sql.SQL Exception: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Cannot open database requested in login 'bro'. Login fails.
When the User ID is Bad
Ex1ception in thread "main" java.sql.SQL Exception: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Login failed for user 'bro'. at com. microsoft.jdbc. base.Base Exceptions. createExcep-tion(Unknown Source)
When the Password is Bad
Exception in thread "main" java.sql.SQL Exception: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Login failed for user 'sa'. at com.microsoft. jdbc. base.BaseExceptions.create Excep-tion(Unknown Source)
The Installation
Think about it: At least 90 percent of the Windows NT marketplace is using SQL Server on Windows 2000. So how do we enable ReportCaster with pure JDBC on SQL Server on 2000 or UNIX?
The easiest thing to do is re-install the Distribution Server and chose Oracle as the repository. And when it asks you for the type of install, choose custom.
The next important question is, Which Distribution Server Repository is to be used? Pick Oracle. (We have to fake out the installation.) Next you are asked to supply the following information for the Oracle Repository: Enter the path to the Oracle JDBC driver. This is when you enter the path to the
files msbase.jar, mssqlserver.jar and msutil.jar. Make sure you separate them with commas. Don’t forget to enter the path and the full file name.
For example, I entered my file this way: F:\temp\msbase.jar;ect… Also be sure to enter the SQL Server owner and password you used to connect to SQL Server. This is important because the installation process updates all the *.bat files in the distribution server bin directory
with the SQL Server jar files as well as the registry.
Let’s take a peek at one of the *.bat files so you can see what I am saying. We will look at the contents of the schbkr1.bat file. Notice that the bat files contain the appropriate jar files.
"C:\jdk1.3.1_01\bin\java" -classpath
C:\mssqljdbc\msbase.jar;C:\mssqljdbc\mssqls
erver.jar;C:\mssqljdbc\msutil.jar;C:\ibi\di
stributionserver436\bin\SCHScheduler.jar;
SCHScheduler C:\ibi\distributionserver436
pause
Now that the distribution server is installed, you must manually change the bkrsched.cfg. Change the DBOWNER and DBPASS to a valid SQL Server user ID and password, then change the REPOSITOTYCLASS and REPOSITORYURL to this:
DBOWNER sa
DBPASS lovasqlserver
REPOSITORYCLASS
com.microsoft.jdbc.sqlserver.SQLServerDriver
REPOSITORYURL
jdbc:microsoft:sqlserver://localhost:1433;
DatabaseName=pubs;User=BRKID;Password=BRKID
One last necessary step is to add the three jar files to New Atlanta’s classpath. If you are using some other Application Server you must update the WEB_INF/lib with the SQL Server's three jar files.
Once you have completed these steps, run the createUtil.exe file in the distribution servers bin directory. You must complete this successfully before you can begin to use ReportCaster.
If you do not want to re-install ReportCaster you can do all the above manually. Make sure you edit all the *.bat files in the distribution servers bin directory and add the appropriate three jar files in the C:\Program Files\New Atlanta\Servlet Exec ISAPI\ServletExec Data\Classpath.
perf like this:
C:\Program Files\New Atlanta\ServletExec
ISAPI\classes
C:\ibi\WebFOCUS436\ibi_html\javaassist\DSTS
ervlets.jar
C:\ibi\WebFOCUS436\ibi_html\javaassist\WFSe
rvlet.jar
C:\ibi\WebFOCUS436\ibi_html\javaassist\tdg.
jar
C:\ibi\WebFOCUS436\ibi_html\javaassist\IBIR
PASSTBean.jar
C:\ibi\WebFOCUS436\ibi_html\javaassist\xala
n.jar
C:\ibi\WebFOCUS436\ibi_html\javaassist\xerc
es.jar
C:\ibi\WebFOCUS436\ibi_html\javaassist\xml-
apis.jar
C:\ibi\WebFOCUS436\ibi_html\javaassist\IBIG
etDDMAPServlet.jar
C:\mssqljdbc\msbase.jar
C:\mssqljdbc\mssqlserver.jar
C:\mssqljdbc\msutil.jar
Finally, you need to update the registry under HKEY_LOCAL_MACHINE for the distribution server’s classpath to add the three jar files.
Classpath
C:\mssqljdbc\msbase.jar;C:\mssqljdbc\mssqls
erver.jar;C:\mssqljdbc\msutil.jar;C:\ibi\di
stributionserver436\bin\SCHScheduler.jar;C:
\ibi\distributionserver436\bin\SCHStop.jar
This is a good way to enhance functionality on our products, and enhancements are always a good idea. Give me a call if you have any questions.
Java and all Java-based marks are trademarks of Sun Microsystems, Inc. in the U.S. and other countries.
 
|
