Supporting SQL Server and Oracle query syntax

[Davidb]

Hi,

We are currently developping a system that has to work with both Oracle and
SQL Server. So far we've built a small library to help us connect to SQL
Server and Oracle using the IDb* interfaces instead of using each provider
specific classes. It works well.

Unfortunately we still have to deal with the differences in the SQL syntax.
The JOIN syntax is probably the most obvious difference between SQL Server
and Oracle. We would like to avoid using If and Case to define two different
queries. I'd like to know how to deal with this problem in a more
maintanable way... Any idea?

Maybe there is a product that could allow me to define a query in an OO
fashion and then translate it to the current dbms syntax? Somethig like:

Dim select as SelectStatement = currentProvider.GetSelectStatement()
Dim t as table = new Table("Publishers", "P")
select.Fields.Add(t, "*")
select.From = t
select.From.InnerJoin(new Table("Titles"), "ID", "pub_ID")

Dim command as IDbCommand = select.GetCommand()
....

Thanks,

David

 

[=?ISO-8859-1?Q?Fredrik_=C5lund?=]

Davidb,

We have developed a framework called Mimer Provider Manager (Mpm).
Instead of working directly towards a specific Data Provider, Mpm lets
you work towards the Mpm framework and you can change Data Provider
and/or database without changing any code. To accomplish this, Mpm uses
plug-ins that works with the underlying Data Provider. Mpm currently
comes with plug-ins for ODBC, Sql Server, Oracle, Mimer SQL and OleDB.
There is also a generic plug-in that works with any provider using
reflection. It's easy to build new plugins if you want to and there is
even a new project wizard that is added to Visual Studio when you
install Mpm (as well as full integration with drag and drop of the
different Mpm component and active help).
There is also a Mimer Provider Administrator that lets you graphically
define and modify data sources, almost like the ODBC administrator. This
data source name is all that you refer to in your code and this means
that all you have to do to change from Oracle to SQL Server is to change
the datasource definition.
Mpm uses the underlaying provider so you don't have any performance
penalties like you do with ODBC or OleDB.

A realy cool thing and one that can help you is a feature we call SQL
Filter that intercepts the SQL sent to the database and change it as you
want. This way you can convert between Oracle and SQL Serverdsspecifik
SQL. As of now we ship one SQL Filter that handels the different ways
that the provider handles parameterized queries but you can easily build
your own filter and hook in.

Oooh, and it's free to use and open source.

You can find Mpm at http://developer.mimer.com/mpm or
http://www.sourceforge.net/projects/mimerpm.

Regards,
Fredrik

 

[Rob Panosh]

David,

Are you using Oracle 9i or 8i ... 9i now has ansi joins so the syntax is
getting closer. We also have a tool that bridges the gap. Here is some
sample code.

'Set the factory in the global shared. This is done in the startup
application.
Global.Factory =
Global.DBFactory.Create(SQL.Factory.Provider.SystemDataSQLClient)
Global.Factory.Open("Network Library=DBMSSOCN;Application Name=MyTest;Data
Source=" & _
"testserver;Initial
Catalog=TestDB;User Id=sa;password=***;")

Dim sqlSelect As New
AdvancedSoftwareDesigns.SQL.Commands.Select(Global.Factory)
Dim dataTable As New System.Data.DataTable

'Build and execute a select statement.
sqlSelect.Column.Add("fmformula.*")
sqlSelect.From.Add("fmformula")
sqlSelect.Where.Add.ExprLeft("fmcode")
sqlSelect.Where.Last.Parameters.Add("1000")
sqlSelect.Execute(dataTable)

'Sample insert statment.
Dim sqlInsert As New
AdvancedSoftwareDesigns.SQL.Commands.Insert(Global.Factory, "customer")
With sqlInsert.Parameters
.Add("firstname", "Sponge Bob")
.Add("lastname", "Square Pants")
.Add("address", "Pineapple under the sea")
End With
'Execute the insert statement.
Dim rows As System.Int32 = sqlInsert.ExecuteNonQuery()


This library also supports Update, Delete, Stored Procedures and has many
more features. If you are interest in using this library drop me an email
at (e-mail address removed) , note remove NOSAMPX from the address
before sending.

Regards,
Rob