Tuesday, December 07, 2004

Ingres Has Its Limits

Unfortunately we run an Ingres database for our internal systems.
Fortunately our web stuff uses SQLServer 2000 for its backend so we have a workaround for one of Ingres' major limitations(You know there is a problem when you are praising the quality of a Microsoft product). Ingres does not allow cross catalog joins, you can set up a distributed database but that requires an insane amount of work and repeated definitions.

The solution:

  • Set up Ingres ODBC on the Windows 2K server.

  • Make sure it auto starts etc.

  • Add an ODBC entry for each Ingres database you wish to access.

  • Go into SQLServer Enterprise manager.

  • Add a remote server for each ODBC entry with appropriate security access.

The sql

select *
from RemoteServerName1..schema.tablename1 as a
inner join RemoteServerName2..schema.tablename2 as b
on a.pkfield = b.pkfield

now works.
We now have the ability to do cross catalog joins on an Ingres database server.


Blogger medge said...

Of course this also allows you to use server side cursors for those really complex batch updates.
This is so cool.

11:54 am  
Blogger medge said...

Although through ODBC the locks are still crude, and can't be overwritten.
IE \noreadlock doesn't actually work and declaring a cursor locks an entire table!

12:00 am  

Post a Comment

<< Home