PDF (adobe.com)

Define an advanced recordset by writing SQL



Write your own SQL statements by using the advanced Recordset dialog box, or create a SQL statement by using the graphical Database Items tree.

  1. In the Document window, open the page that will use the recordset.
  2. Select Windows > Bindings to display the Bindings panel.
  3. In the Bindings panel, click the Plus (+) button and select Recordset (Query) from the pop‑up menu.

    The advanced Recordset dialog box appears. If you are developing a ColdFusion site, the Recordset dialog box is slightly different. (If the simple Recordset dialog box appears instead, switch to the advanced Recordset dialog box by clicking the Advanced button.)

  4. Complete the advanced Recordset dialog box.

    For instructions, see the topics below.

  5. Click the Test button to execute the query and ensure that it retrieves the information you intended.

    If you defined a filter that uses parameters input by users, the Test button displays the Test Value dialog box. Enter a value in the Test Value box and click OK. If an instance of the recordset is successfully created, a table displaying the data from the recordset appears.

  6. Click OK to add the recordset to the list of available content sources in the Bindings panel.

Options for the advanced Recordset dialog box (PHP, ASP)

Define a recordset as a source of dynamic content by writing a custom SQL statement, or by creating a SQL statement using the graphical Database Items tree.

  1. In the Name box, enter a name for the recordset.

    A common practice is to add the prefix rs to recordset names to distinguish them from other object names in the code. For example: rsPressRelease

    Recordset names can only contain letters, numbers, and the underscore character (_). You cannot use special characters or spaces.

  2. Select a connection from the Connection pop‑up menu.
  3. Enter a SQL statement in the SQL text area or use the graphical Database Items tree at the bottom of the dialog box to build a SQL statement from the chosen recordset.

    Do the following to use the Database Items tree to build the SQL statement:

    • Ensure the SQL text area is blank.

    • Expand the branches of the tree until you find the database object you need—a column in a table, for example, or a stored procedure in the database.

    • Select the database object and click one of the buttons on the right side of the tree.

      For example, if you select a table column, the available buttons are SELECT, WHERE, and ORDER BY. Click one of the buttons to add the associated clause to your SQL statement.

      You can also use a predefined SQL statement in a stored procedure by selecting the stored procedure from the Database Items tree and clicking the Procedure button. Dreamweaver automatically fills in the SQL and Variable areas.

  4. If the SQL statement contains variables, define their values in the Variables area by clicking the Plus (+) button and entering the variable’s name, default value (the value the variable should take if no run-time value is returned), and run-time value.

    If the SQL statement contains variables, make sure the Default Value column of the Variables box contains valid test values.

    The run-time value is usually a URL or form parameter entered by a user in an HTML form field.

    URL parameters in the Run-time Value column.

    Server Model

    Run-Time value expression for URL parameter

    ASP

    Request.QueryString(“formFieldName”)

    PHP

    #formFieldName#

    Form parameters in the Run-time Value column:

    Server Model

    Run-Time value expression for form parameter

    ASP

    Request.Form(“formFieldName”)

    PHP

    #formFieldName#
  5. Click Test to connect to the database and create an instance of the recordset.

    If the SQL statement contains variables, make sure the Default Value column of the Variables box contains valid test values before clicking Test.

    If successful, a table appears displaying the data in your recordset. Each row contains a record and each column represents a field in that record. Click OK to clear the recordset.

  6. If satisfied with your work, click OK.

Options for the advanced Recordset dialog box (ColdFusion)

Use the advanced Recordset dialog box to write custom SQL queries, or use the Database Items tree to author SQL queries using a point-and-click interface.

  1. In the Name box, enter a name for the recordset.

    A common practice is to add the prefix rs to recordset names to distinguish them from other object names in your code. For example: rsPressReleases

    Recordset names can only contain letters, numbers, and the underscore character (_). You cannot use special characters or spaces.

    If you’re defining a recordset for a ColdFusion component (that is, if a CFC file is currently open in Dreamweaver), select an existing CFC function from the Function pop‑up menu, or click the New Function button to create a new function.

    Note: The Function pop‑up menu is only available if a CFC file is the current document and you have access to a computer running ColdFusion MX 7 or better.

    The recordset is defined in the function.

  2. Select a data source from the Data Source pop‑up menu.

    If no data source appears in the pop‑up menu list, you will need to first create a ColdFusion data source.

  3. In the Username and Password boxes, enter the user name and password for the ColdFusion application server if required.

    Data sources in ColdFusion may require a user name and password to access them. If you do not have the user name and password to access a data source in ColdFusion, contact your organization’s ColdFusion administrator.

  4. Enter a SQL statement in the SQL text area or use the graphical Database Items tree at the bottom of the dialog box to build a SQL statement from the chosen recordset.
  5. (Optional) Do the following to use the Database Items tree to build the SQL statement:

    • Ensure the SQL text area is blank.

    • Expand the branches of the tree until you find the database object you need—for example, a column in a table.

    • Select the database object and click one of the buttons on the right side of the tree.

    For example, if you select a table column, the available buttons are Select, Where, and Order By. Click one of the buttons to add the associated clause to your SQL statement.

    If your SQL statement contains parameters, define their values in the Parameters area by clicking the Plus (+) button and entering the parameter’s name and default value (the value the parameter should take if no run-time value is returned).

    If the SQL statement contains parameters, make sure the Default Value column of the Parameters box contains valid test values.

    The Page Parameters allow you to provide default values for run-time value references in the SQL you write. For example, the following SQL statement selects an employee record based on the value of the employee’s ID. You can assign a default value to this parameter, ensuring that a run-time value is always returned. In this example, FormFieldName refers to a form field in which the user enters an employee ID:

    SELECT * FROM Employees WHERE EmpID = + (Request.Form(#FormFieldName#))

    The Add Page Parameters dialog box would contain a name-value pairing similar to:

    Name

    Default values

    FormFieldName 

    0001

    The run-time value is usually a URL or form parameter entered by a user in an HTML form field.

  6. Click Test to connect to the database and create an instance of the recordset.

    If the SQL statement contains run-time references, make sure the Default Value column of the Page Parameters field contains valid test values before clicking Test.

    If successful, a table appears displaying the data in your recordset. Each row contains a record and each column represents a field in that record. Click OK to clear the recordset.

  7. If satisfied with your work, click OK.

Define parameters in a SQL statement (ColdFusion)

Define parameters in a SQL statement; the default value is the value that the parameter should use if no run-time value is returned.

  1. Select a parameter name from the Name pop‑up menu.
  2. Enter a default value for the parameter in the Default Parameter box, and click OK.

Define parameters in a SQL statement (PHP)

Define parameters in a SQL statement; the default value is the value that the parameter should use if no run-time value is returned.

  1. Enter a parameter name in the Name box.
  2. Enter a default value for the parameter in the Default Parameter box.
  3. Enter a run-time value for a parameter in the Run-time Value box, and click OK.