Unix: Database connection testing

Before you try to try to get your Unix system collecting data from a database that someone else manages, you might want to run some simple tests from a Windows desktop. Yes, I said "Windows".

If one of the systems you manage has to connect and get data from a remote system that you don't manage, it's a good idea to have a couple tools on hand that will make it fairly easy to test your connection before you need to integrate the data collection in a more complex procedure. One way to do this is to have a series of tools that you can use from a Windows system that provide an easy and reliable way for you to verify your connection parameters (username, password, port, etc.) and test your database queries. Once you do these things, you can move ahead with more confidence when implementing your database connections on your application. You will have ruled out a number of potential problems. In this post, I describe some of the tools that can help you do this. Before I begin, let me say that the tools described in this post are not at all specific to testing Unix systems. Instead, they just provide easy ways to test database connections and queries. I like these tools for verifying my login credentials and ensuring my queries work as intended before implementing them in more complex applications and for verifying that the results that I'm seeing are what I should expect to see.

UDL Files

One easy way to verify that an account provided to you will allow you to connect to a remote database is to use something call a "UDL" file. You can easily create one on a Windows system. A UDL file is a file with the extension "udl" and all you need to do to create one is open notepad and save an empty file with this extension. Select File => Save As..., Change "Save as type" to "All Files" and type something like dbtest.udl in the File name field. That's it. When you then click on the file's icon, a surprising thing happens. A tool opens up that you can use for describing database connections and testing them. The UDL (Data Link Properties) form that opens up has four tabs -- Provider, Connection, Advanced, and All. Since I had to connect one of my Unix systems to a Microsoft SQL Server database, I selected Microsoft OLE DB Provider for SQL Server on the Provider tab. In the Connection tab, I had to enter the name of the server, the username and password provided to me for the connection and the name of the database that I needed to connect to. Then I hit the Test Connection button and, voila!, I ran into an error -- Test connection failed because of an error in initializing provider ... Server does not exist or access denied. Did I do something wrong? No. There was something that still needed a little kick in the asset on the database side. The point is that I could have struggled for quite a while on my Unix server application, changing settings and trying to determine what I was doing wrong. Instead, a very simple test proved to me that the other side of the connection wasn't yet ready for me to connect. I saved a lot of time and was easily able to demonstrate to someone else that there was something wrong on their side of the setup. No "the problem must be on your Unix server" arguments ensued and the problem was soon resolved. UDL files can also be pre-populated with the configuration information required for a particular system. You can prepare a UDL file for a particular database in notepad by inserting some text like this and it will be ready to test your specific connection. Or you can let the tool save this type of information for you after you've entered your settings using the various tabs. The saved information will look something like this:

; Everything after this line is an OLE DB initstring
Provider=SQLOLEDB.1;Persist Security Info=False;User ID=MyUserName;Initial Ca
talog=RemoteDB;Data Source=ServerName

Just make sure that you replace the MyUserName, RemoteDB, and ServerName strings with the information provided to you for the remote database you are connecting to.

Oracle SQL Developer

SQL Developer is a tool that allows you to connect to and run queries against Oracle. It's free and has a nice interface that allows you to create and save connection profiles for each of the databases you need to query, plus a large window to compose queries and another to view your results. In the properties form for any of your database entries, you provide the username and password for the database user, the host name, port and SID or service name for the database. As with UDL files, there is a Test button that allows you to verify your connection parameters. Type your queries, such as "select last_name, first_name, astro_sign from staff;" and click the little green triangle. Your screen should fill up with the names and astrological signs of all your coworkers. Well, maybe not. But adjust your query for what you are actually looking for. Plus, you can save the data you collect in any of a number of forms. I generally use the CSV format. I can then easily load the data into Excel to do further checking to make sure I'm getting what I expect. At that point, implementing the queries on my Unix server application involves no more than putting the queries and connection parameters in the right places in that system. I can move to that stage with confidence that any problems I run into are problems with that system alone. SQL Developer is also available for Linux and MacOS X.

SQuirreL SQL Client

Squirrel is another free tool that provides a way to test database connections and queries. What you need to do is install the tool along with whatever jar files are required to support your specific database connections. I use Squirrel to connect to Microsoft SQL Server. After the basic Squirrel install, I had to find and install Microsoft JDBC Driver 4.0 for SQL Server. Once the sqljdbc4.jar was in the Squirrel/lib folder, I was able to make the connection and run queries. If you, too, need to do this, note that you do not need both the sqljdbc.jar file and the sqljdbc4.jar file. My setup required the latter and specifically not the former. Like SQL Developer, Squirrel also works on Linux systems and MacOS X, not just Windows. So load either or both of these tools on whatever system is most convenient for you. Both are easy to set up and easy to configure. Depending on your user's privileges, you can also use Squirrel to make changes to your database -- creating new tables, inserting rows of data, and changing content. I only use it for testing, but many Squirrel users go a lot further in using it for their regular database upkeep.

Wrap Up

Checking basic connections and verifying queries with tools that are simple to configure and use allows me to rule out a lot of potential problems before moving ahead with more complex database operations and also provides another way to test my more complex applications. If I get different results that I expect when running a report, for example, I can check the same query using one of the desktop tools and compare the results. If your ice cream comes out of your freezer soft and runny, after all, you might prefer an independent temperature probe to trusting the built-in thermostat. It's always a good idea to have tools on hand that can help you test and validate basic connections before moving on to more complex configuration issues involving your critical applications. Besides, I've worked at a lot of places where, regardless of how many Unix systems I manage, the box on my desktop is running Windows. It's good to know what tools are available to help simplify your work -- especially when you may be the expert on only one side of the connections you need to establish.

Read more of Sandra Henry-Stocker's Unix as a Second Language blog and follow the latest IT news at ITworld, Twitter and Facebook.

Join the Network World communities on Facebook and LinkedIn to comment on topics that are top of mind.

Copyright © 2014 IDG Communications, Inc.

IT Salary Survey: The results are in