CFML 64-bit and 32-bit datasource

Mike's Notes

Pipi 9 uses multiple Application.cfc to configure data sources programmatically. Each agent engine has its own embedded database to store config and state. Sometimes there are several embedded databases, and sometimes non-embedded ones like PostgreSQL. There are hundreds of databases in total.

This is part of the problem I have to solve to fully automate the Pipi Core data centre. Each agent needs to be able to autonomously create or add databases and data sources. It's the last major issue to resolve before moving on to Workspace rendering.

Once automation is complete, productivity will increase by at least x10.

I asked Google Search AI Mode (powered by Google Gemini) how to use 32-bit and 64-bit data sources with a 64-bit ColdFusion Server versions 11 and 12 (2016).

The Google output has been reformatted but not edited, and the code needs to be tested. The questions were turned into headings. There is a lot of duplication.

I find that carefully using Gemini to write CFML code and then checking everything is making me more productive.

Later, I will also find out how this works with different versions of Lucee, Blue Dragon and BoxLang, which also run CFML. This enables future customers to choose their CFML server, version and OS. Pipi runs on any CFML server that supports ColdFusion Components (CFC) and embedded databases.

Since the release of ColdFusion MX (version 6.0) in 2002, the engine has been completely rewritten in Java and runs on a Java Virtual Machine (JVM).

Key Technical Details

  • Java EE Foundation: ColdFusion is built on the Java Enterprise Edition (JEE) technology platform.
  • Bytecode Execution: ColdFusion Markup Language (CFML) code is compiled into Java bytecode, similar to how Java source code is processed.
  • Application Servers: It can be deployed as a standalone server (using an embedded version of Apache Tomcat) or as a web application on third-party JEE servers like IBM WebSphere, JBoss, or Oracle WebLogic.
  • Seamless Integration: Developers can directly invoke Java classes, use Java objects, and integrate existing Java libraries within their ColdFusion applications. 

Historical Context

Prior to version 6.0, ColdFusion was written in C++ and was primarily limited to Microsoft Windows. The transition to Java (originally codenamed "Neo") was implemented to improve cross-platform portability and security.

Because they are all Java-based, Adobe ColdFusion, Lucee, and BoxLang are highly cross-platform and can run on any operating system that supports a Java Runtime Environment (JRE) or Java Development Kit (JDK).

Pipi platform history

  • Pipi 1: Linux, Apache
  • Pipi 2: Linux, Apache
  • Pipi 3: Windows 2000 Server, ColdFusion 6 MX, and MS SQL 6.
  • Pipi 4: Windows 2000 Server, ColdFusion 6 MX, and MS SQL 6.
  • Pipi 5: ArcPad
  • Pipi 6: Windows
  • Pipi 7: Windows
  • Pipi 8: Windows
  • Pipi 9 core: Windows, JVM 21, Adobe, Lucee and BoxLang Servers, PostgreSQL 18, MS Access
  • Pipi 9 applications (open-source): Debian, JVM 21, Adobe, Lucee and BoxLang Servers, PostgreSQL 18
  • Pipi 9 IaC: Debian, JVM 21, BoxLang, PostgreSQL 18
  • Pipi 9 robot: Debian, JVM 21, BoxLang, PostgreSQL 18
  • Pipi 10 core: Debian, JVM 21+, BoxLang, PostgreSQL, Derby, HSQLDB, H2, SQLite.
  • Pipi 10 applications (open-source): Debian, JVM 21+, BoxLang, PostgreSQL. Customers can additionally choose
    •  a production CFML server (Adobe, Lucee, BoxLang)
    • or run and deploy the ColdFusion application, in an enterprise application archive (EAR) or web application archive (WAR) format, on a JEE application server (GlassFish, JBoss, Payara, Apache Geronimo, etc.)
    • a production database for customer data (PostgreSQL, MS SQL, Oracle, Couchbase, etc.)
  • Pipi 10 IaC: Debian, JVM 21+, BoxLang, PostgreSQL 18
  • Pipi 10 robot: Debian, JVM 21+, BoxLang, PostgreSQL 18

Adobe ColdFusion

Adobe officially supports specific enterprise-grade operating systems to ensure stability and compatibility with their installers. 

  • Windows: Windows Server 2025 (latest), 2022, 2019, 2016, and consumer versions like Windows 11 and 10.
  • Linux: Red Hat Enterprise Linux (RHEL) 9.5+, Ubuntu 24 LTS, and SUSE Linux Enterprise Server 15.5+.
  • macOS: macOS 15 (Sequoia) and later, supporting both Intel and Apple Silicon (ARM64).
  • Solaris: Historically supported (up to version 2021) but less common in recent release matrices. 

Lucee 

Lucee is more flexible and can run "almost everywhere" that supports Java, including small devices like the Raspberry Pi. 

  • Main OSs: Windows (all editions), Linux, and macOS.
  • Linux Distributions: Extensively tested on Debian, Fedora, Arch Linux, AlmaLinux, Rocky Linux, Mint, and Gentoo.
  • Other Platforms: FreeBSD and Solaris.

BoxLang

As a modern, dynamic JVM language, BoxLang is designed for high portability across standard and emerging runtimes. 

  • Operating Systems: Windows, macOS, and all *nix-based systems (Linux distributions).
  • Specialised Environments:
    • Chromebooks: Supported via the Linux development environment (ChromeOS).
    • Mobile: Can target Android and iOS devices.
    • Cloud/Serverless: Native support for AWS Lambda and DigitalOcean App Platform.
    • WebAssembly: Can be deployed to run in browser environments via WASM.

Embedded databases

Adobe ColdFusion, Lucee, and BoxLang all support embedded databases, allowing you to run a fully functional database directly within the application server without a separate installation (like SQL Server or MySQL).

1. Adobe ColdFusion

Adobe ColdFusion has historically bundled Apache Derby as its default embedded database solution. 
  • Apache Derby (Embedded Mode): Included in the installer, it runs in the same Java Virtual Machine (JVM) as ColdFusion.
  • Use Cases: Ideal for small applications, development environments, or "Query-of-Query" (QoQ) logic that requires a temporary SQL engine to process memory-resident data.
  • Configuration: You can create a Derby datasource in the ColdFusion Administrator by selecting the Apache Derby Embedded driver. 

2. Lucee

Lucee is highly extensible and supports several embedded or lightweight database engines. 
  • HSQLDB (Hypersonic SQL): This is the default engine Lucee uses for its native Query of Queries (QoQ) when the internal engine is not sufficient.
  • H2 Database Engine: A popular, high-performance embedded Java database often used in the Lucee ecosystem for portable applications.
  • SQLite: While not bundled, Lucee can easily connect to SQLite .db files via a JDBC driver, offering a serverless, file-based database experience. 

3. BoxLang

