Click here to close now.

Welcome!

You will be redirected in 30 seconds or close now.

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

Related Topics: ColdFusion

ColdFusion: Article

The Basics of OLEDB Setup

The Basics of OLEDB Setup

First, a confession: I'm a Microsoft SQL Server plebe. Oh, sure, I've written plenty of SQL commands for Access, Foxpro and even an occasional Oracle database, but the needs of more than one client called out for me to tackle SQL Server.

Have you traveled down this road yet? Maybe you've tried to set up an OLEDB connection only to be stymied by the terminology, or perhaps connection failures stopped you? You're not alone. I searched through multiple books, Books Online and the Allaire Forums without success. Judging from the comments of others I met in the forums, setting up an OLEDB connection has caused quite a few programmers to lose their sanity.

Like many ColdFusion programmers, I would normally just turn to ODBC links to handle the connection for me. When I moved into SQL Server, though, I decided to upgrade my skill with the CFQUERY command and take advantage of the multiple databases that the design of SQL Server allows (much more so than Access does). I also wanted to reduce the number of Data Source Names (DSNs) that I had to rely on and remember.

ODBC Limitations
You may already know this, but you can't code an ODBC DSN to use a SQL Server database, other than the default database associated with that DSN. Consider for instance:

<cfquery datasource="vipclient" dbname="pubs" dbtype="ODBC" username="myid"
password="mypsw">SELECT * FROM tblSecurity</cfquery>

This will work just fine if your ODBC link is, indeed, pointing at the "pubs" database in setup and all other items (like username and password) are correct. What if we change the database name, however, as in this example:

<cfquery datasource="vipclient" dbname="personnel" dbtype="ODBC" username="myid"
password="mypsw">SELECT * FROM tblHumans</cfquery>

Now your code will crash with an "Invalid object name tblHumans'" error. Think database security is the problem? Because your ODBC link is set to the "pubs" database, if you change the username and password to the "personnel" database, you'll get a different error: "Cannot open database requested."

Cue the Knight In Shining Armor...
OLEDB, on the other hand, will handle this task with ease. With one OLEDB link and the username and password for each database, you can access all of your SQL Server databases on that server! Unfortunately, you need to jump a hurdle and squish a bug to get it working properly.

Kicking and screaming, I turned a few more hairs gray as I fought to get OLEDB working on my development LAN, a Microsoft Small Business Server 4.5, IIS 4.0 intranet running single-user ColdFusion Server 4.5 and SQL Server 7.0. After I figured out the process, I assisted my ISP in successfully establishing an OLEDB link on their CF Server 4.5. Now let's get your connection working.

Step by Step
To follow along, you'll need access to the ColdFusion Server Administrator. You should already have SQL Server running with at least one database containing at least one table. Finally, make sure you know the username and password for that SQL Server database.

In the ColdFusion Administrator, select OLE DB from the menu. The first input form you receive looks simple enough (see Figure 1), asking only for a DSN and a Provider. After entering the DSN you want to refer to this connection by, make sure "SQLOLEDB" is listed in the provider box and click the Add button.

Problem No. 1
The next form you receive, seen here in Figure 2, is the one that causes the pain. It's unclear just what is being asked for as inputs and there's no useful help information anywhere to be found.

Note: If I may interject here (and do please listen up, Allaire!)....When you have a form that is entirely utilitarian, please put a description of the terms below the form and include both a description of the field and an example of what should go in that field.

Part of the problem is that this form has a bug in it, if I may use that term somewhat loosely. While the DSN carried over fine, the Provider field didn't. The Provider field should have been prefilled in for you with "SQLOLEDB," the option you chose in the first form.

That one piece of information, that little bug, is the first of two obstacles that stand in the way of aspiring OLEDB administrators. Finish filling out this form with a description (not required, but useful for resolving short-term memory lapses), the name of the SQL Server and the database you'll be using the most often.

Problem No. 2
Before you press Update, however, we need to move into the CF Settings area for a moment to resolve the second obstacle: the username and password. Settle down, you security purists; I'll make things right in a bit. Click on the "CF Settings" button to expand the form, then refer to Figure 3.

