1 Reply Latest reply on Jul 17, 2002 10:54 PM by John-Luc Bakker

    JBoss/Hypersonic: how to inspect tables with SQL?

    John-Luc Bakker Newbie

      Folks,

      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.

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

      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','')]

        • 1. Re: JBoss/Hypersonic: how to inspect tables with SQL?
          John-Luc Bakker Newbie

          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 some a 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','')]