JDBC connection

[David Ma]

Has anyone had success with Java UPOC and JDBC to connect to a database?

If you do, could you share your experience with me 😀

David

Topic

[Levy Lazarre]

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.

[garry r fisher]

Hi David,

For information – Microsoft provide their own JDBC driver for SQL Server. I’ve used this in my own code without any issues.

Regards

Garry

[William Grow]

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.

[Author]

Replies