BoxLang is designed for modern, portable deployment and leverages the best of the JVM’s embedded options.

  • Built-in Derby/H2 Support: Like its predecessors, BoxLang supports Apache Derby and H2 out of the box for lightweight storage.
  • In-Memory Databases: It is optimised to work with in-memory versions of these databases for high-speed automated testing or temporary data processing.
  • Mobile & Serverless: Because it can run on Android or in Lambda functions, using an embedded database like H2 allows BoxLang applications to remain entirely self-contained.

Apache Derby

  • Retired: As of October 2025, the Apache Derby project has been officially retired and moved into a read-only state.
  • No Further Updates: Development and bug fixes have concluded, and no new releases will be published.
  • Availability: Existing releases and documentation remain available for download on an as-is basis from the official Apache Derby website. 

H2 Database

Modes of Operation:

  • In-Memory: Data is stored in RAM and lost when the application stops, making it ideal for unit testing.
  • Embedded: The database runs within the same process as your Java application, storing data in a local file (typically with a .mv.db extension).
  • Server Mode: Runs as a standalone process that applications connect to via TCP/IP

SQLLite

Key Characteristics

  • Single File Storage: A complete database (tables, indexes, and metadata) is stored in a single cross-platform disk file.
  • Serverless and Zero-Config: There is no server process to install, manage, or troubleshoot. It reads and writes directly to disk.
  • Compactness: The library is very small (roughly 600KB to 1MB), making it ideal for resource-constrained environments.
  • Dynamic Typing: By default, SQLite uses "manifest typing," allowing you to store any data type in any column (e.g., a string in an integer column). Note: Recent versions added "Strict Tables" for developers who prefer standard static typing.
  • Reliability: It is ACID-compliant and highly resistant to corruption even during system crashes or power failures.

HSQLDB database

Key Features

  • Operating Modes: Supports In-Memory (fastest, no disk persistence), Embedded/Standalone (persistent local files), and Server Mode (accessible via TCP/IP or HTTP).
  • Table Types:
    • Memory Tables: Default type; data is kept in RAM but persisted to a script on disk for recovery.
    • Cached Tables: For large datasets, only a portion is kept in memory to save RAM.
    • Text Tables: Can directly use CSV or delimited files as SQL tables.
  • Transactions: Features fully multi-threaded MVCC (Multiversion Concurrency Control) and two-phase locking for high-performance transaction processing.
  • Small Footprint: The entire engine is contained in a single JAR file (around 1.3–2.6 MB depending on the version).

Test results 02/04/2026

  • On the test machine
  • UCanAccess unzipped, and 5 JAR files were successfully added to Adobe 64-bit ColdFusion Servers 11 and 12. <path>\cfusion\lib\
  • MS Access 32-bit Datasource successfully added via Administrator Portal
  • Datasource successfully added to Application.cfc
  • Simple <cfquery> test successful

Resources

References

  • Reference

Repository

  • Home > Ajabbi Research > Library >
  • Home > Handbook > 

Last Updated

03/04/2026

CFML 64-bit and 32-bit datasource

By: Mike Peters and Gemini
On a Sandy Beach: 02/04/2026

Mike is the inventor and architect of Pipi and the founder of Ajabbi.

Google Gemini is cool.

In ColdFusion 11, you can define application-specific datasources directly in your Application.cfc, removing the requirement to pre-configure them in the ColdFusion Administrator. [1, 2] 

Defining Datasources in Application.cfc

To create a datasource within your application, use the this.datasources structure. This allows your application to be more portable across different environments. [2, 3] 

// Application.cfc
component {
    this.name = "myApp";
    
    this.datasources = {
        myDSN = {
            driver = "MSSQLServer", // e.g., MSSQLServer, MySQL, etc.
            host = "localhost",
            database = "myDatabase",
            username = "db_user",
            password = "db_password"
        }
    };
    
    // Set as the default for the entire app
    this.datasource = "myDSN"; 
}

32-bit vs. 64-bit Considerations

