Results 1 to 9 of 9
  1. #1
    Join Date
    Jun 2010
    Posts
    1

    Unanswered: Backward Compatibility in DB Dependent Applications?

    Hi,

    I need to implement Backward Compatibility in my application. The scenario is like this:
    I have an application that connects to SQL Server 2005 for getting informations. I have some classes in my applications that correspond to some tables in the database. Like, a class COrder containing the information from Order table. Now I am adding a new column into the Order table and also to the COrder Class. But I need to support old database instances with my new application. This means that while getting the information the application will try to run the SQL query with new column included but in the database the column will not be available. I want that in this case the query should return all the other columns information and put NULL in the column that is not found in the database.

    Is it possible to implement? Please help me on this.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Yes, there are multiple ways to implement this kind of behavior in an application. How you choose to do it depends on your programming language, infrastructure, etc.

    I can't think of any way to do this at the database level.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I can think of a way through dynamic SQL, but it sure as heck aint going to be pretty!
    George
    Home | Blog

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You can think of a way to have the database "do the version dance" without application support???

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    How about replacing SELECT against tables with SELECT against views? Sure you'd have to modify the views in both databases, and put a NULL in place of the missing column in the old database. But at least no ALTER on the table is needed.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Yes Pat. It would require a data dictionary of the latest version being created in the database and then some icky dynamic SQL.

    I much prefer the option of views though - good shout rdjabarov!
    George
    Home | Blog

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    So you can design a database today that knows what your application will require at '2015-09-22T13:20' and provide a view that will work when you distribute that database to a server that will be untouched until then??? Where can I get the utility that you use to do that? I'm pretty sure that I've got clients that will buy a copy at any price.

    I can't get a view to provide columns that won't be created for several years to provide additional functionality. If I turned a current version of my applications loose on databases that I created even four or five versions old, I'd bet that the app would die prompty (although they'd at least explain the problem in terms the user understood instead of crashing in flames).

    I've always approached the problem of an application being run against an older version database within the application. It never occured to me to try to deal with it in the database because I don't know what feature/column/etc I might add to a newer version of the database.

    Can your idea be expanded to include functions and stored procedures that haven't been written yet too?

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    No, Robert is not suggesting an all-knowing-all-powerful-eternal view. He's suggesting that if it is too complicated and difficult to roll out table modifications to the old version, it might be feasible to just roll out views instead, and I concur.

    Though the main issue here seems to be that the application is being upgraded before the database. The database should be upgraded first, in a manner which is compatible with all supported application versions.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I must not have been clear trying to make my point... I wasn't trying to address rdjabarov's post at all, I was addressing gvee's post and trying to also address the question as originally posted by ashish86.

    One of the problems that I've long been saddled with supporting is when people see multiple supported versions of an application and multiple platforms. They often install bits and pieces to suit themselves. This happens pretty often with GPL and with "federated organizations" such as either governmental or non-profit orgranizations that don't have a central distribution authority.

    I've found two ways to make the code and the databases co-exist. The "brute force" approach is to create a schema versioning table that has the current application version identifier and the date that it was applied, and have the application retrieve that version and the database engine it is running on and refuse to even start using a database outside the range of versions that this application can tolerate. The "elegant" approach is to have the code determine what it can use from a given schema and respond accordingly.

    The elegant approach allows a given version of code to support more schemas and will even allow the code to work against multiple database engines if the code is written carefully (defensively).

    Using views is a great approach for dealing with schemas and database engines that are very close (have very few differences), and will often let the developer create "bridge" schemas that allow significant functionality increases without requiring a database conversion/rebuild. Views can also be a good work-around to allow applications to work for platforms that don't support a data type, etc.

    I haven't had good luck using views to support the kind of growth in terms of functionality and platforms that I often need to support. This is especially true with webmasters/site managers that are often volunteers, office managers, or third party contractors. The ability to upgrade the front end (usually just installing a XOOPS package or extracting some PHP scripts) is pretty easy and happens often. The backend upgrade usually involves a conversion application, frequently requires taking the database offline and sometimes requires a database engine upgrade (which is really hard on shared servers) takes a lot more planning and doesn't happen often.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •