Blob support??

[ZioNemo]

Hi,
after quite a lot of trial and error I managed to have something working.
Now I'm at next stumbling block:

I have, in some tables, some rather free-format fields that can range from nil to several hundreds of kilobytes (comment fields that can grow in short documents or images).

I thought to store them in Blobs.

Is there any support for Blobs in jpersist?
In the simplest form I have a (possibly long) string I want to store into a column that is defined as "Comment blob(10k)" (or something similar).

How should I declare this field in a class like:

public class Publisher extends Database implements SingleTableInheritance {
    protected Integer publisherID;
    protected String name;
    protected Blob comment;
   
    public Publisher(DatabaseManager dbm) {    super(dbm); }

    public Integer getPublisherID() { return publisherID; }
    public void setPublisherID(Integer publisherID) { this.publisherID = publisherID; }
    public String getName() { return name; }
    public void setName(String name) { this.name = name; }
    public ??String?? getComment() { ???? what here ???? }
    public void setComment(??String?? comment) { ???? what here ???? }
}

Later I will have a similar problem with some "CoverThumb blob(200k)" that will have to contain a picture (JPEG or PNG).

Thanks for Your Patience :)
Mauro

[Anonymous]

Hi Mauro,

It's probably returning a byte array.  However, this may be driver dependent.  You can define the method as returning an Object and print its class:

Object getColumn() {...}

System.out.println("obj=" + obj.getClass());

You can also check with:

db.executeQuery("select * from table");

if (db.hasNext() && db.next() != null)
db.getColumn("column_name");

Hope this helps,

Dave

[Anonymous]

Hi Mauro,

Sorry sent the previous by accident.

It's probably returning a byte array.  However, this may be driver dependent.  You can define the method as returning an Object and print its class:

Object getColumn() {...}

System.out.println("obj=" + obj.getClass());

You can also check with:

db.executeQuery("select * from table");

if (db.hasNext() && db.next() != null)
{
  Object obj = db.getColumnValue("column_name");
// then check and see what type obj is
}

Hope this helps,

Dave

[ZioNemo]

AAAARRRRGGGGHHHHHH!!!!

I'm about to throw the towel and collapse on the floor shivering in pain!

The latest incarnation of my xlob code is something like:

public class Publisher extends Database implements SingleTableInheritance {
    private static final long serialVersionUID = 1L;
   
    protected Integer publisherID;
    protected String name;
    protected Object comment;
   
    public Publisher(DatabaseManager dbm) {
        super(dbm);
    }
   
    public Integer getPublisherID() {
        return publisherID;
    }
    public void setPublisherID(Integer publisherID) {
        this.publisherID = publisherID;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        if (this.name != name) {
            this.name = name;
        }
    }
    public String getComment() {
        String s = null;
        if (comment != null)
        try {
            System.out.println("obj=" + comment.getClass());
            if (comment instanceof Clob) {
                Clob cl = (Clob) comment;
                int l = (new Long(cl.length())).intValue();
                s = cl.getSubString(1, l);
            }
        } catch (SQLException e) {}
        return s;
    }
    public void setComment(String comment) {
        try {
            if (this.comment == null)
                this.comment = getConnection().createClob();
            //this.comment.setString(1, comment);
            //Reader sr = new StringReader(comment);
            //Writer w = this.comment.setCharacterStream(1);
            this.setColumnValue("comment", comment);
        }
        catch (Throwable t) {
            System.out.println("setComment: Exception '"+t.getMessage()+"'");
        }
    }
}

with a DDL like:

s.execute(
    "create table Publishers (" +
    "    PublisherID int not null generated always as identity constraint publishers_pk primary key," +
    "    Name        varchar(32) not null," +
    "    Comment        clob(10k)," +
    "    www_comicbookdb_com_ID int" +
    ")"
);

This bombs when I try to setComment("stupid string.") with error:

setComment: Exception 'java.sql.SQLException: 'updateObject' not allowed because the ResultSet is not an updatable ResultSet. '

How in hell am I supposed to write a Blob/Clob to database? (reading it back will be next problem)
I could send You my complete test program, but I don't know how to attach a small (~5k) zip to this forum.

Please help me.
I have a fair amount of experience in programming (not only java) and database usage, but I never used DB in Java (that may well be my problem!).
I hoped Your package could help me, but I'm now very tangled.

Thanks in advance
Mauro

[Anonymous]

Hi Mauro,

Sorry for the frustrations.  I've looked into how Derby handles blobs and the following is what will work for you:

import java.sql.Clob;
import java.util.Iterator;
import java.util.Vector;
import java.util.logging.Level;
import javax.sql.rowset.serial.SerialClob;
import jpersist.DatabaseManager;

public class DerbyTest
  {
/*
CREATE TABLE PUBLISHER
(
   PUBLISHERID INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
   NAME VARCHAR(32) not null,
   COMMENT CLOB,
   WWW_COMICBOOKDB_COM_ID INTEGER
);
*/
    public static class Publish
      {
        private Integer publisherID, www_comicbookdb_com_ID;
        private String name;
        private Clob comment;

        public Integer getPublisherID() { return publisherID; }
        public void setPublisherID(Integer publisherID) { this.publisherID = publisherID; }

        public Integer getWww_comicbookdb_com_ID() { return www_comicbookdb_com_ID; }
        public void setWww_comicbookdb_com_ID(Integer www_comicbookdb_com_ID)
          {
            this.www_comicbookdb_com_ID = www_comicbookdb_com_ID;
          }

        public String getName() { return name; }
        public void setName(String name) { this.name = name; }

        public Clob getComment() { return comment; }
        public void setComment(Clob comment) { this.comment = comment; }
       
        public String toString() { return publisherID + ", " + name + ", "
                + comment + ", " + www_comicbookdb_com_ID; }
      }
   
    public static class Publisher
      {
        private Integer publisherID, www_comicbookdb_com_ID;
        private String name;
        private Object comment;

        public Integer getPublisherID() { return publisherID; }
        public void setPublisherID(Integer publisherID) { this.publisherID = publisherID; }

        public Integer getWww_comicbookdb_com_ID() { return www_comicbookdb_com_ID; }
        public void setWww_comicbookdb_com_ID(Integer www_comicbookdb_com_ID) { this.www_comicbookdb_com_ID = www_comicbookdb_com_ID; }

        public String getName() { return name; }
        public void setName(String name) { this.name = name; }

        public Object getComment() { return comment; }
        public void setComment(Object comment) { this.comment = comment; }
       
        public String toString() { return publisherID + ", " + name + ", "
                + comment + ", " + www_comicbookdb_com_ID; }
      }

    public static void main(String[] args)
      {
        try
          {
            DatabaseManager.setLogLevel(Level.FINEST);
            DatabaseManager dbm = new DatabaseManager("test", 2, "org.apache.derby.jdbc.EmbeddedDriver",
                                                      "jdbc:derby:/derby/test;create=true",
                                                      null, null, "test", "test");

            Publisher p = new Publisher();
            p.setName("name");
            p.setComment("This is a test");
            dbm.saveObject(p);
           
            Publish p2 = new Publish();
            p2.setName("Name");
            p2.setComment(new SerialClob("This is a test".toCharArray()));
            dbm.saveObject(p2);
           
            Vector v = (Vector)dbm.loadObjects(new Vector(), new Publisher());
           
            for (Iterator it = v.iterator(); it.hasNext();)
              {
                p = (Publisher)it.next();
                System.out.println(p);
                System.out.println(((Clob)p.getComment()).getSubString(1,20));
              }
          }
        catch (Exception e)
          {
            e.printStackTrace();
          }
      }
  }

The first class (PUBLISH) uses a Clob type and returns a Clob and I used a SerialClob to store the data.

The second class (PUBLISHER) uses an object.  In this case a Clob is returned from the database, but you can use just about any thing to store data, including Strings.

This is a driver dependent issue, as MySQL uses byte[] for both sides.

Hope this helps,

Dave

[Anonymous]

Hi Mauro,

I have additional information about your Derby/jPersist problems.  You have probably been seeing the following when you try to use any of the write methods of the Blob/Clob classes:

java.sql.SQLFeatureNotSupportedException: Feature not implemented: no details.
        at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source)
        at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Unknown Source)
        at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Unknown Source)
        at org.apache.derby.impl.jdbc.Util.notImplemented(Unknown Source)
        at org.apache.derby.impl.jdbc.Util.notImplemented(Unknown Source)
        at org.apache.derby.impl.jdbc.EmbedClob.setString(Unknown Source)

This is actually being produced by Derby as it doesn't support any updates  through these classes (Clob/Blob).  Derby only supports reads. 

See: http://db.apache.org/derby/docs/dev/ref/rrefjdbc96386.html

And refer to the tables (scroll down) showing the methods implemented in Derby for the Clob/Blob classes.  Only the read methods are supported, no write methods are supported.  The problems are actually in Derby.

Dave