Means: "Don't Know, Don't Care what RDBMS is behind my application."
How does one write code so that any RDBMS can be used as a data store?
I posed this question on a couple VFP forums, and am attempting to assemble the results here
(any additions are welcome)
- Buy a Solution, or write it yourself. Doesn't say much: How do commercial solutions approach the problem?
- Write a SqlExec wrapper. This is by far the most common suggestion.
- Build a persistence layer, see http://www.agiledata.org/essays/impedanceMismatch.html and http://www.ambysoft.com/persistenceLayer.html and http://www.ambysoft.com/mappingObjects.html
- Perform all data access with dbms stored procedures. Your data layer will only need to know how to call an SP for the back end. The SP's for each back end provide the same API yet can vary in implementation.
However, the SP's for each back end will have to be authored somehow.
Um, "Perform all data access with dbms stored procedures" is the antithesis of being RDMS agnostic. The point is precisely Not to do this.-- Steven Black
You could look at it that way, It depends on your point of view. From the point of the middle tier that doesn't care what the database is, if the API of the database is provided via SP's. Yes, each DB needs specific SP's, but to the middle tier it doesn't matter, it just calls SP's and gets results. -- Bob Archer
- Of course, the most obvious VFP method isn't even list here, use remote views. This is the reason they exist in VFP! -- Bob Archer
But, Do They? How do you switch a RemoteView that was aiming at a .DBF to point instead at Sql Server? And then point it at Oracle RDBMS, or My Sql? (Programmatically, on the fly) -- ?wgcs
Well, if you look at the doc for the USE command:
USE MyView [CONNSTRING cConnectionString | (m.nStatementHandle) ]
You just provide the connection to whichever database you want it to talk to. If you want to talk to DBFs you are better of having local views also. Codebook has a dynamic cursor class that uses either the local or remote view on the fly, and that was back in 3.0 days.
Things to consier when designing your SqlExec wrapper:
- How to generate primary keys. ( Tek - Tips thread184-655666 )
- How to switch between RDBMS s
- How to build the SQL statement:
- Always use a "meta SQL" that is "translated" to the appropriate back end, or never build SQL.
- or, Have separate methods (say: sqlSELECT, sqlINSERT, sqlDELETE, etc) and pass the appropriate parameters to let the method build the SQL statement.
See also Tightline Data Classes, which I have been using for years and does exactly this, only brilliantly. This will take care of your SQL statements. Other than this, you'll need to eschew back-end stored procedures as much as you can, and you're good to go.-- Steven Black
The page Tightline Data Classes seems to indicate that it describes a commercial version of these classes, while a public domain version is also available, yet I only see references to the public domain classes on their website.... Is there a commercial version available too, or is it the PD version that you're referring to? - ?wgcs
As far as I know there is no commercial version, it's all PD.-- Steven Black
( Topic last updated: 2003.09.23 09:37:03 PM )