Creating architecture to access different databases

[Dennis]

We are creating an N-tier web application that we want to have the ability
to use with different databases. The idea is that we would just have
different DLLs for the data tier for different databases - like DataSQL.dll
for SQL Server and DataOracle.dll for Oracle, etc. - and then we could just
install the appropriate DLL for the client. Has anyone done this or know
the best way to do it?

The problem that we see is that we would need different DLLs for the
business tier that reference the difference data DLLs. We would prefer to
just have one set of DLLs for the business tier. The idea we thought of is
to have the different data DLLs have the same name and have binary
compatibility so they have the same GUID. This seems like it should work,
but it just seems "wrong." Does anyone have any ideas how to do this?

Thanks,
Dennis

[Kevin Downs]

I have recently had to deal with almost exactly the same problem.
The way we finally decided on was as follows;

A set of common interfaces were designed.
Each data tier component implements these interfaces.
The data tier components are instantiated using a progid stored in the client
configuration.

I assume from your mentioning "binary compatibility" that you are using VB; am
I correct?

If so the instantiation would be along the following lines

Dim TheInterface as IDataInterface 'this is one of the defined data tier
interfaces
Set TheInterface = CreateObject(strDataTierName) 'where strDataTierName is of
the form DataSQL.Class

--
Regards,
Kevin

"Dennis" <adfd@sadfas.com> wrote in message news:3a4a661b$1@news.devx.com...
> We are creating an N-tier web application that we want to have the ability
> to use with different databases. The idea is that we would just have
> different DLLs for the data tier for different databases - like DataSQL.dll
> for SQL Server and DataOracle.dll for Oracle, etc. - and then we could just
> install the appropriate DLL for the client. Has anyone done this or know
> the best way to do it?
>
> The problem that we see is that we would need different DLLs for the
> business tier that reference the difference data DLLs. We would prefer to
> just have one set of DLLs for the business tier. The idea we thought of is
> to have the different data DLLs have the same name and have binary
> compatibility so they have the same GUID. This seems like it should work,
> but it just seems "wrong." Does anyone have any ideas how to do this?
>
> Thanks,
> Dennis
>
>

[Matthew Cromer]

"Dennis" <adfd@sadfas.com> wrote:
>We are creating an N-tier web application that we want to have the ability
>to use with different databases. The idea is that we would just have
>different DLLs for the data tier for different databases - like DataSQL.dll
>for SQL Server and DataOracle.dll for Oracle, etc. - and then we could just
>install the appropriate DLL for the client. Has anyone done this or know
>the best way to do it?

People still making big investments in COM after MS put a bullet in it's
head?

Anyway, your best bet is probably to create a common interface (either with
IDL or in VB if you rather). The clients can reference the interface DLL
and create objects using CreateObject (not via New) and assign them to objects
of the interface type.

This will give you the speed and intellisense advantage of early binding,
yet allow you to swap our implementations as desired.

I would NOT try the GUID matching trick, you will be cruising for a bruising
if you try that.

Matthew Cromer

[Dennis]

Kevin,

Thanks for the reply. But doesn't this still have the problem where you
need two sets of business tier components to specify which data tier objects
you are using?

Dennis


