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

Stored Procedures In Access

Stored Procedures In Access

While many experienced CF developers will spurn the notion of creating a production CF application with a back-end database in Access, there are far more developers who for one reason or another proceed to - or simply have to - do so.

If you've got to deal with Access, you may as well deal with it in the most effective manner possible. This article introduces a significant new feature for Access developers that will bring dramatic improvements in query processing performance as well as modularization of code. In the bargain, you'll find it significantly eases upsizing from Access, or may enable development of one code base to execute effectively against both Access and an enterprise version of a database.

Bring on the Stored Procedures
One of the greatest benefits of using a "real" database management system such as Oracle, MS SQL Server, Ingres or DB/2 is the ability to leverage "stored procedures" or SPs. An SP is a set of SQL statements stored in the database rather than passed on each request within a CFQUERY tag. Later I'll discuss the many performance benefits of this approach.

To execute such a stored procedure, the traditional way would be to call the named SP in the CFQUERY as follows:

<CFQUERY DATASOURCE=--->
{CALL spname(param1,param2)}
</CFQUERY>

Until recently this approach wasn't available to CF developers coding against an Access database because Access doesn't support stored procedures per se. .Some developers may have noticed, though, that they could indeed execute Access "queries" using this CALL statement. An Access "query" is more akin to a traditional SQL "view." They're accessible in the Access interface by using the "Queries" tab next to "tables" when viewing a database (see Figure 1).

These "queries" are indeed a stored set of SQL code, but using the {CALL} syntax above, one can't pass parameters to them. CF 4.0 introduces a new means of calling SPs, CFSTOREDPROC and two associated tags. While it's not documented, we've discovered that one can indeed call Access queries as if they were stored procs and can even pass parameters to them. For folks using Access, this is a significant discovery. Allaire recently started documenting this discovery in their Advanced CF Development class.

The Benefits of Stored Procedures
Why would any developer, using Access or not, want to consider the SP approach? Again, with SPs, the SQL is stored in the DBMS rather than being passed to the database on every CFQUERY call (often referred to as "pass-through SQL"). Using SPs has several benefits over pass-through SQLs.

Most significant is that the DBMS precompiles the SQL in the SP when the SP is created and reuses that compilation until the SP is changed; pass-through SQL is compiled every time it's executed. Just the reduced compile effort alone can bring dramatic performance improvements in the course of a query that's executed hundreds or thousands of times a day. There's also the minor benefit of reduced network traffic - a call to an SP in a CFQUERY typically requires far fewer characters than spelling out all the SQL in a normal SQL query.

Also important is that in most larger DBMSs the compilation of the SQL in the SP will result in creation of a data-access plan based on indexes and other data characteristics, resulting in faster query execution. A pass-through query doesn't benefit from this optimization. Because of this, most developers choose to use SPs in enterprise DBMSs.

 


Another benefit is that using SPs to hold SQL is a more modular programming approach, bringing all the attendant benefits of code reuse. This is especially useful if the query can be reused in multiple applications or templates. (Some programmers never consider reuse until they have access to SPs, making this another compelling benefit.)

Finally, in most DBMSs you can also impose security over use and editing of SPs as well as implement version control. (This latter benefit won't translate to Access, but that doesn't diminish the other significant benefits.)

SPs are a hallmark of a professionally developed production application, yet Access developers have been basically precluded from using the approach. Now that the CFSTOREDPROC tag can call and pass parameters to an Access query, you merely need to learn how to set up those queries and then call them.

Setting up "SPs" in Access
When you open a database in the native Access interface, several tabs show the tables in the database, etc., as shown previously in Figure 1. To create something similar to stored procedures in Access, you'll want to create what Access calls queries or "parameter queries." These are accessed using the "Queries" tab next to the table tab. Let's walk through creating an Access query. Figure 2 shows the steps used to begin creating the GetBeans query.

Selecting the Queries tab shows any previously defined queries. Using the New button available to the right of the screen, we can create a new query. (The "design" button is used to edit queries, and "open" is used to run them from within Access.) In the list of choices presented next, choose the default "design view" and press OK. When you choose to create or edit a query in Access, it opens a visual query builder similar to that in Studio; the first prompt is to choose the tables to be used in the query.

 


You can use that feature to build a query, but more likely you'll want to copy in some SQL you're currently using in a CFQUERY. Let's assume you want to do the latter. Close the table selector window to see the display in Figure 3.

