Wildfly 8 Datasource StoredProcedure Problem
conrad.winchester Apr 3, 2014 12:24 PMHi,
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