"Cold Fusion provides a simple, powerful
and flexible way of developing dynamic,
database-driven web sites without the need
for sophisticated programming." No,
I didn't forget to run my spell checker.
This quote comes from an Allaire website
archive, back in the days when it was
spelled "Cold Fusion."
It's amazing to consider, given the amount
of change that ColdFusion has gone through
since its conception, that the ability
to easily connect to and work with databases
is still one its greatest features. And
since the content for this Developer Center
launch is all about databases, it's a good
time to talk about the various aspects
involved in using databases with ColdFusion.
Getting Started
A good place to start is here in DevNet,
with the Database
Topic Center and the Get
Started with ColdFusion MX page. Whether
you're new to databases or just to ColdFusion,
there are several articles that explain
why you might want to have a dynamic web
site and how to get started.
Also helpful are the various tutorials
within the Support Centers. The Wildlife
Project, for instance, teaches you how
to plan, design, create, and deploy a fully-functional
website using Studio MX. And, if you're
looking for a more traditional approach,
we have books from Macromedia
Press, online product courses through
the Macromedia University,
and instructor-led
classes from our Authorized
Training Partners.
ODBC or JDBC?
Historically, ODBC was the most prevalent
method of connecting to a database in ColdFusion.
When Macromedia introduced ColdFusion MX
on the Java platform, JDBC became the primary
way to connect ColdFusion to a database.
JDBC is the Java API that ColdFusion uses
to execute SQL statements and is available
in 4
types. ColdFusion MX comes with JDBC
type 4 drivers from DataDirect for the most
popular databases. But even if ColdFusion
MX doesn't include a type 4 driver for your
particular database, you can typically get
them from a database vendor. Use them whenever
possible! To find a driver for your database,
visit industry.java.sun.com/products/jdbc/drivers
or your database vendor website.
Two other options are also available, the
DataDirect SequeLink ODBC Socket Server
and the Sun JDBC-ODBC bridge. Use these
only when you cannot use a type 4 JDBC driver,
such as with MS Access data sources (or
similar file-based databases) or SQL Server
data sources, which require Trusted
Connections. The JDBC-ODBC bridge is
a type 1 driver, which means it must translate
all JDBC calls into ODBC calls and then
send them to the ODBC driver. Type 1 drivers
perform below par, especially since it is
not multi-threaded, and can even cause the
Java Virtual Machine to fail if many connections
overlap. We recommend that you use the JDBC-ODBC
Bridge driver to prototype apps or when
no other JDBC technology-based drivers exist.
The SequeLink ODBC Socket Server is a type
3 driver, which translates JDBC calls into
the middle-tier server, in this case, the
SequeLink Server. Although this is certainly
preferable over a type 1 driver implementation,
it still falls short of a type 4 driver.
Since a type 3 driver relies on a separate
server component, it introduces an additional
area where problems may occur, such as failure
of the "ColdFusion MX ODBC Server"
service, which is the service that provides
ODBC access through the Sequelink Server.
Connecting to your database
Now that you've decided which technology
and driver type to use, the next step is
to configure your data source. Where should
you go for help? Jonathan Quint covers connecting
to a database in ColdFusion in his new DevNet
article, Connecting
to Databases in ColdFusion MX . The
manuals are also an excellent source of
information. If you prefer the electronic
version, check out livedocs.macromedia.com,
especially since you can see comments that
have been submitted regarding the documentation.
The instructions vary based on the version
and edition of ColdFusion, so be sure to
pick the appropriate Data Source Management
section for your installation. There are
also TechNotes within the ColdFusion
Support Center that cover installation
of other popular drivers, such as the Oracle
JDBC thin driver or the Sybase
jConnect 5.5 driver.
So you've created your data source and
now you're getting the dreaded "Connection
verification failed for data source" message.
What now? First, install ColdFusion
MX Updater 3 or higher
if you haven't already done so. Prior to
Updater 3, the error message always indicated
that you should check the database username
and password, regardless of the true nature
of the error.
Armed with the real cause of the connection
failure, you can begin to work through the
issue. The ColdFusion
Support Center includes a number of
TechNotes to help you through this process.
For instance, check out TechNotes for troubleshooting
data sources and database connectivity for
Windows
platforms and Unix
platforms. A more comprehensive list
is available on the Database
Troubleshooting page in the ColdFusion Support Center.
cfquery and Beyond
Most likely, cfquery will be
your first method of accessing your database.
You may find, however, that the basic cfquery
tag doesn't meet all of your application
needs. In this week's DevNet launch, Sue
Hove, director of instructor readiness at
Macromedia writes about Building
Advanced Queries in ColdFusion MX, explaining
the finer points of cfquery,
such as nesting query output, using cfdump
to display data structures, grouping and
filtering query results, querying a query,
and query caching.
Other options you might want to explore
include stored procedures and transactions.
Sam Neff discusses the advantages of stored
procedures in his article, Learning
Stored Procedure Basics in ColdFusion MX,
and also provides code samples for SQL Server,
Oracle, MSDE, and MS Access. In the next
DevNet launch, Simon Horwith discusses ColdFusion
transactions and rollbacks: who should use
them and when, best practices, limitations
and more. If you've ever considered using
Stored Procedures or transactions but weren't
sure how to use them in your application,
don't miss these two articles.
Even if you're an old hat at ColdFusion
and think there's nothing else for you to
learn, there are always new areas to venture
into. Macromedia instructor Matt Boles writes
about RIAs
and Databases, starting you on the road
of using databases with your Rich Internet
Applications. He provides the ActionScript
and ColdFusion component code that you need
to enable the interaction between Macromedia
Flash and the database.
Simple. Powerful. Flexible.
The name may have changed (however slightly),
the code base may have changed, and there
are more features than ever before, but
really, some things never change. Database
interaction is still the heart and soul
of ColdFusion—make the most of it!
|
