Querying

Execute SQL queries against databases using queryExecute() or bx:query component

BoxLang provides powerful and flexible ways to execute SQL queries against your databases. You can use either the queryExecute() Built-In Function (BIF) or the bx:query component, both offering the same capabilities with different syntax styles.

📋 Overview

Database querying in BoxLang supports:

Parameterized Queries - Prevent SQL injection with bound parameters
Multiple Return Types - Get results as query objects, arrays, or structs
Query Caching - Cache frequently-used queries for performance
Query of Queries (QoQ) - Execute SQL against in-memory result sets
Flexible Syntax - Choose between BIF and component styles
Connection Pooling - Automatic connection management via HikariCP

🔧 Query Execution Methods

Using queryExecute() BIF

The queryExecute() function is the preferred method for script-based code:

// Basic query
employees = queryExecute( "SELECT * FROM employees" );

// With positional parameters
employee = queryExecute(
    "SELECT * FROM employees WHERE id = ?",
    [ 42 ]
);

// With named parameters
salesTeam = queryExecute(
    "SELECT * FROM employees WHERE department = :dept AND salary > :minSalary",
    {
        dept: "Sales",
        minSalary: 50000
    }
);

// With options
result = queryExecute(
    "SELECT * FROM products WHERE category = ?",
    [ "Electronics" ],
    {
        datasource: "myDB",
        returntype: "array",
        maxrows: 100
    }
);

Using bx:query Component

The bx:query component is ideal for template-based code islands, or when you prefer XML-style syntax. Please also note that you can also use the bx:query component inside script code as well.

bx:query name="employees" datasource="myDB"{
    SELECT * FROM employees
    WHERE department = bx:queryParam value="#dept#" sqltype="varchar"{}
    ORDER BY lastName
}

<bx:query name="employees" datasource="myDB">
    SELECT * FROM employees
    WHERE department = <bx:queryParam value="#dept#" sqltype="varchar">
    ORDER BY lastName
</bx:query>

<!-- With result capture -->
<bx:query name="products" datasource="myDB" result="queryInfo">
    SELECT * FROM products
    WHERE price > <bx:queryParam value="#minPrice#" sqltype="decimal">
</bx:query>

<p>Query executed in #queryInfo.executionTime# ms</p>
<p>Returned #queryInfo.recordCount# rows</p>

🛡️ Parameterized Queries

Always use query parameters for user input to prevent SQL injection attacks.

Positional Parameters

Use ? placeholders and provide values in an array:

queryExecute(
    "SELECT * FROM users WHERE username = ? AND active = ?",
    [ username, true ]
);

Named Parameters

Use :paramName placeholders and provide values in a struct:

queryExecute(
    "SELECT * FROM orders WHERE customerId = :id AND orderDate > :date",
    {
        id: customerId,
        date: createDate( 2024, 1, 1 )
    }
);

bx:queryParam Component

For template syntax, use bx:queryParam for type-safe parameter binding:

<bx:query name="results">
    SELECT * FROM products
    WHERE category = <bx:queryParam value="#category#" sqltype="varchar">
    AND price BETWEEN <bx:queryParam value="#minPrice#" sqltype="decimal">
                  AND <bx:queryParam value="#maxPrice#" sqltype="decimal">
    AND inStock = <bx:queryParam value="#true#" sqltype="bit">
</bx:query>

Advanced Parameter Options

Parameters can include additional options for fine-grained control:

queryExecute(
    "SELECT * FROM products WHERE id IN (:ids) AND name LIKE :pattern",
    {
        ids: {
            value: [ 1, 2, 3, 4, 5 ],
            type: "integer",
            list: true,
            separator: ","
        },
        pattern: {
            value: "Widget%",
            type: "varchar",
            maxLength: 50
        }
    }
);

Parameter Options:

value - The parameter value
type - SQL type (varchar, integer, decimal, date, timestamp, bit, etc.)
null - Set to true for NULL values
list - Set to true for IN clause lists
separator - List separator (default: ",")
maxLength - Maximum length for string types
scale - Decimal scale for numeric types

