Large Objects may not be used in auto-commit mode ERROR

dev support
1

I’m getting this error when inserting a PDF binary object into a database. I’m not having this issue with ACF on my local test, only happens on the lucee deployment…

I found this on the forum: Exclusive Connections for Request commit problem

which is the only thing close… but that’s not my issue. for whatever reason, it seems to be setting auto-commit on, and I don’t have that set, and I’m not doing a transaction. I also couldn’t find any specific settings in the datasource or for the cfquery tag to turn that off?

maybe I’m missing something? any suggestions?

here is the code for reading and inserting:

<cffile action="readBinary" file="#thisfiledir##thisfilename#" variable="thisfile">

<cfquery name="insert_invoice_draft" datasource="#application.qs_writer#">
	INSERT INTO invoice_drafts	(invoice_draft_name, business_id, invoice_file)
	VALUES (
			<cfqueryparam value="#thisfilename#" cfsqltype="CF_SQL_VARCHAR" maxlength="255">, 
			<cfqueryparam value="#session.businessid#" cfsqltype="CF_SQL_INTEGER">, 
			<cfqueryparam value="#thisfile#" CFSQLType="CF_SQL_BLOB">
		);
</cfquery>

lucee.runtime.exp.DatabaseException: Large Objects may not be used in auto-commit mode. at org.postgresql.largeobject.LargeObjectManager.createLO(LargeObjectManager.java:285)

here are my datasource settings:

this.datasources[“datasource”] = {
class: “org.postgresql.Driver”,
bundleName: “org.postgresql.jdbc”,
bundleVersion: “42.7.3”,
connectionString: “jdbc:postgresql://localhost:5433/dbname”,
username: “dbuser”,
password: “encrypted:string”,

// optional settings
blob:true, // default: false
clob:true, // default: false
connectionLimit:-1, // default:-1
liveTimeout:15, // default: -1; unit: minutes
validate:false, // default: false

};

here is the stack:

Version Lucee 6.0.1.83
Version Name Gelert
Release date Mar 18, 2024
Loader Version 5.3.9.133
Servlet Container Apache Tomcat/9.0.62
Java 11.0.15 (Eclipse Adoptium) 64bit
OS Windows Server 2019 (10.0) 64bit
Architecture 64bit
DatabaseName
string PostgreSQL
DatabaseVersion
string 16.3
DriverName
string PostgreSQL JDBC Driver
DriverVersion
string 42.7.3
2

Auto commit is the default transaction mode for most databases, and in ODBC JDBC SQL Server and probably SQLCLI as well.

In CF if you want auto commit mode to be disabled you should place the code in question inside a cftransaction block.