Hi Everybody, i created this thread is to discuss the way of standard coding for data access for Oracle, DB2 and Ms SQL without changing the source code.

The language we used are Delphi 5, so i intend to discuss the way for standardized the coding for data access which connect to Oracle, DB2 and Ms SQL. Because of the theses databases have different features whcih is not supported for each other. So there is the problem for us to code the data access coding in the single way, so i want to discuss the way of coding.

Thankx

To start with, you are probably going to have to reconcile yourself to the fact that you are not going to have an easy time of it. Then I would suggest you become extremely well acquainted with the DataModule concept.

If you are saying that your app is going to be connected to DB2, Oracle, and SQL Server AT THE SAME TIME, you are going to have to have at least three Connections.

Have you thought about using DataProviders that are running as a service on the machine that runs the application. (That lets you avoid licensing issues because the app and the dataprovider are on the same box.) essentially, that removes the database access from the app and moves it into separate provider-apps that could be accessing any kind of database.

By the way, these are just some thoughts I had when I was trying to think of how _I_ would approach the problem . . . this could be an interesting thread!

Hi RDWilson,

The apps is not going to connected these three databases at the same time, my intention is to write the apps which can connect to these three databases without modify the source code! :>

You might still want to consider having the three data providers, one for each database, and let the User trigger their usage depending on which database is to ae accessed. That would let you move all of your database-specific access and manipulation into the data provider and then you could code the app based upon a "common set of (returned) data".

I am about to start working on an app of my own that I have been considering using this technique in. Since I am not totally sure which DB I will eventually need to access, I can prototype the app using any handy DB (e.g. Access) and then create the DB specific data provider apps as needed (and figured out ;-).

It's mean that, we cannot write any standard procedures and triggers which can run with different databases or we need to write three set stored procedures and triggers.

Since "stored procedures" are stored in the database, you would have to create three separate stored procedures if you were using three separate databases anyway. The same is true for tirggers.

Essentially, anything other than SQL statements that you submit to the database engine (e.g. dynamically created SQL) is stored in, part of, and confined to the database in which it is created. You wouldn't expect a SQL Server _table_ to exit in an Oracle database unless you _also_created_it_ in the Oracle database, would you? The same has to be true of stored procs and triggers that you _crweate_ in the SQL Server database.

Now, having said that, to the extent that you isolate the "standard procedures" from the actual database and to the extent that you limit your SQL to those aspects that are compatible _across_ the various databases, you _can_ write standard routines. However, because of the "minor" variations in the various databases and the fact that each of them has, in effect, its own dialect of ANSII SQL, the best approach I have found is to isolate the database interfacing activities to a datamodule. To a great extent, you can replicate the datamodule and all of it's code as a "starting point" but you will probably discover either a better way to do some things in some databases or instances of "you can't do that here" in some databases.

Thus, creating a dataprovider app for the various databases lets you provide a means, either at install time or, with a bit of judicious coding, at run time, of letting the primary appliation execute against which ever database it needs to fo a given installation.

This mean that i cannot write standard code for targeting all the databases and i need to create the three set of coding for stored procedure and trigger.

Another way, means that i cannot write any dynamic SQL, if need, i need to have more than one set of dynamic SQL in my apps?

There are two ways of acheiving database engine independance. Each has its own problems and rewards.

The first way is to limit your application to 100% portable SQL syntax. All code exists in the application, and direct user access to the database must be jealously prevented. This is a good strategy for "canned" applications that need very limited (typicall no) customization, and do not need to interface to any other product (because they can't tolerate that interface in most cases).

The second way is to "compartmentalize" database specific code, and logically "firewall" it from the rest of the application. This allows full use of all of the database features, it allows easy interfaces to other products, and it allows reasonable user access to the data. The down side is that every new database engine that is supported needs to have that "firewalled" code re-written to use this particular engine.

There are a lot of ways to trade off between these two extremes, but they all carry varying degrees of risk and corresponding degrees of function/performance... To put it simply, the extremes are both pretty safe, everything else is complicated.

One of the biggest advances that have been made in newer generations of development products has been the ability to insulate developers from the vagaries of the database engine. PowerBuilder has made some enormous strides in this area. The Visual Studio products have created ADO and the related libraries to provide a similar kind of insulation, while also allowing micro-metric control when needed. I'm not sure where Delphi stands, but I think that it is closer to middle-ground, but lightyears ahead of where it was just a few years ago.

Before you get too excited about writing your own infrastructure support, take a look at what the newer tools provide. In almost every case I've ever seen, they offer a better return than rolling your own!

-PatP

Patp,

"One of the biggest advances that have been made in newer generations of development products has been the ability to insulate developers from the vagaries of the database engine. PowerBuilder has made some enormous strides in this area. The Visual Studio products have created ADO and the related libraries to provide a similar kind of insulation, while also allowing micro-metric control when needed. I'm not sure where Delphi stands, but I think that it is closer to middle-ground, but lightyears ahead of where it was just a few years ago."

Delphi has been able to use ADO for some time now and also offers some other optoins regarding insulation of developers from, as you so politely put it, "the vagaries of the database engines." I have dealt with a variety of database engines and have found that, unless one is doing fairly generic activity, even ADO often requires database engine specific SQL to be coded with the result that, unless additional "insulation" is built into the app, the app becomes somewhat database specific. That is where oneof Delphi's "additional options" really comes in handy . . . the technique of isolating the database interface into a data-provider app.

I have not used Power Builder since the early days but at that time I was somewhat disappointed with it when compared to even the early versions of Delphi. (But, then, again, I am an old Pascal coder from a way back. ;-)

I think that we're saying the same thing, in slightly different ways.

Delphi does provide some great tools for insulating the developer from the database engine. I've always liked Delphi, but I've never had an opportunity to use it professionally.

I'm a UCSD Pascal geek from Waaaay back. I wrote the UCSD Kermit implementation if that gives you any perspective.

PowerBuilder provides some great tools, particularly in the DataWindow object that are tough to match in any other development environment. I don't know of anything that Kylix offers that compares to them.

In any event, Delphi 5 is what chneoh is using. There are lots of newer tools, and the newer tools fix many of the problems (database and other) that existed in older tools.

-PatP

Quite true.

I am not familiar with the "Data Window". I realize it is a bit "off thread" but can you give a brief description? I am wondering if it might not be sort of like the Delphi Data Module. (By the way, I am about 98% certain that Kylix has the Data Module approach avaiable. ;-)

I have to agree that, were I in the position that chneoh is in, I'd probably be giving serious consideration to moving to a newer tool, either an upgrade to at least D7 (which would open up .NET capabilities and Kylix) or a newer variant of another tool. Unfortunately, tools are being obsoleted faster than OS's in the Windows world!

A PowerBuilder DataWindow is a wonderous thing, unlike anything else I've ever seen in a compiled language. I don't know of anything I can safely compare a DataWindow to, other than Microsoft Office.

A DataWindow is a more-or-less database independant, mostly GUI based component. A DataWindow can be columnar (like a grid), freeform (like a Word document), banded (like a Crystal Report), graphic (like an Excel chart), and a couple of other formats that I can't draw a ready analogy for. The developer can switch a given DataWindow between these formats at will.

DataWindows have the ability to run in an N-tier environment using a Jaguar server. This means that the DataWindow can exist (to some extent at least) without even having a program to support it!

I could ramble on for hours trying to explain just what they are, but I'm sure that I'd fall short. The only real way to "grok" a DataWindow is to either use it yourself, or watch someone that understands them. The process is both amazing and fascinating!

-PatP

Gee, was it something I said ???

-PatP