Fog Creek Software
Discussion Board




Database Platform Independence

I am working on a suite of software that needs to be made database independent.

Currently, the existing software is ASP/VB based and all the database interaction is achieved using ADO. The system currently runs against SQL Server 2000. Some parts of the system run on PDAs using embedded VB with wireless/GSM links.

The SQL is all inline and parameters are passed as ‘?’ using objCommand.Parameters, e.g. Select * from tblExample where RecordId = ?. No stored procedures are currently used.

Constraints:
Need to keep code rewriting/refactoring to a minimum;
Need to script schema/demo data and provide easy install. This doesn’t need to be idiot proof or client friendly.
No budget for tools – keep suggestions free/cheap (sub $250).
Don’t have implementation deadline (yet).
Future migration of system to .Net
DB Platform is currently SQL Server, but I need to support Oracle and possibly MS Access. I have no details of any others, yet.

I’m not a DBA, but am quite competent with creation of scripts for all tables, triggers, stored procs, views etc.

I have 20+ years coding experience. Key skills are VB, ASP, XML/XSL, COM+. I know very little about ADO.Net and VB.Net.

I have previously worked on systems that were platform independent, where the SQL was all inline and used ‘SQL Helper’ functions to ensure that joins, date functions, aggregate functions etc all conformed to the selected database target. The schema was built by the installer from generic scripts – I don’t remember how the differences were catered for.

I have some ideas, based on my existing experience, but would welcome any suggestions/comments.

Justin
Friday, February 21, 2003

Could you please elaborate on exactly why you would database independence for a niche app? ... I take it since it requires SQL servers (of some sort) and has PDA devices using GSM links, I take it its nothing that I can pick up at CompUSA.

In my experience, when you write niche-apps that solves a business problem, for a company they will buy it regardless of database plattform. If it runs on MS-SQL server and I have Oracle DBs, but the application does something that would save me $100K anually, I would take the investment and be done with it.

We had a niche applications for hospitals at a place I used to work, we decided to go with Oracle databases since if you standardize on one DB you can use the specific features of that DB to make your application more effective.

Supporting multiple databases always, always leads to least-common-denomination problems, like ineffective storage of datatypes, handling all data as text, for portability and no referential integrity checking in the database, because its all implemented differently.

In my oppinion going with one DB (Im not going to be religious about which one) will give your application a competetive edge compared to the applications that fall into the platform-independence trap.

Patrik
Friday, February 21, 2003

I agree with Patrick.

More often than not, there really is no compelling reason to be database platform independent. Beware of the lowest common denominator syndrome.

Having said that though, you can start by looking on hotscripts and freshmeat

http://www.hotscripts.com/ASP/Scripts_and_Components/Database_Tools/

http://freshmeat.net/browse/67/?topic_id=67

There are a few data abstraction tools and libs

tapiwa
Friday, February 21, 2003

I agree with Patrik, too.

For preference, I would choose SQL Server 2k as that's what has been used already (plus I'm much more competent with it than Oracle).

However, this is what I have to implement. I'm sure there are sound commercial reasons for doing it (and when I learn what they are, I shall share them with you).

Perhaps I overstated the PDA aspect of it. Most clients will be connected using conventional browsers. I should imagine most of these will be IE on a Windows client.

Justin
Friday, February 21, 2003

Just write a class from scratch that works as an abstraction layer.  You are probably well aware that every DB vendor has its small (and sometimes large) deviation from the ansi SQL standards.  So all you have to do is create a class that abstracts those differences.

Your class might be called DBAdapter and feature methods like:

Connect(host, username, password, vendor)
StartTransaction()
EndTransaction()
RollBack()
ExecStoredProc(spname)
DoSelect(table, columns, where)
DoDelete(table, where)
DoUpdate()

yadda yadda yadda, you know what you need

DoSelect's "where" argument could be a strings containing the full clause, a list object containing key value pairs, or perhaps just an array.  I like to use strings as it provides a lot of flexibility and is easy to implement.  For eample "id>= 200 and category=4".  These kinds of structures are more difficult to implement as lists or arrays.  If you are worried about a particular DB will handle this kind of argument, you can always do simple string replacement in the adapter to modify the clause to work properly.

