CUBRID Through SQuirreL SQL Client

SQuirreL SQL Client is a quite famous graphical Java program that allows you to view the structure of a JDBC compliant database, including CUBRID, browse the data in tables, execute SQL queries, and perform many other tasks which we will cover below. It is very powerful, convenient, fast and easy to use. You will like it.

Requirements

CUBRID Database

There are two options how you can use SQuirreL SQL Client.

1. Remote database connection: In this case you do not need a database installed on your machine, but just its JDBC driver. You can download the CUBRID JDBC Driver from http://www.cubrid.org/cubrid_java_programming#downloads.
2. Local database connection: This option required you to have a database installed locally on your computer. You can download CUBRID from http://www.cubrid.org/downloads. Follow the Installation Instructions if necessary.

Java

Since SQuirreL SQL Client is a Java program, you have to have at least Java 1.6 (as of SQuirreL version 3.0) installed on your computer. See Instructions to download and install Java either on Windows or Linux.

Downloads

This SQuirreL SQL Client is open source and hosted on Sourceforge.net. It is available for Windows, Linux, Mac OS X, and others. You can download the latest version from the link below.

Installation

The SQuirreL SQL Client is packed in IzPack Java installer. Once you have downloaded the JAR file (e.g. squirrel-sql-3.2.1-install.jar), open the command line terminal and type the following command.

java -jar squirrel-sql-3.2.1-install.jar

This will launch the IzPack installer. Click on the Next button to continue.

Read the information about SQuirreL SQL Client, then click on the Next button to continue.

Choose the destination to install the SQuirreL SQL Client, then click Next.

If the directory you indicated does not exist, it will prompt for confirmation. Click OK and continue.

This step provides options for installing additional plugins or translations. Choose the necessary plugins if you need, then click Next.

The installation process will start. Once finished, click Next.

Here you are prompted if you want to create the program shortcuts. Once done, click Next.

The installation is completed, so click Done.

Start SQuirreL SQL Client

There are two ways to launch the program.

Command Line

On Windows, execute the file squirrel-sql.bat to run the application. On Linux, run the squirrel-sql.sh script.

Shortcut

If you have checked during the installation process to create shortcuts, you can start the program by clicking on the shortcut. See the following example taken on Windows machine.

Once started, you should see its main window.

Add JDBC Driver

This step can be performed only once when you launch the program for the first time. Let's just check if CUBRID JDBC (or any other driver you want) is present. Click on the Drivers tab on the left. All available drivers should be listed there. This time almost all of them are market with red cross icon because have not been downloaded. So you have to manually locate them on your computer. If the one you want is not listed at all, click on the Create a New Driver button.

A new window should appear which let you add the new driver.

• Name: enter a name for the new driver (e.g. CUBRID JDBC Driver).
• Example URL: add the following connection string. The variables between <> angle brakets will give you a hint that instead of them later when creating a new connection you need to include the actual values. We will see it later.

jdbc:cubrid:<hostname>:33000:<dbname>:<username>:<password>:

• Web URL: enter the following URL which is the home page for the CUBRID JDBC Driver.

http://www.cubrid.org/cubrid_java_programming

Now we need to add the JDBC driver. For this, click on the Extra Class Path tab and click on the Add button to browser for the CUBRID JDBC Driver.

In the new File Manager Window locate the CUBRID JDBC Driver which is often located in c:CUBRIDjdbccubrid_jdbc.jar in Windows OS, or in /home/CUBRID/jdbc/cubrid_jdbc.jar in Debian based Linux OS, or in /opt/cubrid/jdbc/cubrid_jdbc.jar in Redhat Linux. Once selected, press OK.

Then press the List Drivers button to automatically list the Driver Class Name in the Class Name field below. Then press OK.

This is all. The CUBRID JDBC Driver has been added. Now if you want to make this driver the default driver, so that every time you create a new connection this driver is chose by default, then press on the Set Default button on the right. Then press OK.

Create New Alias (or Connection)

To establish a new connection click on the Aliases tab on the left and click on the Create New Alias button.

In the new connection window fill out all the required fields.

• Name: enter any name for your connection (e.g. localhost).
• Driver: choose the CUBRI JDBC Driver.
• URL: here you should first see the previous jdbc:cubrid:<hostname>:33000:<dbname>:<username>:<password>: template you have entered before. So, instead of <hostname> enter your host name or IP (if CUBRID is installed locally, then enter localhost). The port number by default is 33000, but sometimes it is 8001. Instead of <dbname> enter your database name (in this case we will connect to the demodb database which comes be default with CUBRID). As a result you should get the connection URL like this:

jdbc:cubrid:localhost:33000:demodb:::

• User Name: enter your database username (for the demodb database it is dba).
• Password: enter your database username password (the demodb database does not require password, so you can leave it blank).

http://www.cubrid.org/cubrid_java_programming

Optionally you can choose to Auto Logon or Connect at Startup. If you see the Properties, there are many options you can set such as Load all and cache all Schemas.

Or you can indicate if you want to keep your connection always alive. Navigate to the Connection tab, check the Enable Keep-Alive and input the query you want it to execute to keep the connection live (e.g. SELECT 1;).

Once you configure everything, press OK to create the new connection. If you have previously checked to Auto Logon, pressing OK button will automatically try to establish a connection. Otherwise, you have to manually choose to connect.

Common Connection Errors

Cannot connect to a broker

If you see this error when you try to connect to the database, it means something is wrong with your Connection String/URL. Make sure it is correct.

Failed to connect to database server, 'demodb', on the following host(s): localhost

If you see this error when you try to connect to the database, it means your database has not been started. Type cubrid service start to start the CUBRID Service and cubrid server start demodb to start the demodb database.

Using SQuirreL SQL Client

Host Metadata

Once you connect to the database the main window should look like this. When you select the host name, many properties can be viewed such as the Metadata. You can see what Value is defined for a particular Property.

Data Types

You can see what Data Types are supported and their default values, properies, and behavior.

Table Columns

There are many properties and actions your can see or perform against the tables. To view the table columns, select the table and selec the Columns tab.

Primary Key

Similarly see Primary Keys defined in the table.

Row Count

Or Row Count.

Contents

Also you can see top 100 records stored in that table. Select the Contents tab for it.

Privileges

To see the table privileges, click on the Privileges tab.

Column Privileges

Or Column Privileges.

Create Table Script

Right click on any table to see possible actions you can apply to it. To create a table script, choose the Scipts menu ⇒ Create Table Script.

This will generate an SQL tab and place the SQL script there.

Toggle Auto Commit

To change the default behavior you can either turn on or off the auto commit functionality.

SQL Editor

There is a nice SQL Editor tool in the SQuirreL SQL Client which is capable of highlighting the keywords and do some more tricks (see further). One of the nice features is that it stores the history of queries you executed. You can also keep several tabs of fetched results open and close them when you want.

Unlike in other similar tools, SQuirreL SQL Client allows to edit the records right from the results table. To do that, right click on the results table and choose Make Editable.

Click on the record column to edit.

Auto Completion

SQuirreL SQL Client has powerful auto completion feature. Press Ctrl + Space to have it display you hints.

Another nice feature it has is that you can create your own user-defined abbreviations for auto completion.

To configure it, press the Configure auto correct/abbreviations button in the toolbar.

Tools Popup Menu

Another feature is the Tools Popup Menu. In the SQL Editor press the Ctrl+t shortcut. You can start typing to search incrementally.

SQL Template Bookmarks

To make your coding easier, you can save the template of SQL statements such as CREATE TABLE or SELECT into your bookmarks. For this, press the Add current SQL to bookmark or Edit Existing Bookmarks button.

You can add new or edit existing from the list. To enable Ctrl+j shortcut, check the Show SQuirreL bookmarks in ctrl+j popup.

In the SQL Editor when you press the Ctrl+j shortcut the list will be displayed.

Common SQL Errors

Often users encounter difficulties to execute SQL queries in CUBRID. The common mistake the make is use the double quotes to enclose strings. This is wrong in CUBRID. This will show you something like the error shown below.

Remeber to use single quotes for strings in your SQL queries.

Graphs

Wonderful feature of SQuirreL SQL Client is its ability to draw the Relationship Graph for the selected tables. Select the necessary tables and right click on them. Then choose the Add to graph menu item.

You will see something like this. You can drag each table and do additional operations. Right click on the panel to see the options.

Getting Help

If you have any questions related to SQuirreL SQL Client, write your messages to SQuirreL SQL Client Mailing List or to CUBRID Apps & Tools forum.

See also

• SQuirreL SQL Client Home Page
• SQuirreL, a Universal SQL Client
• CUBRID Tutorials