"Kevin Downs" <kdowns@nospam.optushome.com.au> wrote in message
news:3a4a9eaa$1@news.devx.com...
> I have recently had to deal with almost exactly the same problem.
> The way we finally decided on was as follows;
>
> A set of common interfaces were designed.
> Each data tier component implements these interfaces.
> The data tier components are instantiated using a progid stored in the
client
> configuration.
>
> I assume from your mentioning "binary compatibility" that you are using
VB; am
> I correct?
>
> If so the instantiation would be along the following lines
>
> Dim TheInterface as IDataInterface 'this is one of the defined data tier
> interfaces
> Set TheInterface = CreateObject(strDataTierName) 'where strDataTierName is
of
> the form DataSQL.Class
>
> --
> Regards,
> Kevin
>
> "Dennis" <adfd@sadfas.com> wrote in message
news:3a4a661b$1@news.devx.com...
> > We are creating an N-tier web application that we want to have the
ability
> > to use with different databases. The idea is that we would just have
> > different DLLs for the data tier for different databases - like
DataSQL.dll
> > for SQL Server and DataOracle.dll for Oracle, etc. - and then we could
just
> > install the appropriate DLL for the client. Has anyone done this or
know
> > the best way to do it?
> >
> > The problem that we see is that we would need different DLLs for the
> > business tier that reference the difference data DLLs. We would prefer
to
> > just have one set of DLLs for the business tier. The idea we thought of
is
> > to have the different data DLLs have the same name and have binary
> > compatibility so they have the same GUID. This seems like it should
work,
> > but it just seems "wrong." Does anyone have any ideas how to do this?
> >
> > Thanks,
> > Dennis
> >
> >
>
>

[Dennis]

"Matthew Cromer" <matthew@sdaconsulting.com> wrote in message
news:3a4c26d1$1@news.devx.com...
>
>
> People still making big investments in COM after MS put a bullet in it's
> head?

..Net is at least a year away, and then it will be at least several months
before companies start moving over. In the meantime we have a product we
need to get to market, so we will have at least a year to sell it based on
COM.

> Anyway, your best bet is probably to create a common interface (either
with
> IDL or in VB if you rather). The clients can reference the interface DLL
> and create objects using CreateObject (not via New) and assign them to
objects
> of the interface type.
>
> This will give you the speed and intellisense advantage of early binding,
> yet allow you to swap our implementations as desired.

Again, as I said in response to Kevin, don't you still need two sets of
business components so you know which set of data objects to reference? Or
am I missing something?

Thanks,
Dennis

[Matthew Cromer]

>Again, as I said in response to Kevin, don't you still need two sets of
>business components so you know which set of data objects to reference?
Or
>am I missing something?
>
>Thanks,
>Dennis

Sorry, I thought that was what you _wanted_ to do.

If you don't want two sets of business components, just isolate the DB differences
(perhaps just SQL changes) and do the same thing with some SQL components
that you instantiate from the business components.

IE:

- create an interface defining your SQL sources (in VB or IDL)
- create two DLLs, one for Oracle and the other for SQL server
- in your business objects, instantiate one or the other SQL source and assign
to a variable of your SQL interface type. You reference the interface type,
but instantiate via CreateObject depending on which DB you are using
- get your SQL from one or the other DLL.

HTH,

Matthew Cromer

[Matthew Cromer]

"Dennis" <db@yahoox.com> wrote:
>"Matthew Cromer" <matthew@sdaconsulting.com> wrote in message
>news:3a4c26d1$1@news.devx.com...
>>
>>
>> People still making big investments in COM after MS put a bullet in it's
>> head?
>
>..Net is at least a year away, and then it will be at least several months
>before companies start moving over. In the meantime we have a product we
>need to get to market, so we will have at least a year to sell it based
on
>COM.

So the product is almost done then?

Ok, we are in the same boat with a huge existing framework written in VB/COM.

But the new stuff is Java, baby!

Matthew Cromer

[Richard Dalton .]

"Dennis" <adfd@sadfas.com> wrote:
>The problem that we see is that we would need different DLLs >for the business
tier that reference the difference data DLLs.

Dennis,

I was in an identical position a year ago although
I was fortunate in that I knew I'd be dealing with either
Informix, Access or SQL Server.

I started off with a plan similar to that described by
some of the others here. I'll explain my original idea
here.

1. Define interfaces for the set of classes that you
need in your DB Layer.
2. Implemenent these interfaces in an ActiveX DLL called
IDBLayer.DLL
This dll should have one of each type of class with all
it's properties and methods, but no code. E.g. IClassA,
IClassB etc.
3. Create a DLL for each type of DB that requires it's own
layer. IT should contain all of the Classes in IDBLayer.DLL
and each should implement their associated interface.

Now the bit I think you were asking about:

