5 Replies Latest reply on Aug 23, 2005 12:59 AM by Jack Frosch

    Using Firebird 1.5

    Jack Frosch Newbie

      I'm an absolute newbie to JBoss Portal, but I thought I'd like to get Portal 2.0 working with the Firebird 1.5 database and jboss-4.0.3RC2. (jboss-4.0.3RC2 seemed to be working okay without the Portal stuff, so I felt it was stable enough for configuring the Portal services.)

      I was optimistic when I read that any Hibernate-supported dialect should work, but am a bit less so after several failed attempts.

      To get started, I changed the hibernate.cfg.xml files in the jboss-portal.sar and portal-forums.ear directories to mandate the FirebirdDialect, I created an empty database, and deployed a portlet-firebird-ds.xml with the DB connection info. I then tested the connection outside JBoss to ensure my settings would work. (BTW, I'm using the FirebirdSQL Jaybird drivers.)

      However, when I started JBoss, a lot of exceptions were thrown indicating tables were not found. I browsed the database and saw that indeed, no tables were present. I then went into the JMX Manager and used the portal and portal.forums MBean services to create the schema and content. This created the tables and cleared most of the errors.

      I'm still getting an error because the JBP_ROLES table was not created. Here's the extract from server.log:

      2005-08-21 22:17:47,890 DEBUG [org.hibernate.hql.ast.QueryTranslatorImpl] HQL: from org.jboss.portal.core.impl.role.RoleImpl
      2005-08-21 22:17:47,890 DEBUG [org.hibernate.hql.ast.QueryTranslatorImpl] SQL: select roleimpl0_.jbp_rid as jbp1_0_, roleimpl0_.jbp_name as jbp2_0_, roleimpl0_.jbp_displayname as jbp3_0_ from jbp_roles roleimpl0_
      2005-08-21 22:17:47,906 DEBUG [org.hibernate.hql.ast.ErrorCounter] throwQueryException() : no errors
      2005-08-21 22:17:47,906 DEBUG [org.hibernate.jdbc.AbstractBatcher] about to open PreparedStatement (open PreparedStatements: 0, globally: 0)
      2005-08-21 22:17:47,906 DEBUG [org.hibernate.SQL] select first ? roleimpl0_.jbp_rid as jbp1_0_, roleimpl0_.jbp_name as jbp2_0_, roleimpl0_.jbp_displayname as jbp3_0_ from jbp_roles roleimpl0_
      2005-08-21 22:17:47,906 INFO [STDOUT] Hibernate: select first ? roleimpl0_.jbp_rid as jbp1_0_, roleimpl0_.jbp_name as jbp2_0_, roleimpl0_.jbp_displayname as jbp3_0_ from jbp_roles roleimpl0_
      2005-08-21 22:17:47,906 DEBUG [org.hibernate.jdbc.AbstractBatcher] preparing statement
      2005-08-21 22:17:47,921 DEBUG [org.hibernate.util.JDBCExceptionReporter] could not execute query [select roleimpl0_.jbp_rid as jbp1_0_, roleimpl0_.jbp_name as jbp2_0_, roleimpl0_.jbp_displayname as jbp3_0_ from jbp_roles roleimpl0_]
      org.firebirdsql.jdbc.FBSQLException: GDS Exception. 335544569. Dynamic SQL Error
      SQL error code = -204
      Table unknown
      JBP_ROLES
      At line 1, column 132.

      I browsed the database and saw the table was not there. I'm not sure if other tables are missing. Here are the ones I have:

      JBP_FORUMS_CATEGORIES
      JBP_FORUMS_FORUMS
      JBP_FORUMS_FORUMWATCH
      JBP_FORUMS_POLLS
      JBP_FORUMS_POSTERS
      JBP_FORUMS_POSTS
      JBP_FORUMS_TOPICS
      JBP_FORUMS_TOPICSWATCH
      JBP_FORUMS_WATCH
      JBP_ROLE_MEMBERSHIP
      JBP_USER_PREF
      JBP_USER_PREF_PROP_VALUE
      JBP_USER_PREF_SET

      I looked through the Portal download for an SQL file that might show all the DDL, but didn't see the DB schema published in the Reference or User Guide. However, I can infer a lot about the the jbp_roles table from the
      RoleImpl.hbm.xml file.

      I did reread the install instructions a couple of times, but still may be overlooking something obvious.

      I'm open to any suggestions except switching from JBoss or Firebird! :) Just point me to where all the

      As soon as I can get my install working, I'll post step-by-step instrcutions for any work-arounds I needed to take to get JBoss Portand and Firebird working together.

      TIA for any help getting Jboss 4.0, Portal 2.0, and Firebird 1.5 working together. I really hope I've made some simple mistake in my setup because I'm eager to learn about the Portal service and developing some portlets!

      jack

        • 1. Re: Using Firebird 1.5
          Jack Frosch Newbie

          Sorry for the sentence fragment:

          >> Just point me to where all the

          It should have been deleted before I posted my message. I was going to say, just point me to where all the table creation activity is supposed to take place and I will investigate why Firebird isn't doing so.

          jack

          • 2. Re: Using Firebird 1.5
            Roy Russo Master

            All you should need to do is hardwire the SQL dialect for hibernate:

            org.hibernate.dialect.FirebirdDialect

            Its covered in the user guide: http://docs.jboss.com/jbportal/v2.0Final/user-guide/en/html/installation.html#d0e679

            • 3. Re: Using Firebird 1.5
              Jack Frosch Newbie

              Roy,

              >> All you should need to do is hardwire the SQL dialect
              for hibernate <<

              That's what I thought, too. Yet, after two fresh installs of the database and Portal services, I get the same results: the tables are not created and after I create the schema and content through JMX, the JBP_ROLES tables is still not created.

              Maybe the third time will be a charm? I'll try a fresh install of everything tonight (JBoss, Portal, Forums, and the database). I'll post my results.

              In case it doesn't work, is there an SQL file around for the table creation?

              Thanks for responding.

              Jack

              • 4. Re: Using Firebird 1.5
                Roy Russo Master

                I can provide you a DB dump for my MySQL install, but I'm not familiar with FireBird enough to know what to change.

                • 5. Re: Using Firebird 1.5
                  Jack Frosch Newbie

                  Roy,

                  Well, the problem definitely seems to be Firebird related. I did a fresh install of JBoss 4.02 and an empty database. I got the same results as before.

                  I then repeated the above, but used the default Hypersonic DB and it portal-hsqldb-ds.xml provided in the setup. All the tables were apparently created okay and the default Portal 2.0 screen came up.

                  So, now the question is why? I'll start with the mapping files and see if I can figure out why the Hibernate table generation isn't working.

                  I would appreciate getting the MySQL DDL dump (or a hint on coercing one out of Hypersonic.)

                  Thanks for your help.

                  Jack