Welcome!

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
Enthusiasm for the Internet of Things has reached an all-time high. In 2013 alone, venture capitalists spent more than $1 billion dollars investing in the IoT space. With "smart" appliances and devices, IoT covers wearable smart devices, cloud services to hardware companies. Nest, a Google company, detects temperatures inside homes and automatically adjusts it by tracking its user's habit. These technologies are quickly developing and with it come challenges such as bridging infrastructure gaps, abiding by privacy concerns and making the concept a reality. These challenges can't be addressed w...
The Domain Name Service (DNS) is one of the most important components in networking infrastructure, enabling users and services to access applications by translating URLs (names) into IP addresses (numbers). Because every icon and URL and all embedded content on a website requires a DNS lookup loading complex sites necessitates hundreds of DNS queries. In addition, as more internet-enabled ‘Things' get connected, people will rely on DNS to name and find their fridges, toasters and toilets. According to a recent IDG Research Services Survey this rate of traffic will only grow. What's driving t...
Connected devices and the Internet of Things are getting significant momentum in 2014. In his session at Internet of @ThingsExpo, Jim Hunter, Chief Scientist & Technology Evangelist at Greenwave Systems, examined three key elements that together will drive mass adoption of the IoT before the end of 2015. The first element is the recent advent of robust open source protocols (like AllJoyn and WebRTC) that facilitate M2M communication. The second is broad availability of flexible, cost-effective storage designed to handle the massive surge in back-end data in a world where timely analytics is e...
Scott Jenson leads a project called The Physical Web within the Chrome team at Google. Project members are working to take the scalability and openness of the web and use it to talk to the exponentially exploding range of smart devices. Nearly every company today working on the IoT comes up with the same basic solution: use my server and you'll be fine. But if we really believe there will be trillions of these devices, that just can't scale. We need a system that is open a scalable and by using the URL as a basic building block, we open this up and get the same resilience that the web enjoys.
We are reaching the end of the beginning with WebRTC, and real systems using this technology have begun to appear. One challenge that faces every WebRTC deployment (in some form or another) is identity management. For example, if you have an existing service – possibly built on a variety of different PaaS/SaaS offerings – and you want to add real-time communications you are faced with a challenge relating to user management, authentication, authorization, and validation. Service providers will want to use their existing identities, but these will have credentials already that are (hopefully) i...
"Matrix is an ambitious open standard and implementation that's set up to break down the fragmentation problems that exist in IP messaging and VoIP communication," explained John Woolf, Technical Evangelist at Matrix, in this SYS-CON.tv interview at @ThingsExpo, held Nov 4–6, 2014, at the Santa Clara Convention Center in Santa Clara, CA.
How do APIs and IoT relate? The answer is not as simple as merely adding an API on top of a dumb device, but rather about understanding the architectural patterns for implementing an IoT fabric. There are typically two or three trends: Exposing the device to a management framework Exposing that management framework to a business centric logic Exposing that business layer and data to end users. This last trend is the IoT stack, which involves a new shift in the separation of what stuff happens, where data lives and where the interface lies. For instance, it's a mix of architectural styles ...
The Internet of Things will put IT to its ultimate test by creating infinite new opportunities to digitize products and services, generate and analyze new data to improve customer satisfaction, and discover new ways to gain a competitive advantage across nearly every industry. In order to help corporate business units to capitalize on the rapidly evolving IoT opportunities, IT must stand up to a new set of challenges. In his session at @ThingsExpo, Jeff Kaplan, Managing Director of THINKstrategies, will examine why IT must finally fulfill its role in support of its SBUs or face a new round of...
Cultural, regulatory, environmental, political and economic (CREPE) conditions over the past decade are creating cross-industry solution spaces that require processes and technologies from both the Internet of Things (IoT), and Data Management and Analytics (DMA). These solution spaces are evolving into Sensor Analytics Ecosystems (SAE) that represent significant new opportunities for organizations of all types. Public Utilities throughout the world, providing electricity, natural gas and water, are pursuing SmartGrid initiatives that represent one of the more mature examples of SAE. We have s...
The Internet of Things will greatly expand the opportunities for data collection and new business models driven off of that data. In her session at @ThingsExpo, Esmeralda Swartz, CMO of MetraTech, discussed how for this to be effective you not only need to have infrastructure and operational models capable of utilizing this new phenomenon, but increasingly service providers will need to convince a skeptical public to participate. Get ready to show them the money!
One of the biggest challenges when developing connected devices is identifying user value and delivering it through successful user experiences. In his session at Internet of @ThingsExpo, Mike Kuniavsky, Principal Scientist, Innovation Services at PARC, described an IoT-specific approach to user experience design that combines approaches from interaction design, industrial design and service design to create experiences that go beyond simple connected gadgets to create lasting, multi-device experiences grounded in people's real needs and desires.
P2P RTC will impact the landscape of communications, shifting from traditional telephony style communications models to OTT (Over-The-Top) cloud assisted & PaaS (Platform as a Service) communication services. The P2P shift will impact many areas of our lives, from mobile communication, human interactive web services, RTC and telephony infrastructure, user federation, security and privacy implications, business costs, and scalability. In his session at @ThingsExpo, Robin Raymond, Chief Architect at Hookflash, will walk through the shifting landscape of traditional telephone and voice services ...
The Internet of Things is tied together with a thin strand that is known as time. Coincidentally, at the core of nearly all data analytics is a timestamp. When working with time series data there are a few core principles that everyone should consider, especially across datasets where time is the common boundary. In his session at Internet of @ThingsExpo, Jim Scott, Director of Enterprise Strategy & Architecture at MapR Technologies, discussed single-value, geo-spatial, and log time series data. By focusing on enterprise applications and the data center, he will use OpenTSDB as an example t...
Explosive growth in connected devices. Enormous amounts of data for collection and analysis. Critical use of data for split-second decision making and actionable information. All three are factors in making the Internet of Things a reality. Yet, any one factor would have an IT organization pondering its infrastructure strategy. How should your organization enhance its IT framework to enable an Internet of Things implementation? In his session at Internet of @ThingsExpo, James Kirkland, Chief Architect for the Internet of Things and Intelligent Systems at Red Hat, described how to revolutioniz...
Bit6 today issued a challenge to the technology community implementing Web Real Time Communication (WebRTC). To leap beyond WebRTC’s significant limitations and fully leverage its underlying value to accelerate innovation, application developers need to consider the entire communications ecosystem.
The definition of IoT is not new, in fact it’s been around for over a decade. What has changed is the public's awareness that the technology we use on a daily basis has caught up on the vision of an always on, always connected world. If you look into the details of what comprises the IoT, you’ll see that it includes everything from cloud computing, Big Data analytics, “Things,” Web communication, applications, network, storage, etc. It is essentially including everything connected online from hardware to software, or as we like to say, it’s an Internet of many different things. The difference ...
Cloud Expo 2014 TV commercials will feature @ThingsExpo, which was launched in June, 2014 at New York City's Javits Center as the largest 'Internet of Things' event in the world.
SYS-CON Events announced today that Windstream, a leading provider of advanced network and cloud communications, has been named “Silver 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. Windstream (Nasdaq: WIN), a FORTUNE 500 and S&P 500 company, is a leading provider of advanced network communications, including cloud computing and managed services, to businesses nationwide. The company also offers broadband, phone and digital TV services to consumers primarily in rural areas.
"There is a natural synchronization between the business models, the IoT is there to support ,” explained Brendan O'Brien, Co-founder and Chief Architect of Aria Systems, in this SYS-CON.tv interview at the 15th International Cloud Expo®, held Nov 4–6, 2014, at the Santa Clara Convention Center in Santa Clara, CA.
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...