The business layer needs to know which DB layer to use.
If you use the following code:

Set obj_DB = CreateObject("DBSQL.ClassA")

Then you are obviously hardcoding the link between business
layer and DB layer. But if you stored the name of the DBLayer
in an ini file or the registry you could do something like the
following:

Set obj_DB = CreateObject(gs_DBLayer & ".ClassA")

where gs_DBLayer is a global string containing the name of your
db layer. This can be set at startup.


One thing I should point out though. Once I had decided on this
I also decided that my DB layer would connect through ADO I
found that in practice my various DB Layers were in fact more
similar than I thought they would be. So I created a
Generic db layer called DBGeneric.dll

I figured that if I needed a different dll for a specific DB
I could use my plan. But since then I've managed to get
all the DB's to work through the generic DB layer, so my
plan is out the window.

Hope this helps.

-Richard






We would prefer to
>just have one set of DLLs for the business tier. The idea we thought of
is
>to have the different data DLLs have the same name and have binary
>compatibility so they have the same GUID. This seems like it should work,
>but it just seems "wrong." Does anyone have any ideas how to do this?
>
>Thanks,
>Dennis
>
>

[Dennis]

Richard,

Thanks for your response. This seems like it would do what we need. Have
you done any testing to see if there's any kind of performance hit reading
the name from the registry or an ini file? We'll take a look at it.

Thanks again,
Dennis

<Richard Dalton .> wrote in message news:3a4cf790$1@news.devx.com...
>
>
> Now the bit I think you were asking about:
>
> The business layer needs to know which DB layer to use.
> If you use the following code:
>
> Set obj_DB = CreateObject("DBSQL.ClassA")
>
> Then you are obviously hardcoding the link between business
> layer and DB layer. But if you stored the name of the DBLayer
> in an ini file or the registry you could do something like the
> following:
>
> Set obj_DB = CreateObject(gs_DBLayer & ".ClassA")
>
> where gs_DBLayer is a global string containing the name of your
> db layer. This can be set at startup.
>
>
> One thing I should point out though. Once I had decided on this
> I also decided that my DB layer would connect through ADO I
> found that in practice my various DB Layers were in fact more
> similar than I thought they would be. So I created a
> Generic db layer called DBGeneric.dll
>
> I figured that if I needed a different dll for a specific DB
> I could use my plan. But since then I've managed to get
> all the DB's to work through the generic DB layer, so my
> plan is out the window.
>
> Hope this helps.
>
> -Richard

[Dennis]

We are already up to version 1.5, but it is a two-tier App with ASP hitting
SQL Server directly. We want to COM-*** it for V 2.0 to improve the
scalability and performance, and to protect our intellectual property.

Dennis


"Matthew Cromer" <matthew@sdaconsulting.com> wrote in message
news:3a4cbdea$1@news.devx.com...
>
>
> So the product is almost done then?
>
> Ok, we are in the same boat with a huge existing framework written in
VB/COM.
>
> But the new stuff is Java, baby!
>
> Matthew Cromer

[Richard Dalton .]

"Dennis" <db@yahoox.com> wrote:
>Thanks for your response. This seems like it would do what we >need. Have
you done any testing to see if there's any kind of >performance hit reading
the name from the registry or an ini >file? We'll take a look at it.

Not really. In practice I have been able to get a single
Generic DB layer to work for all databases, so this idea never
made it off the drawing board.

I have just realised that in your case you would have to hit the INI file
or registry each time a business object is instantiated, or at least pass
in the string from the client. Both of these are a bit messy, although passing
in the string
would obviously give much better performance.

One thing I forgot to mention in my original post which may help.
If you are using COM+ you can set a constructor string for each object.
This object can be picked up by the object when it is
instantiated.
I use this for the database layer to store the ADO connection
string. You could use it in the business layer to store the
database layer string. To point your business object at a
different database layer you would just need to use the Com+ admin tool to
change the string.

One thing to watch out for though, you would probably have to
set this string for each object. But from a performance and
flexibility point of view it would be worth it.

