ColdFusion Day 31:

================

 

CHAPTER 10 - BUILDING DYNAMIC QUERIES

 

    *    Setting the data source name dynamically

 

    *    Creating dynamic SELECT statements

 

    *    Ordering Dynamically

 

    *    Filtering dynamically

 

    *    Storing queries in variables

 

    *    Creating dynamic updates

 

    *    Creating dynamic deletes

 

Once you've mastered CFQUERY and building static SQL statements, you will start wondering about how to dynamically build SQL queries within your ColdFusion application. With ColdFusion you can build these dynamic queries quite easily. You can insert dynamic parameters in your SQL statement or within your CFQUERY tag. These dynamic parameters are ColdFusion variables such as:

 

    *    Client, session, or application variables

    *    Form fields

    *    URL parameters

 

Any or all of these ColdFusion variables can be used as substitutes for values in the CFQUERY tag and within the SQL query.

 

Creating dynamic queries means being able to do any of the following:

 

    *    Set your data-source name as an application variable

 

    *    Store a list of column names in a variable for the SELECT statement or for the ORDER BY clause

 

    *    Use CFIF tags to build a column list for the SELECT statement or for building conditions in the WHERE clause or in the ORDER BY clause

 

    *    Use IsDefined to check whether a variable is defined for inclusion in your WHERE clause or ORDER BY clause

 

    *    Store your SQL statement in a variable, pass it around from page to page, and place it within the CFQUERY tag

 

You can dynamically set the data-source name in your ColdFusion application. Create an application variable and set the data-source name to that application variable. Application variables are a special type of variable created in an application template file called application.cfm. Both application variables and the template file are described in detail in chapter 16, "Implementing the ColdFusion Web Application Framework".

 

Your application.cfm will include the following line:

 

    <CFSET #ApDBUse# = "sybex">

 

The application variable ApDBUse will now be available to all your templates in the application. Your static CFQUERY contains the following:

 

    <CFQUERY NAME="GetProduct" DATASOURCE="sybex">

        SELECT p_name, p_price

        FROM product

      ORDER BY p_price

    </CFQUERY>

 

Within the new and improved dynamic CFQUERY, the data-source parameter will be the application variable, ApDBUse.

 

    <CFQUERY NAME="GetProduct" DATASOURCE=" # ApDBUse # ">

        SELECT p_name, p_price

        FROM product

      ORDER BY p_price

    </CFQUERY>

 

If all references to the data source in your ColdFusion templates are dynamically defined, then changing the data source throughout the application can be a simple CFSET reassignment. This is useful if you have two data sources for your application - one for testing and one for production. For example, if you have a data source called bookprd, you set it up as an application variable as shown earlier. You can create another data-source called sybextest in your ColdFusion Administration setup and then use this data source for testing, without changing the data in your production data source, bookprd. Changing from test mode and production mode is a simple matter of changing a single variable assignment in your application template file (in this case, the value of ApDBUse needs to change from sybex to sybextest to change from production mode to test mode).

 

CREATING DYNAMIC SELECT STATEMENTS

 

Customization features are one of the things that users look for in applications. Users like to have a way to select columns to be displayed. This means that you have to create dynamic columns in the SELECT statement. Using our Supplier table, you can have a check box that lists all the column fields available for input and enable the user to select which fields to display. Listing 10.1 displays a form with the list of column fields and a check box beside it.

 

 

LISTING 10.1:    ch1001.cfm

Select fields to be displayed:

 

            <FORM ACTION="ch1002.cfm" METHOD="Post">

            <INPUT TYPE="checkbox" NAME="SuplSel" VALUE="s_name">Supplier Name<BR>

            <INPUT TYPE="checkbox" NAME="SuplSel" VALUE="s_city">City<BR>

            <INPUT TYPE="checkbox" NAME="SuplSel" VALUE="s_state">State<BR>

            <INPUT TYPE="checkbox" NAME="SuplSel" VALUE="s_phone">Phone<BR>

            <INPUT TYPE="checkbox" NAME="SuplSel" VALUE="s_email">E-mail<BR>

            <INPUT TYPE="checkbox" NAME="SuplSel" VALUE="s_bb">Better Bus. Bureau<BR>

            <INPUT TYPE="Submit">

            </FORM>

 

 

The user selects fields to be included in the SELECT statement by checking off the box opposite the field. Upon submission of the form, variable FORM.SuplSel contains a comma-delimited list of column names that were selected. the variable, FORM.SuplSel, is the dynamic parameter that is inserted in the SQL statement see listing 10.2

 

LISTING 10.2:    ch1002.cfm

            <CFQUERY NAME="SelSupplier" DATASOURCE="sybex">

                SELECT #SuplSel#

                FROM supplier

            </CFQUERY>

 

    Columns Selected:<CFOUTPUT>#SelSupplier.ColumnList#</CFOUTPUT>

 

 

When using CFOUTPUT, there are three fields available that reference a previous query:

 

    *    #RecordCount#    The total number of records returned by the query

    *    #CurrentRow#    The number of the current row incremented each time it is called in a CFOUTPUT or in a CFLOOP

    *    #ColumnList#    The list of a comma-delimited column names in the query.

 

In a dynamic query, you sometimes need to get a list of the column names, you can use ColumnList to show the fields selected. ColumnList returns a comma-delimited list of query columns.

 

 

OK, that's it for the start of chapter 10 on dynamic SQL. You can look at today's example live on the net by going to: http://www.accc.net.au/sybex/ch1001.cfm

 

Back   Next