Configuring the JDBC node to connect to remote databases using the LAE Server

This document was developed by Stony Smith of our Professional Services team - it covers a range of topics, and is focused on Server installations. For information on how to connect to a database using the Desktop version, follow this link: Desktop Remote Connection to Database

Users that wish to connect to remote databases have the option of using the JDBC node.  There is a bit of work required to make it operate correctly.

First, you need to perform a few steps:

• 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 userid/password for the server.

Step 1: Locate JDBC driver

              These are Java Archive (JAR) files. You will need to check with the database vendor to obtain the correct version of the file – they’re not supplied by Lavastorm.  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.  The standard is to load them into <installdir>/lib/java/ext, but  you also need to be careful that you don’t have conflicting versions in the same folder.

              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 on the server. 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.

              It is possible to place the files somewhere else on the server, but you will then have to add a parameter “Classpaths” to the node:

• Create a new parameter and name it “Classpaths” (capitalization required)
• Set the Parameter Type to “inline file”
• List the entire path name of each JAR in the folder, especially if there is more than one JAR involved
• Separate the file names each on a new line by itself.
• ** QUESTION ** Does Classpaths work in Dataverse-JDBC?

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 through 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

We strongly recommend that you read the message after every failure. There are 5 or 6 steps to the process of performing a JDBC Query and after you pass each step, you may get a NEW error on the next step of the process, such as:

- locating the jdbc driver
- locating the server
- finding the right port on the server
- logging in with username xxxx
- logging in with password xxxx
- properly formatted SQL query
- do the requested tables exist
- do the requested columns exist

COMMON ERRORS:

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.

If you still cannot establish a connection, we recommend you seek help from you Database Administrator and / or your Network Administrator.