 Tips on Writing Portable SQL Code
started 4/23/2002; 12:45:30 PM - last post 4/24/2002; 7:23:18 PM
|
|
Ehud Lamm - Tips on Writing Portable SQL Code 
4/23/2002; 12:45:30 PM (reads: 860, responses: 4)
|
|
| Tips on Writing Portable SQL Code |
|
(via PHP everywhere)
Most database vendors practice product lock-in. The best or fastest way to do things is often implemented using proprietary extensions to SQL. This makes it extremely hard to write portable SQL code that performs well under all conditions.
Long time since we last mentioned SQL.
I always found it a bit ironic that SQL has so many variants and vendor specific extensions. SQL was meant to be a standard query language for RDBMSes. But as the saying goes, some standards are more standard than others...
This page is quite informative, but I guess that those of you working with SQL won't find anything really new here.
Those thinking about DSLs, language interfaces to achieve portability etc. may find some food for thought.
Posted to general by Ehud Lamm on 4/23/02; 1:00:22 PM
|
|
|
|
Chris Rathman - Re: Tips on Writing Portable SQL Code
4/23/2002; 9:26:45 PM (reads: 802, responses: 1)
|
|
|
Unfortunately, most of the SQL that I write is within Stored Procedures, where it's impossible to get away from vendor lock in. From a transactional integrity, speed, and security standpoint, the databases have to have built in protection. The chances that the language for defining stored procedures will ever be standardized is zero - which is a shame since the current slate (T-SQL, PLSQL, etc) is quite horrid to work with.
"Application front ends come and go, but the database lasts forever".
|
|
Patrick Logan - Re: Tips on Writing Portable SQL Code
4/23/2002; 11:41:45 PM (reads: 806, responses: 0)
|
|
|
Dimensional models are simple, portable, efficient, and surprisingly useful in a large number of applications.
|
|
Ehud Lamm - Re: Tips on Writing Portable SQL Code
4/24/2002; 2:18:26 PM (reads: 811, responses: 0)
|
|
|
That was my feeling when I was a DBA. I started out wanting to use SP, but then I realized how unportable the whole thing is (just to show the extreme legnths vendors went to: Adabas require coding in Natural which is Software AGs 4GL. DB2 only supported Java IIRC). When it came to triggers things got even worse. One DBMS only supported triggers on update, while another supported on read triggers as well, and so on.
I was quite horrified when I went to design reviews and saw how, feature by feature, we designed things that could only run on Oracle.
I say: always add one level of indirection. If you can't encapsulate the DBMS with your own wrappers, I really don't envy the maintenance work you are in for.
|
|
Chris Peterson - Re: Tips on Writing Portable SQL Code
4/24/2002; 7:23:18 PM (reads: 770, responses: 0)
|
|
|
I am not a database expert, but many vendors (like IBM and Oracle) seem to allow stored procedures to be written in Java. I can imagine Microsoft SQL Server inevitably allowing .NET stored procedures. This might solve the SP portable language problem, but I doubt it will help with other vendor-specific DB features mentioned like differences in triggers or indexes.
|
|
|
|