THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Tibor Karaszi

Do you remember Database Gateways?

I replied to a question in a forum today about intercepting and changing a SQL query, before it reaches SQL Server. I started typing a reply, and realized that... wow... "Does this take me on a trip down memory lane, or what!". So, just for fun, here is what I wrote (slightly modified):

There used to be products on the market, called "Database Gateways", something that looks like one type of DBMS but actually accepts the SQL queries and submits them to some other type of DBMS. Basically an "in between" piece of software. For instance I used to work with such a product, which looked like a SQL Server but submitted the queries to IBM's mainframe DB2.

That market pretty much disappeared around when ODBC was born. Yes, we are talking ancient here, around 1992.

There was a leftover from this time period in SQL Server, called "Open Data Services", which was basically an API allowing you to write something that looks like a SQL Server. There was even a sample app which allowed you to intercept the SQL queries and then re-submit them to a SQL Server.

This API was in turn transformed over time to what became known as "extended stored procedures", which in turn was deprecated with SQL Server 2005 and the SQL CLR support.

Published Wednesday, September 8, 2010 10:04 PM by TiborKaraszi
Filed under:

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



Slapo said:

I know almost nothing about database gateways, but it seems that the gist of them is still here implemented as some database access libraries, at least in PHP. They don't usually rewrite the SQL, but perform replacements in pseudo-SQL strings. Take a look at the fDatabase class of the Flourish library (which actually does have its own pseudo-SQL dialect) or Dibi for examples. They're probably also different in that they were made with database SQL portability in mind.

Dibi is kind of nice to use most of the time.

September 9, 2010 1:11 AM

Gianluca Hotz said:

Wow, now I really have to go back in time to remember :)

I believe the product that I used was called Show Case, it was a Gateway to the AS/400 (I actually found the manual when I cleaned up the garage a couple of weeks ago).

The app to intercept queries I think it was SQLEYE:

September 10, 2010 5:23 AM

Andy Clark said:

I've read quite a lot on the "Open Data Services" as I wrote some extended stored procs a few years back (ok a lot of years) that UUENCODED picture files so they could be passed into a database for replication to a shipboard system. Lots of fun binding the connection made by the xSP back into the database and getting permissions right.

Not sure it's simpler but it's a lot better documented in the new CLR way of doing things.

September 10, 2010 5:44 AM

TiborKaraszi said:

Slapo: To me, the libraries you describe (which I know nothing of), sounds more like the ODBC library. ODBC was basically what in the Windows world killed the market for database gateways. Interestingly enough, ODBC was an implementation of the SQL-CLI ANDI SQL standard (I have a feeling that many think that ODBC was something Microsoft propriatary...

Gianluca: You have to clean up your garage more frequently ;). Nope, I haven't heard of Show Case. This was for connecting to mainframe DB2, and I think the product was named or came from Micro DecisionWare (or something like that). AFAIK, SQLEYE was something you would use (probably 6.0 timeframe - which became SQLTrace in 6.5 and warped into PRofiler) and was made a product in 6.5. I recall reading source code, basically example code for ODS - however it is possible that this example code was just also shipped compiled = SQLEYE. Can't say I feel bac not remembering the exact bits of this... :)

Andy: Yes, SQLCLR is a great improvement to old xp_'s...

September 12, 2010 12:27 PM

Leave a Comment


This Blog


Privacy Statement