Get started Bring yourself up to speed with our introductory content.

Squirrel: The universal SQL client

Jim Mason, president of ebt-now, shares his thoughts on the open-source product Squirrel. According to Jim, Squirrel is a great tool for accessing any type of data anywhere using SQL. In this tip, he explains what Squirrel is, the challenges of working with Squirrel and how this product could work as your only SQL database tool.


 

Jim Mason

Squirrel is a great tool for accessing any type of data anywhere using SQL! It's great for developers, administrators, testers, even end-users needing to view, validate or update data anywhere. It works with every database I've tested ( MySQL, DB2/400, DB2, Oracle, SQL Server and more). Even better, it's an open-source product so I can share it easily within all areas of our organization and with our customers as well. We've now adopted Squirrel as our SQL database tool for all our work replacing a whole set of tools for each database product. Our team now has one, easy to use tool for all our work thanks to the Squirrel project team!

Challenges of working with Data 

As a Web architect and developer, I spend a lot of time working with relational data and databases using SQL across MANY platforms and databases. When you design, build, test and support applications (Web or traditional) you need to work with data. Often, I need to:

1. Design or modify a database model: create a database, table definitions, views, constraints
2. Create some test data using SQL
3. Capture an existing data model from another database and move it to my test database
4. Capture selected test data and move it to my test database
5. Create a user-defined function to extend the database capability
6. Use Java database access for my applications to do: SELECT, INSERT, UPDATE, DELETE, CALL
7. Check performance benchmark results on the database
8. Check data results in our QA test cases
9. Check data results when we build our application features
10. Check data results when we have production support issues

I may be building a WebSphere application that will run on Linux and use Oracle as the database. I may be using DB2/400 on an iSeries with a Web application running Apache Tomcat on Windows. Whatever the scenario, whatever the project task, I'm working with data.

While the Java API JDBC (Java Database Connectivity) provides a common architecture for accessing ANY type of relational data (even text files, spreadsheets, RPG programs, Visual Basic applications and so on) on ANY database on ANY platform, I normally used a wide variety of tools to do that: Microsoft, IBM DB2 Control Center, IBM iSeries Navigator, database vendors and more. The differences, incompatibilities, limited function, training, documentation issues, productivity problems were horrendous costs of time.

I wanted a single solution that REALLY could work with ANY data source on ANY platform that solved all my needs above. Ideally, this would be open source so I wouldn't have problems getting my staff or customers to use the same tool (eliminating communication and productivity issues). Traditional database tools are usually licensed and, therefore, can't be used in common for any of these scenarios. They are also normally designed for just 1 platform and can't work across platforms.

Squirrel -- the universal SQL client 

Squirrel is an open-source database toolset for working with relational data. Squirrel describes itself as the Universal SQL Client by the project team for this open-source project. You can access Squirrel from the Source Forge Web site. You can access the project home page for more information on Squirrel.

After doing a review of popular open-source database tools, I found Squirrel (an open-source project) at Source Forge, a very popular open source foundry supporting many open-source software projects. After testing it against tools from Microsoft, IBM, Oracle and others, Squirrel became the obvious choice as our universal SQL database toolset.

Where does Squirrel run? 

Squirrel can run on a variety of client systems because it's a Java application with Java plugins for added functionality. You actually install it from a downloaded jar file.

Why haven't you heard about Squirrel? 

Open-source companies don't have license revenues to pay for large advertising budgets. You need to find them via the Web at sites like Source Forge or through references like this article. The companies that sell database client tools (or include them with the databases you buy) don't want to advertise there's a better SQL client tool on the market.

Is Squirrel easy to setup and configure? 

Yes and yes. The install (after the download) takes about two minutes. The nice thing is Squirrel has both default and session properties that you can manage on your SQL connections to a database to control how much data is downloaded for a given request for instance.

How do I connect to a database? 

It's a simple process:

1. Get the right JDBC driver (usually a jar or zip file) to add to Squirrel
2. Register the driver with Squirrel
3. Create a connection to the database
4. Run the connection to create a session and access data

Once you've defined a database connection, you just need to run it each time you want to connect.

Here's the driver I created for a JDBC connection to an AS/400:

 

I specified the driver name. The example URL is created as a template so you can modify it each time you want to connect to a different database. The jar file containing the JDBC driver is listed under the Extra Class Path information so Squirrel can access the driver at runtime. Finally, the class name for the JDBC driver in the jar file is automatically generated normally because Squirrel knows which class in the jar file is a JDBC driver.