📊 Return Types

Control how query results are returned using the returntype option.

Query Object (Default)

Returns a BoxLang Query object with rows accessible via array notation:

employees = queryExecute( "SELECT * FROM employees" );

for ( row in employees ) {
    echo( row.firstName & " " & row.lastName );
}

// Access specific row
firstEmployee = employees[ 1 ];

// Get column as array
names = employees[ "firstName" ];

Array of Structs

Returns an array where each element is a struct representing a row:

employees = queryExecute(
    "SELECT * FROM employees",
    [],
    { returntype: "array" }
);

for ( employee in employees ) {
    echo( employee.firstName & " " & employee.lastName );
}

Struct (Keyed by Column)

Returns a struct keyed by a specific column, with each value being a struct of the row data:

employees = queryExecute(
    "SELECT * FROM employees",
    [],
    {
        returntype: "struct",
        columnKey: "id"
    }
);

// Access by employee ID
employee42 = employees[ 42 ];
echo( employee42.firstName );

⚡ Query Caching

Cache frequently-executed queries to improve performance:

// Cache for 1 hour
products = queryExecute(
    "SELECT * FROM products WHERE active = ?",
    [ true ],
    {
        cache: true,
        cacheTimeout: createTimeSpan( 0, 1, 0, 0 )
    }
);

// Custom cache key
categories = queryExecute(
    "SELECT * FROM categories ORDER BY name",
    [],
    {
        cache: true,
        cacheKey: "all_categories",
        cacheTimeout: createTimeSpan( 1, 0, 0, 0 ), // 1 day
        cacheProvider: "default"
    }
);

Cache Options:

cache - Enable caching (boolean)
cacheKey - Custom cache key (defaults to SQL hash)
cacheTimeout - Time-to-live duration
cacheLastAccessTimeout - Idle timeout
cacheProvider - Named cache provider to use

Be cautious caching queries with user-specific data. Cache keys should include relevant user identifiers when needed.

🔄 Query of Queries (QoQ)

Execute SQL queries against in-memory query objects without hitting the database:

// Get all employees from database
allEmployees = queryExecute( "SELECT * FROM employees" );

// Filter in-memory using SQL
salesTeam = queryExecute(
    "SELECT firstName, lastName, salary
     FROM allEmployees
     WHERE department = ?
     ORDER BY salary DESC",
    [ "Sales" ],
    { dbtype: "query" }
);

// Join multiple queries in memory
employeeOrders = queryExecute(
    "SELECT e.firstName, e.lastName, o.orderDate, o.total
     FROM employees e
     INNER JOIN orders o ON e.id = o.employeeId
     WHERE o.orderDate > ?
     ORDER BY o.orderDate DESC",
    [ createDate( 2024, 1, 1 ) ],
    { dbtype: "query" }
);

QoQ Features:

Standard SQL syntax (SELECT, WHERE, ORDER BY, GROUP BY, JOIN)
Aggregation functions (COUNT, SUM, AVG, MIN, MAX)
No database round-trips
Ideal for filtering, sorting, and transforming result sets

QoQ Limitations:

Limited SQL function support compared to full databases
No transaction support
Performance depends on result set size

📈 Query Result Information

Capture metadata about query execution using the result option:

queryExecute(
    "SELECT * FROM employees",
    [],
    { result: "queryInfo" }
);

// Access execution metadata
echo( "Execution time: " & queryInfo.executionTime & " ms" );
echo( "Record count: " & queryInfo.recordCount );
echo( "Cached: " & queryInfo.cached );
echo( "SQL: " & queryInfo.sql );

Result Struct Properties:

recordCount - Number of rows returned
columnList - Comma-delimited list of columns
executionTime - Query execution time in milliseconds
cached - Boolean indicating if result was cached
sql - The SQL statement executed
sqlParameters - Array of bound parameter values

🎛️ Query Options

Complete list of options available for queryExecute() and bx:query:

Option

Type

Description

datasource

