NightFox logo NightFox personal tech cave

Queries, Query of Queries, and Transactions

Go beyond a basic cfquery: understand query objects, in-memory queries, bind parameters, transactions, and common database performance traps.

Datasource-Centered Database Access

ColdFusion applications usually reference a datasource name configured in ColdFusion Administrator. The datasource contains the driver and connection information, while templates refer to its logical name.

<cfquery name="openTickets" datasource="support_portal">
    SELECT id, subject, priority, created_at
    FROM tickets
    WHERE status = <cfqueryparam value="open" cfsqltype="cf_sql_varchar">
    ORDER BY created_at
</cfquery>

Useful Query Metadata

<cfoutput>
    Rows returned: #openTickets.recordCount#<br>
    Columns: #HTMLEditFormat(openTickets.columnList)#
</cfoutput>

Query columns can be accessed as arrays by row number, such as openTickets.subject[1].

Looping Over a Query

<cfoutput query="openTickets">
    <article>
        <h3>#HTMLEditFormat(subject)#</h3>
        <p>Priority: #HTMLEditFormat(priority)#</p>
    </article>
</cfoutput>

Query of Queries

ColdFusion can query an in-memory query object using SQL-like syntax. This was useful for filtering, sorting, or joining data already retrieved from a database.

<cfquery name="urgentTickets" dbtype="query">
    SELECT id, subject, priority
    FROM openTickets
    WHERE priority = 'urgent'
    ORDER BY subject
</cfquery>
Use thoughtfully: Query of Queries can simplify reporting, but it can also hide inefficient data retrieval. Let the database perform filtering and joins when practical.

Transactions

Use a transaction when several SQL statements represent one business operation.

<cftransaction>
    <cfquery datasource="support_portal">
        INSERT INTO tickets (customer_id, subject, status, created_at)
        VALUES (
            <cfqueryparam value="#variables.customerId#" cfsqltype="cf_sql_integer">,
            <cfqueryparam value="#variables.subject#" cfsqltype="cf_sql_varchar" maxlength="200">,
            'open',
            CURRENT_TIMESTAMP
        )
    </cfquery>

    <cfquery datasource="support_portal">
        UPDATE customers
        SET last_ticket_at = CURRENT_TIMESTAMP
        WHERE id = <cfqueryparam value="#variables.customerId#" cfsqltype="cf_sql_integer">
    </cfquery>
</cftransaction>

If either statement fails, the transaction can roll back rather than leaving only half of the business operation completed.

Explicit Commit and Rollback

<cftransaction action="begin">
    <cftry>
        <!--- related queries --->
        <cftransaction action="commit">
        <cfcatch type="any">
            <cftransaction action="rollback">
            <cfrethrow>
        </cfcatch>
    </cftry>
</cftransaction>

Test transaction behavior against the exact database driver and engine used by the application. Autocommit, isolation, and supported transaction features can differ.

Dynamic IN Lists

<cfparam name="url.ids" default="0">

<cfquery name="selectedCustomers" datasource="support_portal">
    SELECT id, name
    FROM customers
    WHERE id IN (
        <cfqueryparam value="#url.ids#" cfsqltype="cf_sql_integer" list="true">
    )
</cfquery>

Validate list length and expected content before executing large user-controlled lists.

Pagination in Legacy Systems

Older applications may retrieve a large query and use startrow and maxrows while rendering. That reduces displayed rows but may not reduce database work. Prefer database-native pagination appropriate to the database version when maintaining performance-critical pages.

N+1 Query Warning

A query inside a query-output loop can execute once per row. Look for a parent query followed by repeated child queries and replace it with a join, a batch query, or a cached lookup where appropriate.

Continue Learning