“Conventional wisdom dictates that code, all code, be written with portability in mind. After all, you wouldn’t want to have to revisit and rewrite code when moving between platforms or environments, would you? And while I do believe that coding for portability is a good thing in general, I also believe that when it comes to databases and SQL, coding for portability is a very bad thing indeed.” See http://www.sys-con.com/coldfusion/article.cfm?id=705.

6 thoughts

  1. I think it highly depends on the project you’re working on. For example, if you’re building a corporate Intranet application that will likely be run by a single group of people. You’re absolutely right. It is very rare to switch DBMS’s.
    On the other hand. What about web application products? Web applications sold to other companies that may have a plethera of hardware/software configurations. In this case, it requires market research to determine what needs to be supported.
    It would be interesting to see if Macromedia could make these issues go away. They have demonstrated the ability to create the query of queries, could they extend that to create a cross-DBMS CFSQL? In other words, extend the cfquery tag to create a ‘true’ SQL standard that would automatically convert a single set of SQL commands to DBMS specific statements. That would be powerful.

  2. The answer, at least to coding for cross platform database access, is to use an object oriented methodology and restrict all database access to "persist" objects. Pass your object into the persist and the persist will make sure it gets written to the database correctly (or xml, or flat text, or whatever). Then, if you need to port DBMS then all you need to do is update the persistors to write to the new DBMS.

  3. Steve, agreed, and I note as such in the column. As for a generic abstraction, that would be cool, but very difficult, it would be a full time job updating and maintaining that functionality.
    Doug, my point exactly, except in the column I suggest the use of CFCs.

  4. I can understand this if you use OO persisters like xml or writing to a flat file. But that just won’t fly for huge amounts of data and lots of users. If the persistors are actually scalable wouldn’t they write to a DBMS? I must be missing something, how would the cfc save upgrade work? For example:
    Say you use an Access db and have a simple update statement like this:
    update people
    set active=0
    where email = ”
    Then you decide to upgrade to SQL server which does not treat NULL fields as ”, so you have to modify the query to do this:
    update people
    set active=0
    where email is null
    Now say you had created a cfc and have a method deactivateNoEmails(), which ultimately runs the update statement. When you upgrade to SQL Server you still have to modify the sql. How does the cfc save you any effort with changing the DBMS?

  5. Steve, it doesn’t. If DBMSs use different SQL then you are going to have to write those different SQL statements. I can live with that, there is not really another choice. What is more important to me is that those differences are transparent to the actual application. So, I could see a SQL Server version of the CFC, and Access version, and other versions, all with the same methods exposed but with differing content, and the "right" CFC gets loaded as needed so as to generate the right SQL. "How does the cfc save you any effort with changing the DBMS?" It helps by localizing changes so that the effort required is far smaller.

Leave a Reply to Steve Nelson Cancel reply