string

Datasource name or inline struct

dbtype

string

"query" for QoQ, "hql" for Hibernate

returntype

string

"query", "array", or "struct"

columnKey

string

Column to use as struct key (when returntype="struct")

maxrows

integer

Maximum rows to return

timeout

integer

Query timeout in seconds

cache

boolean

Enable query caching

cacheKey

string

Custom cache key

cacheTimeout

duration

Cache time-to-live

cacheLastAccessTimeout

duration

Cache idle timeout

cacheProvider

string

Named cache provider

result

string

Variable name to store execution metadata

username

string

Override datasource username

password

string

Override datasource password

fetchsize

integer

Number of rows to fetch at once

📊 Query Metadata

Every query result has metadata that provides information about the query execution, columns, and result set. You can access this metadata using the getMetadata() function or the query's $bx.meta property.

Accessing Query Metadata

// Execute a query
employees = queryExecute( "SELECT * FROM employees WHERE active = ?", [ true ] );

// Get metadata using getMetadata() BIF
meta = getMetadata( employees );

// Or access directly via the query object
meta = employees.getMetadata();

// Access via $bx.meta property
meta = employees.$bx.meta;

Metadata Structure

Query metadata includes the following keys:

Key

Type

Description

type

string

Always "Query"

recordCount

integer

Number of rows in the result set

columnList

string

Comma-delimited list of column names

columnMetadata

struct

Detailed information about each column

executionTime

integer

Query execution time in milliseconds

cached

boolean

Whether the result was retrieved from cache

cacheKey

string

Cache key if query was cached

cacheProvider

string

Cache provider name if cached

cacheTimeout

duration

Cache time-to-live duration

cacheLastAccessTimeout

duration

Cache idle timeout duration

sql

string

The executed SQL statement (when available)

sqlParameters

array

Bound parameter values (when available)

_HASHCODE

integer

Java hashcode of the query object

Column Metadata

The columnMetadata key contains detailed information about each column:

employees = queryExecute( "SELECT id, firstName, lastName, salary FROM employees" );
meta = getMetadata( employees );

// Access column metadata
for ( column in meta.columnMetadata ) {
    writeOutput( "Column: #column.name#<br>" );
    writeOutput( "Type: #column.type#<br>" );
    writeOutput( "SQL Type: #column.sqltype#<br>" );
    writeOutput( "Index: #column.index#<br><br>" );
}

Each column has the following properties:

Property

Type

Description

name

string

Column name

type

string

BoxLang type (String, Integer, Double, DateTime, etc.)

sqltype

string

JDBC SQL type (VARCHAR, INTEGER, DECIMAL, TIMESTAMP, etc.)

index

integer

Zero-based column index in the result set

Practical Examples

Display Query Information

result = queryExecute(
    "SELECT * FROM products WHERE category = ?",
    [ "Electronics" ],
    { cache: true, cacheTimeout: createTimeSpan( 0, 1, 0, 0 ) }
);

meta = getMetadata( result );

writeOutput( "Records returned: #meta.recordCount#<br>" );
writeOutput( "Execution time: #meta.executionTime# ms<br>" );
writeOutput( "Cached: #meta.cached#<br>" );
writeOutput( "Columns: #meta.columnList#<br>" );

Debug Query Columns

<bx:query name="employees">
    SELECT id, firstName, lastName, department, salary, hireDate
    FROM employees
</bx:query>

<bx:set meta = getMetadata( employees )>

<table>
    <thead>
        <tr>
            <th>Column Name</th>
            <th>BoxLang Type</th>
            <th>SQL Type</th>
            <th>Position</th>
        </tr>
    </thead>
    <tbody>
        <bx:loop collection="#meta.columnMetadata#" item="colName" index="colMeta">
            <tr>
                <td>#colMeta.name#</td>
                <td>#colMeta.type#</td>
                <td>#colMeta.sqltype#</td>
                <td>#colMeta.index + 1#</td>
            </tr>
        </bx:loop>
    </tbody>
</table>

