Announcement Announcement Module
No announcement yet.
Reverse Engineer - DB2 Packed Field Issue Page Title Module
Move Remove Collapse
Conversation Detail Module
  • Filter
  • Time
  • Show
Clear All
new posts

  • Reverse Engineer - DB2 Packed Field Issue

    I got the reverse engineer command to successfully generate code for a table that exists on a System i using the DB2_400 libraries (jt400). It appears as though all the field types and lengths are correct in dbre.xml, however, when I attempt to start up the web app, I get a Hibernate error indicating that it was unable to map one field. Below is the line from the dbre.xml

    <column description="Created By Emp. ID" name="CREATED_BY" primaryKey="false" required="true" scale="0" size="10" type="3,DECIMAL"/>
    And this is the definition of the field from the DDS:


    Finally, here is the error:

    org.hibernate.HibernateException: Wrong column type in TST1.JWGTEMP.OFF753F for column CREATED_BY. Found: decimal, expected: numeric(19,2)

    It appears as though it found the field correctly (decimal), but for some strange reason it expects a numeric(19,2).

    Has anyone else experienced this issue? I'm thinking of trying to tweak the dbre.xml, but it already appears to be correct...

  • #2
    Yes, i had the same problem. NUMERIC and DECIMAL are supposed to be synonyms, but Hibernate insists on calling it NUMERIC, and DB2 is stubbornly storing DECIMALs. To solve the problem, use a customized DB2Dialect:

    public class CustomDB2Dialect extends DB2Dialect {
      public CustomDB2Dialect() {
        registerColumnType(Types.NUMERIC, "decimal($p,$s)");
        registerColumnType(Types.DECIMAL, "decimal($p,$s)"); 

    and register it in persistence.xml with:

          <property name="hibernate.dialect" value="com.example.db.CustomDB2Dialect" />


    • #3
      Thanks a bunch, I am going to give this a try. Are you using java.sql.Types?


      • #4
        Yes, java.sql.Types.


        • #5
          Thanks again, that seemed to resolve that issue. Now I have another; it's trying to access SYSIBM.SYSSEQUENCES, which I believe should be QSYS2.SYSSEQUENCES. By default, Roo appears to use jt400 version 6.7; this may be the reason. I will need to see if I can upgrade to the latest version of jt400 without breaking Roo functionality.

          EDIT: Upgrading to jt400 version 7.4 did not resolve the issue. It is still looking in SYSIBM for that table. I will have to look into this further, maybe look at the source code for jt400 (unless someone else already has the answer :-) )
          Last edited by jwglista; Jun 15th, 2011, 10:30 AM.


          • #6
            Hm, never had that, but i'm using LUW, maybe that doesn't have that problem. The sequence name is set in DB2Dialect, too, so you could add this

            	public String getQuerySequencesString() {
            		return "select seqname from qsys2.syssequences";

            to CustomDB2Dialect.


            • #7
              Thanks so much again for the help. You must have been using Hibernate for some time on the System i.

              I am now getting *another* type conversion error. This time I have a column in the table that is defined as TIMESTAMP, but Hibernate is expecting a "date" type:

              Caused by: org.hibernate.HibernateException: Wrong column type in TST1.JWGTEMP.OFF753F for column CREATED_DT. Found: timestamp, expected: date

              I see the column defined inside an .aj file with a TemporalType of "date", which from what I understood, should be the correct way to handle this situation:

              @Column(name = "CREATED_DT", nullable = false)
                  @DateTimeFormat(style = "S-")
                  private Date Off753fPK.createdDt;
              In an attempt to fix this issue, I added two more lines to the CustomDB2Dialect constructor to register both the DATE and TIMESTAMP column types as "date". This did not work, however. Any ideas on this one?


              • #8
                Is the column definition in the database table a DATE or a TIMESTAMP? For DATE, the annotation should be TemporalType.DATE, as in

                  @Column(name = "VALID_FROM")
                  private Date           validFrom;
                and for TIMESTAMP it should read:

                  @Column(name = "CHANGED_AT")
                  private Date           changedAt;
                You can use Date as the Java type for both, Hibernate will map them correctly (stripping the time part for DATE columns).

                If your database column is a TIMESTAMP and Roo dbre'd that to TemporalType.DATE that seems wrong to me. Maybe a bug in dbre? You might want to file a ticket for that.

                Meanwhile, you should try to change the annotation. Push the field from the aspect into your class, replace the TemporalType.DATE with TemporalType.TIMESTAMP, and try again.


                • #9
                  I actually did exactly what you suggested earlier today, and it did fix that issue. However, ANOTHER mapping issue has now been encountered. I have a field defined in my table as CHAR(80), which is acceptable in DB2_400. However, if you look at the source for DB2400Dialect, it has the char column type defined as CHAR(1). When Hibernate attempts to perform the mapping at runtime, it discovers a char, but expects a VARCHAR.

                  I'm beginning to wonder if it's going to be worth it to use Hibernate on the System i.


                  • #10
                    Try using the --includeNonPortableAttributes on the database reverse engineer command. It will define columnDefinition attributes for each column that match better the database used.

                    Please note that the dbre uses the JDBC driver of your database to build the columns. Going forward to the database with an ORM like Hibernate afterward is very different and is dependent on the ORM to map correctly. Also, we find that Hibernate is not the best JPA implementation to use in most cases. EclipseLink is the reference implementation of JPA 2 and usually is more compatible with various databases and moreover I have noticed less issues with it



                    • #11
                      If it helps, I always use the columnDefinition attribute of the @Column annotation. I put the value as is defined in the database, in order to avoid the errors like "found CHAR(80) expected VARCHAR".

                      @Column(name = "field_name", columnDefinition = "char(80)")
                      private String myField;