I started to work on a private project using JBoss Seam, Facelets, RichFaces (proven stack from Seam’s Booking demo 
) with PostgreSQL 8.3 as a backing database – database I prefer for years for whatever reason. Hibernate is used as the JPA provider (packaged with Seam anyway) and the most recent JDBC 4 driver for PostgreSQL is used.
I added column:
picture bytea;
Somehow – after reading 8.4 Binary Data Types – I thought that bytea is the right guy for the job. Of course I had an entity with proper annotation and field:
private byte[] picture;
@Lob
@Basic(fetch = FetchType.LAZY)
public byte[] getPicture() {
return picture;
}
public void setPicture(byte[] picture) {
this.picture = picture;
}
What was my surprise when following exception occurred:
java.sql.SQLException: ERROR: column "picture" is of type bytea but expression is of type oid
Now – when I know the solution – this message is pretty clear. It would be in case different types were mentioned in the message. While I understood that “he” somehow dislikes bytea, I couldn’t understand what the hell “oid” (as an “object identifier”) has to do with it?
I started my investigation with Google in one hand and PostgreSQL in the other. While 8.16 Object Identifier Types was not very helpful – as in “what does have ‘unsigned four-byte integer’ to do with my byte array?!” – some googled pages convinced me that oid indeed is that type I should use – mostly one from postgresql.com domain. Truth is that I didn’t understand why and how that oid type manages to store my byte array. But I’ve tried it, it stores it, it reads it, no exception is thrown.
Now I’ve finished second part of my investigation and the results are:
- There is special Large Object facility in PostgreSQL and it’s based on using oid type that references actual large object stored in different table behind the scene.
- JDBC documentation also mentions that you can use oid instead of bytea.
- In that same chapter you can learn that bytea is supported since JDBC 7.2 version – that is not very long ago.
- I don’t know if JPA/Hibernate can use bytea, but – honestly – I don’t care while this works fine. And it does.
So if you use PostgreSQL along with JPA and bytea type is throwing an exception, try oid. I’m not sure how well JDBC supports bytea, I don’t know why JPA wants to use oid and if other JPA providers (Toplink?) can use bytea. It’s not straightforward to use oid as a type for binary data – because you can learn this only in JDBC documentation and not in the mainstream PostgreSQL manual (I don’t count that chapter 31 because you’ll likely never use it while creating your tables). If you google this with along with “JPA” or “@Lob” your results might not lead you to proper articles quickly enough… so hopefully this post might help it a bit.
If you have some facts to add, feel free to comment.
shashi said
I came across the same issue …I am using OID datatype to store image…
id | branding_logo
—+—————
1 | 206533
2 | 206534
3 | 206535
9 | 206543
7 | 206537
How can i identify that is store the image…
Please help me..
shashi
virgo47 said
I’m not sure what you need. I don’t care what’s in that oid column – I leave it to PostgreSQL/Hibernate and when I ask for byte[] from an entity, I always get what I want.
shashi said
I am using jboss seam …and richfaces .. for file upload..
I used bytea for storing image into daatabase … in postgres 8.3
I got exception
is of type bytea but expression is of type Integer…
Then i change to OID datatype same as bolb in oracle…. .
This is my java class :
private byte[] brandingLogo;
@Column(name = “branding_logo”)
@Lob
public byte[] getBrandingLogo() {
return this.brandingLogo;
}
public void setBrandingLogo(byte[] brandingLogo) {
this.brandingLogo = brandingLogo;
}
public void fileUploadListener(UploadEvent event) throws Exception{
upload = event.getUploadItem();
if (upload.isTempFile()) {
file = upload.getFile();
} else {
ByteArrayOutputStream b = new ByteArrayOutputStream();
b.write(upload.getData());
}
}
public UploadItem getUpload() {
return upload;
}
public void setUpload(UploadItem upload) {
this.upload = upload;
}
public File getFile() {
return file;
}
public void setFile(File file) {
this.file = file;
}
But when i store it…
id | branding_logo
—+—————
1 | 206533
2 | 206534
3 | 206535
9 | 206543
7 | 206537
How can i identify that is store the image… in database..
I guess it store sequence number…
Please help me…
shashi
virgo47 said
I guess there would be null in branding_logo if there is no image. Byte array is stored elsewhere – this is PostgreSQL’s magic. Data are stored in some “secret” Postgresql store and oid is only referencing them. Can you obtain the image back from the entity? I can’t understand your problem – you don’t mention if it works or not. It works for me. I can display the picture directly with seams tag referencing the property of the entity.
I wrote everything I know into the blog post. Sorry.
shashi said
In then front end this is my xhtml file:
—————————————–
…..
….. [ Other Text Fields.....]
……
LOGO
…..
……
…..
….
Java Entity class :
——————
….
…..
…..
private byte[] brandingLogo;
…..
…..
@Column(name = “branding_logo”)
@Lob
public byte[] getBrandingLogo() {
return this.brandingLogo;
}
public void setBrandingLogo(byte[] brandingLogo) {
this.brandingLogo = brandingLogo;
}
…..
…..
…..
My JavaHome Class :
——————-
@In(scope=ScopeType.SESSION,required=false)
@Out(scope=ScopeType.SESSION, required=false)
File file;
@In(scope=ScopeType.SESSION,required=false)
@Out(scope=ScopeType.SESSION, required=false)
UploadItem upload;
/* ****** File upload section ***** */
// UploadItem upload;
public void fileUploadListener(UploadEvent event) throws Exception{
upload = event.getUploadItem();
if (upload.isTempFile()) {
file = upload.getFile();
} else {
ByteArrayOutputStream b = new ByteArrayOutputStream();
b.write(upload.getData());
}
}
public UploadItem getUpload() {
return upload;
}
public void setUpload(UploadItem upload) {
this.upload = upload;
}
public File getFile() {
return file;
}
public void setFile(File file) {
this.file = file;
}
————————————————————————
public String persist() {
try {
SaasTenants saasTenants = getInstance();
/* ****** File upload section ***** */
if(upload!=null){
saasTenants.setBrandingLogo(file.getAbsolutePath().getBytes());
String fileName = upload.getFileName();
int postion = fileName.lastIndexOf(’.');
saasTenants.setLogoExtension(fileName.substring(postion, fileName.length()));
}
saasTenants.setCreationDate(new Date());
saasTenants.setUpdateDate(new Date());
entityManager.persist(saasTenants);
}
——————————————————————————
Sorry for not mentioning if it works or not….
I am using
richfaces-ui-3.2.1.GA.jar
richfaces-api-3.2.1.GA.jar
richfaces-impl-3.2.1.GA.jar files
In richfaces i set immediateUpload=”true” so when i select a image file
I calls then fileUploadListener method in java home class….
UploadItem upload; (for getting the extension..)
File file; ( for getting the upload file…)
from this object i can able to get bytes of the selected image and extension..setBrandingLogo
I put this object in session scope…
Then i entered all the textfield like…
name
Address… etc..
When i submit the form i get the byte from the (File file;) object and set to branding..
at the time i have byte array… with values…
Then i call persist() in java Home class to insert image…
Please can u suggest me is this the right way to do it….
shashi
shashi said
Here is xhtml file…
shashi said
my xhtml file..
rich:fileUpload id=”fileUploadID” immediateUpload=”true”
fileUploadListener=”#{saasTenantsHome.fileUploadListener}”
listWidth=”460″ listHeight=”60″
immediate=”true”
virgo47 said
I don’t know, I’ve never tried upload. Please try to display the image. I can’t help you. Try something like:
<s:graphicImage value=”#{entity.picture}”…/>
If it displays the image, it’s good. I really can’t help you more – your questions are probably better for forums: http://seamframework.org/Community/Forums
vl_avan said
I had the same problem with
@Lob
@Basic(fetch = FetchType.LAZY, optional = false)
public byte[] getContentData() {
return contentData;
}
The solution is to omit @Lob annotation leaving just
@Basic(fetch = FetchType.LAZY, optional = false)
public byte[] getContentData() {
return contentData;
}
Brian Krahmer said
I ran across this same error message. I solved it by setting my object’s byte[] field to a new byte[0] instead of null before saving it into the database.
cheers