JBoss/Hypersonic: how to inspect tables with SQL?

[jlbakker]

Folks,

I am reasonably new to JBoss, so please bear with me.
My Entity bean creates a table upon startup, according to the log file. It names the table "user" which corresponds to the JAWS <ejb-name>. The JAWS <table-name> is "usertable". When I try to write some SQL to access the table named "usertable" I get an exception.

So, what database, username and passwd is used by JBoss to create the table? And what is the table's name: "user" or "usertable"?

See relevant code and log below:

Many thanks,

John-Luc

JDBCCommand] Init SQL: CREATE TABLE user (passwd VARCHAR(256),login VARCHAR(256),lastName VARCHAR(256),userId VARCHAR(256),firstName VARCHAR(256),middleInitial VARCHAR(256))
JDBCCommand] Destroy SQL: DROP TABLE user
JDBCCommand] Exists SQL: SELECT COUNT(*) FROM user WHERE userId=?

It creates table "user", however in JAWS I named it "usertable".

<ejb-name>user</ejb-name>
<table-name>usertable</table-name>

Next, I try to set some values using SQL:

String driver = p.getProperty("driver","org.hsqldb.jdbcDriver");
String url = p.getProperty("url","jdbc:hsqldb:");
String database = p.getProperty("database","mas");
String user = p.getProperty("user","sa");
String password = p.getProperty("password","");

[..]

"insert into usertable values ('Jlbakker', 'Jlbakker', 'Bakker', 'John-Luc','')"

This results in:

INFO [STDOUT] Exception: java.sql.SQLException: Table not found: USERTABLE in statement [insert into usertable values ('Jlbakker','Jlbakker','Bakker','John-Luc','')]

[jlbakker]

Dear all,

I just checked "http://www.jboss.org/online-manual/HTML/ch04s04.html". With that information I will answer my own question and coin a new one.

> I am reasonably new to JBoss & JDBC, so please bear
> with me.
> My Entity bean creates a table upon startup,
> according to the log file. It names the table "user"
> which corresponds to the JAWS <ejb-name>. The JAWS
> <table-name> is "usertable". When I try to write
> some SQL to access the table from a servlet
> (bypassing the EJBs) named "usertable" I get a "table
> not found" exception.

My table gets the name specified by <ejb-name>. I should probably set <create-table> to false. That will tell JBOSS not to create the table with the <ejb-name>.

Apparantly I misconceived a number of things here. So, why is this table with the ejb-name created anyway? In the LOG file, the CREATE TABLE is followed by "JDBCCommand] Destroy SQL: DROP TABLE user". Just like to know why I misunderstood this.

> So, what database, username and passwd is used by
> JBoss to create the table? And what is the table
> name: "user" or "usertable"?

Assuming the table is created by me and <create-table> is set to false, I should know the database and credentials.

I am sorry for posting stuff that is mentioned in the online documentation. But, hey, the documentation was offline part of the day ...

Regards,

John-Luc

> See relevant code and log below:
>
> Many thanks,
>
> John-Luc
>
> Here is the log fragment that lets me think the table
> is created (it already exists).
>
> JDBCCommand] Init SQL: CREATE TABLE user (passwd
> VARCHAR(256),login VARCHAR(256),lastName
> VARCHAR(256),userId VARCHAR(256),firstName
> VARCHAR(256),middleInitial VARCHAR(256))
> JDBCCommand] Destroy SQL: DROP TABLE user
> JDBCCommand] Exists SQL: SELECT COUNT(*) FROM user
> WHERE userId=?
>
> It creates table "user", however in JAWS I named it
> "usertable". See fragment of my jaws file:
>
> <ejb-name>user</ejb-name>
> <table-name>usertable</table-name>
>
> Next, I try to set some values using SQL from an
> servlet/application. This is my guess for the values
> but I am sure the database property is wrong as I did
> not instruct the EJB to create the table in the "mas"
> database.
>
> String driver =
> p.getProperty("driver","org.hsqldb.jdbcDriver");
> String url = p.getProperty("url","jdbc:hsqldb:");
> String database = p.getProperty("database","mas");
> String user = p.getProperty("user","sa");
> String password = p.getProperty("password","");
>
> This is the query:
>
> "insert into usertable values ('Jlbakker',
> 'Jlbakker', 'Bakker', 'John-Luc','')"
>
> This results in:
>
> INFO [STDOUT] Exception: java.sql.SQLException:
> Table not found: USERTABLE in statement [insert into
> usertable values
> ('Jlbakker','Jlbakker','Bakker','John-Luc','')]

[schaefera]

An open-source tool to see the content of Hypersonic DB is "iSQL" which can be found on www.sf.net.

Have fun - Andy

[dobblou]

I tried to use iSQL with JBoss 3.0 and got the an error saying the Hypersonic JDBC driver is not JDBC compliant.
I tried with the JDBC driver from HSQLDB 1.7 and it didn't work either. Am I missing something? Accessing the DB through JBoss is fine.

Dobb


********************************************************

[01:39:31] iSQL-Viewer Console Ready...
[01:39:33] Using class org.hsqldb.jdbcDriver Version 1.6
[01:39:33] Driver States that it is not JDBC Compliant, Some Functions May Not Work Correctly
[01:39:33] Error Occured While Attempting to change iSQL Services.
[01:39:33] Exception :: SQLException(Connection-Exception :: Connection is broken: 1476,)
[01:39:33] java.sql.SQLException: Connection-Exception :: Connection is broken: 1476,
[01:39:33] at org.I00acres.lib.sql.DatabaseConnection.init(DatabaseConnection.java:433)
[01:39:33] at org.I00acres.isql.forms.frmiSQLMain.performServiceChange(frmiSQLMain.java:879)
[01:39:33] at org.I00acres.isql.forms.frmiSQLMain.promptForService(frmiSQLMain.java:1130)
[01:39:33] at org.I00acres.isql.forms.frmiSQLMain.performQuery(frmiSQLMain.java:918)
[01:39:33] at org.I00acres.isql.forms.frmiSQLMain.handleActionEvent(frmiSQLMain.java:693)
[01:39:33] at org.I00acres.isql.forms.frmiSQLMain$ActionManagerHandler.actionPerformed(frmiSQLMain.java:1228)
[01:39:33] at org.I00acres.lib.swing.ActionManager.processNextEvent(ActionManager.java:300)
[01:39:33] at org.I00acres.lib.swing.ActionManager.run(ActionManager.java:251)
[01:39:33] at java.lang.Thread.run(Thread.java:484)
[01:39:33] Connection to iSQL Service [myConnection] failed.
[01:39:33] No Database Service Connection. Re-Connect To Your Service and Try Again.