MAXO Studio - Merging Strength & Style

Let's imagine that you do not have access or administrative privileges to your ColdFusion server. Maybe you're having your site hosted remotely or perhaps you work in a company with large amounts of red tape and not-so-friendly server admins. Regardless, you got a DB, you want to interact with it, but alas, you're DSN-Less in Seattle (this technique works in other cities too).

The 2 new concepts that you need to learn about here are the DbType and ConnectString attributes of the cfquery tag. Let's look at an example.

<cfquery name="MyDynamicQuery" dbtype="dynamic"
          connectstring="Driver={Microsoft Access Driver (*.mdb)};
                         Dbq=D:\Path2DB\MyDB.mdb;
                         UID=XXX;
                         PWD=YYY;">
          SELECT * FROM MyTable
</cfquery>

Notice that the ConnectString attribute of your cfquery tag has 4 attributes itslef. "Driver" tells the query what type of database you're connecting to. "DBQ" contains the path to the database. "UID" and "Pwd" are optional.

Here is some generic syntax for the "ConnectString" attribute for other types of DB's.

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

Driver={Microsoft ODBC for Oracle}; Server=Server.Path; UID=xxx; PWD=yyy;

Driver={SQL Server}; Server=DBServerName; Database=MyDBName; Uid=xxx;Pwd=yyy;

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

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

driver={mysql}; database=MyDBName; server=MyServerName; uid=xxx; pwd=yyy; option=16386;

Also, if you wish to use you DB connection ubiquitously (all over your application) it would behoove you to define it once as a session or application variable and then simply refer to it in your "cfquery" tag. This way, if anything about the DB changes, you only have one line of code to update. For example, you can put the following line of Code in your application file:

Now, whenever you run queries in your code you can just use this more elegant syntax:

<cfquery name="MyQuery" dbtype="dynamic"
connectstring="#Application.ConnectString#">
SELECT * FROM MyTable
</cfquery>