Welcome!

You will be redirected in 30 seconds or close now.

ColdFusion Authors: Yakov Fain, Jeremy Geelan, Maureen O'Gara, Nancy Y. Nee, Tad Anderson

Related Topics: ColdFusion

ColdFusion: Article

ODBC Escape Sequences

ODBC Escape Sequences

Within the field of computer science, competing products often implement standards differently. Database management systems (DBMSs), with their different syntaxes for SQL queries, are no exception. How can we write queries within our applications that will be compatible with several DBMSs, especially when we need to use functionality that different DBMSs have implemented differently?

Recently, Technomedia Training, Inc., was awarded a contract to deliver our core product, a Web-based human resources management software application, to a new client. The client would be hosting the application, and the IT department's infrastructure and expertise were strictly Oracle-based. However, our application was designed to work with SQL Server 2000. Our challenge was to transform our Web application to make it compatible with an Oracle8i database.

The first step was to convert the database and all its objects from SQL Server 2000 to Oracle8i. (We employed some interesting conversion techniques, but they're a whole subject themselves and we won't cover them in this article.) Next, we discovered that many of the queries within the application didn't work anymore with the Oracle8i database because of differences in implementation of the SQL standard. For example, the following outer join query in Transact-SQL works with SQL Server 2000 but is incompatible with Oracle8i:

SELECT EMP.ID, EMP.NAME, DEP.NAME
FROM EMPLOYEES EMP
LEFT OUTER JOIN DEPARTMENTS DEP ON
DEP.DEPARTMENT_ID = EMP.ID
In Oracle8i, the syntax is:

SELECT EMP.ID,EMP.NAME.DEP.NAME
FROM EMPLOYEES EMP, DEPARTMENTS DEP
WHERE EMP.DEPARTMENT_ID = DEP.ID(+)
We looked at several alternatives for resolving this problem, and concluded that ODBC escape sequences are the best way to make our application's queries compatible with several DBMSs.

Technological Architecture
Our core product is a Web application that uses a standard, three-tier architecture. A ColdFusion Server works with an IIS Web server to run the application code. The ColdFusion Server uses an ODBC driver to connect to the database. Users' Web browsers call up pages from the ColdFusion/IIS servers using the HTTP protocol, and the application works with a SQL Server 2000 database - we need to make it compatible with Oracle8i (see Figure1).

Dealing with DBMS-Specific Queries
To deal with syntax differences among DBMSs, we considered three possible solutions. We could:

1.   Change the application's queries to remove the DBMS-specific elements.
2.   Put conditional statements around all queries with DBMS-specific terms and have both syntaxes present in the code. Then, depending on which DBMS the application is connected to, the appropriate syntax would be used.
3.   Use ODBC escape sequences and let the ODBC driver map to the DBMS-specific syntax.

We'll examine these three possibilities next.

Simple SQL
The first option for dealing with DBMS-specific queries is to remove all terms that are DBMS-specific and to use only SQL syntax that is common to all DBMSs. For example, the SQL Server 2000 syntax for concatenating a first and a last name looks like this:

<cfquery name = "qsEmployees" datasource="Sigal">
SELECT first_name + ' ' + last_name AS name
FROM employees
</cfquery>
Instead, the concatenation could be done outside of the query like this:

<cfquery name = "qsEmployees" datasource="Sigal">
SELECT first_name, last_name, NULL AS name
FROM employees
</cfquery>
<cfloop query = "qsEmployees">
<cfset tmp = QuerySetCell (qsEmployees, "name",
qsEmployees.first_name & " " &

qsEmployees.last_name,
qsEmployees.CurrentRow)
</cfloop>
The advantage of this solution is that the query will work with all types of DBMSs and with all types of drivers, including non-ODBC drivers. However, we discarded this solution because of its disadvantages. If we limit our queries to a lowest-common-denominator SQL, we can no longer use some functionality that is essential to our application. There's no way to execute outer joins, and most date and time functions are excluded. All queries have to be relatively simple, which implies having to restructure certain queries or even sub-dividing them to make them more compatible. In addition, a lot of the processing is transferred from the database to the application code. This incurs a performance penalty, since the database server is optimized for many of the data operations, while the application server is not.

Different Versions of Incompatible Queries
The second option for making DBMS-specific queries compatible with several DBMSs is to create different versions of the query, each version containing DBMS-specific syntax. Using this method, our concatenation example would look like this:

<cfquery name = "qsEmployees" datasource="Sigal">
<cfif application. DBType eq "ORA">
SELECT first_name || ' ' || last_name AS name
FROM employees
<cfelse>
SELECT first_name + ' ' + last_name AS name
FROM employees
</cfif>
</cfquery>
The advantage of this solution is that it's relatively easy to implement. A new, DBMS-specific version of each incompatible query is written and the application executes the appropriate version for the database it's connected to. However, doubling queries makes the application code bigger. Modifying and maintaining the code becomes more complex because changes to a query need to be repeated for each DBMS-specific version. Moreover, this solution doesn't scale with other DBMSs. If one day the need arose to make the application compatible with a DBMS other than Oracle or SQL Server, the whole process would need to be repeated. The code would become even more complex and difficult to modify and to maintain.

ODBC Escape Sequences
Using ODBC escape sequences for the DBMS-specific queries is the third option for making queries compatible with different DBMSs. ODBC escape sequences are a way of coding database functionalities that have different syntaxes in different DBMSs. As we have seen, the syntax for concatenations in Oracle8i is different from that used in SQL Server 2000, but we can use an escape sequence that represents the concatenation functionality provided by the DBMSs instead of the DBMS syntax. The concatenation example now looks like this:

<cfquery name = "qsEmployees" datasource="Sigal">
SELECT {fn concat (first_name, {fn concat (' ', last_name)})}
AS name
FROM employees
</cfquery>

ODBC escape sequences allow queries to be compatible with several DBMSs while giving access to the functionalities that have been implemented differently by different DBMSs. It's important to note that an escape sequence will only work with a DBMS if the DBMS supports the escape sequence functionality. Escape sequences are not implementations of database functionalities. Rather, they are an alternative way of writing a functionality, an alternative syntax that is translated by the ODBC driver into the DBMS syntax. It's possible to have one version of the queries using this technique. This solution also scales much better than the previous one because the queries are not only compatible with Oracle8i and SQL Server 2000, but also with any other DBMS that has an ODBC driver, in the measure that the DBMS supports the functionality represented by the escape sequences. The learning process is simplified because we don't need to learn the DBMS syntax of different DBMSs once we have learned the ODBC escape sequences. However, using ODBC escape sequences can make the code more complex because of the somewhat ornery syntax. You'll need to plan for a learning period for everyone to become comfortable with the new syntax and when to use it. Despite these mild difficulties, ODBC escape sequences are, in our opinion, the best way to deal with the different ways different DBMSs implement certain functionalities.