The bitness of your ColdFusion installation (32-bit vs. 64-bit) primarily impacts ODBC connections and legacy drivers: [4] 

  • Driver Compatibility: 64-bit ColdFusion 11 requires 64-bit JDBC drivers. Most modern databases (SQL Server, MySQL, [Oracle](https://www.oracle.com/)) use JDBC, which is generally platform-independent.
  • Microsoft Access/Excel: On 64-bit ColdFusion 11, connecting to Access databases is often problematic because the "ODBC Agent and Server" typically runs as a 32-bit process. This can lead to errors like "Unable to update the NT registry" when trying to create a DSN via the Administrator.
  • Solution for Legacy Apps: If you must use 32-bit drivers (like certain Access versions), you may need to use a 32-bit version of ColdFusion or configure a 32-bit ODBC bridge, though utilizing [modern JDBC drivers](https://helpx.adobe.com/coldfusion/configuring-administering/data-source-management-for-coldfusion.html) is the recommended path for 64-bit environments. [5, 6, 7, 8, 9] 

Key Benefits in ColdFusion 11

References

The specific JDBC connection string parameters for a particular database like MySQL or Oracle.

Connecting a 64-bit ColdFusion 11 instance to a 32-bit MS Access database via Application.cfc is generally not supported directly because 64-bit Java applications (which power ColdFusion) cannot natively load 32-bit DLL-based ODBC drivers. [1, 2, 3]

To achieve this, you must use a bridge or a specific driver configuration.

Recommended Workaround: Using an ODBC Socket

Instead of a direct file path, you must first register a 32-bit System DSN on your Windows server and then call it via the ODBC Socket driver in your Application.cfc. [4] 

  1. Create a 32-bit System DSN:
    • Open the 32-bit ODBC Administrator located at C:\Windows\SysWOW64\odbcad32.exe.
    • Create a System DSN (e.g., named myAccess32bit) pointing to your .mdb or .accdb file using the Microsoft Access Driver.
  2. Configure Application.cfc:
    • Define the datasource using the ODBC Socket driver, which allows the 64-bit ColdFusion engine to communicate with the Windows ODBC layer. [5, 6, 7, 8] 
// Application.cfc
component {
    this.name = "accessApp";

    this.datasources = {
        myAppDSN = {
            driver = "ODBCSocket",
            dsn = "myAccess32bit", // Must match the System DSN name exactly
            username = "",
            password = ""
        }
    };

    this.datasource = "myAppDSN";
}

Critical Troubleshooting Tips

  • Driver Availability: Ensure the Microsoft Access Database Engine is installed on the server. If you have 64-bit Office installed, you may need to install the 32-bit runtime using the /passive command-line flag to avoid conflicts.
  • NT Registry Error: If you see "Unable to update the NT registry," it typically means ColdFusion is trying (and failing) to create a 64-bit DSN for a 32-bit driver. Manually creating the DSN in the SysWOW64 ODBC tool as described above bypasses this.
  •  JDBC Alternative: For a cleaner 64-bit setup without ODBC, consider using a third-party Type 4 JDBC driver (like UCanAccess), which is platform-independent and can be dropped into your cfusion/lib folder. [2, 9, 10, 11, 12, 13]

References

The UCanAccess JDBC configuration steps to avoid using Windows ODBC entirely

Using the UCanAccess JDBC driver is the most reliable way to connect 64-bit ColdFusion 11 to a 32-bit MS Access database because it bypasses the Windows ODBC layer entirely. [1, 2, 3]

1. Download and Prepare the Driver

  1.    1. Download the latest UCanAccess "bin" zip file from [SourceForge](https://sourceforge.net/projects/ucanaccess/).
  2.    2. Extract the zip file. You will need the following 5 JAR files:
    • ucanaccess-x.x.x.jar (the main driver)
    • lib/commons-lang3-x.x.jar
    • lib/commons-logging-x.x.jar
    • lib/hsqldb-x.x.jar
    • lib/jackcess-x.x.jar [3, 4, 5, 6, 7] 

2. Add to ColdFusion Classpath [8] 

To make the driver "visible" to ColdFusion, place these files in the server's library folder: [6, 8] 

  • Location: Copy all 5 JAR files to {ColdFusion_Install_Dir}\cfusion\lib (e.g., C:\ColdFusion11\cfusion\lib).
  • Restart: You must restart the ColdFusion Application Server service for these changes to take effect. [4, 6, 9, 10, 11] 

3. Configure Application.cfc

Define the datasource using the "Other" driver type syntax. Note that the connection string uses forward slashes even on Windows. [12, 13] 

// Application.cfc
component {
    this.name = "myAccessApp";

    this.datasources = {
        accessJDBC = {
            // "class" tells ColdFusion which JDBC driver to load
            class = "net.ucanaccess.jdbc.UcanaccessDriver",
            // Use forward slashes for the file path
            url = "jdbc:ucanaccess://C:/path/to/your/database.accdb",
            // Access usually doesn't require a username/password unless protected
            username = "",
            password = ""
        }
    };

    this.datasource = "accessJDBC";
}

Key Implementation Notes

  • No Admin Needed: Once configured in Application.cfc, you do not need to add anything to the [ColdFusion Administrator](https://helpx.adobe.com/coldfusion/configuring-administering/data-source-management-for-coldfusion.html) UI.
  • File Paths: If your path contains spaces, ensure they are handled correctly. UCanAccess generally supports standard Windows paths if formatted with forward slashes.
  • Performance: UCanAccess is a "pure Java" driver, meaning it is often faster and more stable than the old ODBC-JDBC bridge for 64-bit environments. [1, 2, 12, 13, 14] 

References

How to handle encrypted or password-protected Access files using this driver.

To rewrite this using tag-based syntax, you will define the this scope variables inside a <cfcomponent> block.

For the UCanAccess JDBC driver to work, ensure you have already placed the 5 required JAR files in your CFUSION/lib folder and restarted the ColdFusion service.

Application.cfc (Tag-Based)

<cfcomponent>
    <cfset this.name = "myAccessAppTag version">
    
    <!--- Define the UCanAccess JDBC Datasource --->
    <cfset this.datasources = {
        accessJDBC = {
            class = "net.ucanaccess.jdbc.UcanaccessDriver",
            url   = "jdbc:ucanaccess://C:/path/to/your/database.accdb",
            username = "",
            password = ""
        }
    }>

    <!--- Set as the default datasource for all <cfquery> tags --->
    <cfset this.datasource = "accessJDBC">

    <cffunction name="onApplicationStart" returnType="boolean">
        <!--- Application initialization code here --->
        <cfreturn true>
    </cffunction>

</cfcomponent>

Important Details for Tag-Based Setup:

  • Case Sensitivity: While ColdFusion tags are not case-sensitive, the class string (net.ucanaccess.jdbc.UcanaccessDriver) is case-sensitive because it refers to a Java class.
  • Forward Slashes: Even in the tag version, use forward slashes (/) in the url path to avoid escaping backslash characters.
  • Placement: This file must be named Application.cfc (not .cfm) and placed in your application's root directory.

How to add a connection timeout or limit the number of connections within this structure?

In ColdFusion 11, you can control connection limits and timeouts by adding specific keys to your this.datasources structure.

Tag-Based Application.cfc with Advanced Settings [1]

To limit connections and set timeouts, use the limitConnections, restrictConnectionsTo, and timeout keys. Note that for UCanAccess, certain timeouts like loginTimeout are often passed within the url string as JDBC parameters. [2, 3, 4, 5] 

<cfcomponent>
    <cfset this.name = "myAccessAppAdvanced">

    <cfset this.datasources = {
        accessJDBC = {
            class = "net.ucanaccess.jdbc.UcanaccessDriver",
            <!--- loginTimeout (seconds) and inactivityTimeout (minutes) can be appended to the URL --->
            url = "jdbc:ucanaccess://C:/data/db.accdb;loginTimeout=30;inactivityTimeout=5",
            username = "",
            password = "",
            
            <!--- ColdFusion Connection Pooling Settings --->
            maintainConnections = true,
            limitConnections = true,
            restrictConnectionsTo = 10,   <!--- Max simultaneous connections --->
            timeout = 20,                 <!--- Idle timeout in minutes before CF closes a connection --->
            loginTimeout = 30             <!--- Seconds to wait for a login attempt --->
        }
    }>

    <cfset this.datasource = "accessJDBC">
</cfcomponent>

Key Property Explanations

  • limitConnections: Set to true to enable the cap on the number of simultaneous database hits.
  • restrictConnectionsTo: The maximum number of concurrent connections allowed for this specific datasource. For MS Access, keeping this low (e.g., 5-15) is recommended to prevent file locking issues.
  • timeout: The number of minutes ColdFusion will keep an unused connection in the pool before destroying it to save resources.
  • loginTimeout: The maximum time (in seconds) ColdFusion will wait to establish the initial connection before throwing an error.
  • inactivityTimeout (URL parameter): A [UCanAccess-specific setting](https://spannm.github.io/ucanaccess/20-getting-started.html) that shuts down the underlying HSQLDB instance if no connections are active for the specified minutes, releasing file system locks. [3, 6, 7, 8]

References

How to handle read-only access or exclusive locking through these connection parameters

In ColdFusion 11, you can control read-only access and exclusive file locking for MS Access through the UCanAccess connection URL. These parameters directly instruct the Java driver how to handle the physical .accdb or .mdb file.

1. Read-Only Access

To ensure the database is accessed in a strictly read-only mode, append readOnly=true to the JDBC URL. This is useful for reporting or preventing accidental data modification. [1, 2] 

2. Exclusive Locking

To prevent other processes (like the MS Access GUI or other ColdFusion instances) from modifying the file while your application is connected, use the openExclusive=true parameter. [3]

Updated Tag-Based Application.cfc

<cfcomponent>
    <cfset this.name = "myAccessAppAdvanced">

    <cfset this.datasources = {
        accessJDBC = {
            class = "net.ucanaccess.jdbc.UcanaccessDriver",
            <!--- 
                URL Parameters:
                - readOnly=true: Prevents any INSERT/UPDATE/DELETE operations.
                - openExclusive=true: Locks the file from other processes.
            --->
            url = "jdbc:ucanaccess://C:/data/db.accdb;readOnly=true;openExclusive=true",
            username = "",
            password = "",
            
            <!--- Pool management --->
            maintainConnections = true,
            limitConnections = true,
            restrictConnectionsTo = 5,   <!--- Lower limit recommended for Access to avoid locks --->
            timeout = 20
        }
    }>

    <cfset this.datasource = "accessJDBC">
</cfcomponent>

Critical Usage Notes

  • Property Separation: Multiple parameters in the url must be separated by semicolons (;).
  • Driver Evolution: In modern versions of UCanAccess, openExclusive replaces the older lockMdb parameter.
  • File Permissions: Even with readOnly=true in the code, the ColdFusion service account must have Read and Execute permissions on the folder containing the database file so it can create the temporary .laccdb locking file if needed.
  • Concurrency: Using openExclusive=true will cause other applications (including the MS Access desktop app) to fail to open the database for editing while ColdFusion has it open. [3, 4, 5, 6, 7]

References

How to implement row-level locking for specific queries using <cfquery>

In ColdFusion 2016 (v12), the core logic for defining datasources in Application.cfc remains identical to version 11, but the 32-bit vs. 64-bit conflict becomes more rigid as newer server environments drop legacy support.

Application.cfc (Version 2016/12 Tag Syntax)

The syntax below works for the 64-bit version of ColdFusion 2016, utilizing a DSN defined via the 64-bit ODBC Administrator. [1, 2] 

<cfcomponent>
    <cfset this.name = "myAppCF2016">
    
    <cfset this.datasources = {
        myAccessDSN = {
            <!--- 
                In 64-bit CF2016, you must use a 64-bit driver. 
                "ODBCSocket" acts as the bridge to the Windows ODBC service.
            --->
            driver = "ODBCSocket",
            dsn = "My64BitSystemDSN",
            username = "",
            password = "",
            
            <!--- Advanced Pooling for CF2016 --->
            limitConnections = true,
            restrictConnectionsTo = 10,
            maintainConnections = true,
            timeout = 20
        }
    }>

    <cfset this.datasource = "myAccessDSN">
</cfcomponent>

Key Differences & Setup for CF 2016 (v12)

  • Architecture Mismatch: 64-bit ColdFusion 2016 cannot talk to 32-bit ODBC drivers. If you try to use a 32-bit System DSN, you will receive an "Architecture Mismatch" error.
  • The "64-bit Access Engine" Fix: To use Access on a 64-bit CF 2016 server, you must install the 64-bit Microsoft Access Database Engine. This allows you to create a 64-bit System DSN that ColdFusion can actually see and use.
  • Service Requirements: For the ODBCSocket driver to work, the ColdFusion ODBC Server and ColdFusion ODBC Agent services must be running on the Windows machine.
  • UCanAccess as the Best Practice: Adobe and community experts increasingly recommend the [UCanAccess JDBC driver](https://helpx.adobe.com/coldfusion/configuring-administering/data-source-management-for-coldfusion.html) for CF 2016 and above to avoid the headache of Windows ODBC bitness entirely. [2, 3, 4, 5, 6, 7, 8] 

Why Bitness Still Matters in CF 2016

While you can run a 32-bit version of ColdFusion 2016 on a 64-bit Windows OS, it is not common for production. If you are on 64-bit CF 2016, you have two paths: [9] 

  1. Install 64-bit Access drivers and use a 64-bit System DSN.
  2. Use the UCanAccess JDBC driver (Java-based), which doesn't care about 32-bit or 64-bit Windows environments. [8, 10]

References

The specific UCanAccess configuration again, and help installing the 64-bit Microsoft Access Engine for the ODBC method

To configure the UCanAccess JDBC driver for ColdFusion 2016 (v12) using Application.cfc, follow these steps to bypass the 32-bit/64-bit ODBC limitations. [1, 2, 3] 

1. Driver Installation

Download the UCanAccess bin zip from [SourceForge](https://sourceforge.net/projects/ucanaccess/) and extract these 5 JAR files into your ColdFusion lib directory (typically C:\ColdFusion2016\cfusion\lib): [4, 5] 

  • ucanaccess-x.x.x.jar
  • commons-lang-x.x.jar (or commons-lang3)
  • commons-logging-x.x.jar
  • hsqldb.jar
  • jackcess-x.x.x.jar [3, 4, 5, 6, 7] 

Important: You must restart the ColdFusion 2016 service after adding these files. [8, 9]

2. Application.cfc Configuration (Tag-Based) [4]

Define the datasource using the class and url properties. This tells ColdFusion to use the specific Java driver instead of the built-in ones. [4, 10, 11] 

<cfcomponent>
    <cfset this.name = "myAccessAppCF2016">

    <cfset this.datasources = {
        accessDSN = {
            <!--- The exact Java class name of the UCanAccess driver --->
            class = "net.ucanaccess.jdbc.UcanaccessDriver",
            
            <!--- JDBC URL using forward slashes for the file path --->
            url = "jdbc:ucanaccess://C:/path/to/your/database.accdb",
            
            <!--- Required even if Access has no password; use 'admin' as default --->
            username = "admin",
            password = "",
            
            <!--- Recommended settings for performance and stability --->
            maintainConnections = true,
            limitConnections = true,
            restrictConnectionsTo = 10,
            timeout = 20
        }
    }>

    <!--- Automatically use this DSN for all queries in this app --->
    <cfset this.datasource = "accessDSN">
</cfcomponent>

3. Advanced Parameters

You can append these parameters to the url string (separated by semicolons) to fine-tune the connection: [3, 12, 13] 

  • ;memory=false: Recommended for large databases to prevent high RAM usage by the driver.
  • ;openExclusive=true: Locks the file to prevent other applications from editing it while ColdFusion is connected.
  • ;readOnly=true: Forces a read-only connection, useful for security or reporting. [6, 13, 14, 15, 16] 

Verification

Once saved, any <cfquery> without a datasource attribute will use this configuration. You can verify it by running a simple query like SELECT * FROM yourTable. If it fails, check the exception.log for ClassNotFound errors, which usually mean the JAR files aren't in the correct lib folder or the service wasn't restarted. [3, 17, 18, 19]

References

The specific SQL syntax adjustments often required when switching from standard ODBC to UCanAccess

When switching from the standard Microsoft Access ODBC driver to the UCanAccess JDBC driver in ColdFusion 11 or 2016, you may need to adjust your SQL syntax. UCanAccess is based on HSQLDB, which follows ANSI SQL standards more strictly than the legacy Access engine. [1, 2, 3] 

1. Date and Time Literals

UCanAccess supports the traditional Access # delimiter but is stricter about the format. [1, 4] 

  • ODBC Standard: Often allowed various string formats depending on regional settings.
  • UCanAccess: Prefers #MM/dd/yyyy# or #yyyy-MM-dd#. Avoid dd/MM/yyyy unless it is unambiguous.
  • Example: SELECT * FROM Orders WHERE OrderDate > #01/01/2024#. [1, 4] 

2. Wildcard Characters

UCanAccess is flexible and supports both Access-style and standard SQL wildcards. [1] 

  • Multi-character: Use % (standard) or * (Access).
  • Single-character: Use _ (standard) or ? (Access).
  • Example: WHERE LastName LIKE 'Sm%' or WHERE LastName LIKE 'Sm*' both work. [1] 

3. Case Sensitivity

By default, UCanAccess text comparisons are case-insensitive, matching standard Access behaviour. [1, 5] 

  • Column Names: While UCanAccess may return column names in UPPERCASE in metadata, you can still refer to them in your SQL using their original case.
  • Comparison: If you need case-sensitive searches, you must add ;ignoreCase=false to your JDBC connection URL in Application.cfc. [3, 5] 

4. Join and Update Syntax

Complex Access-specific JOINs in UPDATE statements often fail because HSQLDB (the underlying engine) requires standard ANSI syntax. [2] 

  • Access ODBC: UPDATE TableA INNER JOIN TableB ON ... SET TableA.Col = TableB.Col
  • UCanAccess Recommendation: Use subqueries for updates involving multiple tables.
UPDATE TableA SET Col = (SELECT Col FROM TableB WHERE TableA.ID = TableB.ID)WHERE EXISTS (SELECT 1 FROM TableB WHERE TableA.ID = TableB.ID)

[2] 

5. Built-in Functions

UCanAccess supports core Access functions like IIf(), Date(), and Now(), but VBA-specific or user-defined functions (UDFs) saved within the .accdb file will not work. [1, 6, 7] 

  • Supported: Left(), Right(), Mid(), Year(), Month(), DateAdd(), IIf().
  • Not Supported: Custom VBA modules or complex Access-only functions like Replace() (in some contexts) or Domain Aggregate functions (DLookup, DSum). [1, 6, 7, 8] 

6. Reserved Words and Brackets

Always use square brackets [] for table or column names that contain spaces or match SQL reserved words to avoid "Unexpected Token" errors. [1] 

  • Correct: SELECT [First Name] FROM [User Table] WHERE [Order] = 1. [1]

References

A list of common UCanAccess error codes to help troubleshoot existing queries that might fail after the switch

When troubleshooting UCanAccess in ColdFusion 11 or 2016, most errors are wrapped in a net.ucanaccess.jdbc.UcanaccessSQLException or surface as standard Java exceptions. [1, 2, 3, 4] 

Common JDBC & Connection Errors

  • java.lang.ClassNotFoundException: net.ucanaccess.jdbc.UcanaccessDriver:
    • Meaning: ColdFusion cannot find the driver files.
    • Fix: Ensure all 5 JAR files are in {CF_Install}\cfusion\lib and that you have restarted the ColdFusion service.
  • UcanaccessSQLException: Unsupported newer version: 32:
    • Meaning: You are trying to open a database version (often a very new Access format) that the current version of the Jackcess library (within UCanAccess) does not yet support.
  • given file does not exist:
    • Meaning: The path in your url string is incorrect or the ColdFusion service account lacks permissions to see it.
    • Fix: Use absolute paths with forward slashes (e.g., C:/data/my.accdb) and ensure the Windows service is running as a user with folder access. [2, 4, 5, 6, 7, 8] 

Common SQL Syntax Exceptions

  • UCAExc::: user lacks privilege or object not found: [Name]:
    • Meaning: The table or column name you referenced does not exist, or you are using a reserved word without brackets.
    • Fix: Wrap the name in square brackets: [Name].
  • Unexpected Token: [Token]:
    • Meaning: Syntax error, often caused by using Access-specific SQL that isn't ANSI-compliant (like complex INNER JOIN updates) or unquoted aliases containing special characters.
  • data exception: invalid datetime format:
    • Meaning: The date string being inserted doesn't match the required format.
    • Fix: Use <cfqueryparam cfsqltype="cf_sql_timestamp"> to let the driver handle the conversion automatically.
  • incompatible data type in operation:
    • Meaning: Typically occurs when trying to perform math on non-numeric fields or when inserting a string into a numeric/boolean column. [4, 9, 10, 11, 12, 13, 14] 

Internal Driver Errors

  • Feature not supported:
    • Meaning: You are attempting an operation that UCanAccess or the underlying HSQLDB engine does not support, such as DROP CONSTRAINT.
  • File marked as read only / Concurrent process access:
    • Meaning: Another application (like MS Access itself) has an exclusive lock on the file, or you haven't enabled openExclusive=true while another process is active. [15, 16, 17, 18] 

Check the [ColdFusion Administrator logs](https://helpx.adobe.com/coldfusion/configuring-administering/basic-troubleshooting-and-faqs.html) (exception.log or application.log) for the full stack trace to identify exactly which line in your code triggered the exception. [3, 19]

References

Help setting up a global error handler in Application.cfc to catch and log these specific database exceptions [18] 

To catch UCanAccess or other database errors globally, you can use the onError function in your Application.cfc. This prevents users from seeing raw Java stack traces and allows you to log the specific JDBC error message.

Global Error Handler (Tag-Based)

Add this function inside your <cfcomponent> block:


<cffunction name="onError" returnType="void" output="true">
    <cfargument name="exception" type="any" required="true">
    <cfargument name="eventName" type="string" required="true">

    <!--- 1. Log the error to ColdFusion's application.log --->
    <cflog file="#this.name#_errors" 
           type="error" 
           text="Event: #arguments.eventName#, Message: #arguments.exception.message#">

    <!--- 2. Check specifically for UCanAccess/Database errors --->
    <cfif structKeyExists(arguments.exception, "rootCause") AND 
          findNoCase("ucanaccess", arguments.exception.rootCause.type)>
        
        <h2>Database Connection Error</h2>
        <p>There was a problem communicating with the Access database.</p>
        
        <!--- Optional: Show details only in development environment --->
        <cfif cgi.remote_addr eq "127.0.0.1">
            <cfoutput>
                <p><strong>Detail:</strong> #arguments.exception.rootCause.message#</p>
            </cfoutput>
        </cfif>

    <cfelse>
        <!--- Handle non-database errors --->
        <h2>Application Error</h2>
        <p>An unexpected error occurred. Please try again later.</p>
    </cfif>

    <!--- 3. Stop further processing --->
    <cfabort>
</cffunction>

Why this is useful for UCanAccess:

  • Root Cause Analysis: JDBC errors are often nested. Accessing arguments.exception.rootCause gets you the actual UCanAccess driver message (e.g., "user lacks privilege") rather than a generic ColdFusion "Query Error."
  • Environment Filtering: The check for cgi.remote_addr ensures that sensitive database path information (often revealed in JDBC errors) is only shown to you on the local server, not to external users.
  • Custom Logging: By using <cflog>, you create a dedicated log file (e.g., myAccessApp_errors.log) in the ColdFusion logs directory, making it easier to track driver-specific crashes.

Testing the Handler

To test this, temporarily change your url in this.datasources to a file path that doesn't exist. Refresh your app, and you should see your custom "Database Connection Error" message instead of a standard CF error page.

How to wrap individual queries in cftry/cfcatch for more granular error handling

To handle errors at the query level, use <cftry> and <cfcatch> blocks. This is especially useful for UCanAccess because it lets you catch specific database issues—like a locked file or a syntax error—without crashing the entire page.

Example: Granular Query Error Handling

<cftry>
    <cfquery name="getUsers">
        SELECT [User ID], [User Name] 
        FROM Users
        WHERE Status = <cfqueryparam value="Active" cfsqltype="cf_sql_varchar">
    </cfquery>

    <!--- If the query succeeds, display data --->
    <cfoutput query="getUsers">
        #getUsers["User Name"]#<br>
    </cfoutput>

    <cfcatch type="database">
        <!--- Handle UCanAccess specific database errors --->
        <cfif findNoCase("ucanaccess", cfcatch.message) OR findNoCase("jackcess", cfcatch.detail)>
            <p style="color:red;">
                <strong>Database Error:</strong> 
                The Access database is currently locked or unavailable. 
                Please try again in a moment.
            </p>
        <cfelse>
            <p>A general database error occurred: <cfoutput>#cfcatch.message#</cfoutput></p>
        </cfif>
        
        <!--- Log the full error for the admin --->
        <cflog file="app_db_errors" type="error" text="SQL State: #cfcatch.sqlstate# | Message: #cfcatch.message#">
    </cfcatch>
    
    <cfcatch type="any">
        <!--- Handle other non-DB errors (like coding typos) --->
        <p>An unexpected application error occurred.</p>
    </cfcatch>
</cftry>

Why use cfcatch type="database"?

  1. Access Locking: Since MS Access is file-based, it’s prone to "File already in use" errors. A cfcatch block allows you to show a friendly "Please retry" message instead of a broken page.
  2. SQL State: The cfcatch.sqlstate variable often contains the specific vendor code from UCanAccess/HSQLDB, which helps identify if the error is a syntax problem or a connection timeout.
  3. Graceful Degraded Mode: If a non-essential query (like "Recent News") fails, you can catch the error and simply hide that section of the page while the rest of the application continues to work.

Pro-Tip: Using cfqueryparam

Always use <cfqueryparam> inside your queries. It not only prevents SQL injection but also helps the UCanAccess driver correctly map ColdFusion data types to Access/Java types, reducing the "Incompatible data type" errors common in 64-bit environments.

How to use transactional processing with <cftransaction> to ensure multiple Access updates succeed or fail together

In ColdFusion 11 and 2016, <cftransaction> ensures that multiple database operations are treated as a single "unit of work." If one query fails (e.g., a file lock occurs or a constraint is violated in Access), all previous queries in that block are rolled back.

Transactional Processing for MS Access

When using the UCanAccess driver, you must ensure that your Application.cfc datasource does not have readOnly=true enabled for these operations to work.

<cftry>
    <cftransaction action="begin">
        
        <!--- 1. Insert into the main table --->
        <cfquery name="addOrder">
            INSERT INTO Orders (OrderDate, CustomerID)
            VALUES (
                <cfqueryparam value="#now()#" cfsqltype="cf_sql_timestamp">,
                <cfqueryparam value="#form.custID#" cfsqltype="cf_sql_integer">
            )
        </cfquery>
        <!--- 2. Get the last ID (UCanAccess/HSQLDB specific syntax) --->
        <cfquery name="getID">
            SELECT IDENTITY() AS lastID FROM Orders
        </cfquery>
        <!--- 3. Insert into the related table --->
        <cfquery name="addItems">
            INSERT INTO OrderDetails (OrderID, ProductID, Qty)
            VALUES (
                <cfqueryparam value="#getID.lastID#" cfsqltype="cf_sql_integer">,
                <cfqueryparam value="#form.prodID#" cfsqltype="cf_sql_integer">,
                <cfqueryparam value="#form.qty#" cfsqltype="cf_sql_integer">
            )
        </cfquery>
        <cftransaction action="commit" />
        <p>Order processed successfully!</p>
    </cftransaction>
    <cfcatch type="database">
        <!--- If ANY query fails, roll back everything in the transaction --->
        <cftransaction action="rollback" />
        
        <p>Error: Transaction failed. No data was saved.</p>
        <cflog file="db_transactions" type="error" text="Rollback occurred: #cfcatch.message#">
    </cfcatch>
</cftry>

Key Considerations for MS Access Transactions

  • ACID Compliance: UCanAccess (via HSQLDB) provides full ACID support, meaning your .accdb file remains consistent even if the ColdFusion service crashes mid-transaction.
  • Identity Selection: While standard Access uses SELECT @@IDENTITY, UCanAccess often prefers SELECT IDENTITY() or SELECT LAST_INSERT_ID() to retrieve the most recent AutoNumber value within the current transaction.
  • Isolation Levels: Access is a file-based database, so complex isolation levels (like Serializable) are limited. The default behavior is usually sufficient for standard web apps.
  • Locking: Transactions can hold locks on the .accdb file longer than single queries. Keep your transaction blocks short to avoid "File already in use" errors for other users.

How to handle multi-table updates using this transactional logic

To perform multi-table updates in ColdFusion 11/12 (2016) using the UCanAccess driver, you wrap your queries in a <cftransaction> block. This ensures that if the update to the second table fails (e.g., due to a validation error or file lock), the first update is automatically reversed.

Multi-Table Update Pattern (Tag-Based)

In this example, we update a product's stock levels in one table and log the change in an audit table simultaneously.

<cftry>
    <cftransaction action="begin">
        
        <!--- 1. Update the Inventory table --->
        <cfquery name="updateStock">
            UPDATE Inventory 
            SET UnitsInStock = UnitsInStock - <cfqueryparam value="#form.qty#" cfsqltype="cf_sql_integer">
            WHERE ProductID = <cfqueryparam value="#form.prodID#" cfsqltype="cf_sql_integer">
        </cfquery>
        <!--- 2. Log the transaction in the Audit table --->
        <cfquery name="logChange">
            INSERT INTO StockLog (ProductID, ChangeQty, LogDate, UserID)
            VALUES (
                <cfqueryparam value="#form.prodID#" cfsqltype="cf_sql_integer">,
                <cfqueryparam value="-#form.qty#" cfsqltype="cf_sql_integer">,
                <cfqueryparam value="#now()#" cfsqltype="cf_sql_timestamp">,
                <cfqueryparam value="#session.userID#" cfsqltype="cf_sql_integer">
            )
        </cfquery>
        <!--- If we reach here without errors, save both changes permanently --->
        <cftransaction action="commit" />
        <p>Inventory updated and logged successfully.</p>
    </cftransaction>
    <cfcatch type="database">
        <!--- If either query fails (e.g., ProductID doesn't exist), undo everything --->
        <cftransaction action="rollback" />
        
        <p style="color:red;">
            <strong>Error:</strong> The update could not be completed. 
            No changes were made to the database.
        </p>
        
        <cflog file="inventory_errors" type="error" 
               text="Rollback on Product #form.prodID#: #cfcatch.message#">
    </cfcatch>
</cftry>

Critical Implementation Details

  • The Identity Function: If your second table needs an ID generated by the first query, use SELECT IDENTITY() immediately after the first INSERT within the same transaction.
  • Access File Locking: Because MS Access uses file-level locking, keep these transactions as fast as possible. If a transaction stays open too long, other users might receive a "Database is already in use" error.
  • UCanAccess Versioning: Ensure your Application.cfc does not have readOnly=true in the connection string, or these update/insert queries will fail instantly.
  • Constraint Checking: If your Access database has "Relationships" with "Enforce Referential Integrity" enabled, the rollback will trigger automatically if a foreign key violation occurs.

By , No comments:
Tags , , ,

Toward Ultra-Long-Horizon Agentic Science: Cognitive Accumulation for Machine Learning Engineering

Mike's Notes

Very useful.

Resources

References

  • Toward Ultra-Long-Horizon Agentic Science: Cognitive Accumulation for Machine Learning Engineering. arXiv:2601.10402

Repository

  • Home > Ajabbi Research > Library > Subscriptions > Turing Post
  • Home > Handbook > 

Last Updated

01/04/2026

Toward Ultra-Long-Horizon Agentic Science: Cognitive Accumulation for Machine Learning Engineering

By: Xinyu Zhu, Yuzhu Cai, Zexi Liu, Bingyang Zheng, Cheng Wang, Rui Ye, Jiaao Chen, Hanrui Wang, Wei-Chen Wang, Yuzhi Zhang, Linfeng Zhang, Weinan E, Di Jin, Siheng Chen, Yanfeng Wang
arXiv: 15/01/2026

Abstract

The advancement of artificial intelligence toward agentic science is currently bottlenecked by the challenge of ultra-long-horizon autonomy, the ability to sustain strategic coherence and iterative correction over experimental cycles spanning days or weeks. While Large Language Models (LLMs) have demonstrated prowess in short-horizon reasoning, they are easily overwhelmed by execution details in the high-dimensional, delayed-feedback environments of real-world research, failing to consolidate sparse feedback into coherent long-term guidance. Here, we present ML-Master 2.0, an autonomous agent that masters ultra-long-horizon machine learning engineering (MLE) which is a representative microcosm of scientific discovery. By reframing context management as a process of cognitive accumulation, our approach introduces Hierarchical Cognitive Caching (HCC), a multi-tiered architecture inspired by computer systems that enables the structural differentiation of experience over time. By dynamically distilling transient execution traces into stable knowledge and cross-task wisdom, HCC allows agents to decouple immediate execution from long-term experimental strategy, effectively overcoming the scaling limits of static context windows. In evaluations on OpenAI's MLE-Bench under 24-hour budgets, ML-Master 2.0 achieves a state-of-the-art medal rate of 56.44%. Our findings demonstrate that ultra-long-horizon autonomy provides a scalable blueprint for AI capable of autonomous exploration beyond human-precedent complexities.

Introduction

Refer to the original at arXiv >

By , No comments:
Tags ,

Cache me if you can: A Look at Common Caching Strategies, and how CQRS can Replace the Need in the First Place

Mike's Notes

A great article by Mario about CQRS.

Resources

References

  • Reference

Repository

  • Home > Ajabbi Research > Library >
  • Home > Handbook > 

Last Updated

31/03/2026

Cache me if you can: A Look at Common Caching Strategies, and how CQRS can Replace the Need in the First Place

By: Mario Bittencourt
Medium:SsenseTech : 28/10/2022

Principal Software Architect.

Caching data is a useful pattern for any application that needs to serve high traffic and finds itself with latency requirements incompatible with the selected persistence choice.

While simple at first, creating and maintaining a cache of your data has aspects often overlooked by those leveraging this pattern. In this article I will cover some of the challenges with caching, typical solutions used, and the notion of using Command Query Responsibility Segregation (CQRS) as a better strategy.

It’s (Almost) all About Latency

Low latency requests are a standard non-functional requirement, especially for e-commerce applications, as there is an established understanding that the business loses potential sales for every X milliseconds your application takes to serve data to your customers.

The path to addressing high latency, specifically for the retrieval of information, will most likely come in the form of adding a cache — a copy of your data — that can be retrieved significantly faster than if we attempted to do the same from the origin. Figure 1 illustrates this process.


Figure 1. The cache holds a copy of the data where the access is much faster than the origin.

Caching is widely used, from inside the microprocessor, to the network infrastructure used to serve the web pages you see and the applications that we run. While caching sometimes has additional advantages and objectives, such as providing reliability and redundancy, in this article I will focus primarily on the latency aspect.

Before we look at it further, let’s discuss if we really need a cache.

When is Caching Beneficial?

It may sound odd to bring up in an article about caching, but before deciding to add a cache make sure to actually consider if your application needs one. Customer-facing applications are usually more sensitive than server-to-server needs. A given result in a customer-facing application has more impact than in a server to server call that happens without user interaction.

Traditionally, the need for caching stems from your response being the result of a query that executes multiple joins behind the scenes. These joins are expensive, leveraging resources that can’t be used for other requests, taking time before delivering the response back to the client, and effectively limiting how many requests you can serve at the same time. Having a quickly accessible pre-joined result becomes enticing or even essential in conditions where your system is under high load.

With the development of NoSQL solutions such as CosmosDB and DynamoDB, it is possible to obtain low latency responses at scale, which may negate the necessity of adding more complexity to create and maintain a caching infrastructure. How those technologies achieve low latency is beyond the scope of this article, but suffice it to say it does not come without trade-offs, so make sure to read the fine print before making the switch.

Prior to implementing a cache in the development of your application, I recommend assessing the non-functional requirements and accessing the patterns you will be using. Your analysis may reveal that you do not need a cache at all. For example, if your application is not sending information back to someone who is browsing your e-commerce website, it may not matter if it takes 10 or 100 milliseconds to process your request.

If you decide to have a cache, and the expected end result is the same, there are different strategies to create a cache, each with their pros and cons. Let’s dive deeper into the mainstream ones.

Common Caching Strategies

Read-Through Caching

The first and simplest strategy is known as read-through. In this approach, your application will first attempt to read from your cache. If the requested information is not found, it will fetch from the original source, add that information to the cache, and then return to the client.

Figure 2. Read-through cache.

A pseudo-code of a read-through cache implementation can be seen below:

In best case scenario, the information resides in the cache and its contents are returned as is. Worst case, the information is not present in the cache and the origin must be accessed to retrieve the contents and add them to the cache prior to returning to the client.

Since there is a built-in fallback mechanism, it is not necessary for all data to fit in the cache and you may have an eviction strategy to remove entries that are too old and possibly outdated, or are not as frequently accessed and can be removed in order to save space.

Write-Through Caching

A second strategy, write-through, populates the cache automatically as part of each write to the origin.

Figure 3. The writing process persists in both the origin and cache.

The read process only reads from the cache.

A pseudo-code of a write-through cache implementation can be seen below:

This strategy will keep the cache in sync with the origin, and also has the upside of eliminating stale data (a problem explained in the next section), but comes with two additional costs: your cache needs to fit the entire data set, and your writes will be slower and more complex as they need to write to both persistence solutions and potential failures of one of them ie. writing to the origin succeeds but writing to the cache fails.

Write-Behind Caching

The final strategy I will review is write-behind, which flips the source of truth (SOT) to the cache.

Figure 4. In write-behind, the item is copied to the origin in a separate process.

A pseudo-code of the implementation can be seen below:

Similarly to the write-through, the cache must be able to hold the entire data set, but the SOT is temporarily the cache and eventually makes its way toward the origin. Because the information is first written to the cache, it is always up-to-date and retrieval will always return the information with low(er) latency.

Unfortunately, there are two complexities with this solution: the cache must be resilient to make sure it does not lose any information prior to it making its way to the origin, and there is an additional sync process to be developed and maintained.

Why Adding a Cache is a Non-Trivial Task

On the surface adding a cache is simple, take the read-through strategy as an example. You already have the ability to retrieve the data from the origin, then wrap that with a call that saves this data in the cache.

In reality, you will encounter several details that make even the most straightforward approach error-prone. Let’s look at the often overlooked pitfalls.

Stale Data

For an application to use caching, it must already accept that it will serve potentially outdated information. But how do we determine when the freshness of the data is good enough?

The simplest approach is to establish some time, also known as Time to Live (TTL), wherein after this elapses the data found in a cache is to be ignored. That’s great but what value should you put it at? 5 seconds? 5 minutes?

If you put a value too big, the information in the origin may have changed too much and you’ll be sending back a substantial amount of outdated information to clients. If you put it too small you may negate the benefits of the cache altogether, as the frequency of the requests for a particular entry is lower than the TTL. In the end, your application context should dictate the TTL you select.

Cache Stampede

As we saw when we looked at different cache strategies, we encounter situations where the data we are trying to receive is not yet on a cache or it has been deemed unfit for use. In those cases, a request to the origin of the data is required.

Nothing out of the ordinary, but imagine if the data you do not have in the cache is popular, which could be the case for a “hot” product just released and in high demand. You could have hundreds of requests for the entry arriving almost at the same time, all of them not finding the entry in the cache and triggering an expensive request to the origin.

Depending on the volume of these requests, they can cause overhead on the origin while trying to serve the same operation over and over again.

Figure 5. A stampede of requests to the same (missing) entry on the cache can overload the persistence.

There are established ways to deal with a cache stampede, from locking the access so only one request will actually trigger the access to the origin, to preemptively making sure an item is found in the cache prior to enabling any traffic to it.

All of these methods add complexity to the solution, which is rarely factored into the estimation or cost of maintenance.

Why CQRS can be your Cache

Command Query Responsibility Segregation is a pattern that has been around for a while. In a nutshell, it acknowledges that the needs for interaction differ between those that request some information and those that mutate the state of a given system.

A query is an action that does not mutate the state while a command does. Additionally, although not mandatory, CQRS implementations leverage different persistence solutions — or uses — between the query and command sides.

Figure 6. A simple example of CQRS with two different persistence used for write and read operations.

One reason is that the writing side is where the complexity lies to ensure the business rules are respected. The read side, on the other hand, may just require a (subset) of the entity and no logic to guard changes.

The write side will emit messages — events — representing the state changes that resulted from a command. You will use those messages to create and maintain the read side.

Figure 7. Sample implementation leveraging AWS services to stream events and build one or more read models.

Because the read side has no obligation to match the write side on the technology to use, you can select one that addresses the latency requirements. Figure 6 illustrates one example where it may not be possible to satisfy all access patterns using the same persistence technology. In this case, you can use the same events to build different models.

So let’s recap the properties commonly found in a cache:

  • Cached data is eventually consistent with the origin
  • Provides a pre-computed result
  • Satisfies a read-only pattern with low(er) latency requirements

With CQRS we end up with a solution that matches all the above, with a couple of advantages:

  • Contrary to most cache implementations, CQRS is not an afterthought but rather planned from the beginning
  • The read side can be simple as there is no need for repositories or manipulating entities. Simple data transfer objects (DTOs) can be used to represent the data.

Conclusion

Caching is a ubiquitous pattern used in application development. The typical cycle is to develop your application, deploy it, and after some time find out that you have to add a cache because your application can’t cope with demand.

On the surface, it may look like a trivial task, but managing TTLs and handling stampedes are two of the most overlooked complexities that you should factor in when deciding to add a cache.

Alternatively, if you are already developing an event-drive application (EDA), a potential solution instead of adding a cache is to leverage a CQRS pattern as it will make use of the already existing event approach and infrastructure to deliver the desired outcome from the get-go.

Finally, remember to challenge if your application really benefits from a specific low latency operation. Faster is often better but the cost to achieve it may be steep. No matter what solution you choose, make sure to distinguish between the desire to have caching and a truly advantageous use case where the effort required to implement caching has significant improvements on your application.

By , No comments:
Tags ,
Subscribe to: Posts (Atom)