2111901 - A sample java code to test JDBC connection

How to test JDBC connectivity outside of BI Client Tools

To troubleshoot JDBC connectivity issues with BI client tools, start by creating a small java application.

If JDBC connection works outside of BI client tools then start performing product specific troubleshooting.

A sample java code to test the JDBC connection:
----------------------------------------------------------------------------------------------------------------------------------------

import java.sql.*;

public class JDBC2_Oracle {
   // JDBC driver name and database URL
   static final String JDBC_DRIVER = "oracle.jdbc.driver.OracleDriver"; 
   static final String DB_URL = "jdbc:oracle:thin:@//VANPGREPDB02.pgdev.sap.corp:1523/f11r2u02";

   //  Database credentials
   static final String USER = "guest";
   static final String PASS = "password";
  
   public static void main(String[] args) {
   Connection conn = null;
   Statement stmt = null;
   try{
      //STEP 2: Register JDBC driver
      Class.forName("oracle.jdbc.driver.OracleDriver");

      //STEP 3: Open a connection
      System.out.println("Connecting to database...");
      conn = DriverManager.getConnection(DB_URL,USER,PASS);

      //STEP 4: Execute a query
      System.out.println("Creating statement...");
      stmt = conn.createStatement();
      String sql;
      sql = "SELECT COUNTRY FROM XTREME.CUSTOMER";
      ResultSet rs = stmt.executeQuery(sql);

      //STEP 5: Extract data from result set
      while(rs.next()){
         //Retrieve by column name
         String id  = rs.getString("COUNTRY");
         //Display values
         System.out.println("ID: " + id);
        
      }
      //STEP 6: Clean-up environment
      rs.close();
      stmt.close();
      conn.close();
   }catch(SQLException se){
      //Handle errors for JDBC
      se.printStackTrace();
   }catch(Exception e){
      //Handle errors for Class.forName
      e.printStackTrace();
   }finally{
      //finally block used to close resources
      try{
         if(stmt!=null)
            stmt.close();
      }catch(SQLException se2){
     }// nothing we can do
      try{
         if(conn!=null)
            conn.close();
      }catch(SQLException se){
         se.printStackTrace();
      }//end finally try
   }//end try
   System.out.println("Goodbye!");
}//end main
}//end JDBC2_Oracle

----------------------------------------------------------------------------------------------------------------------------------------

Copy the above code in Notepad and save it with name "JDBC2_Oracle".

A batch file to run a java code:
----------------------------------------------------------------------------------------------------------------------------------------

@echo off
::*** Place all the JAR files (e.g. ojdbc6.jar for Oracle) and .Java file (e.g. JDBC2_Oracle.java) in one of the directory (e.g. TestJDBC)  ***
set BOBJ_JAVA_LIB=C:\Users\Administrator\Desktop\TestJDBC

::*** Default directory for JVM ***
set JAVA_HOME=C:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\win32_x86\sapjvm

set CURRENT_DIR=%cd%

echo Using JAVA_HOME: %JAVA_HOME%
echo Using CURRENT_DIR: %CURRENT_DIR%

set _RUNJAVA="%JAVA_HOME%/bin/java"
set _RUNJAVAC="%JAVA_HOME%/bin/javac"
set _RUNJAR="%JAVA_HOME%/bin/jar"

set CLASSPATH=%BOBJ_JAVA_LIB%\ojdbc6.jar;%CLASSPATH%

::*** Compile a Java File ***
%_RUNJAVAC% -classpath "%CLASSPATH%" JDBC2_Oracle.java

::*** Run the Java File ***
%_RUNJAVA% -classpath "%CLASSPATH%" JDBC2_Oracle

Pause

----------------------------------------------------------------------------------------------------------------------------------------

How to execute a code:

Run the batch file to execute the code

JDBC, crystal, BI client, connection, jave code , KBA , BI-RA-CR , Crystal Reports designer or Business View Manager , How To