DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Page 1 of 3 123 LastLast
Results 1 to 15 of 45

Thread: Creating architecture to access different databases

  1. #1
    Dennis Guest

    Creating architecture to access different databases

    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



  2. #2
    Kevin Downs Guest

    Re: Creating architecture to access different databases

    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
    >
    >




  3. #3
    Matthew Cromer Guest

    Re: Creating architecture to access different databases


    "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

  4. #4
    Dennis Guest

    Re: Creating architecture to access different databases

    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
    > >
    > >

    >
    >




  5. #5
    Dennis Guest

    Re: Creating architecture to access different databases

    "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




  6. #6
    Matthew Cromer Guest

    Re: Creating architecture to access different databases


    >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



  7. #7
    Matthew Cromer Guest

    Re: Creating architecture to access different databases


    "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

  8. #8
    Richard Dalton . Guest

    Re: Creating architecture to access different databases


    "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
    >
    >



  9. #9
    Dennis Guest

    Re: Creating architecture to access different databases

    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





  10. #10
    Dennis Guest

    Re: Creating architecture to access different databases

    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




  11. #11
    Richard Dalton . Guest

    Re: Creating architecture to access different databases


    "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




  12. #12
    Dennis Guest

    Re: Creating architecture to access different databases

    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
    >
    >
    >




  13. #13
    Guest

    Re: Creating architecture to access different databases

    (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
    > >
    > >
    > >

    >
    >




  14. #14
    Kevin Downs Guest

    Re: Creating architecture to access different databases

    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
    > >
    > >

    >




  15. #15
    Kevin Downs Guest

    Re: Creating architecture to access different databases

    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
    >
    >
    >




Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center