11 Replies Latest reply on Apr 6, 2006 2:44 PM by Cheryl McDiarmid

    dukebank with HSQL

    chahua wang Newbie

      Hi guys,

      I am setting up dukebank with HSQL databse.
      refer to http://docs.jboss.org/jbossas/getting_started/v4/html/dukesbank.html#X-22013

      When I did this step:

      ant -f jboss-build.xml db-create-table

      C:\j2eetutorial14\examples\bank>ant -f jboss-build.xml db-create-table
      Buildfile: jboss-build.xml

      db-create-table:
      [java] java.sql.SQLException: socket creation error
      [java] ScriptTool.init error: socket creation error
      [java] at org.hsqldb.jdbc.jdbcUtil.sqlException(Unknown Source)
      [java] at org.hsqldb.jdbc.jdbcConnection.(Unknown Source)
      [java] at org.hsqldb.jdbcDriver.getConnection(Unknown Source)
      [java] at org.hsqldb.jdbcDriver.connect(Unknown Source)
      [java] at java.sql.DriverManager.getConnection(Unknown Source)
      [java] at java.sql.DriverManager.getConnection(Unknown Source)
      [java] at org.hsqldb.util.ScriptTool.execute(Unknown Source)
      [java] at org.hsqldb.util.ScriptTool.main(Unknown Source)
      [java] Exception in thread "main" java.lang.NullPointerException
      [java] at org.hsqldb.util.ScriptTool.execute(Unknown Source)
      [java] at org.hsqldb.util.ScriptTool.main(Unknown Source)
      [java] Java Result: 1

      BUILD SUCCESSFUL
      Total time: 1 second
      C:\j2eetutorial14\examples\bank>



      I don't know what is wrong? Do I need to setup HSQL databse first? Please help, thank you very much!

      chwang

        • 1. Re: dukebank with HSQL
          shashi kanth boddula Newbie

          You will get this problem when you didn't modify the hsqldb-ds.xml file properly

          • 2. Re: dukebank with HSQL
            gary bor Newbie

            I can't login to the actual dukebank log on page.
            I created the db tables and the data is there.
            But, when I try to log in, the message in the server/log is
            DEBUG [org.jboss.security.auth.spi.DatabaseServerLoginModule] Bad password for username=200. also the when the client starts I see a lot of errors in the cmd promt [java] java.rmi.
            nested exception is:.ServerException: RemoteException occurred in server thread. Any ideas why it happenes?

            • 3. Re: dukebank with HSQL
              don rosengartner Newbie

              Part of the example app tutorial is how to set up encryption in the security API. If you have properly configured the encryption, then you MUST also have updated the db to reflect the encrypted password. On the flip side, you can skip the configuration of encryption(or undo it), and leave the DB table alone.

              Do a select on the user table and if the password reads j2ee, you know the security must NOT be using encryption.

              • 4. Re: dukebank with HSQL
                Daniel Fernandez Newbie

                I am getting the same problem as chwang. This is how my hsqldb-ds.xml file looks like:

                <?xml version="1.0" encoding="UTF-8"?>

                <!-- The Hypersonic embedded database JCA connection factory config
                $Id: hsqldb-ds.xml,v 1.15 2004/09/15 14:37:40 loubyansky Exp $ -->



                <local-tx-datasource>

                <!-- The jndi name of the DataSource, it is prefixed with java:/ -->
                <!-- Datasources are not available outside the virtual machine -->
                <jndi-name>DefaultDS</jndi-name>

                <!-- for tcp connection, allowing other processes to use the hsqldb
                database. This requires the org.jboss.jdbc.HypersonicDatabase mbean.-->
                <connection-url>jdbc:hsqldb:hsql://localhost:1701</connection-url>

                <!-- for totally in-memory db, not saved when jboss stops.
                The org.jboss.jdbc.HypersonicDatabase mbean necessary
                <connection-url>jdbc:hsqldb:.</connection-url>
                -->
                <!-- for in-process persistent db, saved when jboss stops. The
                org.jboss.jdbc.HypersonicDatabase mbean is necessary for properly db shutdown

                <connection-url>jdbc:hsqldb:${jboss.server.data.dir}${/}hypersonic${/}localDB</connection-url>
                -->
                <!-- The driver class -->
                <driver-class>org.hsqldb.jdbcDriver</driver-class>

                <!-- The login and password -->
                <user-name>sa</user-name>


                <!--example of how to specify class that determines if exception means connection should be destroyed-->
                <!--exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.DummyExceptionSorter</exception-sorter-class-name-->

                <!-- this will be run before a managed connection is removed from the pool for use by a client-->
                <!--<check-valid-connection-sql>select * from something</check-valid-connection-sql> -->

                <!-- The minimum connections in a pool/sub-pool. Pools are lazily constructed on first use -->
                <min-pool-size>5</min-pool-size>

                <!-- The maximum connections in a pool/sub-pool -->
                <max-pool-size>20</max-pool-size>

                <!-- The time before an unused connection is destroyed -->
                <!-- NOTE: This is the check period. It will be destroyed somewhere between 1x and 2x this timeout after last use -->
                <!-- TEMPORARY FIX! - Disable idle connection removal, HSQLDB has a problem with not reaping threads on closed connections -->
                <idle-timeout-minutes>0</idle-timeout-minutes>

                <!-- sql to call when connection is created
                <new-connection-sql>some arbitrary sql</new-connection-sql>
                -->

                <!-- sql to call on an existing pooled connection when it is obtained from pool
                <check-valid-connection-sql>some arbitrary sql</check-valid-connection-sql>
                -->

                <!-- example of how to specify a class that determines a connection is valid before it is handed out from the pool
                <valid-connection-checker-class-name>org.jboss.resource.adapter.jdbc.vendor.DummyValidConnectionChecker</valid-connection-checker-class-name>
                -->

                <!-- Whether to check all statements are closed when the connection is returned to the pool,
                this is a debugging feature that should be turned off in production -->
                <track-statements/>

                <!-- Use the getConnection(user, pw) for logins
                <application-managed-security/>
                -->

                <!-- Use the security domain defined in conf/login-config.xml -->
                <security-domain>HsqlDbRealm</security-domain>

                <!-- Use the security domain defined in conf/login-config.xml or the
                getConnection(user, pw) for logins. The security domain takes precedence.
                <security-domain-and-application>HsqlDbRealm</security-domain-and-application>
                -->

                <!-- corresponding type-mapping in the standardjbosscmp-jdbc.xml (optional) -->

                <type-mapping>Hypersonic SQL</type-mapping>


                <!-- This mbean can be used when using in process persistent hypersonic -->
                jboss:service=Hypersonic,database=localDB
                </local-tx-datasource>

                <!-- This mbean should be used only when using tcp connections. Uncomment
                when the tcp based connection-url is used.

                1701
                true
                default
                false
                true

                -->

                <!-- This mbean can be used when using in process persistent db -->

                localDB
                true



                Any ideas why I am getting the error?
                Thanks.

                • 5. Re: dukebank with HSQL
                  Borje Lewin Newbie

                  I have the same problem. Did anyone find out what's missing?
                  I tried to turn off my firewall, but it didn't work anyway.

                  /Borje

                  • 6. Re: dukebank with HSQL
                    Borje Lewin Newbie

                    Workaround
                    ========

                    This socket error occurs when running SQL scripts to create and fill the database tables. I could not get it to work and nobody on the forums I have used could pinpoint it either.

                    However, you can populate the database yourself and avoid using SQL scripts. Really, EJB is made to take care of these things anyway, right?

                    Start up the database manager from the JBOSS window (i.e. http://localhost:8080). Copy the scripts in and run them.

                    NOTE: The CONSTRAINT keyword is not understood by this manager, but you can just remove all the "CONSTRAINT ". The application works fine anyway.

                    /Borje

                    • 7. Re: dukebank with HSQL
                      Borje Lewin Newbie

                      With the help of my students, we sorted it all out: The last sentence in 4.1.7.1 should be replaced by the following sentences:
                      You must both delete the other MBean definition, and change the dependency on the datasource from jboss:service=Hypersonic,database=localDB to jboss:service=Hypersonic for the SQL scripts to work. Before running the SQL scripts, also make sure that the server is running, or you will get a socket creation error!

                      • 8. Re: dukebank with HSQL
                        Pramod Waingankar Newbie

                        I am also getting similar error while trying to deploy DukeBank sample example.

                        Can anyone tell step by step instruction to correct this problem and successfully deploy this app on JBOSS using HSQL.

                        Also, when I run the script directly in manager, I get some errors as well.


                        • 9. Re: dukebank with HSQL
                          sel piso Newbie

                          i think you should use apache ant as a tool to create ear file. try it out.

                          • 10. Re: dukebank with HSQL
                            Astri Ek Larsen Newbie

                            Hi!

                            I think I have the same problem as the rest of you. When I'm starting up jBoss, it tells me this:

                            17:01:06,847 ERROR [URLDeploymentScanner] Incomplete Deployment listing:
                            --- MBeans waiting for other MBeans ---
                            ObjectName: jboss.ejb:service=EJBTimerService,persistencePolicy=database
                            State: CONFIGURED
                            I Depend On:
                            jboss.jca:service=DataSourceBinding,name=DefaultDS

                            ObjectName: jboss.mq:service=InvocationLayer,type=HTTP
                            State: CONFIGURED
                            I Depend On:
                            jboss.mq:service=Invoker
                            jboss.web:service=WebServer

                            .......

                            --- MBEANS THAT ARE THE ROOT CAUSE OF THE PROBLEM ---
                            ObjectName: jboss:service=Hypersonic,database=localDB
                            State: NOTYETINSTALLED
                            Depends On Me:
                            jboss.jca:service=ManagedConnectionFactory,name=DefaultDS


                            Later, when I try to log into the bankapplication through http://localhost:8080/bank/main, jboss tells me:

                            17:07:26,306 ERROR [LogInterceptor] EJBException in method: public abstract java.util.Collection com.sun.ebank.ejb.account.AccountHome.findByCustomerId(java.lang.String) throws javax.ejb.FinderException:
                            javax.ejb.EJBException: ejbFindByCustomerId Unable to connect to database. Could not dereference object


                            My hsqldb-ds.xml file looks like this:
                            <?xml version="1.0" encoding="UTF-8"?>

                            <!-- The Hypersonic embedded database JCA connection factory config
                            $Id: hsqldb-ds.xml,v 1.15 2004/09/15 14:37:40 loubyansky Exp $ -->



                            <local-tx-datasource>

                            <!-- The jndi name of the DataSource, it is prefixed with java:/ -->
                            <!-- Datasources are not available outside the virtual machine -->
                            <jndi-name>DefaultDS</jndi-name>

                            <!-- for tcp connection, allowing other processes to use the hsqldb
                            database. This requires the org.jboss.jdbc.HypersonicDatabase
                            mbean.
                            -->
                            <connection-url>jdbc:hsqldb:hsql://localhost:1701</connection-url>

                            <!-- for totally in-memory db, not saved when jboss stops.
                            The org.jboss.jdbc.HypersonicDatabase mbean necessary
                            <connection-url>jdbc:hsqldb:.</connection-url>

                            -->
                            <!-- for in-process persistent db, saved when jboss stops. The
                            org.jboss.jdbc.HypersonicDatabase mbean is necessary for properly db shutdown

                            <connection-url>jdbc:hsqldb:${jboss.server.data.dir}${/}hypersonic${/}localDB</connection-url>
                            -->

                            <!-- The driver class -->
                            <driver-class>org.hsqldb.jdbcDriver</driver-class>

                            <!-- The login and password -->
                            <user-name>sa</user-name>


                            <!--example of how to specify class that determines if exception means connection should be destroyed-->
                            <!--exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.DummyExceptionSorter</exception-sorter-class-name-->

                            <!-- this will be run before a managed connection is removed from the pool for use by a client-->
                            <!--<check-valid-connection-sql>select * from something</check-valid-connection-sql> -->

                            <!-- The minimum connections in a pool/sub-pool. Pools are lazily constructed on first use -->
                            <min-pool-size>5</min-pool-size>

                            <!-- The maximum connections in a pool/sub-pool -->
                            <max-pool-size>20</max-pool-size>

                            <!-- The time before an unused connection is destroyed -->
                            <!-- NOTE: This is the check period. It will be destroyed somewhere between 1x and 2x this timeout after last use -->
                            <!-- TEMPORARY FIX! - Disable idle connection removal, HSQLDB has a problem with not reaping threads on closed connections -->
                            <idle-timeout-minutes>0</idle-timeout-minutes>

                            <!-- sql to call when connection is created
                            <new-connection-sql>some arbitrary sql</new-connection-sql>
                            -->

                            <!-- sql to call on an existing pooled connection when it is obtained from pool
                            <check-valid-connection-sql>some arbitrary sql</check-valid-connection-sql>
                            -->

                            <!-- example of how to specify a class that determines a connection is valid before it is handed out from the pool
                            <valid-connection-checker-class-name>org.jboss.resource.adapter.jdbc.vendor.DummyValidConnectionChecker</valid-connection-checker-class-name>
                            -->

                            <!-- Whether to check all statements are closed when the connection is returned to the pool,
                            this is a debugging feature that should be turned off in production -->
                            <track-statements/>

                            <!-- Use the getConnection(user, pw) for logins
                            <application-managed-security/>
                            -->

                            <!-- Use the security domain defined in conf/login-config.xml -->
                            <security-domain>HsqlDbRealm</security-domain>

                            <!-- Use the security domain defined in conf/login-config.xml or the
                            getConnection(user, pw) for logins. The security domain takes precedence.
                            <security-domain-and-application>HsqlDbRealm</security-domain-and-application>
                            -->

                            <!-- corresponding type-mapping in the standardjbosscmp-jdbc.xml (optional) -->

                            <type-mapping>Hypersonic SQL</type-mapping>


                            <!-- This mbean can be used when using in process persistent hypersonic -->
                            jboss:service=Hypersonic,database=localDB
                            </local-tx-datasource>

                            <!-- This mbean should be used only when using tcp connections. Uncomment
                            when the tcp based connection-url is used. -->


                            1701
                            true
                            default
                            false
                            true



                            <!-- This mbean can be used when using in process persistent db

                            localDB
                            true

                            -->




                            Can anyone please help me and tell me what I'm doing wrong here?

                            Astri

                            • 11. Re: dukebank with HSQL
                              Cheryl McDiarmid Newbie

                              I finally got this to work after reading just about every forum topic on HSQL and all the different error messages such as:
                              MBEANS THAT ARE THE ROOT CAUSE OF THE PROBLEM
                              jboss.jca:service=ManagedConnectionFactory,name=DefaultDS
                              jboss.jca:service=DataSourceBinding,name=DefaultDS
                              ejbFindByCustomerId Table not found: CUSTOMER_ACCOUNT_XREF
                              Verification of Enterprise Beans failed ???
                              HSQL-DB Connection Refused
                              The part that isn't clear in the tutorial or in the hsqldb-ds.xml comments is that the xml tag which is found ABOVE the mbean declarations (and just before the closing </local-tx-datasource> tag) needs to be altered as follows:
                              change from: jboss.server.Hypersonic,database=localDB
                              change to:jboss:service=Hypersonic

                              Hope this helps and I appreciate this forum!

                              Cheryl