Connecting to Ingres 2006 on Linux via JDBC

Ok, I had a hell of a time trying to get this figured out. It seems like my Ingres 2006 install environment did not match the documentation on the ingres website, which looks to be docos for the older r3 product. This seriously sucked. C’mon guys – get the docos right.

Anway, here’s what I had to do.

I didn’t install this ingres instance, but I did realize that a process named ‘iijdbc’ was running. Apparently this is what allows jdbc to connect to an Ingres 2006 installation. We do not have Data Access Server running (iigdc).

When doing a process listing, this is what I get:

And here is the corresponding output from the ‘ingstatus’ command (corroborates the above, just less verbose):

So, given this starting point, here’s what I had to do:

As the ingres user, we had created a ‘cls’ database via the createdb command. Then we used accessdb to create the ‘cls’ user account inside ingres. Then we loaded the sql schema via accessdb.

So now, I had a cls user account and a schema with tables, but I couldn’t connect for the life of me over JDBC using Ingres 2006′s provided jdbc driver (found at $II_SYSTEM/ingres/lib/edbc.jar).

It turns out this is because Ingres uses Operating System user accounts for logging in over tcp. So, this meant I had to create a cls user as root:

and gave it a suitable password:

(this OS level password is then used for the JDBC connection later on).

Now that there is an OS user called ‘cls’ – exactly the same as my previously created Ingres database – I then had to let ingres know about this os user account and password:

(as root):

This command compiles and installs a program called ‘ingvalidpw’ that is not is not included in the standard Ingres2006 installation. This allows you to validate if a password is valid against an operating system user account known to Ingres.

Once that’s done, you’ll have to restart Ingres (as root):

To validate that Ingres password checking works, you should see that the Ingres env var II_SHADOW_PWD is set to the location of that compiled ingvalidpw executable:

In the list that is generated, you should see the II_SHADOW_PWD var set.

Now, you can verify that the username and password you would use for JDBC for your user will work:

If you see any value other than 0 returned, then the username/password entered doesn’t match the OS username/password. If you do see non-zero, then you can look in the $II_SYSTEM/ingres/files/iipwd/ingvalidpw.c file and see what the integer corresponds to find out what went wrong.

I then fired up DB Visualizer, pointed it to the edbc.jar file I specified earlier, and then used this url:

So, in my case it looked like this:

I clicked ‘test connection’ in DbVisualizer, and lo and behold! I was able to access all my tables!

I hope this is of use for someone – it took me close to 3 or 4 hours to get this working.

Here is the original Ingres forums thread that led me to my posting (you may need an Ingres account to access it, but its free):

http://opensource.ingres.com/projects/ingres/forum/30/121418044682?b_start:int=0#235464875124