Here's a list of JDBC connections to different databases I created in Squirrel:

 

To connect, I just double-click one and a session opens. Here, I have a session to a DB2 sample database. It shows all the database schemas and database object types in a tree on the left. For each object I select, a different object notebook opens with the appropriate tabs. I've selected a view in the sample database and you can see the data accessible in the view.

 

By clicking on the columns tab, I can see the column definitions for the view:

 

Where do I get JDBC drivers to use with Squirrel for databases? 

You need JDBC drivers but most of the time you can get those for free from a vendor or as part of a database or database tool you already own. If you can get a JDBC driver for a specific database, you can use the default Sun JdbcOdbcDriver that can leverage an ODBC connection to get to a database. I've used that to read and write SQL to Excel spreadsheets for instance.

What's wrong with Squirrel? 

Honestly, nothing. If you don't know SQL at all that's an issue. The easiest thing to get a quick intro on basic statements like SELECT, INSERT, UPDATE, DELETE is go out to Google and do a quick search and go through the options:

How Squirrel helps 

We use Squirrel everywhere now and it does address nicely all the challenges we've had with database work:

1. Design or modify a database model: create a database, table definitions, views, constraints We run SQL DDL scripts to maintain database structures on any database we work with.

2. Create some test data using SQL. We can run custom SQL scripts to insert data. If you don't know how to write an INSERT statement, just run the option that generates INSERT SQL statements for any table you select.

3. Capture an existing data model from another database and move it to my test database Squirrel provides a simple option to capture the DDL that describes your tables easily in 1 step. Here's a DDL definition to create a DEPARTMENT table generated by Squirrel:

 

4. Capture selected test data and move it to my test database. I use the create data option all the time to capture data quickly to setup test databases and so on. Here, I've generated in one step the SQL INSERT statements as a text file to recreate the data in the DEPARTMENT table:

 

5. Create a user-defined function to extend the database capability More advanced databases allow generated stored procedures and user-defined functions (DB2, Oracle, MySQL.)

6. Use Java database access for my applications to do: SELECT, INSERT, UPDATE, DELETE, CALL
In one step, I wrote and executed a simple SELECT statement to generate a quick report table below. Having generated the output, I can easily turn it into any other form I need to save but getting the right button menu on the result table. I can copy it, generate HTML pages, print to a PDF etc! The console window is helpful for showing the resulting messages from running my SQL statements including any errors I generate.

 

7. Check performance benchmark results on the database

8. Check data results in our QA test cases

9. Check data results when we build our application features

10. Check data results when we have production support issues

Plugins and bookmarks 

Despite the fact that Squirrel is an open-source tool, it has an advanced architecture supporting plugins (similar to Eclipse concepts). Other contributors have created great plugins providing added functionality. One of the most valuable for me is the bookmark capability. When we create test SQL scripts to run over and over again with different parameters, we don't have to remember them. We can just bookmark a script and then rerun it any time. In addition, I can actually create variables for that script so I can be prompted at runtime for a value to enter. For many complex SQL scripts we build, the ability to save, rerun and use variables to enter just the differences is enormously productive. Here's a bookmark script I created that uses a variable to prompt for the application name when I run it. It then creates the SQL substituting the value I key and generates the output.

Summary on Squirrel 

How does it stack up as a database tool? I haven't found anything close. And it has replaced all the other database tools we used in development, testing and so on. Normally we develop training courses on software we like so we can resell them to customers and teach them how to use these tools. Unfortunately for us, the online help documentation in the product is great. As a result we didn't think we could add any value by creating a course for this tool. I think if you check out Squirrel you'll agree it's a great product and a great contribution to the open-source community!

---------------------------------------

About the author: Jim Mason works at ebt-now, an iSeries Web integration company, providing Quick Web Services for iSeries customers: Web planning, WebSphere, WebFacing, Web development, Web networking, Web support, Web security and training services. Jim is creating a self-study course for RPG programmers that teaches "hands-on" rapid visual development with WDSC for all types of iSeries and e-business applications without the need to become a Java expert. Rochester Initiative will publish the course. You can reach Jim at jemason@ebt-now.com.


 

Dig Deeper on iSeries SQL commands and statements

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataCenter

Close