Choose "View>SQL View"; you'll be presented with a blank screen on which to build the SQL for your query. Here you can enter SQL by hand or copy it from an existing CFQUERY. If we entered some simple SQL and stopped at that, this query, once saved, could then be called and executed using either the {CALL} approach or the CFSTOREDPROC tag.

If you intend to pass in parameters to dynamically control the processing of the query, you'll need to create what Access refers to as "parameters," then use the CFSTOREDPROC tag to execute the query.

See Figure 4 for an example of such a parameter query.

The parameter is enclosed in square brackets (get_bean_id in our example). Normally this parameter is a string of words that, when the query is executed within the native Access interface or related Access programming languages, form a prompt shown to the user. After the user enters a value in response to the prompt, the value is substituted in the query for the parameter. That's interesting, but not what we want at all. Instead, we're going to pass in the value of that parameter on the call to the query in the CFSTOREDPROC (specifically on the CFPROCPARAM). Thus, when naming the parameter, we should choose a single word rather than several words to form a prompt.

Save the query using File>Save and give it a name (following the conventions for naming a table in SQL), which will be the name we use to call it in the CFSTOREDPROC. Calling SPs (Whether in Access or Other DBMSs)

 

The CFSTOREDPROC tag, usually reserved for use with SPs, names the stored procedure to be executed. We can now use it to execute the Access query. CFSTOREDPROC takes parameters naming the data source and the SP, plus a username and password if appropriate.

The related CFPROCPARAM tag has several attributes naming the parameters to be passed into the SP, indicating their name, value, data type, etc. We can pass in any number of these parameters, with additional CFPROCPARAM tags.

CFPROCRESULT gives a name to the resulting record set, similar to the name we'd normally give to a CFQUERY, so we can refer to the record set later in the template. (This tag also has a "resultset" attribute, used only in DBMSs like Oracle that can return multiple result sets in a single SP call.)

Note that CFPROCPARAM and CFPROCRESULT are subtags to the CF-STOREDPROC tag. In addition, a "type" attribute must be specified in the CFPROCPARAM tag to indicate whether the parameter is being passed data into or out of the SP. The manual says this isn't required and should default to "in," but testing in both 4.0 and 4.01 suggests this isn't true, at least for an ODBC connection to Access.

That's it. Executing the new tags as shown above will result in your being able to process the resulting record set as if you had coded the SQL in a CFQUERY tag. There are several benefits to using this approach, which are reviewed later.

 


Things to Keep in Mind
While this trick in using Access is nifty, be aware that it doesn't really open up the full range of complex programmatic SQL typically available in full-fledged DBMSs like Oracle or SQL Server. You can't specify any sort of conditional logic in the query, nor does Access support use of temporary tables. You can only perform "simple" SQL, which you could have done in a CFQUERY. (There may be some interesting new SQL opportunities introduced by this approach that we have yet to explore.)

A more important point is that you may find you can't use this approach against an existing Access data source in your environment. If, when you attempt to run a query like the one in Figure 5, you get an error like that shown in Figure 6, you may need to make an alteration to the data source. Though many may not realize it, there is in fact a set of options that can control the type of SQL actions permitted against a given data source (including insert, update and delete statements, as well as execution of stored procedures). When Access data sources are turned off, they have the option to execute stored procedures by default. You may find that support for stored procedures has been disabled this way for your data source. You can easily turn that on by editing the data source in the CF administrator and choosing the "CF settings" button, where these choices are available. The way this interface works can be a little confusing: if any of the choices are selected, only the checked statements are allowed. If none are checked, then any sort of SQL statement is allowed. Either check the "stored procedures" option or remove all the other choices, as appropriate to your security needs.

Another challenge is that it can be more difficult to debug SQL errors occurring within stored procedures (using any DBMS). CFQUERY error messages normally show the actual SQL statement in error, but this isn't the case with SPs. (Despite there being a DEBUG parameter on CFSTOREDPROC, it had no effect on this issue in testing.)

 


There are also some specific issues in using Access queries as SPs. First, when building Access queries, the table or column name can't be parameter driven. In other words, you can't pass in a column name as a parameter to the SP to make the query more dynamic. Second, be aware that testing has shown you can't give a parameter (the item enclosed in brackets) the same name as the column. While that would seem to be a trivial issue, there's a gotcha. It doesn't give an error. Instead, I've found that in a SELECT statement with this mistake, Access simply interprets the parameter as the column name, resulting in the intended criteria being an equality all the time!

That is, if the column were named "bean_id" and the parameter were also named "[bean_id]", any value passed in CFPROCPARAM for bean_id would be ignored. The statement would be compiled as bean_id=bean_id, which evaluates to true. While it doesn't fail, it's as if the criteria weren't there at all. This could be a serious problem if you're not careful.

