Connecting to Databases using JDBC

This article will walk you through the process of setting a connection to your database by leveraging the JDBC node. 

Overview of the Process
The following is a quick overview of the steps required to connect to your database.  Each step will be discussed in detail below this section.

• Locate the JDBC driver(s) for the database
• Load the drivers to a place where the JDBC node can find them.
• Make note of the URL required for the database:
  1. What is the proper communication protocol required
  2. IP address or name of the server
  3. What port is required
• Make note of the proper name for the driver
• Obtain a proper username and password for the server.

Step 1: Locate the JDBC driver

These are Java Archive (JAR) files. Many of the common drivers are shipped with Data360 Analyze.  If you will need to check with the database vendor to obtain the correct version of the file – they’re not all supplied by Infogix.  Very often, these files are version-dependent, so you must match up the proper version of the JAR with the version of the database you want to connect to.

Step 2: Load the drivers

The driver(s) may be several JAR files.  We recommend installing drivers in a sub-directory under the Site folder, e.g. <site directory>/lib/java/db/<driverName>. The 'db' folder needs to be created under the site directory as it is not created during the install.

After placing the driver there, you will need to set 'DbDriverClasspath' on the node to the folder where you placed the driver.. for more info see the JDBC nodes, under the 'Advanced database connection' section, which can be found HERE.

Note: if the vendor provides you with a big ZIP file containing the JAR file(s), you will need to unpack them as you place them into the aforementioned folder. It is also common for a vendor to include several documentation and “auxiliary” files in the ZIP. most of the time, you only need the JAR file(s).

Note: It is not recommended to install third party JAR files in any location within the Data360 Analyze installation directory as these files will not be retained when the application software is upgraded.

Step 3:  URL for the server

The general form for the URL is  jdbc:protocol://servername:port

This may change as the vendor updates the driver in future versions.

Examples:

• For jTDS 1.2, the URL is jdbc:jtds:sqlserver://sql_server_machine_name.
• For MS-SQL Server 2000, the URL is jdbc:microsoft:sqlserver://sql_server_machine_name:port
• For MS-SQL Server 2005, the url is jdbc:sqlserver://sql_server_machine_name:port
• For MS-SQL Server 2008, the URL is jdbc:microsoft:sqlserver://sql_server_machine_name:port
• For Cloudera Impala, the url is: jdbc:impala://servername:port

Step 4: Database Driver

The name of the driver should be provided by the vendor.

This may change as the vendor updates the driver in future versions.

Examples:

• For the jTDS 1.2, driver name is sourceforge.jtds.jdbc.Driver.
• For MS-SQL Server 2000, the driver name is microsoft.jdbc.sqlserver.SQLServerDriver
• For MS-SQL Server 2005, the driver name is com.microsoft.sqlserver.jdbc.SQLServerDriver
• For MS-SQL Server 2008, the driver name is com.microsoft.jdbc.sqlserver.SQLServerDriver
• For Cloudera Impala, the driver name is cloudera.impala.jdbc4.Driver

Troubleshooting:

The JDBC node has several processes that it must go thru before a connection will succeed.  Each step may need its own troubleshooting.

• Locate and open the JAR driver file(s)
• Find the requested DbDriver within the JAR file(s)
• Establish a TCP/IP connection to the server
• Establish a Database connection on the server
• Log into the server
• Send the Query
• Receive the response back from the database

There are a several messages that may appear while trying to attempt a connection. When you are first setting up/testing a connection, you may have to cycle thru more than one of these.   Admittedly, the messages are confusing, but they are usually unique enough to guide you to determine what needs to be fixed. The important part about troubleshooting is -- pay attention to the error message(s).  You may solve one problem, and still be faced with another (different) issue before you can finally connect.

• The following classpath entry for the node does not exist: [folder].
  Error Code: brain.execution.java.node.classpathNotFound
  • You asked for a JAR file that could not be found in the folder
    
    
• ERROR: unable to load db driver: [driver]
  • The driver was not found within the JAR. Did you get the right name?
    
    
• Unable to connect: Error initialized or created transport for authentication: java.net.UnknownHostException
  • The driver could not communicate to the server
    • Do you have the correct server name
    • Do you have the correct port
    • Does your firewall allow you to connect to the server
      
      
• Unable to connect: No suitable driver found for [url]
  • The node communicated to the server, but the communication protocol selected is incorrect. Example:
    • unable to connect: No suitable driver found for jdbc:hive2://server:port
    • in this case, the proper protocol needed was jdbc:impala://server:port
      
      
• ERROR: unable to connect: The UserId, Password or Account is invalid.
  • Simply put, you’ve got the wrong userid or password, and/or that userid is not allowed to sign into the database
  • More often than not, your standard network userid/password will NOT work, and you must be granted specific permission to log into the database.

*tip for power users: If you have the JAR file and do not know exactly what the driver name is, you can copy the jar file, change the extension to ZIP, then unzip and browse the file to locate the Java class that seems to be the one you’re looking for.