Of course it does allow the interesting situation where some
of your objects use one DB layer, while other objects use a differnt layer.
I'll let you puzzle out all the possibilities
of that.

-Richard

[Dennis]

Richard, I thought of using a constructor as well. Actually all the objects
would use the same DB layer. This is a commercial app, so there will be one
database and DB layer for each installation. We just want to have the
flexibility to support different databases (SQL Server and Oracle being the
main 2 obviously) so if a client only uses Oracle they won't have to use SQL
Server just for our product.

Dennis

<Richard Dalton .> wrote in message news:3a4e1db5$1@news.devx.com...
>
> One thing I forgot to mention in my original post which may help.
> If you are using COM+ you can set a constructor string for each object.
> This object can be picked up by the object when it is
> instantiated.
> I use this for the database layer to store the ADO connection
> string. You could use it in the business layer to store the
> database layer string. To point your business object at a
> different database layer you would just need to use the Com+ admin tool to
> change the string.
>
> One thing to watch out for though, you would probably have to
> set this string for each object. But from a performance and
> flexibility point of view it would be worth it.
>
> Of course it does allow the interesting situation where some
> of your objects use one DB layer, while other objects use a differnt
layer.
> I'll let you puzzle out all the possibilities
> of that.
>
> -Richard
>
>
>

(darin)
I've done similiar projects to this in the past. It always sounds easiler
than it is. You've invariably got lots of messy details to deal with that
different DB's handle every so slightly differently.

Like:
Row level locking (in the rare cases where you actually, really do +need+
it)
Transactions (esp of the nested variety)
Stored procs (put a bunch of code in stored procs and you end up rewriting
it for every DB, not pretty, so put it in codeat the business logic layer,
not nearly as flexible, sigh....)
Fieldnames (and reserved words)
Primary ID's (ie retrieving the primary autogenerated ID for a just inserted
record).
ScaleOut issues (replication of static data among several severals)
Replication of dynamic data among servers (much harder to deal with)

It often goes against the grain, but I've generally maintained that using no
stored procs, and basically treating the SQL DB like a dumb record manager
is about the only sensible way to go when building a system that's going to
connect to various backend DB's.

Just make sure you're wearing flame retardent underwear if you suggest stuff
like this. Most IS types will erupt<g>



"Dennis" <db@yahoox.com> wrote in message news:3a4e82ba@news.devx.com...
> Richard, I thought of using a constructor as well. Actually all the
objects
> would use the same DB layer. This is a commercial app, so there will be
one
> database and DB layer for each installation. We just want to have the
> flexibility to support different databases (SQL Server and Oracle being
the
> main 2 obviously) so if a client only uses Oracle they won't have to use
SQL
> Server just for our product.
>
> Dennis
>
> <Richard Dalton .> wrote in message news:3a4e1db5$1@news.devx.com...
> >
> > One thing I forgot to mention in my original post which may help.
> > If you are using COM+ you can set a constructor string for each object.
> > This object can be picked up by the object when it is
> > instantiated.
> > I use this for the database layer to store the ADO connection
> > string. You could use it in the business layer to store the
> > database layer string. To point your business object at a
> > different database layer you would just need to use the Com+ admin tool
to
> > change the string.
> >
> > One thing to watch out for though, you would probably have to
> > set this string for each object. But from a performance and
> > flexibility point of view it would be worth it.
> >
> > Of course it does allow the interesting situation where some
> > of your objects use one DB layer, while other objects use a differnt
> layer.
> > I'll let you puzzle out all the possibilities
> > of that.
> >
> > -Richard
> >
> >
> >
>
>

[Kevin Downs]

Dennis,

I'm sorry my reply was a bit terse - I was in a hurry to go away for new
year's <g>.
Richard's answer explains the technique far better than my posting.

--
Regards,
Kevin

