Hi,
I have a bytea column which I want to return as a base64 string. If I run the following sql query in postgreSQL (7.4.1):

Select encode(a.artist_photo2, 'base64') as artist_photo2 from artisttbl a where a.artist_id=46

I get:
artist_photo2

/9j/4AAQSkZJ.......more base64.......

If I run the same query in hibernate(Version 2.1.1) I get:

10:36:14,689 DEBUG SessionImpl:528 - opened session
10:36:14,691 DEBUG JDBCTransaction:37 - begin
10:36:14,695 DEBUG JDBCTransaction:41 - current autocommit status:false
10:36:14,723 DEBUG SessionImpl:3614 - SQL query: Select encode({a}.artist_photo2, 'base64') as {a.ArtistPhoto2} from artisttbl {a} where {a}.artist_id=:artist_id
10:36:14,732 DEBUG SessionImpl:2193 - flushing session
10:36:14,737 DEBUG SessionImpl:2321 - Flushing entities and processing referenced collections
10:36:14,739 DEBUG SessionImpl:2664 - Processing unreferenced collections
10:36:14,740 DEBUG SessionImpl:2678 - Scheduling collection removes/(re)creates/updates
10:36:14,742 DEBUG SessionImpl:2217 - Flushed: 0 insertions, 0 updates, 0 deletions to 0 objects
10:36:14,756 DEBUG SessionImpl:2222 - Flushed: 0 (re)creations, 0 updates, 0 removals to 0 collections
10:36:14,758 DEBUG SessionImpl:1745 - Dont need to execute flush
10:36:14,760 DEBUG BatcherImpl:192 - about to open: 0 open PreparedStatements, 0 open ResultSets
10:36:14,762 DEBUG SQL:223 - Select encode(a.artist_photo2, 'base64') as artist_p5_0_ from artisttbl a where a.artist_id=? limit ?
Hibernate: Select encode(a.artist_photo2, 'base64') as artist_p5_0_ from artisttbl a where a.artist_id=? limit ?
10:36:14,763 DEBUG BatcherImpl:227 - preparing statement
10:36:14,795 DEBUG Loader:196 - processing result set
10:36:14,815 DEBUG JDBCExceptionReporter:36 - SQL Exception
org.postgresql.util.PSQLException: The column name artist_id0_ not found.

my hibernate code is:
transaction = session.beginTransaction();

//Get Artist image
String queryString = "Select encode({a}.artist_photo2, 'base64') as " + photo2 + " as {a.ArtistPhoto2} from artisttbl {a} where {a}.artist_id=:artist_id";
artist = (String)session.createSQLQuery(queryString,"a",Artist.class)
.setParameter("artist_id",artID)
.setMaxResults(1)
.uniqueResult();

transaction.commit();

my map is:
<hibernate-mapping package="test">
<class name="Artist" table="artisttbl">
<id name="ID" column="artist_id" type="integer" unsaved-value="0">
<generator class="sequence">
<param name="sequence">artist_seq_id</param>
</generator>
</id>
<property name="ArtistName" column="artist_name" type="string" not-null="true"/>
<property name="ArtistInfo" column="artist_info" type="string"/>
<property name="ArtistPhoto1" column="artist_photo1" type="binary"/>
<property name="ArtistPhoto2" column="artist_photo2" type="binary"/>
<property name="ArtistPhoto3" column="artist_photo3" type="binary"/>
</class>
</hibernate-mapping>

What obvious point am I overlooking here?

many thanks in advance

...that (as the docs says) createSQLQuery only supports returning mapped objects - not scalars.

If you want scalar results then just do session.connection().<put ya' sql here>

for your reply Max, but could you elaborate a little bit more on

session.connection().<put ya' sql here>

Did you mean as in:

session.connection().prepareStatement???

many thanks in advance

session.connection() give you the underlying JDBC connection. Do plain SQL/JDBC with it.

could you point me in the direction of an example as I am not fully understanding what you mean within the realms of Hibernate.

many thanks in advance

Session.connection() returns the underlying database connection resource so you can use the JDBC API to perform the query and thus the JDBC result set to collect the data. You still use the same Transaction API you are using with hibernate and close the session as normal.

thanks for the explanation. All works fine now. but just for future reference, is there no way of doing what i wanted to do within the Hibernate API??

many thanks in advance

Andrew

ps the notification system is not working when a reply is posted here. I am not being updated via email that a reply has been made to my post!

Action: failed
Status: 5.2.3
Diagnostic-Code: smtp;552 5.2.3 This message is larger than the current system limit or the recipient's mailbox is full. Create a shorter message body or remove attachments and try sending it again.

I have checked my mailbox and all is fine this end, I am well within my mailbox limit.

I don't know, thats what your MX says if the forum sends you an e-mail. Ask your provider. Oh, its hotmail. Change your provider.

provider i changed and all is working fine now. Cheers. Andrew..

I actually think that the docs are rather ambiguous on this -- it would there was a very explicit "native queries only work with persistent classes, not value types -- if you just want to return values, use JDBC directly" at the top of chapter 15.

I just filed an enhancement request in JIRA regarding this:
http://opensource.atlassian.com/project ... wse/HB-846

must say that I fully agree with this as well.

Peter

I'm new to hibernate and it took me a while to figure out that I needed to use the underlying JDBC to do the query I wanted, since I just wanted it to return an array of objects from a query using "group by" and "having".

I guess it's obvious now, but I am so thick that I would need something like this rained down on me:
"If you just want to do a query that returns a bunch of results, and you don't want to populate the members of some class that you create for this purpose, then just use JDBC."

No, use a HQL projection query, as documented.