JDBC connectivity in SAP BI4

If you want to connect IDT(Information Design Tool)  to reporting data sources using JDBC you need a .jar file for that relational database.  Here in this example, I am going to show you how to connect to ORACLE database 11g and SQL Server 2008 databases.  First make sure you have the middle ware /client software installed on the machine where you are using IDT. Also every database vendor will provide their own JDBC connectivity driver software to connect to their databases or you can write your own class definition and use it.

The most common errors that you will see are the following ones when you try to connect to a database using JDBC drivers using IDT: 

 Java Class not found in classpath and No suitable driver found (Generic) 
 java.lang.ClassNotFoundException: oracle.jdbc.driver.OracleDriver (for Oracle only) 
 oracle.JDBC.OracleDriver (for Oracle only) 
 com.microsoft.sqlserver.jdbc.SQLServerDriver (for SQLServer only)

Oracle database

1) Lets create a new relational connection in IDT to connect to Oracle using JDBC drivers. If you don’t find ojdbc14.jar(required for oracle 11g) then you can download directly from www.oracle.com for the version you need.

2) Give it a name and click Next, Select the database middleware driver, Oracle 11.

3) In the above screen select JDBC Drivers and click Next to go to next screen

4) In the above screen specify the user name, password, oracle server host name:port # and oracle service.  Click Test Connection, it may display the error discussed above.  To resolve this  error copy the ojdbc14.jar to the following
 directory : <install folder>\SAP BusinessObjects Enterprise XI 4.0\dataAccess\connectionServer\jdbc\drivers\oracle\ojdbc14.jar on the BI Server or a client machine.   If you don’t see oracle directory under drivers directory 
 in the said path then create it and save the .jar file under it.

5) Restart IDT and try creating the JDBC connection again, It should be successful and hence we resolved the error.

SQL Server 2008

Similarly trying to connect to SQL Server 2008  using IDT throws an error shown below.

To resolve the error, download the latest jdbc driver for SQL Server 2008 from Microsoft website (http://www.microsoft.com/en-us/download/details.aspx?id=11774) , As of this writing the file available from microsoft is “sqljdbc_4.0.2206.100_enu.exe”, extract this file to a temp folder on your machine and copy sqljdbc4.jar to the following folder: <install folder>\SAP BusinessObjects Enterprise XI 4.0\dataAccess\connectionServer\jdbc\drivers\sqlsrv2008.

After saving the sqljdbc4.jar in the right folder, try to create a new JDBC connection to connect to SQL Server 2008 using IDT the connection was successful.

One last thing I would like to bring is that most of jdbc connectivity errors will be resolved if the classes or jar files are found in the path environment variable.  That is all for today.