Be careful in designing your methods.  Look closely and specifically at all the DB needs of your application.  As always, use polymophismic techiniques such as method overloading to make the methods flexible to the code that uses your class. 

Avoid using stored procedures for your application as they will have to be completely rewritten for each DB you support (and in my experience, T-SQL works nothing like PL/SQL).  Also, try to limit the amount of specialized vendor-specific functionality.  Examples might be MySQL's LIMIT clause of or SQL Server's SELECT TOP functionality.  Where things can get hairy is in the implementation of enforcement of foreign key relationships transactions and primary keys (auto incrementing identifiers) as every vendor seems to have its own way of handling things.  The simple SELECTs, UPDATEs, and DROPs should be pretty straightforward, though.

If your class is designed correctly, it will be easy to slide in unique SQL syntax handling adaptions for any modern SQL-based DB vendor.  I do work on a content management system and we are quite happy with the way our DB abstraction layer turned out.  We can port our entire application to a new DB vendor in about 30 minutes. 

Don't be dissuaded from creating this adapter.  The wider DB vendor support you have, the greater the market for your product.  This comes at a cost of extended functionality unique to particular vendors, but OTOH how great is it to approach your client and say that your software runs on SQL Server, Oracle, DB/2, Sybase, PervasiveSQL, PostgreSQL, MySQL or whatever else they might have lying around.  This can save the client thousands of dollars and make your solution much pallatable even if it costs more or lacks other features that your competitors solution's offer.

Final note, if your application is of a particularly sensitive matter, like banking or other mission critical data where data integrity is of pinnacle importance, you may want to forego this endeavour.  Use one DB and take full advantage of it's specific tools to ensure reliable data.  DB vendor independence is not worth a borked table containing financial records because your particular implementation of transactions on one of your 10 supported database failed.  Beware the leaky abstractions.

fdwhitlark
Friday, February 21, 2003

Regarding the reasons for needing to support more than one database for a custom/niche (non-shrink-wrap) application - this is usually driven by customer insistence. If you are selling the application to more than one customer, then some customers have "standardized" on Oracle, some on SQL Server, some on DB2, etc. Most of these customers are not willing to install one copy of a different database, such as one copy of SQL Server in an all-Oracle environment. Therefore, you need to be able to use the customer's choice of database server.

We currently support multiple databases with our intranet application sold to a variety of customers. The approach you are taking (use ADO with inline SQL and no stored procedures, etc) should work well. You can certainly use a variety of datatypes quite successfully when supporting multiple databases (integer, char, varchar, nchar, date, text, image, real, float, etc), provided you allow for some differences.

The database creation scripts can still be relatively generic, with relatively minor changes for datatypes and slightly different syntaxes for creating tables, indexes, etc between SQL Server and Oracle.

A few things to be aware of:
- For text and image fields, use LONG and LONG RAW in Oracle, not the newer CLOB and BLOB datatypes, because ADO does not support CLOB and BLOB. (There are some limitations with LONG and LONG RAW, such as difficulties transferring them between tables if you ever need to do that).
- Date formats are very restrictive in Oracle; SQL Server allows you to insert a date in almost any format, Oracle requires it in the specific format '21-FEB-2003' for inserts, and in where clauses you may often need to use the "TO_DATE" function in Oracle.
- Functions such as "ISNULL" are different in Oracle.
- Numeric fields returned by ADO from Oracle must be converted to numeric before they can be used as numbers; ADO returns Oracle numeric fields as "adNumeric" or "adDecimal" and for these IsNumeric returns false, and statements like "If fieldvalue < 10" will cause an error, you need to convert the value (with CLng, CDec, etc) before using it as a number
- If you are using the "JOIN" keyword in SQL queries, you will need to change the queries to the "older" syntax (comma-separated table names) that is recognized by Oracle (and also SQL Server)
- Oracle has more restrictions on sub-queries than SQL Server.
- If you are using the "IN (a,b,...)" condition, Oracle has a limit of 1000 items in the IN list.
- If you are using "autonumber" columns in SQL Server, these need to be simulated in Oracle with a sequence and trigger.
- etc.

Philip Dickerson
Friday, February 21, 2003

>DoSelect(table, columns, where)
>DoDelete(table, where)
>DoUpdate()

>yadda yadda yadda, you know what you need

This is what happens when you abstract the specific database SQL out of an application and wrap it in generic classses.

How would you handle multiple table outer and inner joins,
for example? How about subselects? Oracle specific tree-walker SQL, ie. CONNECT BY and such. You don't. Or you have 10K lines of code in your DB abstraction class to handle all this. Leave SQL parsing to the SQL parser :-)

SQL related least common denominator problems, because the poor developer will be fed up about all this and have a very limited DB fuctionality. And we havent even got to the datatypes yet.

Patrik
Friday, February 21, 2003

I don't think there are any tools out there
that will be of much help--it doesn't matter
the price--it's a matter of the solution. If
you try to keep to a specific database
access interface.. like
ODBC/OLEDB/ADO/ADO.NET or JDBC--you still
can't access your database in the most
efficient manner without serious code
changes when the vendor of the interface
updates it. (ADO is not the same as
ADO.NET).. Sometimes your vendor also
bundles the interface with the language.. so
you end up having to upgrade both because if
you don't.. you have one part of the
solution supported and the other part
orphaned.
There's a few not so elegant ways to go
about this:
Learn the nature of your data access..and
partition them. Then find a plan for these
actions.
0) How do you import/export/transform data
in bulk. These actions are speed-critical.

1) Anything you can't do on a simple server
like MySQL is probably vendor specific and
vendors like to differenciate themselves..
But let's isolate the basic DML and
DDLs....for data
reporting/manipulation/setting up
triggers/stored procedures...
One common tip you'll here for these basic
DML/DDL calls: Most of these follow some
kind of standard if you keep to the very
basic options.  You can write thin
wrappers.. small functions and subroutines
that call these DDL and DMLs using a naming
convention that stays the same even if you
change databases. So imagine a few vbscript
calls that are named in english for what
they do.. and beneath that they do their
call in ADO or whatever.
Yes.. you have twice the work when you
recode.. but the high point of the day would
be trying to figure out what the old code is
trying to do verses the newer.. and allowing
you to change one at a time knowing what's
affecting what.
2) For the fancy stuff.. again.. place them
into thin wrappers.. and clearly group them
together in a way you know they are vendor
specific.. and once you isolate these you
might find that you don't use that many
vendor specific features.. and you can start
finding the new vendor's version of doing
this. Regular exprsession.. data
translation.. bulk export and imports..
ability to access OLE, locking options..
they go into this group. You'll have to
rewrite all of these regard less of which
technology you use (JDBC/ADO.. etc etc)

3) Translating things optimized: It's never
fun to discover how elaborate it can be to
write a ADO.Command structure just to write
a call some stupid stored procedure...and
naturally the thought of translating 20 or
30 of these into the NEW LANGUAGE just
drives people insane. First, your client
shoudl be paying for this, and the cost
should be transparent.. but Secondly... have
a plan to write these code automatically if
possible. Use the Factory pattern. Create a
plan that will code for you... and then hand
tune in minor ways to do what it really
should do.
I have found generators on the net
(http://www.vbrad.com/'s ADO Stored
Procedure Generator add-in comes to mind in
the Free Component section)that will
generate the ADO.Command calling code. Your
responsibility is then to find such
generators or write your own. And have a
system where you can take the latest spec
and generate either vendor's crap interface.