Enter the username and password to access the SQL Server database, then press the "Update" button. You should have a "Verified" indicator on the right side of your new connection when your list of OLEDB DSNs is displayed. If you want to be absolutely sure that this connection is working, click on the Verify link. Congratulations! You've successfully established an OLEDB link!

Don't Compromise Security!
Feeling a little nervous about having your username and password hard-coded in the connection? You should be. Anyone can easily get access to your database because of this, so let's fix it. Realize, though, that this was a necessary step for us to ensure that our connection was working. You could have left it off and used a <CFQUERY> command to test the link, but as long as we're here, why not make sure?

Return to the list of OLEDB DSNs if you're not there already, then click on your DSN to edit it. Click again on the "CF Settings" button and remove the username and password, then press the "Update" button.

Now when you return to your OLEDB DSN list, you'll see that your connection has failed (see Figure 4). This isn't a problem! It simply means that CF Administrator couldn't verify that the link worked because SQL Server's security is preventing access. This is a good thing.

Your OLEDB connection is finished and ready for use. Remember to supply the username and password whenever you submit a <CFQUERY> using the OLEDB DSN. I would recommend that you keep the DSN, username and password in your Application.cfm file as variables and use the variable names in your CFQUERYs.

Speed Benchmarks?
I first got the idea to pursue OLE- DB from Ben Forta. Ben had used OLEDB connections, of course, but he admitted that setup could be less painful. When I informed him that I'd not only figured out the trick to get OLEDB going but that I would be submitting this article on it for publication as well, he suggested that I run some benchmarks to show speed differences between ODBC and OLEDB.

Well, I did run some comparisons...but my MBA prevents me from reporting those statistics because I didn't adhere to any form of scientific method and time prevents me from going back and doing it all over again! I did find that OLEDB was a little faster than ODBC to the same SQL Server database. Specifying the database name instead of relying on the OLEDB connection to fill in the default database also seemed to speed it up. Perhaps another day we can pursue speed, but I think that's fodder for a new article and outside the scope of this one.

Summary
Speed issues aside, the primary reason that I wanted to get OLEDB up and running was to have a single DSN that could be used to access multiple databases - and OLEDB succeeds in that respect. Now that the mystery is cleared up and the Administrator bug is identified, I'd be willing to bet the OLEDB flood-gates will open to more flexible SQL Server access for a great number of CF programmers.

More Stories By Randy Smith

