Database Abstraction in ASP
Printed From: Web Wiz Forums
Category: General Discussion
Forum Name: Classic ASP Discussion
Forum Description: Discussion on Active Server Pages (Classic ASP).
URL: https://forums.webwiz.net/forum_posts.asp?TID=10768
Printed Date: 31 March 2026 at 5:13am
Software Version: Web Wiz Forums 12.08 - https://www.webwizforums.com
Topic: Database Abstraction in ASP
Posted By: ljamal
Subject: Database Abstraction in ASP
Date Posted: 07 June 2004 at 6:53am
I'm in the midst of beginning a new ASP project and the goal is to be able for the project to work despite the database back end AND for the SQL statements to be customized for the database. However, I have no desire to mix the SQL information with the ASP code.
In looking at projects such as SNITZ and WWF, I've noticed 2 methods.
1) Treat all databases the same and use a single set of SQL statements for them all (SNITZ method)
2) Separate the SQL statements and attempt to optimize for MSSQL by using stored procedures.
The problem I have with both methods is neither truly uses the power of MSSQL to its full ability. The greatest power of MSSQL is the ability return multiple recordsets and if you are treating it like ACCESS, you lose this ability and thus lose the main strength of MSSQL.
My thoughts for a solution is to separate all SQL statement into include files and based on the complexity of the page use a separate SQL include per page or a united one. For instance, something like the front page of this forum would have a SQL include for the common SQL tasks (such as the login check) and then a separate SQL include file for returning the main data for the page. The SQL includes would return recordsets populated into arrays.
The benefits of this solution is that all SQL quesries are separate from the display. This means if ACCESS is used then you drop in the ACCESS SQL files and it's optimized for ACCESS. If MSSQL is used you drop in the MSSQL files and ditto for MYSQL and another other supported database.
The disadvantage is that in order to cut down on the potential size of the SQL include file, there would be more total files in the project. For a project like WWF that could easily double the file count. However, I have not found an alternate solution.
For one of the e-commerce applications that I developed in the past, I placed all the SQL statements into one file and used page variables to select the correct SQL statement. The result was an 130 line function to be included into every page. That works beautifully for MSSQL because all the SQL statements are stored procedures with application having over 50 stored procedures. The SP for the add to cart function itself was over 47 lines of SQL code, so to replicate it in access would have taken at least 47 lines if not more. That would have resulted in a severely bloated include file if all the SQL statements were included in a single file.
I'm sure there's a point in here some where, but basically, what I'm pondering out loud. Has anyone solved this problem in classic ASP? How? What other methods have you considered?
-------------
L. Jamal Walton
http://www.ljamal.com/" rel="nofollow - L. Jamal Inc : Web/ Print Design and ASP Programming
|
Replies:
Posted By: michael
Date Posted: 07 June 2004 at 10:02am
Depending on how advanced you application is you can write a COM Component, acting as a Data Access Layer. You could have a component for whichever Database and it returns data always in the same way i.e. array. That way it does not matter what DB backend you use, all you do in the asp applications is call it's method... Of course, this is not usually doable for "freebie" applications whereas most ppl don't have access to register com.
-------------
http://baumannphoto.com" rel="nofollow - Blog | http://mpgtracker.com" rel="nofollow - MPG Tracker
|
Posted By: ljamal
Date Posted: 07 June 2004 at 10:05am
No COM objects, this is to be just ASP files only using standard components.
-------------
L. Jamal Walton
http://www.ljamal.com/" rel="nofollow - L. Jamal Inc : Web/ Print Design and ASP Programming
|
|