0 Replies Latest reply on Apr 3, 2014 12:24 PM by Conrad Winchester

    Wildfly 8 Datasource StoredProcedure Problem

    Conrad Winchester Newbie

      Hi,

       

      I am converting a Glassfish 4 application over to Wildfly 8. So far I have managed to migrate everything, and am very impressed - the Wildfly version is actually simpler in the way it is configured.

       

      Unfortunately I have reached a show stopper and can find nothing about it in the docs or the web.

       

      I use a MySQL database via prepared statements and stored procedures. It worked fin in Glassfish, but when using the same code against the wildfly datasource I get an error.

       

      I use a mysql module in the standalone config of Wildfly.

       

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

      <module xmlns="urn:jboss:module:1.3" name="com.mysql">

          <resources>

              <resource-root path="mysql-connector-java-5.1.30-bin.jar" />

          </resources>

          <dependencies>

              <module name="javax.api" />

              <module name="javax.transaction.api" />

          </dependencies>

      </module>

       

      I have a datasource defined that uses this module

       

      <datasources>

          <datasource jndi-name="java:jboss/datasources/ShiftDS" pool-name="ShiftDS" enabled="true">

              <connection-url>${datasource.url}</connection-url>

              <driver>mysql</driver>

              <security>

                  <user-name>${datasource.username}</user-name>

                  <password>${datasource.password}</password>

              </security>

          </datasource>

          <drivers>

               <driver name="mysql" module="com.mysql">

                    <driver-class>com.mysql.jdbc.Driver</driver-class>

               </driver>

          </drivers>

      </datasources>

       

      The url, username and password are loaded from a properties file (different depending on the environment).

       

      I then use this datasource as follows

       

          @Resource(name = "java:jboss/datasources/ShiftDS")

          private DataSource dataSource;

       

          @Override

          public User getUser(Integer id) throws ResourceNotFoundException, ServiceException {

              try (

                      Connection con = dataSource.getConnection();

                      CallableStatement statement = con.prepareCall("CALL getUser(?)")

              ) {

                 ...

              } catch (SQLException e) {

                  logger.warning("Error retrieving user with id " + id + ": " + e.getMessage());

                  throw new ServiceException(e.getMessage());

              } catch (Exception e) {

                  logger.severe("Severe error retrieving user: " + e.getMessage());

                  throw new ServiceException(e.getMessage());

              }

          }

       

      Unfortunately the moment I try to create the CallableStatement the following exception is thrown

       

      17:15:06,643 WARNING [com.shift.service.user.SQLUserService] (default task-1) Error retrieving user with id 2: Function "GETUSER" not found; SQL statement:

      CALL getUser(?) [90022-173]

       

      I am certain everything is configured correctly and that the datasource has access to that stored procedure - but why does it say it can't find a 'Function'

       

      Any pointers, and or help would be greatly appreciated.

       

      Conrad