Using Coldfusion to store and retrieve an image from Postgres

I am in the process of migrating a database from mssql express 2005 to postgr开发者_StackOverflow中文版esql 9.0. In mssql the column is an image type and in postgresql i'm using the bytea type.

<cffile 
       action="readbinary" 
       file="#ExpandPath('./uploads/')##theLogo.SERVERFILE#" 
       variable="myLogo">

<cfquery 
        name="saveLogo" 
        datasource="#SESSION.DSN#">
        UPDATE bright.group SET LOGO =  <cfqueryparam  
                                                       cfsqltype="cf_sql_blob"                    
                                                       value="#myLogo#">        
</cfquery>

The snippet above seems to work when I do a save but when I try to display the image with the snippet below I get nothing.

<cfquery 
       datasource="#SESSION.dsn#" 
       name="image">
        SELECT LOGO
        FROM bright.group
        WHERE groupid=#URL.groupid#
</cfquery>
<cfcontent variable="#image.LOGO#" type="image/png">

This works on mssql but not on postgres.

Any help/direction would be greatly appreciated. Thanks

Could you do a proper refactoring and abandon saving images in the database?

Use the file system for binary files and the database to keep the paths to these resources. It's going to speed up your application and make any future migration much easier.

Don't know much to explain the real mechanics but this is what I found out. The image gets stored differently. I opened up the returned images in a hex viewer. The postgresql text matched the mssql hex values from the 2nd byte to the end.

I tried everything to convert the postgresql output to no avail. It didn't help that the first byte was different. In the end I changed the data type from bytea to text and saved the encoded binary file instead.

<cffile action="readbinary" file="#ExpandPath('./logo.png')#" variable="myLogo">
<cfquery name="saveLogo" datasource="#session.dsn#">
        UPDATE bright.group SET LOGO = 
        (<cfqueryparam value="#BinaryEncode(myLogo,'hex')#" cfsqltype='cf_sql_longvarchar'> )
    </cfquery>

<cfquery datasource="#session.dsn#" name="qryGetLogo">
        SELECT logo
        FROM bright.group
        WHERE groupid=#groupid#
    </cfquery>

    <cfcontent  type="image/png" variable="#BinaryDecode(qryGetLogo.logo,'hex')#">

Hope someone finds this helpful.