Randy L. Smith is president/CEO of Midwest Computer Programming and Internet (www.mcpi.com), an Internet/intranet database solution provider based in Hudson, Wisconsin. He has been developing large-scale, Web-based applications for businesses and nonprofits of all sizes, as well as state and federal entities, since 1993. Randy has been working in the computer industry since 1978, and with ColdFusion since 1996.

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
Operational Hadoop and the Lambda Architecture for Streaming Data Apache Hadoop is emerging as a distributed platform for handling large and fast incoming streams of data. Predictive maintenance, supply chain optimization, and Internet-of-Things analysis are examples where Hadoop provides the scalable storage, processing, and analytics platform to gain meaningful insights from granular data that is typically only valuable from a large-scale, aggregate view. One architecture useful for capturing and analyzing streaming data is the Lambda Architecture, representing a model of how to analyze rea...
SYS-CON Events announced today that Vitria Technology, Inc. will exhibit at SYS-CON’s @ThingsExpo, which will take place on June 9-11, 2015, at the Javits Center in New York City, NY. Vitria will showcase the company’s new IoT Analytics Platform through live demonstrations at booth #330. Vitria’s IoT Analytics Platform, fully integrated and powered by an operational intelligence engine, enables customers to rapidly build and operationalize advanced analytics to deliver timely business outcomes for use cases across the industrial, enterprise, and consumer segments.
The explosion of connected devices / sensors is creating an ever-expanding set of new and valuable data. In parallel the emerging capability of Big Data technologies to store, access, analyze, and react to this data is producing changes in business models under the umbrella of the Internet of Things (IoT). In particular within the Insurance industry, IoT appears positioned to enable deep changes by altering relationships between insurers, distributors, and the insured. In his session at @ThingsExpo, Michael Sick, a Senior Manager and Big Data Architect within Ernst and Young's Financial Servi...
SYS-CON Events announced today that Open Data Centers (ODC), a carrier-neutral colocation provider, will exhibit at SYS-CON's 16th International Cloud Expo®, which will take place June 9-11, 2015, at the Javits Center in New York City, NY. Open Data Centers is a carrier-neutral data center operator in New Jersey and New York City offering alternative connectivity options for carriers, service providers and enterprise customers.
When it comes to the Internet of Things, hooking up will get you only so far. If you want customers to commit, you need to go beyond simply connecting products. You need to use the devices themselves to transform how you engage with every customer and how you manage the entire product lifecycle. In his session at @ThingsExpo, Sean Lorenz, Technical Product Manager for Xively at LogMeIn, will show how “product relationship management” can help you leverage your connected devices and the data they generate about customer usage and product performance to deliver extremely compelling and reliabl...
SYS-CON Events announced today that CodeFutures, a leading supplier of database performance tools, has been named a “Sponsor” of SYS-CON's 16th International Cloud Expo®, which will take place on June 9–11, 2015, at the Javits Center in New York, NY. CodeFutures is an independent software vendor focused on providing tools that deliver database performance tools that increase productivity during database development and increase database performance and scalability during production.
The IoT market is projected to be $1.9 trillion tidal wave that’s bigger than the combined market for smartphones, tablets and PCs. While IoT is widely discussed, what not being talked about are the monetization opportunities that are created from ubiquitous connectivity and the ensuing avalanche of data. While we cannot foresee every service that the IoT will enable, we should future-proof operations by preparing to monetize them with extremely agile systems.
There’s Big Data, then there’s really Big Data from the Internet of Things. IoT is evolving to include many data possibilities like new types of event, log and network data. The volumes are enormous, generating tens of billions of logs per day, which raise data challenges. Early IoT deployments are relying heavily on both the cloud and managed service providers to navigate these challenges. Learn about IoT, Big Data and deployments processing massive data volumes from wearables, utilities and other machines.
The explosion of connected devices / sensors is creating an ever-expanding set of new and valuable data. In parallel the emerging capability of Big Data technologies to store, access, analyze, and react to this data is producing changes in business models under the umbrella of the Internet of Things (IoT). In particular within the Insurance industry, IoT appears positioned to enable deep changes by altering relationships between insurers, distributors, and the insured. In his session at @ThingsExpo, Michael Sick, a Senior Manager and Big Data Architect within Ernst and Young's Financial Servi...
“In the past year we've seen a lot of stabilization of WebRTC. You can now use it in production with a far greater degree of certainty. A lot of the real developments in the past year have been in things like the data channel, which will enable a whole new type of application," explained Peter Dunkley, Technical Director at Acision, in this SYS-CON.tv interview at @ThingsExpo, held Nov 4–6, 2014, at the Santa Clara Convention Center in Santa Clara, CA.
SYS-CON Events announced today that Intelligent Systems Services will exhibit at SYS-CON's 16th International Cloud Expo®, which will take place on June 9-11, 2015, at the Javits Center in New York City, NY. Established in 1994, Intelligent Systems Services Inc. is located near Washington, DC, with representatives and partners nationwide. ISS’s well-established track record is based on the continuous pursuit of excellence in designing, implementing and supporting nationwide clients’ mission-critical systems. ISS has completed many successful projects in Healthcare, Commercial, Manufacturing, ...
PubNub on Monday has announced that it is partnering with IBM to bring its sophisticated real-time data streaming and messaging capabilities to Bluemix, IBM’s cloud development platform. “Today’s app and connected devices require an always-on connection, but building a secure, scalable solution from the ground up is time consuming, resource intensive, and error-prone,” said Todd Greene, CEO of PubNub. “PubNub enables web, mobile and IoT developers building apps on IBM Bluemix to quickly add scalable realtime functionality with minimal effort and cost.”
The major cloud platforms defy a simple, side-by-side analysis. Each of the major IaaS public-cloud platforms offers their own unique strengths and functionality. Options for on-site private cloud are diverse as well, and must be designed and deployed while taking existing legacy architecture and infrastructure into account. Then the reality is that most enterprises are embarking on a hybrid cloud strategy and programs. In this Power Panel at 15th Cloud Expo (http://www.CloudComputingExpo.com), moderated by Ashar Baig, Research Director, Cloud, at Gigaom Research, Nate Gordon, Director of T...
Sensor-enabled things are becoming more commonplace, precursors to a larger and more complex framework that most consider the ultimate promise of the IoT: things connecting, interacting, sharing, storing, and over time perhaps learning and predicting based on habits, behaviors, location, preferences, purchases and more. In his session at @ThingsExpo, Tom Wesselman, Director of Communications Ecosystem Architecture at Plantronics, will examine the still nascent IoT as it is coalescing, including what it is today, what it might ultimately be, the role of wearable tech, and technology gaps stil...
DevOps tends to focus on the relationship between Dev and Ops, putting an emphasis on the ops and application infrastructure. But that’s changing with microservices architectures. In her session at DevOps Summit, Lori MacVittie, Evangelist for F5 Networks, will focus on how microservices are changing the underlying architectures needed to scale, secure and deliver applications based on highly distributed (micro) services and why that means an expansion into “the network” for DevOps.
With several hundred implementations of IoT-enabled solutions in the past 12 months alone, this session will focus on experience over the art of the possible. Many can only imagine the most advanced telematics platform ever deployed, supporting millions of customers, producing tens of thousands events or GBs per trip, and hundreds of TBs per month. With the ability to support a billion sensor events per second, over 30PB of warm data for analytics, and hundreds of PBs for an data analytics archive, in his session at @ThingsExpo, Jim Kaskade, Vice President and General Manager, Big Data & Ana...
For years, we’ve relied too heavily on individual network functions or simplistic cloud controllers. However, they are no longer enough for today’s modern cloud data center. Businesses need a comprehensive platform architecture in order to deliver a complete networking suite for IoT environment based on OpenStack. In his session at @ThingsExpo, Dhiraj Sehgal from PLUMgrid will discuss what a holistic networking solution should really entail, and how to build a complete platform that is scalable, secure, agile and automated.
We’re no longer looking to the future for the IoT wave. It’s no longer a distant dream but a reality that has arrived. It’s now time to make sure the industry is in alignment to meet the IoT growing pains – cooperate and collaborate as well as innovate. In his session at @ThingsExpo, Jim Hunter, Chief Scientist & Technology Evangelist at Greenwave Systems, will examine the key ingredients to IoT success and identify solutions to challenges the industry is facing. The deep industry expertise behind this presentation will provide attendees with a leading edge view of rapidly emerging IoT oppor...
In the consumer IoT, everything is new, and the IT world of bits and bytes holds sway. But industrial and commercial realms encompass operational technology (OT) that has been around for 25 or 50 years. This grittier, pre-IP, more hands-on world has much to gain from Industrial IoT (IIoT) applications and principles. But adding sensors and wireless connectivity won’t work in environments that demand unwavering reliability and performance. In his session at @ThingsExpo, Ron Sege, CEO of Echelon, will discuss how as enterprise IT embraces other IoT-related technology trends, enterprises with i...
When it comes to the Internet of Things, hooking up will get you only so far. If you want customers to commit, you need to go beyond simply connecting products. You need to use the devices themselves to transform how you engage with every customer and how you manage the entire product lifecycle. In his session at @ThingsExpo, Sean Lorenz, Technical Product Manager for Xively at LogMeIn, will show how “product relationship management” can help you leverage your connected devices and the data they generate about customer usage and product performance to deliver extremely compelling and reliabl...