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.


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.