Sybase database support

This is a wiki page for the RiftSaw-306 issue.

 

Problem:

Sybase JConnector dirver doesn't support the 'text' data type with null value. It is having problem as same as this jira issue. http://opensource.atlassian.com/projects/hibernate/browse/HHH-3975 Because the JPA's @Lob annotation will generate the 'text' data column type in Hibernate.

 

Unperfect workaround (by using jTDS driver):

 

One workaround to this problem would be to use the jTDS driver, but this dirver does NOT support XA transaction for sybase. If you don't need to have XA transaction support, you can take this approach by doing following updates.

1) update your jdbc Driver class to: net.sourceforge.jtds.jdbc.Driver

2) update your connection url in following format: jdbc:jtds:sybase://localhost:5000/database

 

Experiments on using jConnector driver :

1. using hibernate specific annotation.

 

[Jeff Yu] below is the steps for adding hibernate specific annotation.

1) Added a class for SybaseText, like this one from jBPM3 source code.

2) Added a package-info.java class with the content like following:

 

@TypeDefs({
        @TypeDef(name="sybaseText", typeClass=org.apache.ode.dao.jpa.SybaseTextType.class)
})
package org.apache.ode.dao.jpa.bpel;
import org.hibernate.annotations.TypeDef;
import org.hibernate.annotations.TypeDefs;

@TypeDefs({

        @TypeDef(name="sybaseText", typeClass=org.apache.ode.dao.jpa.SybaseTextType.class)

})

 

package org.apache.ode.dao.jpa.bpel;

 

import org.hibernate.annotations.TypeDef;

import org.hibernate.annotations.TypeDefs;

3) Updated entities files with @Lob annotation to @Type(type="sybaseText")
With this approach, it has following issues.
1). In the dao-jpa-hibernate module, it will have following exceptions when using hibernate built-in tool for generating DDL.
/Users/jeffyu/works/jboss/RiftSaw-ODE-2.1.x/dao-jpa-hibernate/build.xml:61: org.hibernate.MappingException: Could not determine type for: sybaseText, at table: BPEL_ACTIVITY_RECOVERY, for columns: [org.hibernate.mapping.Column(DETAILS)]

 

......

 

By using the hibernate specific annotatioins in the source code, because we used the annotation, this means that all databases will share the same metadata, this means that we can NOT use this approach, as it will break all the other existing databases.

In the JPA specification, we can override annotations by using orm.xml, but current orm.xml doesn't support the vendor extension support, so we can NOT define the custom data map as what jBPM3 did in the hibernate configuration file.

 

2. set default NULL value.

 

[Jeff Yu] I tried to add @prePersist, @preUpdate to add the default NULL value for the 'text' fields. The involved Entities are: ActivityRecoveryDAO, FaultDAO, MessageDAO, MessageExchangeDAO, PartnerLinkDAO and XmlDataDAO. Also need to add the @postPersist, @postUpdate and @postLoad to set those fields back to null if their value is the default NULL value.

 

After I've added these listeners, I found that it also has problem with the 'image' data type, this 'image' data type doesn't go well with the null value, so I need to add a listener for the ProcessInstanceDAO entity.

 

After this, I've seen the following error:

 

23:25:07,462 WARN  [EndpointFactory] Couldnt create any endpoint for element <?xml version="1.0" encoding="UTF-8"?>
<data>null</data>
23:25:07,841 INFO  [STDOUT] class org.apache.ode.dao.jpa.listener.MessageExchangeDAOSybaseListenerNUll value
23:25:07,852 ERROR [BpelRuntimeContextImpl] Couldn't find endpoint for partner EPR <?xml version="1.0" encoding="UTF-8"?>
<data>null</data>

 

I've used the '<?xml version="1.0" encoding="UTF-8"?><data>null</data>' for default NULL value, it seems to me that when I set the field into object, it gets passed to other places before it goes to the @PostPersist method, which will set the model back to null value.

 

Also, with this approach, it may adds performance burder, cause it will go through these 7 Listeners, although for current existing working databases, like postgres, mysql, oracle etc.

 

So this approach doesn't look like a viable solution.

 

3. Using hibernat.ejb.cfgfile

 

Hiberante EntityManager offere a property that can be used to point to a hibernate.cfg.xml file. .. To Be Continued....

 

Discussion:

 

[Jeff Yu] Given current Apache ODE supports both OpenJPA and Hibernate by using JPA annotation, also current JPA xml configuration file doesn't support vendor extension (like something we can add for Hibernate JPA provider only), it is very hard for us to fix this sybase support issue. Tried the set default 'NULL' value approach, as I described above, didn't make to work either.

 

So if we want to support the jConnector driver, we will need to fall back our JPA annotation to the Hibernate xml configuration file, as what we did on jBPM3, but this would have problem for the ODE community, as they go with OpenJPA.

 

What do you think?

Reference:

 

1. jBPM3 sybase support solution.

2. jBPM3 on sybase