<Richard Dalton .> wrote in message news:3a4cf790$1@news.devx.com...
>
> "Dennis" <adfd@sadfas.com> wrote:
> >The problem that we see is that we would need different DLLs >for the
business
> tier that reference the difference data DLLs.
>
> Dennis,
>
> I was in an identical position a year ago although
> I was fortunate in that I knew I'd be dealing with either
> Informix, Access or SQL Server.
>
> I started off with a plan similar to that described by
> some of the others here. I'll explain my original idea
> here.
>
> 1. Define interfaces for the set of classes that you
> need in your DB Layer.
> 2. Implemenent these interfaces in an ActiveX DLL called
> IDBLayer.DLL
> This dll should have one of each type of class with all
> it's properties and methods, but no code. E.g. IClassA,
> IClassB etc.
> 3. Create a DLL for each type of DB that requires it's own
> layer. IT should contain all of the Classes in IDBLayer.DLL
> and each should implement their associated interface.
>
> Now the bit I think you were asking about:
>
> The business layer needs to know which DB layer to use.
> If you use the following code:
>
> Set obj_DB = CreateObject("DBSQL.ClassA")
>
> Then you are obviously hardcoding the link between business
> layer and DB layer. But if you stored the name of the DBLayer
> in an ini file or the registry you could do something like the
> following:
>
> Set obj_DB = CreateObject(gs_DBLayer & ".ClassA")
>
> where gs_DBLayer is a global string containing the name of your
> db layer. This can be set at startup.
>
>
> One thing I should point out though. Once I had decided on this
> I also decided that my DB layer would connect through ADO I
> found that in practice my various DB Layers were in fact more
> similar than I thought they would be. So I created a
> Generic db layer called DBGeneric.dll
>
> I figured that if I needed a different dll for a specific DB
> I could use my plan. But since then I've managed to get
> all the DB's to work through the generic DB layer, so my
> plan is out the window.
>
> Hope this helps.
>
> -Richard
>
>
>
>
>
>
> We would prefer to
> >just have one set of DLLs for the business tier. The idea we thought of
> is
> >to have the different data DLLs have the same name and have binary
> >compatibility so they have the same GUID. This seems like it should work,
> >but it just seems "wrong." Does anyone have any ideas how to do this?
> >
> >Thanks,
> >Dennis
> >
> >
>

[Kevin Downs]

Comments in-line

<Richard Dalton .> wrote in message news:3a4e1db5$1@news.devx.com...
>
> "Dennis" <db@yahoox.com> wrote:
> >Thanks for your response. This seems like it would do what we >need. Have
> you done any testing to see if there's any kind of >performance hit reading
> the name from the registry or an ini >file? We'll take a look at it.
>
> Not really. In practice I have been able to get a single
> Generic DB layer to work for all databases, so this idea never
> made it off the drawing board.
>

Nope, neither have I. Especiall if you need to target SLQ Server and Oracle.

> I have just realised that in your case you would have to hit the INI file
> or registry each time a business object is instantiated, or at least pass
> in the string from the client. Both of these are a bit messy, although
passing
> in the string
> would obviously give much better performance.

I prefer the method below if you are running w2k.

> One thing I forgot to mention in my original post which may help.
> If you are using COM+ you can set a constructor string for each object.
> This object can be picked up by the object when it is
> instantiated.
> I use this for the database layer to store the ADO connection
> string. You could use it in the business layer to store the
> database layer string. To point your business object at a
> different database layer you would just need to use the Com+ admin tool to
> change the string.
>
> One thing to watch out for though, you would probably have to
> set this string for each object. But from a performance and
> flexibility point of view it would be worth it.

Yes, you do have to set the connection string for all business objects that
access the data layer directly. However it is much easier (read less tedious
and errr-prone) to set the connection strings using script. In fact this is
the best way to create com+ applications full-stop.

see the following excelent article in msdn mag.
http://msdn.microsoft.com/library/pe...tincts0900.htm

> Of course it does allow the interesting situation where some
> of your objects use one DB layer, while other objects use a differnt layer.
> I'll let you puzzle out all the possibilities
> of that.
>
> -Richard
>
>
>