Cache Performance Monitoring

function getCachedData() {
    result = queryExecute(
        "SELECT * FROM statistics",
        [],
        {
            cache: true,
            cacheKey: "daily_stats",
            cacheTimeout: createTimeSpan( 1, 0, 0, 0 )
        }
    );

    meta = getMetadata( result );

    if ( meta.cached ) {
        writeLog( "Cache HIT for daily_stats - saved #meta.executionTime#ms" );
    } else {
        writeLog( "Cache MISS for daily_stats - executed in #meta.executionTime#ms" );
    }

    return result;
}

Dynamic Column Mapping

// Query from unknown source
data = queryExecute( "SELECT * FROM dynamic_table" );
meta = getMetadata( data );

// Build column mapping based on metadata
columnMapping = {};
for ( column in meta.columnMetadata ) {
    columnMapping[ column.name ] = {
        type: column.type,
        sqltype: column.sqltype,
        position: column.index
    };
}

// Use mapping for data transformation
transformed = data.map( ( row ) => {
    result = {};
    for ( col in columnMapping ) {
        // Transform based on type
        if ( columnMapping[ col ].type == "DateTime" ) {
            result[ col ] = dateFormat( row[ col ], "yyyy-mm-dd" );
        } else {
            result[ col ] = row[ col ];
        }
    }
    return result;
} );

Query Comparison Tool

// Compare two query structures
function compareQueryStructures( query1, query2 ) {
    meta1 = getMetadata( query1 );
    meta2 = getMetadata( query2 );

    differences = [];

    // Compare column counts
    if ( meta1.recordCount != meta2.recordCount ) {
        differences.append( "Record count mismatch: #meta1.recordCount# vs #meta2.recordCount#" );
    }

    // Compare column lists
    if ( meta1.columnList != meta2.columnList ) {
        differences.append( "Column list mismatch" );
    }

    // Compare column types
    for ( colName in meta1.columnMetadata ) {
        if ( structKeyExists( meta2.columnMetadata, colName ) ) {
            col1 = meta1.columnMetadata[ colName ];
            col2 = meta2.columnMetadata[ colName ];

            if ( col1.type != col2.type ) {
                differences.append(
                    "Column #colName# type mismatch: #col1.type# vs #col2.type#"
                );
            }
        } else {
            differences.append( "Column #colName# missing in second query" );
        }
    }

    return differences;
}

Metadata in Result Variable

When using the result parameter, execution metadata is automatically captured:

queryExecute(
    "UPDATE employees SET salary = ? WHERE id = ?",
    [ newSalary, employeeId ],
    { result: "updateResult" }
);

// Result variable contains:
writeOutput( "Rows updated: #updateResult.recordCount#<br>" );
writeOutput( "Execution time: #updateResult.executionTime# ms<br>" );
writeOutput( "SQL: #updateResult.sql#<br>" );

// For INSERT with generated keys
queryExecute(
    "INSERT INTO employees (firstName, lastName) VALUES (?, ?)",
    [ "John", "Doe" ],
    { result: "insertResult" }
);

writeOutput( "Generated ID: #insertResult.generatedKey#<br>" );

Best Practices for Metadata

✅ Use metadata for debugging - Check execution times and column information during development
✅ Monitor cache performance - Log cache hits/misses in production
✅ Validate data structures - Compare expected vs actual column types
✅ Dynamic data handling - Use column metadata for generic data processors
❌ Don't access metadata unnecessarily - Small performance cost for metadata generation
❌ Don't store metadata long-term - Query structure may change over time

💡 Best Practices

Security

✅ Always use query parameters for user input
✅ Never concatenate user input into SQL strings
✅ Validate input before querying
✅ Use environment variables for credentials

Performance

✅ Use connection pooling (automatic with datasources)
✅ Cache static queries (categories, lookups, etc.)
✅ Limit result sets with maxrows when appropriate
✅ Use QoQ for in-memory filtering instead of multiple DB queries
✅ Index database columns used in WHERE clauses

Code Quality

