Unable to insert BLOB to database

dev support
1

OS: Ubuntu 18.04.1 and Windows 10
Database: PostgreSQL 10 and 9.5
Lucee version 5.2.8.50
Datasource: PostgreSQL JDBC Driver
Checked: ‘Enable binary large object retrieval (BLOB)’

Lucee is unable to insert BLOB data into the database.

The error is:

ERROR: column “blob” is of type bytea but expression is of type bigint
Hint: You will need to rewrite or cast the expression.

The code is:

<cfif FileExists(file_path)>
    <cffile action="readBinary" file="#file_path#" variable="file_blob">
    <cftransaction>
        <cfquery datasource="blob_test" name="blob_test">
            INSERT INTO blob_test (id, blob)
            VALUES  (1, <cfqueryparam value="#file_blob#" cfsqltype='cf_sql_blob'>)
        </cfquery>
    </cftransaction>
</cfif>

Lucee seems to recognize BLOB data as BIGINT. Any workaround to solve this?

2

No idea about a work around to inserting blobs as blobs, but could you potentially convert the binary to a base64 string and store that, then convert it back to a binary on reading?

3

Thanks. I might resort to this and change the field type from BYTEA to TEXT if I’m not able to make cf_sql_blob work.