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

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
SYS-CON Events announced today that DatacenterDynamics has been named “Media Sponsor” of SYS-CON's 18th International Cloud Expo, which will take place on June 7–9, 2016, at the Javits Center in New York City, NY. DatacenterDynamics is a brand of DCD Group, a global B2B media and publishing company that develops products to help senior professionals in the world's most ICT dependent organizations make risk-based infrastructure and capacity decisions.
SYS-CON Events announced today TMCnet has been named “Media Sponsor” of SYS-CON's 18th International Cloud Expo, which will take place on June 7–9, 2016, at the Javits Center in New York City, NY, and the 19th International Cloud Expo, which will take place on November 1–3, 2016, at the Santa Clara Convention Center in Santa Clara, CA. Technology Marketing Corporation (TMC) is the world's leading business-to-business and integrated marketing media company, servicing niche markets within the com...
The IoT has the potential to create a renaissance of manufacturing in the US and elsewhere. In his session at 18th Cloud Expo, Florent Solt, CTO and chief architect of Netvibes, will discuss how the expected exponential increase in the amount of data that will be processed, transported, stored, and accessed means there will be a huge demand for smart technologies to deliver it. Florent Solt is the CTO and chief architect of Netvibes. Prior to joining Netvibes in 2007, he co-founded Rift Technol...
Join IBM June 8 at 18th Cloud Expo at the Javits Center in New York City, NY, and learn how to innovate like a startup and scale for the enterprise. You need to deliver quality applications faster and cheaper, attract and retain customers with an engaging experience across devices, and seamlessly integrate your enterprise systems. And you can't take 12 months to do it.
This is not a small hotel event. It is also not a big vendor party where politicians and entertainers are more important than real content. This is Cloud Expo, the world's longest-running conference and exhibition focused on Cloud Computing and all that it entails. If you want serious presentations and valuable insight about Cloud Computing for three straight days, then register now for Cloud Expo.
IoT device adoption is growing at staggering rates, and with it comes opportunity for developers to meet consumer demand for an ever more connected world. Wireless communication is the key part of the encompassing components of any IoT device. Wireless connectivity enhances the device utility at the expense of ease of use and deployment challenges. Since connectivity is fundamental for IoT device development, engineers must understand how to overcome the hurdles inherent in incorporating multipl...
Machine Learning helps make complex systems more efficient. By applying advanced Machine Learning techniques such as Cognitive Fingerprinting, wind project operators can utilize these tools to learn from collected data, detect regular patterns, and optimize their own operations. In his session at 18th Cloud Expo, Stuart Gillen, Director of Business Development at SparkCognition, will discuss how research has demonstrated the value of Machine Learning in delivering next generation analytics to im...
Manufacturers are embracing the Industrial Internet the same way consumers are leveraging Fitbits – to improve overall health and wellness. Both can provide consistent measurement, visibility, and suggest performance improvements customized to help reach goals. Fitbit users can view real-time data and make adjustments to increase their activity. In his session at @ThingsExpo, Mark Bernardo Professional Services Leader, Americas, at GE Digital, will discuss how leveraging the Industrial Interne...
The paradigm has shifted. A Gartner survey shows that 43% of organizations are using or plan to implement the Internet of Things in 2016. However, not just a handful of companies are still using the old-style ad-hoc trial-and-error ways, unaware of the critical barriers, paint points, traps, and hidden roadblocks. How can you become a winner? In his session at @ThingsExpo, Tony Shan will present a methodical approach to guide the holistic adoption and enablement of IoT implementations. This ov...
SYS-CON Events announced today that Stratoscale, the software company developing the next generation data center operating system, will exhibit at SYS-CON's 18th International Cloud Expo®, which will take place on June 7-9, 2016, at the Javits Center in New York City, NY. Stratoscale is revolutionizing the data center with a zero-to-cloud-in-minutes solution. With Stratoscale’s hardware-agnostic, Software Defined Data Center (SDDC) solution to store everything, run anything and scale everywhere...
Angular 2 is a complete re-write of the popular framework AngularJS. Programming in Angular 2 is greatly simplified – now it's a component-based well-performing framework. This immersive one-day workshop at 18th Cloud Expo, led by Yakov Fain, a Java Champion and a co-founder of the IT consultancy Farata Systems and the product company SuranceBay, will provide you with everything you wanted to know about Angular 2.
Digital payments using wearable devices such as smart watches, fitness trackers, and payment wristbands are an increasing area of focus for industry participants, and consumer acceptance from early trials and deployments has encouraged some of the biggest names in technology and banking to continue their push to drive growth in this nascent market. Wearable payment systems may utilize near field communication (NFC), radio frequency identification (RFID), or quick response (QR) codes and barcodes...
SYS-CON Events announced today that Men & Mice, the leading global provider of DNS, DHCP and IP address management overlay solutions, will exhibit at SYS-CON's 18th International Cloud Expo®, which will take place on June 7-9, 2016, at the Javits Center in New York City, NY. The Men & Mice Suite overlay solution is already known for its powerful application in heterogeneous operating environments, enabling enterprises to scale without fuss. Building on a solid range of diverse platform support,...
You deployed your app with the Bluemix PaaS and it's gaining some serious traction, so it's time to make some tweaks. Did you design your application in a way that it can scale in the cloud? Were you even thinking about the cloud when you built the app? If not, chances are your app is going to break. Check out this webcast to learn various techniques for designing applications that will scale successfully in Bluemix, for the confidence you need to take your apps to the next level and beyond.
The increasing popularity of the Internet of Things necessitates that our physical and cognitive relationship with wearable technology will change rapidly in the near future. This advent means logging has become a thing of the past. Before, it was on us to track our own data, but now that data is automatically available. What does this mean for mHealth and the "connected" body? In her session at @ThingsExpo, Lisa Calkins, CEO and co-founder of Amadeus Consulting, will discuss the impact of wea...
Whether your IoT service is connecting cars, homes, appliances, wearable, cameras or other devices, one question hangs in the balance – how do you actually make money from this service? The ability to turn your IoT service into profit requires the ability to create a monetization strategy that is flexible, scalable and working for you in real-time. It must be a transparent, smoothly implemented strategy that all stakeholders – from customers to the board – will be able to understand and comprehe...
So, you bought into the current machine learning craze and went on to collect millions/billions of records from this promising new data source. Now, what do you do with them? Too often, the abundance of data quickly turns into an abundance of problems. How do you extract that "magic essence" from your data without falling into the common pitfalls? In her session at @ThingsExpo, Natalia Ponomareva, Software Engineer at Google, will provide tips on how to be successful in large scale machine lear...
SYS-CON Events announced today that Ericsson has been named “Gold Sponsor” of SYS-CON's @ThingsExpo, which will take place on June 7-9, 2016, at the Javits Center in New York, New York. Ericsson is a world leader in the rapidly changing environment of communications technology – providing equipment, software and services to enable transformation through mobility. Some 40 percent of global mobile traffic runs through networks we have supplied. More than 1 billion subscribers around the world re...
SYS-CON Events announced today that Fusion, a leading provider of cloud services, will exhibit at SYS-CON's 18th International Cloud Expo®, which will take place on June 7-9, 2016, at the Javits Center in New York City, NY. Fusion, a leading provider of integrated cloud solutions to small, medium and large businesses, is the industry's single source for the cloud. Fusion's advanced, proprietary cloud service platform enables the integration of leading edge solutions in the cloud, including cloud...
The IETF draft standard for M2M certificates is a security solution specifically designed for the demanding needs of IoT/M2M applications. In his session at @ThingsExpo, Brian Romansky, VP of Strategic Technology at TrustPoint Innovation, will explain how M2M certificates can efficiently enable confidentiality, integrity, and authenticity on highly constrained devices.