- This topic has 3 replies, 4 voices, and was last updated 14 years, 3 months ago by
.
-
Topic
-
-
Replies
-
-
David,
Connecting to a database with Java UPOC and JDBC is very easy and runs faster than ODBC because you are using native drivers. All you have to do is get the JDBC driver for the particular database you are connecting to. Usually this driver will be in a .jar file. For Oracle, you can download the JDBC drivers from the Oracle web site (depending on the version, it will be ojdbc5. jar, ojdbc6.jar, or ojdbc14.jar …). For Microsoft SQL Server, I use the excellent freeware native driver jTDS (jtds_1.2.jar). Once you have the driver, you copy it to a directory on your Cloverleaf server and modify your CLASSPATH or QDXI_CLASSPATH environment variable to include the location of the .jar file. This is necessary for the driver to become available to the Cloverleaf run-time environment.
Having installed the driver and made it available to Cloverleaf, you can now test the connection. Here is some sample code to show you how I have done it with Microsoft SQL Server. I only include the database connection and SQL execution portions of the tps:
Code:
//****** This section queries the “hsmtest” database on a MS SQLServer
// instance named MSSQLSERVER
// Define database connection parameters
String driver = “net.sourceforge.jtds.jdbc.Driver”;
String databaseURL = “jdbc:jtds:sqlserver://testservername/hsmtest;instance=MSSQLSERVER”;
String databaseUser = “sa”;
String databasePassword = “secret”;
Connection conn = null ; // database connection object
PreparedStatement stmt = null; // to execute the SQL query to the database
ResultSet rs = null; // returned by the SQL statement
String errmsg = “”; // to hold custom error message
try {
// load the jtds JDBC Driver
Class.forName(driver);
} catch (ClassNotFoundException e){
// driver class not found. Send the message to the error database
// and exit
System.out.println(e);
errmsg = “Can’t load the jdbc driver to connect to SQL Server, ” +
“message sent to error database.”;
msgmet.set(”USERDATA”, errmsg);
dispList.add( DispositionList.ERROR, msg );
return dispList;
} // end catch (ClassNotFoundException e)
// If we get here, the jdbc driver has been properly loaded
// Let’s try to connect to the database. We are going to make
// a total of 3 tries with a 1-second interval between tries
int tryConnect = 3;
while((tryConnect>0)&&(conn==null)) {
try {
conn = DriverManager.getConnection(databaseURL, databaseUser, databasePassword);
} catch (SQLException e){
// unable to connect, decrement number of tries and retry after 1 second
tryConnect -= 1;
System.out.println(”Can’t connect to database Test, will retry in 1 second”);
try {
Thread.sleep(1000);
} catch (InterruptedException ie) {
//ignore
e.printStackTrace();
}
} // end catch (SQLException e)
} // end while loop
if(conn==null) { // We were not able to secure a connection after the three tries
System.out.println(”EXITING – Can’t connect to database Test after 3 tries.”);
// Send the message to the error database and bail out.
// Always let the user know why the message is being sent to the
// error database by setting the userdata metadata
errmsg = “Can’t connect to SQL Server database Test, ” +
“message sent to error database.”;
msgmet.set(”USERDATA”, errmsg);
dispList.add( DispositionList.ERROR, msg );
return dispList;
}
// If we get here, connection was successfull
System.out.println(”CONNECTED TO SQL Server ” + new java.util.Date());
// Now we can proceeed with updating the database tables
try {
// Prepare to issue the query to the casemain table of the Test
// database
stmt = conn.prepareStatement(”SELECT appt_id FROM dbo.casemain WHERE casemain_id = ?”);
stmt.setString(1, caseID);
rs = stmt.executeQuery();
// This should return only one row, since the case id is a primary
// key in the table. Move to the first row and grab the
// appointment_id
while (rs.next()) {
apptID = rs.getString(”appt_id”);
System.out.println(”Appointment ID: ” + apptID);
}
} // end try
catch (SQLException e){
// something went wrong, print the error, send the message to the
// error database and quit.
System.out.println(”SQLException caught”);
System.out.println(”—“);
// iterate and get all of the errors as much as possible.
while ( e != null ){
System.out.println(”Message : ” + e.getMessage());
System.out.println(”SQLState : ” + e.getSQLState());
System.out.println(”ErrorCode : ” + e.getErrorCode());
System.out.println(”—“);
e = e.getNextException();
} // end while ( e != null )
errmsg = “Error executing query in SQL Server, ” +
“message sent to error database.”;
msgmet.set(”USERDATA”, errmsg);
dispList.add( DispositionList.ERROR, msg );
return dispList;
} // end catch (SQLException e)
finally {
// release database resources
try {
if (rs != null) {
rs.close();
}
} catch (Exception e) {
//ignore
e.printStackTrace();
}
try {
if (stmt != null) {
stmt.close();
}
} catch (Exception e) {
//ignore
e.printStackTrace();
}
try {
if (conn != null) {
conn.close();
}
} catch (Exception e) {
//ignore
e.printStackTrace();
}
}I hope this gives you ideas.
-
-
Saw this post. For business reasons beyond my control, I am using the 5.3 revision of the cloverleaf integration engine. Per the documentation this revision specifically states that when using java that you should use the jdk.1.3. I checked the version using the java -version command and found that the build was listed as 1.3.1_09-b03. Current revision of the microsoft sql server driver for jdbc require a higher revision of the jre. That being said the jtds driver can work with the older revision of java in the case that you are stuck with an older jre.
BTW nice proc.
-
- The forum ‘Cloverleaf’ is closed to new topics and replies.
