New CFDJ Column: The Case Against Coding for Portability

“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

6 responses to “New CFDJ Column: The Case Against Coding for Portability”

  1. Steve Nelson Avatar
    Steve Nelson

    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. Doug Hughes Avatar
    Doug Hughes

    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. Ben Forta Avatar
    Ben Forta

    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. Steve Nelson Avatar
    Steve Nelson

    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. Ben Forta Avatar
    Ben Forta

    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.

  6. Steve Nelson Avatar
    Steve Nelson

    So the effort required is far smaller than what? What are you comparing it to?

Leave a Reply