JDBC Connectivity for SAP Crystal Reports

[Former Member]

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

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

 

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...

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 N...

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