1 Reply Latest reply on Aug 15, 2006 10:37 AM by Ross Miller

    portlet JSP JDBC problem

    Ross Miller Newbie

      Alright guys, I'm trying to create a JDBC connection (or jndi) connection to a mysql database, and for some reason i think hibernate is not letting me. I can create a jsp on tomcat and it works there but not on jboss.

      here is the code

      public String getDB(){
       String data = "";
      
       try{
       InitialContext ctx = new InitialContext();
       DataSource ds = (DataSource)ctx.lookup("mysqlDS");
       Connection conn = ds.getConnection();
       Statement statement = conn.createStatement();
       ResultSet rs = statement.executeQuery("SELECT * FROM test");
      
      
       try {
       while (rs.next()) {
       data += rs.getString("id") + " <br>";
      
       }
       } catch (Exception e) {
       // TODO Auto-generated catch block
       e.printStackTrace();
       }
      
       conn.close();
      
       }catch(Exception s){
      
       s.printStackTrace();
       }
      
       return data;
       }


      Here is the DS file:



      (note i removed the username and password when posting)

      Pardon the junk code, but I'm just trying to get it working at the moment. <?xml version="1.0" encoding="UTF-8" ?>
      - <!-- $Id: mysql-ds.xml,v 1.3.2.3 2006/02/07 14:23:00 acoliver Exp $
      -->
      - <!-- Datasource config for MySQL using 3.0.9 available from:
      http://www.mysql.com/downloads/api-jdbc-stable.html

      -->
      -
      - <local-tx-datasource>
      <jndi-name>mysqlDS</jndi-name>
      <connection-url>jdbc:mysql://localhost:3306/jbossportal</connection-url>
      <driver-class>com.mysql.jdbc.Driver</driver-class>
      <user-name>****</user-name>
      ****
      <exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.MySQLExceptionSorter</exception-sorter-class-name>
      - <!-- should only be used on drivers after 3.22.1 with "ping" support
      <valid-connection-checker-class-name>org.jboss.resource.adapter.jdbc.vendor.MySQLValidConnectionChecker</valid-connection-checker-class-name>

      -->
      - <!-- 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 - MySQLValidConnectionChecker is preferred for newer drivers
      <check-valid-connection-sql>some arbitrary sql</check-valid-connection-sql>

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

      </local-tx-datasource>


      Now, When i tried this with a JDBC connection it simply said that it was a null pointer, but this returns nothing.

      Here is the log output:

      2006-08-14 14:56:26,183 DEBUG [org.hibernate.impl.SessionImpl] opened session at timestamp: 4733262996205589
      2006-08-14 14:56:26,183 DEBUG [org.hibernate.cache.StandardQueryCache] checking cached query results in region: org.hibernate.cache.StandardQueryCache
      2006-08-14 14:56:26,183 DEBUG [org.hibernate.cache.EhCache] key: sql: select 1 as col_0_0_ from jbp_cms_wsp_prop wspprop0_ where PROP_ID=?; parameters: 8b53c74f-878f-4f36-831f-83d61aaa9d24/{http://jboss.org/jcr/portalcms}language, ; named parameters: {}
      2006-08-14 14:56:26,183 DEBUG [org.hibernate.cache.StandardQueryCache] Checking query spaces for up-to-dateness: [jbp_cms_wsp_prop]
      2006-08-14 14:56:26,183 DEBUG [org.hibernate.cache.EhCache] key: jbp_cms_wsp_prop
      2006-08-14 14:56:26,183 DEBUG [org.hibernate.cache.EhCache] Element for jbp_cms_wsp_prop is null
      2006-08-14 14:56:26,183 DEBUG [org.hibernate.cache.StandardQueryCache] returning cached query results
      2006-08-14 14:56:26,199 DEBUG [org.hibernate.jdbc.JDBCContext] successfully registered Synchronization
      2006-08-14 14:56:26,199 DEBUG [org.hibernate.impl.SessionImpl] opened session at timestamp: 4733262996271104
      2006-08-14 14:56:26,199 DEBUG [org.hibernate.transaction.JTATransaction] Looking for UserTransaction under: UserTransaction
      2006-08-14 14:56:26,199 DEBUG [org.hibernate.transaction.JTATransaction] Obtained UserTransaction
      2006-08-14 14:56:26,199 DEBUG [org.hibernate.transaction.JTATransaction] begin
      2006-08-14 14:56:26,199 DEBUG [org.hibernate.transaction.JTATransaction] commit
      2006-08-14 14:56:26,199 DEBUG [org.hibernate.jdbc.JDBCContext] successfully registered Synchronization
      2006-08-14 14:56:26,199 DEBUG [org.hibernate.impl.SessionImpl] opened session at timestamp: 4733262996271105
      2006-08-14 14:56:26,199 DEBUG [org.hibernate.transaction.JTATransaction] Looking for UserTransaction under: UserTransaction
      2006-08-14 14:56:26,199 DEBUG [org.hibernate.transaction.JTATransaction] Obtained UserTransaction
      2006-08-14 14:56:26,199 DEBUG [org.hibernate.transaction.JTATransaction] begin
      2006-08-14 14:56:26,199 DEBUG [org.hibernate.transaction.JTATransaction] commit
      2006-08-14 14:56:26,199 DEBUG [org.hibernate.event.def.AbstractFlushingEventListener] processing flush-time cascades
      2006-08-14 14:56:26,199 DEBUG [org.hibernate.event.def.AbstractFlushingEventListener] dirty checking collections
      2006-08-14 14:56:26,199 DEBUG [org.hibernate.engine.Collections] Collection found: [org.jboss.portal.identity.db.UserImpl.dynamic#1], was: [org.jboss.portal.identity.db.UserImpl.dynamic#1] (initialized)
      2006-08-14 14:56:26,199 DEBUG [org.hibernate.engine.Collections] Collection found: [org.jboss.portal.identity.db.UserImpl.roles#1], was: [org.jboss.portal.identity.db.UserImpl.roles#1] (initialized)
      2006-08-14 14:56:26,199 DEBUG [org.hibernate.event.def.AbstractFlushingEventListener] Flushed: 0 insertions, 0 updates, 0 deletions to 1 objects
      2006-08-14 14:56:26,199 DEBUG [org.hibernate.event.def.AbstractFlushingEventListener] Flushed: 0 (re)creations, 0 updates, 0 removals to 2 collections
      2006-08-14 14:56:26,199 DEBUG [org.hibernate.pretty.Printer] listing entities:
      2006-08-14 14:56:26,199 DEBUG [org.hibernate.pretty.Printer] org.jboss.portal.identity.db.UserImpl{password=21232f297a57a5a743894a0e4a801fc3, dynamic=[1155580527652], key=1, userName=admin, enabled=true, registrationDate=2006-08-09 16:13:52, realEmail=admin@portal.com, viewRealEmail=true, fakeEmail=null, roles=[org.jboss.portal.identity.db.RoleImpl#1], givenName=null, familyName=null}
      2006-08-14 14:56:26,199 DEBUG [org.hibernate.jdbc.ConnectionManager] aggressively releasing JDBC connection