✅ Use consistent syntax (BIF or component, not mixed)
✅ Name result variables descriptively
✅ Capture execution metadata for debugging
✅ Handle empty result sets gracefully

Anti-Patterns

❌ Don't use string concatenation for SQL building
❌ Don't cache user-specific data without proper keying
❌ Don't fetch all rows when you only need a few
❌ Don't ignore errors - wrap in try/catch for critical operations

Datasources - Configure database connections
Transactions - Manage database transactions
Query Type Reference - Query object methods
queryExecute() BIF - Complete BIF reference
bx:query Component - Complete component reference

📚 Examples

Pagination

page = 1;
pageSize = 20;
offset = ( page - 1 ) * pageSize;

results = queryExecute(
    "SELECT * FROM products
     WHERE active = ?
     ORDER BY name
     LIMIT ? OFFSET ?",
    [ true, pageSize, offset ]
);

Dynamic WHERE Clauses

sql = "SELECT * FROM products WHERE 1=1";
params = [];

if ( structKeyExists( form, "category" ) ) {
    sql &= " AND category = ?";
    params.append( form.category );
}

if ( structKeyExists( form, "minPrice" ) ) {
    sql &= " AND price >= ?";
    params.append( form.minPrice );
}

results = queryExecute( sql, params );

Batch Processing

batchSize = 1000;
offset = 0;

do {
    batch = queryExecute(
        "SELECT * FROM orders WHERE processed = ? LIMIT ? OFFSET ?",
        [ false, batchSize, offset ],
        { returntype: "array" }
    );

    for ( order in batch ) {
        processOrder( order );
    }

    offset += batchSize;
} while ( !batch.isEmpty() );

PreviousDatasources NextTransactions

Last updated 1 month ago

Was this helpful?

hashtag📋 Overview

hashtag🔧 Query Execution Methods

hashtagUsing queryExecute() BIF

hashtagUsing bx:query Component

hashtag🛡️ Parameterized Queries

hashtagPositional Parameters

hashtagNamed Parameters

hashtagbx:queryParam Component

hashtagAdvanced Parameter Options

hashtag📊 Return Types

hashtagQuery Object (Default)

hashtagArray of Structs

hashtagStruct (Keyed by Column)

hashtag⚡ Query Caching

hashtag🔄 Query of Queries (QoQ)

hashtag📈 Query Result Information

hashtag🎛️ Query Options

hashtag📊 Query Metadata

hashtagAccessing Query Metadata

hashtagMetadata Structure

hashtagColumn Metadata

hashtagPractical Examples

hashtagDisplay Query Information

hashtagDebug Query Columns

hashtagCache Performance Monitoring

hashtagDynamic Column Mapping

hashtagQuery Comparison Tool

hashtagMetadata in Result Variable

hashtagBest Practices for Metadata

hashtag💡 Best Practices

hashtagSecurity

hashtagPerformance

hashtagCode Quality

hashtagAnti-Patterns

hashtag🔗 Related Documentation

hashtag📚 Examples

hashtagPagination

hashtagDynamic WHERE Clauses

hashtagBatch Processing

📋 Overview

🔧 Query Execution Methods

Using queryExecute() BIF

Using bx:query Component

🛡️ Parameterized Queries

Positional Parameters

Named Parameters

bx:queryParam Component

Advanced Parameter Options

📊 Return Types

Query Object (Default)

Array of Structs

Struct (Keyed by Column)

⚡ Query Caching

🔄 Query of Queries (QoQ)

📈 Query Result Information

🎛️ Query Options

📊 Query Metadata

Accessing Query Metadata

Metadata Structure

Column Metadata

Practical Examples

Display Query Information

Debug Query Columns

Cache Performance Monitoring

Dynamic Column Mapping

Query Comparison Tool

Metadata in Result Variable

Best Practices for Metadata

💡 Best Practices

Security

Performance

Code Quality

Anti-Patterns

🔗 Related Documentation

📚 Examples

Pagination

Dynamic WHERE Clauses

Batch Processing