dukebank with HSQL

[chwang]

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

[sboddula]

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

[garybor]

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?

[duane5000]

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.

[dferquian]

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.

[borjelewin]

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

[borjelewin]

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

[borjelewin]

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!

[pwaingankar]

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.

[selpiso]

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

[astrill]

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

[mcdiarcr]

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