Sometimes it's as manual as substituting
variable tags with the actual fields you
want to access or write to in a programmer's
text editor.. but once you have verified it
works.. it just works. This at least half
the development time for something that you
CAN'T seriously automate due to the how
vendors differ so much with performance
critical features like stored procdures..
cursors..and so on.
I don't know.. these are the best
solutions... but do check out a real
project's solution:
OpenACS.com has a really large piece of open
source software that does CRM and such.. it
uses Tcl (server side scripting language)
and AOLServer (web server) and Oracle and
PostgreSQL (open source DB).. the way the
project abstracts calls to both postgresql
and oracle is an useful example of
partitioning your database accesses. Check
out how they do it. Just search for Oracle
or PostgresQL keywords in their tcl and sql
source files.

Li-fan Chen
Friday, February 21, 2003

Philip,

>If you are selling the application to more than one
>customer, then some customers have "standardized" on
>Oracle, some on SQL Server, some on DB2, etc.

OK, I appreciate customer demand. But then again, as in our example we had an application doing very vertical market stuff. You had the choice of giving us $20,000 and go with Oracle, or roll your own for a lot more.

This may sound like stupidityness on my part but I believe, if you have say 50 customers, and in selling to the 51st you give in to porting it to work on another DB, you have not made 1 customer a favour, but 50 a disfavour, because you will from then on be limited in which functionallity you can easily implement.

It is sometimes cheaper to let one customer of the hook, than to give in to customization of a software package.

Patrik
Friday, February 21, 2003

<Patrik>
How would you handle multiple table outer and inner joins,
for example? How about subselects? Oracle specific tree-walker SQL, ie. CONNECT BY and such. You don't. Or you have 10K lines of code in your DB abstraction class to handle all this. Leave SQL parsing to the SQL parser :-)
</Patrik>

I agree with you entirely, but much of what you talk about can be achived programatically.  For complex joins, you can certainly work to make the DoSelect() method tolerate such input.  The abstraction is necessary because even joins work differently among vendors.  Furthermore, many applications can be written without using joins.  Now I know some of you may balk at that and cite religious and philisophical arguments, but it is true, particularly of smaller applications.  And please, don't try to explain why subqueries can't be emulated in code.  Return the first data set, put it in a temp table, and then do another query against it.  Is it as easy as doing it all in the same query?  No.  Is it possible?  Yes.  Can it provide you even more functionality in the long run?  Yes.  It is just an abstraction.  So this solution is two trips to the DB instead of one.  Big deal.  For MOST applications, this is probably fine.

Oracle tree functions?  I've not actually used this but I assume it is some sort of hierarchical data model.  If so, make your own tree class and avoid using Oracle's specific functionality.  It really isn't hard.  Just a table with and ID and a ParentID column.  If the intent is to provide DB independence, then vendor specific functionality must be avoided.

Connect By?  I've not used this and I've written applications, therefore I can probably assume that it is not necessary for every application.

Again, making your application independent from a specific DB vendor is not impossible, but it may require you to change your approach to certain problems.  Don't use specific DB functionality that you are accustomed to as a cruch.  Much of it can be programmed around in the application.  Trust me, with varying degrees of success I've implemented refrential integrity and transactions at the application (dang you MySQL).  Not pretty, but doable under certain circumstances.  Finally, if your application needs to provide compeletly faultless data (or close to it), then by all means,  take advantage of the data integrity functionality of one DB vendor and expolit it to the very end. 

fdwhitlark
Friday, February 21, 2003

You know, some of you are making some fairly decent arguments here, but is this really the way to go in the long term?  Lashing your customers to a single vendor?

It would seem to me that over time, a customer (even a tech-savvy one) may find a competing DBMS they like better, but are now so locked in to the previous platform that they can't afford the energy requirement to switch.
"Like better" could include nice-to-haves like extra features, but could also include some potential killers like security holes in their current DB, bad scalability, deteriorating tech support, etc.

Wouldn't a contractor that could relieve the customer from this problem would be better than one that commits to one vendor, and provides a short-term performance boost?

Paul Brinkley
Friday, February 21, 2003

I agree with Patrik here.. you really need
to look at what you are doing to figure out
whether it's justifiable. If the customer
knows the extras cost and is paying for the
compatibility all the best. You are billing
this "stupid work".
Otherwise the people authorizing (marketing
drones, catbert, Asok, dbas, etc) this
extra level of abstraction really gotta
understand whether it's just a checkbox on
the feature list in the magazine reviews or
something that will actually help them
expand the market in a way they think it
would.