Finally, you might think that since Access queries are really like SQL views, you could create them using the SQL DDL statement, CREATE VIEW or, better yet, the CREATE PROCEDURE statement. You'd be half right. You can use the CREATE VIEW command (as long as there are no restrictions in the data source definition regarding the SQL that's allowed, as described above). But you can't use the CREATE PROCEDURE command against an Access database.

Benefits of SPs in Access
All that said, the benefits of using this SP approach in developing CF applications against an Access database are substantial and worth the effort.

 


Improved Performance
Since you're not passing SQL from CF to the database engine, the same query executed as an SP will likely run faster. (Even with CF and the Access .mdb file on the same machine, there's still ODBC communications overhead in even the simplest SQL.)

More important, since the SQL is compiled in the database when the query is saved, there's possibly a substantial reduction in the processing time to execute the query. We've reduced the processing time up to eight times in some queries.

Modularization of Code
You can store common queries that are frequently accessed in the database rather than repeating them within several templates. (It's true that the same benefit could be simulated with a CFINCLUDE holding the SQL, but that wouldn't get the performance benefits above.)

Cross-Platform Development
Finally, using this approach, you can now develop and test an application against an Access database that will be run in production against a SQL Server or a similar database. In most cases the use of stored procedures on a real DBMS will bring significant performance benefits due to the compiling of an access plan, etc. Now the same CF code calling SPs can be run in both platforms (assuming, of course, that you're not leveraging any conditional logic or temp tables that can't be replicated in the Access query).

In a related manner, if you're developing an application that may be deployed on multiple DBMS platforms, you're no longer restricted to suffering the performance pinch of using pass-through SQL on all DB calls. You can code the application to use SPs, and now you know that the benefits of improved performance will translate to all implementations of the application.

It's worth noting, though, that in testing we've found that a very simple SQL statement (perhaps a select returning a single row without a join) executed using the SP approach may actually take just slightly longer than a normal CFQUERY. There is some overhead in executing the SQL as an Access query. However, this overhead is far exceeded by the performance gains on most queries. You may want to test a given query before committing to one approach or the other; the many other benefits of using the SP approach for all your SQL coding may still outweigh any slight performance penalty on a given query.

Now, at least, you know what's possible. Happy coding, and may this be of benefit to all you developers doing production work in Access!

More Stories By Charlie Arehart

