Art of BI: Understanding JDBC Connection Strings for WebLogic

Java web-based applications leverage a Java Database Connectivity (JDBC) connection in order to provide a common syntax for connecting JEE applications with the world’s most common databases. The databases can vary but if you are using a Java application and connecting to a database such as SQLite, Oracle, Microsoft SQL Server, etc. you are more than likely using a JDBC connection. Understanding JDBC Connection Strings for WebLogic can pose an initial challenge for a JEE application as there are many different database connection configurations across the enterprise; no one single configuration of a database works for all organizations.

BITeamwork is Java based web application, as are most Oracle operational applications. As such, during the installation process of BITeamwork, there is a default JDBC string which is needs to be manually configured in order to associate the application with an underlying database for accessing the BITeamwork repository. Oracle BI (OBIEE) 11g uses the same type of JEE to JDBC configuration in WebLogic server to connect to the database schemas created using the Repository Creation Utility (RCU). So the use of this with BITeamwork and other Oracle application is nothing outside the scope of the Oracle Fusion Middleware stack.

This post goes into the two supported database systems for the Oracle BITeamwork metadata repository: Oracle and Microsoft SQL Server. It also looks at the several different JDBC connection strings plausible in today’s enterprise database access configurations. As a reader if you feel like we’ve left out a JDBC connection scenario please leave a comment so that we can address it and add it into this post.

Because JDBC connections in an JEE application server do not take into account a standard Oracle database connection reference file such as a TNSNames.ora (unless the Oracle client is installed on the application server), an EZCONNECT string, or an ONAMES server, the application server requires a full database connection string URL (ex: jdbc:oracle:thin:@ maindbserver:1521:orcl). In the case of an Oracle database the commonly accepted options for a JEE application server boil down to the use of LDAP, SID, or SERVICE NAME associated with the database access connection. Each of these (we’ll even include a RAC instance connection string) is structured with slight differences.

Oracle Database JDBC Options

Connecting to an Oracle database via JDBC typically involves three main connections types allowable in a JDBC connection URL: LDAP, Service Name, and SID.

When connecting with an incorrect JDBC connection string in the WebLogic Server application server to a database where a SERVICE NAME connection is required instead of a SID, or vice versa you may get the following error message.

[INFO] Setting Username for the JNDI/JDBC Database Connection
[INFO] Defining Global Transaction Protocol for the JNDI/JDBC Database Connection
[INFO] Setting the Managed Server(s)/Cluster on which to Deploy the JDBC Connection
This Exception occurred at Thu Nov 14 13:45:14 PST 2013.
javax.management.InvalidAttributeValueException: Array has at least one null element
        at weblogic.rjvm.ResponseImpl.unmarshalReturn(ResponseImpl.java:234)
        at weblogic.rmi.internal.BasicRemoteRef.invoke(BasicRemoteRef.java:223)
        at javax.management.remote.rmi.RMIConnectionImpl_1035_WLStub. setAttribute(Unknown Source)

The above is an example error output from a BITeamwork installation when mistakenly configuring an SID instance using Service Name configuration syntax for the JDBC connection.

Most JEE Application servers do not have the intelligence to test multiple iterations of your keyed in configuration string so this is an explicit configuration setting. Make sure it is correct before creating an application data source connection pool. Sometimes detecting the issue is a real pain so use the below information to verify that you’ve got the correct configuration before pulling your hair out.

To test which connection string type you should use for the JDBC connection string for an Oracle database, you should initially connect to SQL*PLUS (i.e: the Oracle Client) on a workstation or server that you know has connectivity to the desired Oracle database which you are attempting to connect. Run the TNSPING command from the command line, ex: tnsping [SID|Service Name].

This will give you the Oracle db server name, which lookup source is being called, etc. Use that information as input for the below connection strings:

Below are the correct JDBC connection string connection URL per Oracle instance connection type:

If the Oracle db uses an SID, use the following configuration:
jdbc:oracle:thin:@[servername]:[serverport]:[SID]
for example, jdbc:oracle:thin:@ maindbserver:1521:orcl

If the Oracle db uses an SERVICE NAME, use the following configuration:
jdbc:oracle:thin:@[servername]:[serverport]/[servicename]
for example, jdbc:oracle:thin:@ maindbserver:1521/orcl.us.mymachine.net

If the Oracle db uses an TNSName, use the following configuration because the Oracle client and TNSNames.ora configuration exists on the same machine as the application server:
jdbc:oracle:thin:@[TNSNAME]
for example, jdbc:oracle:thin:@ maindbserveralias

If the Oracle db uses an LDAP Configuration, use the following configuration:
jdbc:oracle:thin:@ ldap://<OID server name>:<OID port>/<DB SID or Service Name>, cn=OracleContext,dc=<yourdomain>
for example, jdbc:oracle:thin:@ldap://myldapserver:389/orcl,cn=OracleContext,dc=mycompanyxyz,dc=com

We have found though that for LDAP configurations, usually the direct SID or SERVICE_NAME configuration works perfectly well as the user name and password are eventually passed to the server for authentication anyway.

If the Oracle DB is set up with a RAC configuration, use the following configuration:
jdbc:oracle:thin:@(DESCRIPTION=(LOAD_BALANCE=yes)(ADDRESS=(PROTOCOL=TCP)(HOST=NODE1-VIP)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=NODE2-VIP)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=MYRAC)))

Further details and explanations can be found on the Oracle JDBC driver documentation.

There is also a nice OTN document for setting up and Oracle DB JDBC connection if requiring GridLink.

Microsoft SQL Server 2008+ Database JDBC Options

Microsoft uses a similar tactic in connecting to a database for multiple users or data sets. Instead of services and schemas we are given instances and owners. For SQL Server the default port is 1433. Because there are fewer options for connecting a MS SQL Server database when compared to an Oracle database there are technically only two JDBC URL configurations for a MS SQL Server database:

If the MS SQL Server database uses a default instance, use the following configuration:
jdbc:weblogic:sqlserver://[servername]:[port]
for example, jdbc:weblogic:sqlserver://mysqlservermachine:1433

If the MS SQL Server database uses a named instance, use the following configuration:
jdbc:weblogic:sqlserver://[servername][instancename]:[port]
for example, jdbc:weblogic:sqlserver://mysqlservermachinesqlserverbigdata:1433

As you can see from the configurations above for MS SQL Server, we are assuming that you are using the WebLogic Server driver for SQL Server and not a generic driver.

There is a good technet article on SQLServer JDBC Connection URLs and for any clustered instances. Connecting to legacy MS SQL Server versions via JDBC also have different URL logic.

Application Servers

All JEE application servers provide a location to store data connection strings. WebLogic Server holds the configuration in its underlying physical configuration files but ultimately a JDBC database connection within the application server can be accessed via the GUI WLS Administration Console (Services > Data Sources > Generic Data Sources) or using the WebLogic Scripting Tool (WLST) language. BITeamwork leverages WLST to create the initial jobc/biteamwork connection string. Regardless of the application server or the application you are deploying, if you attempt to connect to an incorrectly configured JDBC connection string your error message may be quite nebulous. So, inspect the connection string carefully and test using the application server or other means to ensure the full JDBC compliant URL actually connects with success.

‘Nuf Said

This post set out to explain why JDBC is used in a JEE application and the different configurations for the supported database repositories leveraged by BITeamwork. We also dropped some knowledge on the storage of a JDBC connection in the application server for good measure. Anytime you are configuring a data source for the first time it is a good idea to work with a DBA if you aren’t too sure about the database attributes or specifications and how they may align with the configuration options you are giving for the application you seek to work with. In regards to BITeamwork, we’ve tried to make it as easy as possible to configure the repository using the single JDBC connection string as you can see from the installation steps in the BITeamwork installation videos.