Using ODBC Escape Sequences
Let's look at how ODBC escape sequences are used. The functionalities that are implemented differently by different DBMSs are coded using a special syntax, called "escape sequences". Escape sequences have the following structure:

  • They are placed within curly braces so the ODBC driver recognizes them.
  • A code that represents the escape sequence type follows the opening curly brace.
  • The escape sequence content is written.

    The ODBC driver reads the escape sequence and translates it into the DBMS-specific syntax before sending the query to the database. ODBC escape sequences can be used for functions, outer joins, LIKE escape characters, GUIDs, intervals, procedure calls, and date, time, and timestamp literals. For example, an escape sequence function looks like this:

    SELECT {fn MonthName (date_membership)}
    AS month_membership
    FROM employees
    An outer join escape sequence has this syntax:

    SELECT emp.id, ent.name
    FROM {oj employees emp
    LEFT OUTER JOIN entities ent
    ON emp.entity_id = ent.id}
    A date literal has this syntax:

    SELECT COUNT(id) AS num
    FROM employees
    WHERE date_membership > {d '2003-01-01'}
    Challenges
    When we started implementing and testing ODBC escape sequences in our application's queries, we discovered that not all ODBC drivers fully implement the ODBC standard. Some of the functions in the ODBC escape sequence specification were not implemented by our Oracle ODBC driver. For example, we weren't able to use the TimeStampDifference() function with the Oracle ODBC driver. In such cases, we used one of the other two alternatives, conditional statements around different versions of the query, or simple SQL with more processing outside the query.

    Another concern was that by using ODBC escape sequences, we were limiting our database connectivity to ODBC drivers. Our company also develops Java objects to extend the functionality of ColdFusion, and we questioned whether the ODBC escape sequences were a good long-term solution, as Java objects use the JDBC protocol. Research revealed that certain JDBC drivers, known as ODBC bridges, use the ODBC protocol. Also, the JDBC protocol specification includes an escape syntax that is very similar to the ODBC escape sequences. Testing with a pure JDBC driver showed that it was compliant with several escape sequences. We strongly recommend testing with the JDBC driver you're planning to use.

    Conclusion
    Although the process of converting an application's queries to be compatible with a new DBMS can be lengthy, using ODBC escape sequences ensures that most of the work will be done only once. It also preserves the application's performance by allowing access to the database functionality, which has different syntaxes in different DBMSs.

    References

  • ODBC escape sequences: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/ odbcescape_sequences_in_odbc.asp
  • JDBC API: http://java.sun.com/products/jdbc/download.html
  • ColdFusion JRun JDBC driversdocumentation: www.macromedia.com/v1/documents/jr31/jdbcref.pdf
  • Comments (0)

    Share your thoughts on this story.

    Add your comment
    You must be signed in to add a comment. Sign-in | Register

    In accordance with our Comment Policy, we encourage comments that are on topic, relevant and to-the-point. We will remove comments that include profanity, personal attacks, racial slurs, threats of violence, or other inappropriate material that violates our Terms and Conditions, and will block users who make repeated violations. We ask all readers to expect diversity of opinion and to treat one another with dignity and respect.


    @ThingsExpo Stories
    The 21st International Cloud Expo has announced that its Call for Papers is open. Cloud Expo, to be held October 31 - November 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA, brings together Cloud Computing, Big Data, Internet of Things, DevOps, Digital Transformation, Machine Learning and WebRTC to one location. With cloud computing driving a higher percentage of enterprise IT budgets every year, it becomes increasingly important to plant your flag in this fast-expanding busin...
    Internet of @ThingsExpo, taking place October 31 - November 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA, is co-located with the 21st International Cloud Expo and will feature technical sessions from a rock star conference faculty and the leading industry players in the world. @ThingsExpo Silicon Valley Call for Papers is now open.
    As cloud adoption continues to transform business, today's global enterprises are challenged with managing a growing amount of information living outside of the data center. The rapid adoption of IoT and increasingly mobile workforce are exacerbating the problem. Ensuring secure data sharing and efficient backup poses capacity and bandwidth considerations as well as policy and regulatory compliance issues.
    DevOps is often described as a combination of technology and culture. Without both, DevOps isn't complete. However, applying the culture to outdated technology is a recipe for disaster; as response times grow and connections between teams are delayed by technology, the culture will die. A Nutanix Enterprise Cloud has many benefits that provide the needed base for a true DevOps paradigm.
    SYS-CON Events announced today that SoftLayer, an IBM Company, has been named “Gold Sponsor” of SYS-CON's 18th Cloud Expo, which will take place on June 7-9, 2016, at the Javits Center in New York, New York. SoftLayer, an IBM Company, provides cloud infrastructure as a service from a growing number of data centers and network points of presence around the world. SoftLayer’s customers range from Web startups to global enterprises.
    Five years ago development was seen as a dead-end career, now it’s anything but – with an explosion in mobile and IoT initiatives increasing the demand for skilled engineers. But apart from having a ready supply of great coders, what constitutes true ‘DevOps Royalty’? It’ll be the ability to craft resilient architectures, supportability, security everywhere across the software lifecycle. In his keynote at @DevOpsSummit at 20th Cloud Expo, Jeffrey Scheaffer, GM and SVP, Continuous Delivery Busine...
    SYS-CON Events announced today that Outscale will exhibit at SYS-CON's 20th International Cloud Expo®, which will take place on June 6-8, 2017, at the Javits Center in New York City, NY. Outscale's technology makes an automated and adaptable Cloud available to businesses, supporting them in the most complex IT projects while controlling their operational aspects. You boost your IT infrastructure's reactivity, with request responses that only take a few seconds.
    SYS-CON Events announced today that Loom Systems will exhibit at SYS-CON's 20th International Cloud Expo®, which will take place on June 6-8, 2017, at the Javits Center in New York City, NY. Founded in 2015, Loom Systems delivers an advanced AI solution to predict and prevent problems in the digital business. Loom stands alone in the industry as an AI analysis platform requiring no prior math knowledge from operators, leveraging the existing staff to succeed in the digital era. With offices in S...
    SYS-CON Events announced today that DivvyCloud will exhibit at SYS-CON's 20th International Cloud Expo®, which will take place on June 6-8, 2017, at the Javits Center in New York City, NY. DivvyCloud software enables organizations to achieve their cloud computing goals by simplifying and automating security, compliance and cost optimization of public and private cloud infrastructure. Using DivvyCloud, customers can leverage programmatic Bots to identify and remediate common cloud problems in rea...
    SYS-CON Events announced today that Tintri, Inc, a leading provider of enterprise cloud infrastructure, will exhibit at SYS-CON's 20th International Cloud Expo®, which will take place on June 6-8, 2017, at the Javits Center in New York City, NY. Tintri offers an enterprise cloud platform built with public cloud-like web services and RESTful APIs. Organizations use Tintri all-flash storage with scale-out and automation as a foundation for their own clouds – to build agile development environments...
    SYS-CON Events announced today that Progress, a global leader in application development, has been named “Bronze Sponsor” of SYS-CON's 20th International Cloud Expo®, which will take place on June 6-8, 2017, at the Javits Center in New York City, NY. Enterprises today are rapidly adopting the cloud, while continuing to retain business-critical/sensitive data inside the firewall. This is creating two separate data silos – one inside the firewall and the other outside the firewall. Cloud ISVs ofte...
    SYS-CON Events announced today that Tappest will exhibit MooseFS at SYS-CON's 20th International Cloud Expo®, which will take place on June 6-8, 2017, at the Javits Center in New York City, NY. MooseFS is a breakthrough concept in the storage industry. It allows you to secure stored data with either duplication or erasure coding using any server. The newest – 4.0 version of the software enables users to maintain the redundancy level with even 50% less hard drive space required. The software func...
    SYS-CON Events announced today that Interoute has been named “Bronze Sponsor” of SYS-CON's 20th International Cloud Expo®, which will take place on June 6-8, 2017, at the Javits Center in New York City, NY. Interoute is the owner operator of Europe's largest network and a global cloud services platform, which encompasses over 70,000 km of lit fiber, 15 data centers, 17 virtual data centers and 33 colocation centers, with connections to 195 additional partner data centers. Our full-service Unifie...
    SYS-CON Events announced today that EARP will exhibit at SYS-CON's 20th International Cloud Expo®, which will take place on June 6-8, 2017, at the Javits Center in New York City, NY. "We are a software house, so we perfectly understand challenges that other software houses face in their projects. We can augment a team, that will work with the same standards and processes as our partners' internal teams. Our teams will deliver the same quality within the required time and budget just as our partn...
    SYS-CON Events announced today that Carbonite will exhibit at SYS-CON's 20th International Cloud Expo®, which will take place on June 6-8, 2017, at the Javits Center in New York City, NY. Carbonite protects your entire IT footprint with the right level of protection for each workload, ensuring lower costs and dependable solutions with DoubleTake and Evault.
    SYS-CON Events announced today that Super Micro Computer, Inc., a global leader in compute, storage and networking technologies, will exhibit at SYS-CON's 20th International Cloud Expo®, which will take place on June 6-8, 2017, at the Javits Center in New York City, NY. Supermicro (NASDAQ: SMCI), the leading innovator in high-performance, high-efficiency server technology, is a premier provider of advanced server Building Block Solutions® for Data Center, Cloud Computing, Enterprise IT, Hadoop/...
    SYS-CON Events announced today that Technologic Systems Inc., an embedded systems solutions company, will exhibit at SYS-CON's @ThingsExpo, which will take place on June 6-8, 2017, at the Javits Center in New York City, NY. Technologic Systems is an embedded systems company with headquarters in Fountain Hills, Arizona. They have been in business for 32 years, helping more than 8,000 OEM customers and building over a hundred COTS products that have never been discontinued. Technologic Systems’ pr...
    SYS-CON Events announced today that Progress, a global leader in application development, has been named “Bronze Sponsor” of SYS-CON's 20th International Cloud Expo®, which will take place on June 6-8, 2017, at the Javits Center in New York City, NY. Enterprises today are rapidly adopting the cloud, while continuing to retain business-critical/sensitive data inside the firewall. This is creating two separate data silos – one inside the firewall and the other outside the firewall. Cloud ISVs oft...
    SYS-CON Events announced today that Cloudistics, an on-premises cloud computing company, has been named “Bronze Sponsor” of SYS-CON's 20th International Cloud Expo®, which will take place on June 6-8, 2017, at the Javits Center in New York City, NY. Cloudistics delivers a complete public cloud experience with composable on-premises infrastructures to medium and large enterprises. Its software-defined technology natively converges network, storage, compute, virtualization, and management into a ...
    New competitors, disruptive technologies, and growing expectations are pushing every business to both adopt and deliver new digital services. This ‘Digital Transformation’ demands rapid delivery and continuous iteration of new competitive services via multiple channels, which in turn demands new service delivery techniques – including DevOps. In this power panel at @DevOpsSummit 20th Cloud Expo, moderated by DevOps Conference Co-Chair Andi Mann, panelists will examine how DevOps helps to meet th...