A veteran ColdFusion developer since 1997, Charlie Arehart is a long-time contributor to the community and a recognized Adobe Community Expert. He's a certified Advanced CF Developer and Instructor for CF 4/5/6/7 and served as tech editor of CFDJ until 2003. Now an independent contractor (carehart.org) living in Alpharetta, GA, Charlie provides high-level troubleshooting/tuning assistance and training/mentoring for CF teams. He helps run the Online ColdFusion Meetup (coldfusionmeetup.com, an online CF user group), is a contributor to the CF8 WACK books by Ben Forta, and is frequently invited to speak at developer conferences and user groups worldwide.

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
SYS-CON Events announced today that CollabNet, a global leader in enterprise software development, release automation and DevOps solutions, will be a Bronze Sponsor of SYS-CON's 20th International Cloud Expo®, taking place from June 6-8, 2017, at the Javits Center in New York City, NY. CollabNet offers a broad range of solutions with the mission of helping modern organizations deliver quality software at speed. The company’s latest innovation, the DevOps Lifecycle Manager (DLM), supports Value S...
The Internet of Things is clearly many things: data collection and analytics, wearables, Smart Grids and Smart Cities, the Industrial Internet, and more. Cool platforms like Arduino, Raspberry Pi, Intel's Galileo and Edison, and a diverse world of sensors are making the IoT a great toy box for developers in all these areas. In this Power Panel at @ThingsExpo, moderated by Conference Chair Roger Strukhoff, panelists discussed what things are the most important, which will have the most profound e...
Multiple data types are pouring into IoT deployments. Data is coming in small packages as well as enormous files and data streams of many sizes. Widespread use of mobile devices adds to the total. In this power panel at @ThingsExpo, moderated by Conference Chair Roger Strukhoff, panelists will look at the tools and environments that are being put to use in IoT deployments, as well as the team skills a modern enterprise IT shop needs to keep things running, get a handle on all this data, and deli...
The explosion of new web/cloud/IoT-based applications and the data they generate are transforming our world right before our eyes. In this rush to adopt these new technologies, organizations are often ignoring fundamental questions concerning who owns the data and failing to ask for permission to conduct invasive surveillance of their customers. Organizations that are not transparent about how their systems gather data telemetry without offering shared data ownership risk product rejection, regu...
SYS-CON Events announced today that Grape Up will exhibit at SYS-CON's 21st International Cloud Expo®, which will take place on Oct. 31 – Nov 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA. Grape Up is a software company specializing in cloud native application development and professional services related to Cloud Foundry PaaS. With five expert teams that operate in various sectors of the market across the U.S. and Europe, Grape Up works with a variety of customers from emergi...
The age of Digital Disruption is evolving into the next era – Digital Cohesion, an age in which applications securely self-assemble and deliver predictive services that continuously adapt to user behavior. Information from devices, sensors and applications around us will drive services seamlessly across mobile and fixed devices/infrastructure. This evolution is happening now in software defined services and secure networking. Four key drivers – Performance, Economics, Interoperability and Trust ...
@ThingsExpo has been named the Most Influential ‘Smart Cities - IIoT' Account and @BigDataExpo has been named fourteenth by Right Relevance (RR), which provides curated information and intelligence on approximately 50,000 topics. In addition, Right Relevance provides an Insights offering that combines the above Topics and Influencers information with real time conversations to provide actionable intelligence with visualizations to enable decision making. The Insights service is applicable to eve...
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.
Cybersecurity is a critical component of software development in many industries including medical devices. However, code is not always written to be robust or secure from the unknown or the unexpected. This gap can make medical devices susceptible to cybersecurity attacks ranging from compromised personal health information to life-sustaining treatment. In his session at @ThingsExpo, Clark Fortney, Software Engineer at Battelle, will discuss how programming oversight using key methods can incre...
Most technology leaders, contemporary and from the hardware era, are reshaping their businesses to do software in the hope of capturing value in IoT. Although IoT is relatively new in the market, it has already gone through many promotional terms such as IoE, IoX, SDX, Edge/Fog, Mist Compute, etc. Ultimately, irrespective of the name, it is about deriving value from independent software assets participating in an ecosystem as one comprehensive solution.
The 20th International Cloud Expo has announced that its Call for Papers is open. Cloud Expo, to be held June 6-8, 2017, at the Javits Center in New York City, brings together Cloud Computing, Big Data, Internet of Things, DevOps, Containers, Microservices 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 business opportunity. Submit your speaking proposal ...
SYS-CON Events announced today that Juniper Networks (NYSE: JNPR), an industry leader in automated, scalable and secure networks, 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. Juniper Networks challenges the status quo with products, solutions and services that transform the economics of networking. The company co-innovates with customers and partners to deliver automated, scalable and secure network...
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...
SYS-CON Events announced today that Hitachi, the leading provider the Internet of Things and Digital Transformation, 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. Hitachi Data Systems, a wholly owned subsidiary of Hitachi, Ltd., offers an integrated portfolio of services and solutions that enable digital transformation through enhanced data management, governance, mobility and analytics. We help globa...
SYS-CON Events announced today that T-Mobile 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. As America's Un-carrier, T-Mobile US, Inc., is redefining the way consumers and businesses buy wireless services through leading product and service innovation. The Company's advanced nationwide 4G LTE network delivers outstanding wireless experiences to 67.4 million customers who are unwilling to compromise on ...
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...
20th Cloud Expo, taking place June 6-8, 2017, at the Javits Center in New York City, NY, will feature technical sessions from a rock star conference faculty and the leading industry players in the world. Cloud computing is now being embraced by a majority of enterprises of all sizes. Yesterday's debate about public vs. private has transformed into the reality of hybrid cloud: a recent survey shows that 74% of enterprises have a hybrid cloud strategy.
With major technology companies and startups seriously embracing IoT strategies, now is the perfect time to attend @ThingsExpo 2016 in New York. Learn what is going on, contribute to the discussions, and ensure that your enterprise is as "IoT-Ready" as it can be! Internet of @ThingsExpo, taking place June 6-8, 2017, at the Javits Center in New York City, New York, is co-located with 20th Cloud Expo and will feature technical sessions from a rock star conference faculty and the leading industry p...
With major technology companies and startups seriously embracing Cloud strategies, now is the perfect time to attend @CloudExpo | @ThingsExpo, June 6-8, 2017, at the Javits Center in New York City, NY and October 31 - November 2, 2017, Santa Clara Convention Center, CA. Learn what is going on, contribute to the discussions, and ensure that your enterprise is on the right path to Digital Transformation.
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.