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

Putting Oracle SQL to Work in Your ColdFusion Applications

Putting Oracle SQL to Work in Your ColdFusion Applications

SQL is the bread-and-butter language of relational databases. Although the language has been standardized (SQL-92 and SQL:99), virtually no vendor's implementation strictly conforms to the standards. Most database products use their own syntax (in joins, for example) and, more important, implement several language extensions.

For the developer it's necessary to know about product-specific extensions in order to leverage the database optimally. This is true whatever the nature of the application - Web or client/server. In addition, for the Web programmer knowledge of these extensions can help shift a lot of the processing load off the Web/application server to the database where it belongs (see Ben Forta's comments on this in "Take Your Database Out of Retirement," CFDJ, Vol. 1, issue 3). In this article we present some features of Oracle SQL that we've found useful when developing ColdFusion applications.

This article is separated into two parts. Part 1 explores some of the built-in functions that you have at your disposal when working with Oracle and compares them to the corresponding ColdFusion functions. Part 2 will show how some basic application problems can be solved with SQL, with particular reference to Oracle-specific syntax. This overview is not comprehensive, but it should help the Oracle novice find his or her way around the database by pointing out features that work particularly well in ColdFusion application development.

Leveraging Oracle Functions in ColdFusion
This section presents functions that Oracle delivers "out of the box." Oracle also lets you define your own functions (analogous to custom tags in CF), but this process will be explained in a later article. For the moment, keep in mind that this discussion is just the tip of the iceberg.

For quick reference, we've listed ColdFusion string, numeric, and date functions with their Oracle counterparts in Tables 1, 2 and 3, respectively. Many ColdFusion functions have no direct counterpart in Oracle and vice versa. However, as you'll see in many of the following examples, much of what can be achieved with ColdFusion functions can also be achieved by using one or more Oracle functions in tandem.

Many examples use an Oracle table called DUAL. This is a single row table that contains a dummy entry (as can be seen by selecting * from DUAL). The table is owned by SYS (which is the user with the most privileges in Oracle installations), and most Oracle installations make it available to all users through a public synonym. Selecting from DUAL is a useful technique when you want to have Oracle do a computation and return the result to you.

Invoking Oracle Functions in ColdFusion
As you may be aware, ColdFusion doesn't allow you to invoke Oracle functions directly through its CFSTOREDPROC tag. However, you can make function calls through CFQUERY by simply SELECTing from DUAL. For example:

<cfquery name="yourFunction"
datasource="yourDSN"
username="yourUsername"
password="yourPwd">

select
yourFunction(arguments) return_value
from
dual

</cfquery>

The variable yourFunction.return_ value[1] contains the returned value.

A Simple Audit Trail Using USER and SYSDATE
It's extremely useful to keep track of modifications (inserts or updates) to tables. One simple way to do this is to add four audit columns to any table whose data movements you wish to keep track of. CREATION_USER keeps track of the user that created the record; CREATION_DATE, the date the record was created; MODIFICATION_USER, the last user who modified the record; and MODIFICATION_DATE, which stores the date the record was last modified. Then, when carrying out an insert or an update, you merely insert (or update) these four (or two) columns using the Oracle functions USER and SYSDATE, respectively.

The insert in Listing 1 enters the current account user and system date (down to the second) into the four columns. These columns can then be used at any time to keep track of who made modifications to your data and when.

It's also worthwhile noting here that if you need extensive auditing of your data, that is, keeping exact track of any and all modifications to your data, Oracle has an advanced auditing system built into the RDBMS. The audit trail produced by the built-in system not only allows you to audit data, but also user sessions. A detailed discussion of the Oracle audit trail is beyond the scope of this article, but it's important for you as a CF developer to know that it exists in case you need advanced auditing capabilities. Inserting Dates into Oracle

As we're sure many readers have experienced, inserting dates into an Oracle database from ColdFusion can be a bit trying. One simple way to do this is to pass date values as strings and convert the string to a date data type using the Oracle TO_DATE function (see Listing 2).

Truncating Dates
An Oracle date data type stores the date and a timestamp. This can get annoying sometimes, since you may want to filter out data based on date only (disregarding the time portion). One way of achieving this is to use the TRUNC function when comparing dates. This function sets the timestamp to midnight, as is made clear when executing the following query:

<cfquery name="yourQuery"
datasource="yourDSN"
username="yourUsername"
password="yourPwd">

