Can I use a MySQL LONGBLOB column type?

firelord · **Joined:** Fri Jul 16, 2004 6:22 pm **Posts:** 17

I am using a Hibernate UserType save a short[] as a Blob closely resembling the pattern given http://hibernate.org/73.html

I am using MySQL which has several flavors of Blob-esque columns( TINYBLOB, BLOB, MEDIUMBLOB, LARGEBLOB). The table to hold my short[] is always created using BLOB, but my objects are very large and require a LONGBLOB.

My problem stems that for the sqlTypes() method looks like:

return new int[]{Types.BLOB};

I didn't see a way to change thus behavior. Does one exist? I looked in the MySQLDialect class and Types.BLOB is mapped like:

registerColumnType( Types.BLOB, "LONGBLOB" );
registerColumnType( Types.BLOB, 16777215, "MEDIUMBLOB" );
registerColumnType( Types.BLOB, 65535, "BLOB" );

Is there someway I can force my column to be a LONGBLOB?

christian · **Posted:** Fri Jul 16, 2004 6:30 pm

Those BLOB types are all the same for JDBC, it's only a difference in your schema.

firelord · **Joined:** Fri Jul 16, 2004 6:22 pm **Posts:** 17

Christian,

Thanks for your reply. I'm not sure I understand your reply. Could you expand a little for me?

Thanks.

christian · **Posted:** Fri Jul 16, 2004 6:44 pm

You are using JDBC in your UserType, not Hibernate. Access your column/type like you would in plain JDBC.

firelord · **Joined:** Fri Jul 16, 2004 6:22 pm **Posts:** 17

I'm sure I'm just really missing something, or perhaps I'm not stating my problem clear enough. I am able to save and load my data without a hitch the column causes my data to be truncated (at 64K I believe). The problem I am having occurs at table creation time, which presumably happens (if needed) when Hibernate is initialized (at least that's when the tables first appear) The table created for my UserType has a column called 'slices' that is created as type BLOB, which does not have sufficient capicity. I need a MEDIUMBLOB or LONGBLOB.

AFAIK, besides the sqlTypes() method, a UserType has no control over table creation.

I hope I am making more sense now. I'm sorry if I'm just not getting what you're saying (probably the case).

christian · **Posted:** Fri Jul 16, 2004 7:13 pm

Map it with <column sql-type="FOO"/>...

firelord · **Joined:** Fri Jul 16, 2004 6:22 pm **Posts:** 17

Thanks for your reply. That sounded like a great idea, but I the UserType is actually an element of a <list> as follows:

Code:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
        "-//Hibernate/Hibernate Mapping DTD 2.0//EN"
        "http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">

<hibernate-mapping>
   <joined-subclass name="common.data.SCD" extends="common.data.CD" table="SCD">
           <key column="uid"/>
           
           <list name="slices" table="SCDS"
               cascade="all" lazy="true">
               
               <key column="slice_id"/>
             <index column="slice_index"/>
               <element column="slice"  type="common.tools.database.hibernate.usertypes.ShortArrayType">
                   
               </element>
               
           </list>
            
       
   </joined-subclass>
</hibernate-mapping>

I looked through the DTD and I didn't see a way to specify the sql-type for an element (although I certainly could have missed something)...

christian · **Posted:** Fri Jul 16, 2004 8:02 pm

Uhm, that should be possible. Maybe it's just missing in the DTD. Add it to your local copy (it's usually in hibernate2.jar) and see if it works. If it does, open a JIRA issue and report it as a minor bug.

firelord · **Joined:** Fri Jul 16, 2004 6:22 pm **Posts:** 17

I was able to add <column name="slice" sql-type="LONGBLOB"/> to the <element> tag, but it didn't seem to have any effect. The table was still created as a BLOB. AFAICS, that is the only place to use the sql-type attribute.

For completeness, my new map looks like:

Code:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
        "-//Hibernate/Hibernate Mapping DTD 2.0//EN"
        "http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">

<hibernate-mapping>
   <joined-subclass name="common.data.SCD" extends="common.data.CD" table="SCD">
           <key column="uid"/>
           
           <list name="slices" table="SCDS"
               cascade="all" lazy="true">
               
               <key column="slice_id"/>
             <index column="slice_index"/>
               <element column="slice"  type="common.tools.database.hibernate.usertypes.ShortArrayType">
                   <column name="slice" sql-type="LONGBLOB"/> 
               </element>
               
           </list>
           
      
   </joined-subclass>
</hibernate-mapping>

christian · **Posted:** Fri Jul 16, 2004 8:19 pm

Remove the column attribute.

firelord · **Joined:** Fri Jul 16, 2004 6:22 pm **Posts:** 17

Good point. After I did that, everything worked great. Thanks for all your help, and thanks for the great product that is Hibernate.

Do you think this warrents an article in the wiki area, or will this forum thread be sufficient to save the next poor soul?

Thanks again for all your help.

firelord · **Joined:** Fri Jul 16, 2004 6:22 pm **Posts:** 17

Also, would it make sense to add the sql-type attribute to the <element> tag to avoid future confusion?

christian · **Posted:** Fri Jul 16, 2004 8:30 pm

Actually, all is good. The DTD has the <column> for <element>. You just didn't know about sql-type, I guess.