Skip to Content

Overview

This document explains how to configure a Java Database Connectivity (JDBC) connection for SAP BI Client tools like Crystal Reports 2008, SAP Crystal Reports 2011/2013, SAP Crystal Reports for Enterprise, Business View Manager, Information Design Tool, etc.

Introduction

SAP BI Client tools support robust, secure and fast connectivity with various databases through Java Database Connectivity (JDBC). Information provided below would guide you some recommended settings to achieve connectivity between BI tools and the reporting databases.

What is JDBC?

JDBC stands for Java Database Connectivity, which is a standard Java API for database-independent connectivity between the Java programming language and a wide range of databases.

The JDBC library includes APIs for each of the tasks commonly associated with database usage:

  • Making a connection to a database
  • Creating SQL statements, sending queries and updating statements to the database
  • To retrieve data from a database

Configure JDBC connection for Crystal Reports 2008

  1. Download a jar file for a respective database (e.g. MS SQL Server: sqljdbc4.jar, Oracle: ojdbc6.jar)
  2. Extract the jar file in any directory (For e. g.: C:\Program Files (x86)\Business Objects\Common\4.0\java\lib)
  3. Open the CRConfig.xml file from the location: C:\Program Files (x86)\Business Objects\Common\4.0\java\
  4. Add the path to a jar file in the <classpath> tag: e.g. C:\Program Files (x86)\Business Objects\Common\4.0\java\lib\ojdbc6.jar

CR2008.png

Configure JDBC connection for SAP Crystal Reports 2011/2013

  1. Download a jar file for a respective database (e.g. MS SQL Server: sqljdbc4.jar, Oracle: ojdbc6.jar)
  2. Extract the jar file in any directory (For e. g.: C:\Program Files (x86)\SAP BusinessObjects\\SAP BusinessObjects Enterprise XI 4.0\java/lib)
  3. Open the CRConfig.xml file from the location: C:\Program Files (x86)\SAP BusinessObjects\\SAP BusinessObjects Enterprise XI 4.0\java/
  4. Add the path to a jar file in the <classpath> tag: e.g. C:\Program Files (x86)\SAP BusinessObjects\\SAP BusinessObjects Enterprise XI 4.0\java/lib/ojdbc6.jar

  CR2011.png

Configure JDBC connection for SAP Crystal Reports for Enterprise and IDT

Method-1:

  1. Navigate to the location: C:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\dataAccess\connectionServer\jdbc\
  2. Search for the file with an extension .sbo (For MS SQL Server the file is: sqlsrv.sbo. For Oracle the file is: oracle.sbo)
  3. Create a folder with a same name (sqlsrv, oracle) in the location: C:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI
    4.0\dataAccess\connectionServer\jdbc\drivers
  4. Place the file sqljdbc4.jar (or ojdbc6.jar) in the folder: C:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\dataAccess\connectionServer\jdbc\drivers\<newly created folder> (sqlsrv or oracle)

Method-2:

  1. Navigate to the location: C:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\dataAccess\connectionServer\jdbc\ 
  2. Search for the file with an extension .sbo (For MS SQL Server the file is: sqlsrv.sbo. For Oracle the file is: oracle.sbo)
  3. Extract the jar file in any directory. (For e.g. C:\Users\Administrator\Desktop\enu\sqljdbc_3.0)
  4. Edit the file with an extension .sbo and add an entry of the sqljdbc4.jar (or oracle6.jar) in the classpath:
    <Path>C:\Users\Administrator\Desktop\sqljdbc_3.0\enu\sqljdbc4.jar</Path>

A sample java code to test the JDBC connection

A file with sample java code to test the JDBC connection is attached here. (Please remove .txt extension before execution. The file name should be: JDBC2_Oracle.java)

A batch file to run a java code

A batch file to run a code is attached here. All the instructions to use are provided in the file. (Please remove .txt extension before execution. The file name should be: JDBC2_Oracle.bat)

Connection URL and Classname for commonly used Databases

  • SQL Server 2005/ 2008/2012:
    • jdbc:sqlserver://VANPGDBSQL03.pgdev.sap.corp:1433
    • com.microsoft.sqlserver.jdbc.SQLServerDriver
  • Oracle 10g/11g:
    • jdbc:oracle:thin:@//VANPGREPDB02.pgdev.sap.corp:1523/f11r2u02
    • oracle.jdbc.driver.OracleDriver
  • IBM DB2:
    • jdbc:db2://<server-name>:<port-number>/<database_name>
    • com.ibm.db2.jdbc.app.DB2Driver
  • MySQL:
    • jdbc:mysql://<server_name>:[port]/<database_name>
    • com.mysql.jdbc.Driver
  • Sybase:
    • jdbc:datadirect:sybase://<server-name>:<port-number>;databaseName=name
    • com.ddtek.jdbc.sybase.SybaseDriver
  • Teradata:
    • jdbc:teradata://databasehost
    • com.teradata.jdbc.TeraDriver

Related Notes

2066749 – How to create a Crystal Report using JDBC connection

2066805 – Configure JDBC connection for SAP Crystal Reports for Enterprise & Information Design Tool (IDT)

1527666 – JDBC Connection URL’s while creating Crystal Reports based on JDBC Connectivity

1850017 – How do you set up an Oracle 11 JDBC Connection in Crystal Reports 2011 that uses Service Name?

1558463 – How to create a Crystal Report against a Teradata database using JDBC connection

To report this post you need to login first.

8 Comments

You must be Logged on to comment or reply to a post.

  1. Ivan Surya

    Hi there,

    I am currently trying to write a Java application that can execute Crystal Report files and pass parameters to the executed reports.

    However, when I tried to pass the parameter using the “addDiscreteParameterValue” method of the CRJavaHelper (that comes with the Crystal Report version of Eclipse), e.g.

    CRJavaHelper.addDiscreteParameterValue(reportClientDocument, “” , “Resource Title Contains”, “J”);

    I got an error saying it cannot find the JNDI, i.e. “Error finding JNDI name”.

    I did some research and someone suggested that I change my report definition to use JDBC connection to connect to my Microsoft SQL Server.

    I’m currently running Microsoft SQL Server 2008 R2 and is using Crystal Report 2013.

    I tried to follow the instruction above to set up the JDBC driver to the Microsoft SQL server, i.e. download “sqljdbc.jar”, “sqljdbc4.jar” and “sqljdbc41.jar” and I’ve added the path to the “sqljdbc4.jar” to the class path.

    My SQL server name is “user-PC\SQLEXPRESS” and I usually connect with Port 1433.

    However, everytime I try to create a JDBC connection to the Microsoft SQL server, I got the following error:

    Failed to open the connection.  Details: SQL Exception: [SQL State:] 08001 [Error Message:] No suitable driver found for jdbc:microsoft:sqlserver://user-PC\SQLEXPRESS:1433″.

    Can anyone help pointing out what I may have done wrong?  My connection string is “jdbc:microsoft:sqlserver://user-PC\SQLEXPRESS:1433”

    Thanks for reading my question.  Any response is greatly appreciated.

    Thanks,

    Ivan.

    (0) 
  2. Guru Kapalavayi

    Nilesh,

    Nilesh,  Thank you for the instructions.. I am able to connect via JDBC in IDT, but not able to when using Crystal Reports Enterprise.

    Error – Java Class not found in classpath : com.ibm.db2.jcc.DB2Driver

    The Path variable was updated in the db2.sbo file.

    (0) 
  3. Shilpa Wagle

    Thanks for the info.

    This is working just fine as long as you need to connect to the database using SQL authentication. For Windows authentication it doesn’t.

    (0) 

Leave a Reply