1218714 - How to troubleshoot JDBC (JNDI) connection issues with Crystal Reports

SAP Knowledge Base Article - Public

1218714 - How to troubleshoot JDBC (JNDI) connection issues with Crystal Reports

Symptom

How to troubleshoot JDBC (JNDI) connection issues with Crystal Reports 10 and XI?

Resolution

To troubleshoot JDBC (JNDI) connection issues with Crystal Reports, start by creating a small Java application to retrieve records through the JDBC connection.

If the JDBC connection works outside of Crystal Reports, then Crystal Reports can use this connection (provided that the JDBC database connection is supported by Crystal Reports).

Once you create the Java program, use the following steps:

1. Copy the Java application in Notepad and save it to your 'jdk bin' folder.

You can save the Java application to another folder provided that your Java compiler classpath is set to enable compiling from other folders. When in doubt, save the Java application to the folder where the javac.exe file is located.

2. Configure the following variables inside the Java application:

  • jdbcdriver
  • jdbcurl
  • username
  • password
  • sql

3. From the command prompt, navigate to the c:\jdk1.4.*\bin folder.

4. From the command prompt, type "javac JDBCtest.java" to compile your Java application.

5. From the command prompt, type "java JDBCtest" to run your Java application.

====================

NOTE: In steps 4 and 5, the spelling and case must be exact since the compiler is case sensitive.

====================

If you can successfully retrieve data, then Crystal Reports can use this connection (provided that the JDBC database connection is supported by Crystal Reports).

If the JDBCtest application does not retrieve data, ensure that the classpath for the database driver is set properly. Once the classpath is set, try JDBCtest again.

The following examples demonstrate setting the classpath. You may need to adjust the examples depending on where the relevant JAR or ZIP files are located on your computer.

Setting the classpath for DB2 7.1 or 8.1

========================

set classpath=%classpath%;C:\ProgramFiles\IBM\SQLlib\java\db2java.zip;

Setting the classpath for Microsoft SQL Server

=====================

set classpath=%classpath%;C:\Program Files\Microsoft SQL Server 2000 Driver for JDBC\lib\msbase.jar;C:\Program Files\Microsoft SQL Server 2000 Driver for JDBC\lib\mssqlserver.jar;C:\Program Files\Microsoft SQL Server 2000 Driver for JDBC\lib\msutil.jar;

Setting the classpath for Oracle 9.0

=====================

set classpath =%classpath%;C:\oracle\ora92\jdbc\lib\nls_charset12.jar;C:\oracle\ora92\jdbc\lib\ojdbc14.jar;

Setting the classpath for Oracle 9.2

=====================

set classpath =%classpath%;C:\oracle\ora92\jdbc\lib\ojdbc14.jar;

Viewing your classpath

==============

To view your classpath, at the command prompt type "echo %classpath%".

//------------------------------------------------------------------

// JDBC connectivity test - Business Objects

//------------------------------------------------------------------

import java.sql.*;

import java.util.*;

import java.io.*;

public class JDBCtest

{

public static void main(String[] args)

{

String jdbcdriver;

String jdbcurl;

String username;

String password;

String sql;

//------------------------------------------------------------------

// manual user configuration needed here

//------------------------------------------------------------------

username = "user";

password = "password";

sql = "select * from customer";

// db2 versions 7.1 and 8.1

jdbcdriver = "COM.ibm.db2.jdbc.app.DB2Driver";

jdbcurl = "jdbc:db2:<databasename>";

//------------------------------------------------------------------

// other configuration samples

//------------------------------------------------------------------

// oracle 8.17, 9.0, or 9.2

// jdbcdriver = "oracle.jdbc.driver.OracleDriver";

// jdbcurl = "jdbc:oracle:thin:@<host>:<port>:<sid>";

// sql server 2000

// jdbcdriver = "com.microsoft.jdbc.sqlserver.SQLServerDriver";

// jdbcurl = "JDBC:microsoft:sqlserver://SERVERNAME:PORTNUMBER";

// load the jdbcdriver

try

{

Class.forName(jdbcdriver);

}

catch (java.lang.ClassNotFoundException e)

{

System.err.print("ClassNotFoundException: ");

System.err.println(e.getMessage());

}

try

{

// make the connection

Connection c;

c = DriverManager.getConnection(jdbcurl, username, password);

Statement s = c.createStatement();

ResultSet rs = s.executeQuery(sql);

ResultSetMetaData rsmd = rs.getMetaData();

int numberOfColumns = rsmd.getColumnCount();

// display the column names

for (int i = 1; i <= numberOfColumns; i++)

{

String columnName = rsmd.getColumnName(i);

int dSize = rsmd.getColumnDisplaySize(i);

int jdbcType = rsmd.getColumnType(i);

System.out.print(columnName);

for (int k = 1; k <= (dSize - columnName.length() + 1); k++)

{

if (jdbcType != 4)

{

System.out.print(" ");

}

else

{

System.out.print(" ");

break;

}

}

}

System.out.println("");

// display the field data

while (rs.next())

{

for (int i = 1; i <= numberOfColumns; i++)

{

if (i > 1) System.out.print(" ");

String columnValue = rs.getString(i);

System.out.print(columnValue);

}

System.out.println("");

}

s.close();

c.close();

}

catch (SQLException e)

{

System.err.println("ERROR: Invalid SQL Statement Entered");

System.err.println(e.getMessage());

e.printStackTrace();

return;

}

}

}

//------------------------------------------------------------------

// JDBC connectivity test - end

//------------------------------------------------------------------

See Also

CR 10 Java Reporting Component JNDI and JDBC Data Sources

Creating JDBC Connections in Crystal Reports XI

Configuring JDBC (JNDI) connectivity in Crystal Reports 10

Note 1217370 - JDBC connection to SQL Server or Oracle in Crystal Reports 10

Note 1217667 - Err Msg: "Java server startup failure..." when selecting XML or JDBC in CR 10

Note 1217776 - Err Msg: "Error: Invalid Argument provided" when creating JDBC or XML connection

Note 1218366 - Err Msg: "Failed to retrieve data from the database" adding MySQL JDBC table

Keywords

CLASS PATH XI CONNECTION TROUBLE SHOOTING SHOOT Crystal Reports Database connectivity JDBC , c2017533 , KBA , BI-RA-CR , Crystal Reports designer or BusinessViews Manager , How To

Product

SAP Crystal Reports XI ; SAP Crystal Reports XI R2