ColdFusion DSNLess Connections:

 by Tom Muck

ColdFusion 5 introduces DSNLess connections for ODBC drivers that use the same syntax as the DSNLess connections that ASP programmers have grown accustomed to. UltraDev doesn't support these connections yet, but they can be hand-coded in your ColdFusion page with a little knowledge of how the syntax works.

The <CFQUERY> tag in ColdFusion 5 has several new options. One of these is a DBTYPE of "dynamic". This allows the use of connection strings using the CONNECTSTRING attribute of the <CFQUERY> tag:

<CFQUERY NAME="rs"
 DBTYPE="dynamic"
 CONNECTSTRING="Driver={Microsoft Access Driver (*.mdb)};Dbq=c:\db\mydb.mdb;Uid=Admin;Pwd=;"
>
SELECT SeekId, SeekUsername, SeekPassword from Seekers
</CFQUERY>

If you define a CFQUERY in this manner, you can't use the Data Bindings palette within UltraDev for your recordset information, such as the columns and views returned from the query, but these can be handcoded.

The advantage of this method is that you don't need to define your connection to the CFAdministrator any more -- they can be done completely on the fly using path information for the database (if it's a file based database) or the server and database name for server-based databases such as SQL Server and MySQL. The connection string typically supplies the username and password, as well as any other values that the database needs for a successful connection.

The following connection strings are some of the more popular ODBC connection strings* that you can use to declare your connection on the fly. The connection string should be placed in quotes on one line entirely using the CONNECTSTRING attribute.

*note: OLE DB connection strings don't work in ColdFusion 5.

ODBC DSNLess Connections:

MS Access ODBC DSNless connection

Driver={Microsoft Access Driver (*.mdb)};Dbq=c:\somepath\dbname.mdb;Uid=Admin;Pwd=pass;

dBase ODBC DSNless connection

Driver={Microsoft dBASE Driver (*.dbf)};DriverID=277;Dbq=c:\somepath\dbname.dbf;

Oracle ODBC DSNless connection

Driver={Microsoft ODBC for Oracle};Server=OracleServer.world;Uid=admin;Pwd=pass;

MS SQL Server DSNless connection

Driver={SQL Server};Server=servername;Database=dbname;Uid=sa;Pwd=pass;

MS Text Driver DSNless connection

Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=c:\somepath\;Extensions=asc,csv,tab,txt;Persist Security Info=False;

Visual Foxpro DSNless connection

Driver={Microsoft Visual FoxPro Driver};SourceType=DBC;SourceDB=c:\somepath\dbname.dbc;Exclusive=No;

MySQL DSNless connection

driver={mysql}; database=yourdatabase;server=yourserver;uid=username;pwd=password;option=16386;

Using ExpandPath in the ConnectString

ExpandPath is a built-in ColdFusion function that will retrieve the full path of a file (including the drive letter) given the relative path of the file. A relative path cannot begin with a slash or backslash:

#ExpandPath('..\bettergig\cgi-bin\bettergig.mdb')#

You can use this in your CONNECTSTRING attribute to complete the string if you are uploading a file to a remote server and don't know the physical path of your database file:

Driver={Microsoft Access Driver (*.mdb)};Dbq=#ExpandPath('..\bettergig\cgi-bin\bettergig.mdb')#;Uid=Admin;Pwd=pass;

The complete <CFQUERY> tag for this string would look like this:

<CFQUERY NAME="rs"
 DBTYPE="dynamic"
 CONNECTSTRING="Driver={Microsoft Access Driver (*.mdb)};Dbq=#ExpandPath('..\bettergig\cgi-bin\bettergig.mdb')#;Uid=Admin;Pwd=pass;"
>
SELECT SeekId, SeekUsername, SeekPassword from Seekers
</CFQUERY>

The connection string could be built up in a variable to make it easier to read as well:

<CFSET rsUser="Uid=#form.username#;">
<CFSET rsPass="Pwd=#form.password#;">
<CFSET theDatabase="#ExpandPath('..\bettergig\cgi-bin\bettergig.mdb')#;">
<CFSET theString="Driver={Microsoft Access Driver (*.mdb)};Dbq=">
<CFSET theString=theString & theDatabase & rsUser & rsPass>

<CFQUERY NAME="rs"
 DBTYPE="dynamic"
 CONNECTSTRING="#theString#"
>
SELECT SeekId, SeekUsername, SeekPassword from Seekers
</CFQUERY>

Tom Muck
July 2001