CUBRID Through SQuirreL SQL Client
Table of Contents
- Back to Top
- Start SQuirreL SQL Client
- Add JDBC Driver
- Create New Alias (or Connection)
- Using SQuirreL SQL Client
- Host Metadata
- Data Types
- Table Columns
- Primary Key
- Row Count
- Column Privileges
- Create Table Script
- Toggle Auto Commit
- SQL Editor
- Getting Help
- See also
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.
There are two options how you can use SQuirreL SQL Client.
- 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.
- 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.
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.
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.
|SQuirreL SQL Client||Size|
|SQuirreL SQL Client stable version||~33.7 MB|
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.
On Windows, execute the file squirrel-sql.bat to run the application. On Linux, run the squirrel-sql.sh script.
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.
- Web URL: enter the following URL which is the home page for the CUBRID JDBC Driver.
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:
- 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).
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
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.
You can see what Data Types are supported and their default values, properies, and behavior.
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.
Similarly see Primary Keys defined in the table.
Or Row Count.
Also you can see top 100 records stored in that table. Select the Contents tab for it.
To see the table privileges, click on the Privileges tab.
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.
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.
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.
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.