JDBC Connectivity for SAP Crystal Reports
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
- Download a jar file for a respective database (e.g. MS SQL Server: sqljdbc4.jar, Oracle: ojdbc6.jar)
- Extract the jar file in any directory (For e. g.: C:\Program Files (x86)\Business Objects\Common\4.0\java\lib)
- Open the CRConfig.xml file from the location: C:\Program Files (x86)\Business Objects\Common\4.0\java\
- 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
- Download a jar file for a respective database (e.g. MS SQL Server: sqljdbc4.jar, Oracle: ojdbc6.jar)
- Extract the jar file in any directory (For e. g.: C:\Program Files (x86)\SAP BusinessObjects\\SAP BusinessObjects Enterprise XI 4.0\java/lib)
- Open the CRConfig.xml file from the location: C:\Program Files (x86)\SAP BusinessObjects\\SAP BusinessObjects Enterprise XI 4.0\java/
- 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:
- Navigate to the location: C:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\dataAccess\connectionServer\jdbc\
- Search for the file with an extension .sbo (For MS SQL Server the file is: sqlsrv.sbo. For Oracle the file is: oracle.sbo)
- 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 - 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:
- Navigate to the location: C:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\dataAccess\connectionServer\jdbc\
- Search for the file with an extension .sbo (For MS SQL Server the file is: sqlsrv.sbo. For Oracle the file is: oracle.sbo)
- Extract the jar file in any directory. (For e.g. C:\Users\Administrator\Desktop\enu\sqljdbc_3.0)
- 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
1527666 – JDBC Connection URL’s while creating Crystal Reports based on JDBC Connectivity
1558463 – How to create a Crystal Report against a Teradata database using JDBC connection
Thanks for sharing the document Nilesh!!
Fantastic document Nilesh
Keep up the Good Work !!!!
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.
Your best bet to get an answer to this would be to ask this question in a new discussion in the Crystal for Eclipse space here: http://scn.sap.com/community/crystal-reports-for-eclipse
-Dell
very useful document.... ℹ
Good One Nilesh.
Thanks
-Raghavendra
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.
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.
None of these steps are working, is any other way to configure on SAP crystal reports for enterprise.
Hi, how to get the jar files of SAP Crystal report ?