This chapter is from the book
This chapter is from the book
This chapter is from the book
Dynamic Datasources
Another new functionality making its debut in ColdFusion 5 is the ability to create dynamic datasource connections. By connecting this way, you can set up connections on the fly without having to set up the ODBC connection. Here is an example of how this works:
Script 1-3 connectionstringQuery.cfm
<CFQUERY NAME="dynamicQuery"
DBTYPE = "dynamic"
CONNECTSTRING="DRIVER=Microsoft Access Driver
*.mdb);DBQ=C:\inetpub\wwwroot\shelley
shelleyCatalog.mdb;DriverId=281;
FIL=MS Access">
SELECT *
FROM Products
</CFQUERY>
<TABLE>
<TR>
<TD>SKU</TD>
<TD>PRODUCT</TD>
</TR>
<CFOUTPUT QUERY="dynamicQuery">
<TR>
<TD>#sku#</TD>
<TD>#name#</TD>
</TR>
</CFOUTPUT>
How this works
|
1. |
Define the datasourse name. |
|
2. |
Use the attribute DBTYPE=dynamic to let ColdFusion know this is going to be a dynamic datasource. |
|
3. |
Using the attribute CONNECTSTRING, you must now define which driver to use depending on the datasource type you are using. |
|
4. |
Enter the SQL statements you wish to use. |
|
5. |
Format your output. |
SQL Server format
If you will be using an SQL Server datasource, use this instead:
CONNECTSTRING =
"DRIVER={SQLSERVER};SERVER=(local);UID=sa;PWD=;
DATABASE=shelleyCatalog"
)
FIGURE 1–14
Output of the Products table using a dynamic
connection.
You may be thinking, "I don't need to pay for extra ODBC connections to my ISP and can run as many datasources as I want!" Before you get a little too happy here, it's not going to be that easy. In ColdFusion Administrator, 'CONNECTSTRING' and 'DBTYPE=dynamic' can both be turned off by the administrator, meaning applications cannot use that functionality.