select
to_char(trunc(sysdate),'DD-MM-YYYY
HH24:MI:SS') truncated_date
from
dual

</cfquery>

It's important to note that the second parameter of both TO_DATE and TO_CHAR functions is extremely flexible and easy to use. This parameter, formerly called a date mask, allows you to deal with just about any date/time format you may want to use.

Using DECODE
Did you know that Oracle provides a function that behaves similarly to the CFIF tag? If you didn't, look closely at the DECODE function. What makes DECODE so appealing is that you can use it just about anywhere within a SQL statement. Its syntax is:

DECODE(expression,
evaluated_value_1, value_1,
evaluated_value_2, value_2,
evaluated_value_N,value_N,
default_value)
where DECODE returns one of value_1, value_2,..,value_N, or default_value depending on whether expression evaluates to evaluated_ value_1,evaluated_value_2,..,evaluated_value_N, or some other value (treated as the default).

This function is extremely useful in making dynamic assignments in SQL statements. As an example, assume that we pass a numeric month value (1 through 12) via a ColdFusion variable (form.month) to a select statement and, depending on the value of form.month, we want to select the appropriate month from a table containing monthly values. DECODE sets the column name on the fly (see Listing 3).

With the select statement in Listing 3 you've effectively turned the month into a parameter that causes the database to return data from different columns depending on its value.

The other way to achieve the same result would be to use a CFCASE or CFIF - a tedious and expensive option. DECODE is much more elegant, and it also pushes the task on to the database, thereby conserving your ColdFusion resources. Also note that Listing 3 also shows that the default_value is optional.

Ensuring Consistent Capitalization of Returned Values
Sometimes data is inserted into tables with inconsistent capitalization. For instance, some values in a column may have been entered in uppercase whereas others may be in lower. This can look quite ugly when displayed on a Web page. You can ensure consistent capitalization by using the INITCAP and LOWER functions in tandem.

<cfquery name="yourQuery"
datasource="yourDSN"
username="yourUsername"
password="yourPwd">

select
initcap(lower(columnName)) initial_cap
from
yourTable

</cfquery>
Simple String Parsing Using INSTR
and SUBSTR

At times you may want to extract a substring from a character string; however, you only know the characters delimiting the substring, not the numeric start and end positions of the substring. In this case, you can't use the SUBSTR (substring) function directly. However, you can use the INSTR function to get the positions of the delimiting characters and pass these on to the SUBSTR function. An (artificial) example should help make the preceding prose a bit clearer. Here we want to extract the word "whom" from the substring "for.whom.the.bells.toll".
<cfquery name="yourQuery"
datasource="yourDSN"
username="yourUsername"
password="yourPwd">

select
substr('for.whom.the.bells.toll', instr('for.whom.the.bells.toll','.',1,1)+1,
instr('for.whom.the.bells.toll','.',1,2)-instr('for.whom.the.bells.toll','.',1,1)-1)
second_string
from
dual

</cfquery>

Yes, we know you can achieve the same thing using the ColdFusion string function GetToken, but, remember, the idea is to stress your database and save your ColdFusion server.

Concatenating Strings
Sometimes it's necessary to concatenate two or more columns from a table into a single quantity for display purposes. One example of this would be concatenating the employee first name, last name, and ID into a single entity for display on a Web page. The easiest way to do this is via the concatenation operator ||, like so:

<cfquery name="yourQuery"
datasource="yourDSN"
username="yourUsername"
password="yourPwd">

select
employee_ID|| ' ' ||Initcap(lower(first_name)) || ' ' || Initcap(lower(last_name))
emp_name
from
employees

</cfquery>

The query column alias emp_name contains the concatenated information. By the way, the concatenation operator is useful if you want to generate a comma-delimited file for import into Excel, for example. We'll leave this as an exercise for our readers.

Trimming Strings
The Oracle functions LTRIM (left trim) and RTRIM (right trim) are useful in trimming strings. These functions allow you to trim arbitrary characters from the ends of a string, as well as the usual whitespace characters.

<cfquery name="yourQuery"
datasource="yourDSN"
username="yourUsername"
password="yourPwd">

select
ltrim(rtrim('abc012cbb','abc'),'abc') trimmed_string
from
dual

</cfquery>

The first argument of these functions is the string to be trimmed and the second is the trim string, which contains the characters to be trimmed out. Thus, the above query returns the string "012" - the characters "a", "b", and "c" are trimmed out from the left and right ends of the string. If you don't specify the second string, the functions assume you want to trim white-space characters - equivalent to the ColdFusion Ltrim and Rtrim functions.
<cfquery name="yourQuery"
datasource="yourDSN"
username="yourUsername"
password="yourPwd">

select
ltrim(rtrim(' 012 ')) trimmed_string
from
dual

</cfquery>

This returns the string "012". Again this can be done (but shouldn't be) on the ColdFusion server.

Replacing Characters in a String
Trimming strings is fine, but what if you want to remove characters from the middle of a string, or if you want to replace one substring with another. No problem, Oracle offers you the REPLACE function. Here's an example:

<cfquery name="yourQuery"
datasource="yourDSN"
username="yourUsername"
password="yourPwd">
select
replace('01abc2','abc') processed_string
from
dual

</cfquery>

This removes the "abc" in the middle of the first string. Here's an example in which one string is replaced by another:
<cfquery name="yourQuery"
datasource="yourDSN"
username="yourUsername"
password="yourPwd">
select
replace('01abc2','abc','def') processed_string
from
dual

</cfquery>

This replaces "abc" with "def".

Translating Characters in a String
The REPLACE function looks for an exact match of the replace string. What if you want to replace strings based on a character-by-character translation? For this, use the TRANSLATE function, see the following:

<cfquery name="yourQuery"
datasource="yourDSN"
username="yourUsername"
password="yourPwd">
select
translate('01abc2','abc','def') processed_string
from
dual

</cfquery>

What would happen if you change the string to "01acc2", keeping the replace and translate strings the same? Now that you've tried it, you know what TRANSLATE can do for you.

Arithmetic with Nulls
Database practitioners know that nulls in arithmetic expressions yield null results. However, sometimes we'd like to pretend that null values contribute a known value (usually zero) to a sum. For example, when summing a salary and commission column, where the commission column is nullable and the salary is not, we would like the sum to yield the salary in case the commission is null; that is, we'd like to treat a null commission as a zero value. This can be handled using the NVL function, as seen in this query:

<cfquery name="yourQuery"
datasource="yourDSN"
username="yourUsername"
password="yourPwd">

select
2+nvl(null,0)
from
dual

</cfquery>

Warning the User About Data Volume
Assume your application has access to some tables with a large number of records, and the specs of the application require that users be permitted to drill down into the data as deep as they want (yes, it's crazy, but unfortunately some clients do demand such functionality). Before executing the (potentially fatal) queries, you may want to give the end user some feedback regarding the damage he or she is about to cause. The most straightforward way of doing this is by counting the number of records before you submit the query:

<cfquery name="countRecs"
datasource="yourDSN"
username="yourUsername"
password="yourPwd">

select
count(*) total_recs
from
...
</cfquery>

However, the number may not be very significant if you're dealing with wide tables with lots of columns or where the data types of the columns are very large, for example, VARCHAR2(2000). You can actually provide users with a good estimate of the volume of data they're going to slosh about over the intranet/Internet by using the VSIZE function as follows:
<cfquery name="dataSize"
datasource="yourDSN"
username="yourUsername"
password="yourPwd">

select
NVL(VSIZE(column_1),0) + NVL(VSIZE(column_2),0) + ... size_in_bytes
from
...
</cfquery>

VSIZE returns the number of bytes in the internal Oracle representation. The above query sums the total value of bytes in the internal representation for all the columns you've included and the records you've selected. Although this won't be exactly the volume of data to be transferred, it will be extremely close (for example, one character is one byte in Oracle internal representation). You can also use the same technique to prevent users from accessing more than a set volume of data.

Oracle SQL Extensions and Idiosyncrasies
Now that you've seen how Oracle SQL functions can be useful in your work, we'll show how you can avoid some complex ColdFusion coding by using some Oracle-specific SQL extensions and concepts.

Pseudo Columns
Pseudo columns behave identically to normal table columns, however, they're not stored anywhere and can't be updated or inserted. You're probably wondering what the last sentence means, so it's probably best to give an example.

One of the pseudo columns that Oracle makes available to every result set is the ROWNUM. For each row returned by a query, the ROWNUM pseudo column returns a number indicating the order in which Oracle selects the row from a table or a set of joined rows. The first row selected has a ROWNUM of 1, the second has 2, and so on. A frequent requirement is to get the top N records in a table. If you combine the ORDER BY clause in a query with a WHERE clause on the ROWNUM pseudo column, you can easily generate top N hit lists. For example:

<cfquery name="topN"
datasource="yourDSN"
username="yourUsername"
password="yourPwd">

select * from
(select employee_id, salary
from employees order by salary desc)
where
ROWNUM < 11;

</cfquery>

This query returns, very simply, the top 10 salaried employees and their salaries. No additional ColdFusion code is necessary.

This example also shows that the FROM clause doesn't necessarily need to contain a table name; it can even refer to a set of records returned by a subquery.

There's another Oracle pseudo column - ROWID - that's essentially the physical storage address of the row. This can be useful in constructing a SQL statement to detect duplicate rows in a table. We'll leave you to mull over how this can be done.

Outer Joins
Outer joins are useful when you want to display the entire primary key of a reference table, even when the fact table (or referring table) doesn't contain data for all values of the foreign key. A simple example may be useful: consider a PRODUCT table with columns PRODUCT_ID and DESCRIPTION, and a SALES table with columns PRODUCT_ID and VALUE_SOLD. You may want to create a report that displays sales for all available products, including those for which no sales have been made. (Listing 4 shows an outer join.)

The first thing to notice in Listing 4 is the syntax for the outer join, which could be quite different from what you're used to in other database products. The principle is the same, though: the (+) on the SALES side of the join tells Oracle that you want all products returned, including those for which no sales have been made.

Bill of Materials (Hierarchy) Problems
In relational databases, hierarchies are usually represented through self-referential tables - the foreign key refers to the primary key of the same table. An example would be an EMPLOYEES table with EMP_ID as the primary key and MANAGER_ID as the foreign key (referring to EMP_ID). To decompose the hierarchy, join the table to itself as many times as there are levels in the hierarchy. However, the number of levels is usually not known beforehand. Oracle provides an easy way to deal with such a situation through its nonstandard START WITH...CONNECT BY PRIOR clauses (see Listing 5).

The query assumes that the top person in the hierarchy has no manager (manager_id is NULL). Note the use of the LPAD function to offset the records depending on the hierarchy level. The pseudo column LEVEL is always returned by such a query, and, as the name suggests, denotes the hierarchy level of the record.

Locking Records
A problem often encountered when developing a system that allows a user to update or delete data in database tables via the browser is that you want to make sure the user is the only one working on the records. Usually a CF developer will call up the records in question with a SELECT statement and then, based on user input, these records will be modified with a DELETE or UPDATE statement. However, if the records are not locked, another user may modify the data through another session and thus the current user is modifying "old" data.

There are many ways of dealing with such problems, often depending on the context of the transactions in question. One of the simplest ways is to use the SELECTŠFOR UPDATE command. When you write your CFQUERY tag to call up the required records for display, add the FOR UPDATE command at the end of the select statement (see Listing 6). The result is that other database sessions will only be able to view the records in question, not modify them. The FOR UPDATE clause has placed a lock on the complete record set. Only the current user's session will be able to process an UPDATE or DELETE statement on the records. Any other session that attempts to modify the same data will automatically be forced to wait by Oracle. To release the locks and make the data available to other database sessions for modification, you need to send a COMMIT or close the current user's database session. Be careful when using such statements: make sure you lock only the necessary records using the WHERE clause. If not, you may accidentally cause other users to wait for COMMITs on data they're not interested in.

Conclusion
At this point we'd like to wrap up our introductory article in this series about using features of the Oracle RDBMS in your ColdFusion applications. If you haven't yet looked at Oracle in detail, we hope we've managed to arouse your curiosity. The Oracle RDBMS is an extremely complex and efficient engine for storing and manipulating data, and it goes a long way in bringing about the principles set out by Dr. Codd, the founder of relational database theory.

As a ColdFusion developer you're in the privileged position of being able to pick and choose how best to utilize Oracle's rich feature set within your Internet applications. If you're clever about your choices, your applications will go into production quicker and run faster with fewer resources. Learn as much as you can about the database you're using - your users will thank you for your efforts.

By the way, one of the best sources for Oracle information is the Oracle developer's Web site at http://technet.oracle.com, and the free membership provides access to countless resources. Among the most important is the complete documentation for the Oracle RDBMS. You can view it online or download it in PDF format. Like any technical documentation it isn't exactly an inspiring read. However, it's comprehensive, well organized, and chock-full of examples, and is definitely your best bet for information on Oracle.

In Part 2 we'll delve into the procedural extensions that Oracle provides to SQL within the database: PL/SQL and Java.

More Stories By Kailasnath Awati

Kailasnath Awati is senior consultant at Williams & Partner
Management Consulting where he manages the U.S. operations. He is an
experienced Internet/database application developer and architect as
well as an Allaire certified ColdFusion developer.

More Stories By Mario Techera

Mario Techera specializes in Internet application design and
development with relational databases. He is one of the founding
partners of Williams & Partner Management Consulting and works out of
Munich, Germany.

Comments (1)

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.


IoT & Smart Cities Stories
Moroccanoil®, the global leader in oil-infused beauty, is thrilled to announce the NEW Moroccanoil Color Depositing Masks, a collection of dual-benefit hair masks that deposit pure pigments while providing the treatment benefits of a deep conditioning mask. The collection consists of seven curated shades for commitment-free, beautifully-colored hair that looks and feels healthy.
The textured-hair category is inarguably the hottest in the haircare space today. This has been driven by the proliferation of founder brands started by curly and coily consumers and savvy consumers who increasingly want products specifically for their texture type. This trend is underscored by the latest insights from NaturallyCurly's 2018 TextureTrends report, released today. According to the 2018 TextureTrends Report, more than 80 percent of women with curly and coily hair say they purcha...
The textured-hair category is inarguably the hottest in the haircare space today. This has been driven by the proliferation of founder brands started by curly and coily consumers and savvy consumers who increasingly want products specifically for their texture type. This trend is underscored by the latest insights from NaturallyCurly's 2018 TextureTrends report, released today. According to the 2018 TextureTrends Report, more than 80 percent of women with curly and coily hair say they purcha...
We all love the many benefits of natural plant oils, used as a deap treatment before shampooing, at home or at the beach, but is there an all-in-one solution for everyday intensive nutrition and modern styling?I am passionate about the benefits of natural extracts with tried-and-tested results, which I have used to develop my own brand (lemon for its acid ph, wheat germ for its fortifying action…). I wanted a product which combined caring and styling effects, and which could be used after shampo...
The platform combines the strengths of Singtel's extensive, intelligent network capabilities with Microsoft's cloud expertise to create a unique solution that sets new standards for IoT applications," said Mr Diomedes Kastanis, Head of IoT at Singtel. "Our solution provides speed, transparency and flexibility, paving the way for a more pervasive use of IoT to accelerate enterprises' digitalisation efforts. AI-powered intelligent connectivity over Microsoft Azure will be the fastest connected pat...
There are many examples of disruption in consumer space – Uber disrupting the cab industry, Airbnb disrupting the hospitality industry and so on; but have you wondered who is disrupting support and operations? AISERA helps make businesses and customers successful by offering consumer-like user experience for support and operations. We have built the world’s first AI-driven IT / HR / Cloud / Customer Support and Operations solution.
Codete accelerates their clients growth through technological expertise and experience. Codite team works with organizations to meet the challenges that digitalization presents. Their clients include digital start-ups as well as established enterprises in the IT industry. To stay competitive in a highly innovative IT industry, strong R&D departments and bold spin-off initiatives is a must. Codete Data Science and Software Architects teams help corporate clients to stay up to date with the mod...
At CloudEXPO Silicon Valley, June 24-26, 2019, Digital Transformation (DX) is a major focus with expanded DevOpsSUMMIT and FinTechEXPO programs within the DXWorldEXPO agenda. Successful transformation requires a laser focus on being data-driven and on using all the tools available that enable transformation if they plan to survive over the long term. A total of 88% of Fortune 500 companies from a generation ago are now out of business. Only 12% still survive. Similar percentages are found throug...
Druva is the global leader in Cloud Data Protection and Management, delivering the industry's first data management-as-a-service solution that aggregates data from endpoints, servers and cloud applications and leverages the public cloud to offer a single pane of glass to enable data protection, governance and intelligence-dramatically increasing the availability and visibility of business critical information, while reducing the risk, cost and complexity of managing and protecting it. Druva's...
BMC has unmatched experience in IT management, supporting 92 of the Forbes Global 100, and earning recognition as an ITSM Gartner Magic Quadrant Leader for five years running. Our solutions offer speed, agility, and efficiency to tackle business challenges in the areas of service management, automation, operations, and the mainframe.