JDBC

Mimer SQL and DbVisualizer using JDBC on macOS

24 views September 24, 2017 March 21, 2018 realisatorerna 0

Introduction

From Mimer SQL version 9.2.4 is the SQL tool DbVisualizer™ bundled with the Mimer SQL distribution. DbVisualizer is a powerful, cross-platform database tool built on Java using JDBC to connect to databases.

This article will show how to connect to Mimer SQL using JDBC on macOS, with DbVisualizer as the example. DbVisualizer comes with a JDBC driver for Mimer SQL but in this example we will use the one from the Mimer SQL development site instead, to illustrate how do create a database connection from scratch.

Description

Install Mimer

First, install Mimer SQL for macOS from the Mimer SQL download page.

Mimer SQL for macOS contains two parts: one is the set of applications that can be run from the Finder and the other is the database server software. The server software is installed in the file system in directories normally not shown to the user unless access with the terminal application whereas the applications are installed in a separate folder, “MimerSQL-11.0.1A”, in the Applications directory where they can be launched in the same manner as all macOS software.

These are the user applications that comes with the Mimer distribution and can be launched from the Finder:

  • Batch SQL, for command line access to a database server
  • DbVisualizer, for a graphical access to a database sever
  • A folder with documentation
  • Mimer SQL Database Admin, with which to control the installed server software, i.e., start and stop
  • Mimer SQL Database Install, to create new databases on the installed server
  • PSM debugger, a tool to debug PSM programs
  • Uninstall Mimer SQL

Create a Mimer SQL database

To create a database, use Finder to start the Mimer SQL Database Install application found in the main directory of the Mimer SQL installation, e.g. /Applications/MimerSQL-11.0.1A.

The first thing to do when this application is started is to choose name for your database and a password for the Mimer SQL database ident (user) called SYSADM. SYSADM is the database user that is commonly referred to as the System Administrator for the database.

If you in addition to the database also want a sample database model with some data to experiment with in DbVisualizer, check the box for “Populate the database with an example environment” found under Database Options; all other options can be safely left with their default values. Click the Install button once done to start the installation.

The installation takes a moment to perform and signals completion with a confirmation dialogue.

Download the Mimer SQL JDBC driver

Open your browser and go to http://developer.mimer.com/downloads. Download the driver for “Java EE and Java SE” to your computer and unzip the file – the contents should be something like this:

Depending on how you downloaded the file, it should typically end up in the Downloads folder where it is also automatically unzipped. If it did the JDBC driver jar-file in the window above will then have the full path

/Users/<your username>/Downloads/mimjdbc-3_31/mimjdbd3.jar

If you downloaded the zip to another directory or it was not automatically expanded from the zip file you would first have to unzip it and then adjust the path above accordingly.

Connect to the local database using JDBC

In order to connect to a database with JDBC and DbVisualizer we have to perform several steps:

  1. Create a new driver in DbVisualizer with the downloaded jar-file as JDBC driver class
  2. Create a new connection in DbVisualizer to the local database previously installed and using the newly created driver
  3. Configure the driver parameters correctly
  4. Connect DbVisualizer using this connection

Since DbVisualizer already comes with a built-in JDBC driver for Mimer will we have to create a new driver that I will call “Mimer2”. This is done by first selecting “Driver Manager…” from the Tools menu and then adding the downloaded driver.

Begin by creating a new driver by clicking the plus icon in the upper left hand corner. This creates a new driver with no Driver Class and an empty name.

Name this new driver “Mimer2” and then click the folder icon on the right

This brings up the file selection dialog. Navigate to the downloaded folder and select the jar-file containing the driver. End by clicking “Choose”.

Now is the driver class loaded into the newly created driver in DbVisualizer, all that remains is to add a hint for the JDBC connection string format to DbVisualizer (which is used when creating the connection and finally verify that the driver has indeed the correct file loaded in the Driver Manager before you close it.

The JDBC connection string hint should be

jdbc:mimer:<protocol>://<server>:<port1360>/<database>

and inserted in the field named “URL Format”.

That was adding the driver; now it is time to create a new connection, using this driver. Begin by selecting “Connection Wizard…” from the Tools menu.

A connection is a combination of driver, physical server, database and a valid login. This combination is called a connection and is referred to in DbVisualizer with a name, a so called connection alias and that is the first thing to enter in the connection wizard: fill out the empty field with a name and click “Next”.

The next step is to select the driver for this connection. Use the selector at the top of the screen and select the newly created driver “Mimer2” and click “Next”

This is where the hint, or URL format, is used; DbVisualizer uses it to create a dialogue with field for all the items enclosed in brackets and in our case that was protocol, server, port and database. The resulting connection dialogue for our Connection Alias “localhost” looks therefore like this.

Fill out the missing information according to the illustration, that is server name should be “localhost”, database is “mimer” (since that is what we called it when we created it) – protocol and port should be already filled with “tcp” and “1360”, respectively.

The last thing to add is the authentication information for the user DbVisualizer should use to connect to the database. In this example we will use the SYSADM user created when we created the database in the beginning of the article and that user’s username and password should go into the respective fields under the Authentication heading.

At this stage one can try to ping the server using the “Ping server” button. Note that this only verifies that a server is listening, not that this connection alias will work, i.e., the user can log in. Pressing the button brings up a screen that verifies (or not) the connection.

Click “Finish” once done and we returns to the DbVisualizer main window. If the login details for the user were correct will DbVisualizer connect to the database and display a screen similar to this.

Her we see that the user is logged in; the connection details are all grayed out and there is a button named “disconnect”. Verify that the connection is indeed working by expanding some of the items in the tree view to the left, for example “Schemas”. If they expand properly the connection is working as it should.

Troubleshooting

Error codes

If DbVisualizer gets an error from the database a message is retrieved from the database server. But in cases when the database cannot be attached a quite sparse error message is given, like “Mimer error -18901 mimdb” if the database server isn’t started. To translate the Mimer SQL error code into a text message, use the tables found in the Programmer’s Manual part of the Mimer SQL Documentation Set. Look for the section called Internal Mimer SQL Return Codes in the Return Codes chapter (Appendix B).

For specific Mimer JDBC return codes, use the following command from a Terminal window:

java -cp /usr/lib/mimjdbc3.jar com.mimer.jdbc.Driver -errors

Connection cannot be established

Initially it can happen that a connection cannot been established from DbVisualizer. Usually the xinetd daemon needs to be restarted and forced to read the configuration file:

$ ps ax | grep inetd
 45 ?? Ss 0:00.02 xinetd -dontfork -stayalive -inetd_compat -pidfile /var/run/xinetd.pid
 4608 p1 S+ 0:00.01 grep xinetd
 $ kill -hup 45

By sending the SIGHUP signal to the PID of the xinetd process this is achieved.

Links

For further details on Mimer SQL and JDBC please see the article about JDBC as database API.

Also, see the Mimer JDBC Driver Guide found at the Documentation page.

More information on the DbVisualizer tool can be found here.

Tags:

Was this helpful?