Li-fan Chen
Friday, February 21, 2003

fdwhitlark,

>Finally, if your application needs to provide compeletly
>faultless data (or close to it), then by all means,  take
>advantage of the data integrity functionality of one DB
>vendor and expolit it to the very end.

Im currently doing bank work.

Your account balance is correct, or close to it will not cut it.

The niche application I worked at earlier kept track of patient treatment data, which is another place where  "close to correct" is not really good enough.

Patrik
Friday, February 21, 2003

Patrik,

Then you completely made the right choice.  However for a website content management system, an e-commerce catalog system, a CRM system, or some other program that is a little more tolerant of data flubs, then providing database platform independence might be in order.

I'm not sure about all the specifics of Justin's scenario, but writing an adapter looks as if it falls out of the boundaries of his project constraints (but maybe not, you can achive a lot when you don't have a deadline, or can you?  a differet topic entirely).  I am simply offering my thoughts on how to achieve independence ONLY WHEN it is appropriate.

fdwhitlark
Friday, February 21, 2003

>Lashing your customers to a single vendor?

This is a very common missunderstanding. If you are in the business of providing DB centric apps, the customer you sell to is YOUR client. Not Oracles, not Microsofts, but YOURS.  You will get the call, cause the database listener is down. Because your application does not work. I would want to see if you hang up on that call saying "Call your DB vendor".

Disallowing customizations for DB related stuff, (and really even for the "we need an extra field on this tab" customizations, will help you serve your client better.

Patrik
Friday, February 21, 2003

>DoSelect(table, columns, where)
>DoDelete(table, where)
>DoUpdate()

This is exactly the *wrong* way to write a DB abstraction layer. Your code doesn't want to do a select - it wants a list of customers. It doesn't want to do a delete - it wants to close an account. It doesn't want to do an update - it wants to enter a deposit.

Write your DB abstration layer to support the operations your app needs to perform. This way, you're not only insulated from database vendor changes, you've also got flexibilty when your DB schema changes.

Chris Tavares
Friday, February 21, 2003

Yeah Chris,  if you do it any other way you are just working for Microsoft--for free--creating framework stuff.

Li-fan Chen
Friday, February 21, 2003

> This is exactly the *wrong* way to write a DB
> abstraction layer. Your code doesn't want to do a select - > it wants a list of customers. It doesn't want to do a
> delete - it wants to close an account. It doesn't want to > do an update - it wants to enter a deposit.

-1 Offtopic

Oh please, we are talking about database vendor independence, not application design.  It goes without saying that you should have a customer class that has processing methods, and you should have an employee class, and an address class, and a whole slew of other more peripherial factory and iterator design pattern classes. 

fdwhitlark
Friday, February 21, 2003

> Yeah Chris,  if you do it any other way you are just working for Microsoft--for free--creating framework stuff.

This is exactly what I'm addressing when I said the following:

> Again, making your application independent from a specific DB vendor is not impossible, but it may require you to change your approach to certain problems.  Don't use specific DB functionality that you are accustomed to as a crutch.

Hello, world!  Maybe the vendor-lock in approach isn't always the best route to go.  And if your only excuse is not wanting to put forth the effort to invesitgate and develop ways around it, then shame on you.  I don't buy it.  Do you ever consider, for example, that it might be better in certain circumstances to write a new framework (that better fits the requirements of a application) than to go with the one that is given to you?  I personally find it a lot easier, quicker, and more fulfilling to create my own abstractions for a particular problem then to go with a one size fits all approach.  I know of plenty of applications used by large firms (Fortune 500) that support multiple database vendors and some that even go so far as to maintain referential integrity in the application, not the database.  Ooooh, can you hear Fabian Pascal's (www.dbdebunk.com) hair coming unrooted and hitting the floor in large clumps?

fdwhitlark
Friday, February 21, 2003

fdwhitlark,

>I personally find it a lot easier, quicker, and more fulfilling
>to create my own abstractions for a particular problem
>then to go with a one size fits all approach.

I guess fullfilling is the key word here isnt it?

I on the other hand try to reuse what others have made when it comes to DBs, you may call me naïve but I have a hard time justifying for myself why I should spend time re-implementing functionality, that would doubtless be mine, but probably alot slower/unstable/more hackish/hairy and I dont know what, since most databases from major vendors are mature products.

Oracle and IBM engineers and others have been working on the most efficient way to store stuff in a database since about when I was born. Shame on me for not trying to implement my own.
   
I can find better things to do with my time, spending time on implementing value adding functionality to my program is one of these things I come to think of.

Have fun,

Patrik
Friday, February 21, 2003

Shame on me for not trying to implement my own.

My own application enforced referential integrity system and application controlled transaction handling, that is.

Patrik
Friday, February 21, 2003

> Oracle and IBM engineers and others have been working on the most efficient way to store stuff in a database since about when I was born. Shame on me for not trying to implement my own.

You are certainly correct and I don't expect anybody to really do it better than them, but consider also that they are a big corporation and are about the business of making money (aren't we all?) and they have intentionally deviated from industry standard in such a way that it makes DB vendor swaps a daunting task.  What I want to tell people is that it is not impossible to be independent of a vendor platform, but it does require some sacrifices.  Every situation should be evaluated individually.  In some circumstances, the costs of DB vendor independence will outweigh the benefits, other times it won't. 

I've already attempted to make the case for both sides of the argument.  I after some cost-benefit analysis and some ROI analysis following an implementation, I'm sure we could draw some lines in the sand and say DB vendor dependence works well here but doesn't work well there.  Case by case evaluation with an open mind to all courses of action will produce desireable results.  Solid experience in all the various routes is priceless.  Procedural or OO?  relational or hierarchal?  DB vendor abstration or no DB vendor abstration?  There will never be an 'always right' answer.  The correct solution will be established case by case. 

Die, horsie! Die.

fdwhitlark
Friday, February 21, 2003

> Shame on me for not trying to implement my own.
> My own application enforced referential integrity system and application controlled transaction handling, that is.

This is hard nasty code.  I wouldn't recommend it and it is not necessarily a necessity in a DB abstraction layer.  It can be avoided if you exclude Access and MySQL from your list of DBs to support. 

Also, I know I can get flammed for saying this, but fkey integrity and transactions are not ALWAYS neccesary meet the requirements of a software specification.  Take FogBUGZ, it works on Jet, SQL Server, and MySQL.  I would presume that this application doesnt use fkeys or transactions.  The very application I'm using now (this forum) is probably DB driven and doesn't use these features.  Meh?

fdwhitlark
Friday, February 21, 2003

Agreed, Little Horsie is as of now dead.

Patrik
Friday, February 21, 2003

Since you mention future migration to .NET, you may want to work toward where you want to end up.  Here are a few articles on ADO.NET database independence:

Transparent Database Access with ADO.NET
http://www.ondotnet.com/pub/a/dotnet/2002/12/16/transadonet.html

Database Independent Data Access
http://www.codeguru.com/columns/DotNet/DotNet200208.html

You may also want to check out the data access application block for .NET
http://msdn.microsoft.com/library/en-us/dnbda/html/daab-rm.asp

ODN
Friday, February 21, 2003

"> My own application enforced referential integrity system and application controlled transaction handling, that is.

This is hard nasty code.  I wouldn't recommend it and it is not necessarily a necessity in a DB abstraction layer.  It can be avoided if you exclude Access and MySQL from your list of DBs to support.  "

FYI, Access/Jet has both referential integrity and transactions.  (No, the transactions aren't ACID compliant, but they're better than nothing.)  And MySQL has at least transactional capability via several different add-ons.

Herbert Sitz
Friday, February 21, 2003

fdwhitlark -- Or maybe you were just referring to ability to use transactions in stored procedures, in which case you're completely right.

Herbert Sitz
Friday, February 21, 2003

ODN: The problem with the "Transparent Database Access with ADO.NET" and other links is that the breaking point is still the sql statement. I still think it's a good direction.

"SELECT top 10 table_name FROM INFORMATION_SCHEMA.TABLES" won't work in oracle, but.
"SELECT * FROM (SELECT rownum, table_name FROM systables ORDER BY table_name)x WHERE x.rownum <= 10 "
will.

In the same way that Sql Server and Oracle uses different functions (getdate() vs. sysdate), etc.

AEB
Saturday, February 22, 2003

I did a system that supported both SQL Server and Oracle but was done in java not vb. I'm doing .net now (I did vb6 a f ew years back) but the same approach could apply.

1. You have to write a layer that hides the specfic database implementation. i.e. an Adapter pattern. This was easier because we used generic jdbc functionality - no driver specific stuff.

This is where those articles in ODN's post are of great help - especially the codeguru one.

2. Connection properties should be configured and stored somewhere - text file, xml, etc. so that they can be replaced and edited in one place.

3. SQL Statements should be configured and stored somewhere as described in #2. Not in the code!! If you do step 1, you just write some code to retrieve the sql string from wherever based on the specific database being used (determined in #2 - i.e. ORA or MSSQL).

As a simple example, you end up with...

SELECT_CUSTOMER_MSSQL = "select blah blah blah"
SELECT_CUSTOMER_ORA = "select blah blah blah"
SELECT_CUSTOMER_DB2 = "select blah blah blah"


Some issues to note...

* Identities/Sequences - i.e. autogenerated values from the db. In mssql, you find out the value AFTER it is inserted whereas Oracle's sequences are determine before the INSERT statement.

* If possible, use SQL 92 standards - I know that
SELECT * FROM product p LEFT JOIN vendor v ON p.vendor_id = v.vendor_id" is not as intuitive as what everyone is used to for outer joins, but it works in both of the recent versions of mssql and oracle.

* Start with 2 database and build out from that. In order for this to work, you HAVE to know each one inside and out. Don't expect your product to support MS SQL, Oracle, DB2, MySQL, Access on day one. What functions do they use (COALESCE vs. NVL vs. NULLIF, etc.) and what is equivalent?

* I haven't found a way to do Oracle's "connect by" w/o using a cursor in a stored procedure. It's a great feature, but if you aren't already using it, don't worry about it yet.

* Try to stick to general datatypes - luckily I didn't have any exotic ones or any BLOBS, so I was somewhat lucky.

AEB
Saturday, February 22, 2003

Don't write a class for your database abstraction, write an interface then create an implementation of the interface for each DBMS. Then create an instance of the appropriate class for your database access.

John Ridout
Sunday, February 23, 2003

Please excuse me if somebody already mentioned this:

Most of the problem descriptions and solutions are looking at the least common sql, how to use the same sql or sql generator on different databases and how the sql differ.

But you aren't interested in the sql, are you? What you care about are the entity classes. The CityDesk entities have methods like List, GetNext, a meaningfull set of properties and so on.

How fortunate we are that entities are abstracted away from the database. They depends on the ADO recordset, only. Why not design one or more database specific classes which does nothing but expose an interface which takes parameters and executes db commands or return recordsets.

Promote the interface into interface classes and implement them for each database. The database implementation would optimize the sql for its database.

OK, so it's more work to write db code almost double up, but I think the savings in not having to generalize the differences will pay off.

Thomas Eyde
Monday, February 24, 2003

I agree with Thomas. When I build my objects in VB 6, VB.net or Java I separate my business classes from my DB classes. Your SQL strings can reside in the business class and your DB class just does the generic connection to the DB, executes whatever SQL is passed to it and then returns the result to the calling class. You could easily keep your business classes the way they are and just add a new DB class which knows how to work with the specific DB platform you are writing for.

For example:

You can have your  business objects generate SQL and pass it to a SQL Sever DB object and then later pass the SQL to a Oracle DB object.

KenB
Tuesday, February 25, 2003

*  Recent Topics

*  Fog Creek Home