EJB3 MySQL Stored Procedure Column Not Found

adam1jen · **Joined:** Mon Jul 03, 2006 4:28 pm **Posts:** 15

Hibernate Core - 3.2
Annotations - 3.3.1.GA
Entity Manager - 3.3.2.GA
Database - MySQL 5.0

I'm trying to map a call to a MySQL Stored procedure back to an entity in ejb 3 and am getting a really odd error. Can anyone shed some light?

The stored procedure signature is:

Code:

CREATE PROCEDURE `sp_createIndustrySnapshot`
  (IN country VARCHAR(15), IN state VARCHAR(15), IN industry VARCHAR(10))

I've tested it in MySQL query browser and it works fine. I'm trying to use the following EJB 3 mapping to execute it:

Code:

@NamedNativeQueries({
    @NamedNativeQuery(
        name="createIndustrySnapshot",
        resultSetMapping="createIndustrySnapshotMapping",        
        query="{call sp_createIndustrySnapshot(?, ?, ?)}",
        hints={
            @QueryHint(name="org.hibernate.callable", value="true")
        }
    )
})
@SqlResultSetMappings({
    @SqlResultSetMapping(
        name="createIndustrySnapshotMapping",
        entities={
            @EntityResult(entityClass=com.myapp.IndustryStatistic.class,
                fields={
                    @FieldResult(name="oneMonthGrowthRate", column="ONE_MONTH_GROWTH"),
                    @FieldResult(name="threeMonthGrowthRate", column="THREE_MONTH_GROWTH"),
                    @FieldResult(name="sixMonthGrowthRate", column="SIX_MONTH_GROWTH"),
                    @FieldResult(name="oneYearGrowthRate", column="TWELVE_MONTH_GROWTH"),
                    @FieldResult(name="avgDaysOpen", column="AVG_DAYS_OPEN")
                }            
            )
        }
    )
})

I'm calling this using the following code:

Code:

            Query q = em.createNamedQuery("createIndustrySnapshot");
            q.setParameter(1, countryCode);
            q.setParameter(2, stateCode);
            q.setParameter(3, industry.getCode());
            stats.add((IndustryStatistic)q.getSingleResult());

I've tested the call using good old fashioned jdbc con.prepareCall and it works fine. I've given the user select access to mysql.proc so there should be no problem there.

I get the following error, anyone have any ideas?

Code:

2008-07-06 15:25:09,312 [httpSSLWorkerThread-8080-1] DEBUG org.hibernate.jdbc.ConnectionManager - releasing JDBC connection [ (open PreparedStatements: 0, globally: 0) (open ResultSets: 0, globally: 0)]
2008-07-06 15:25:09,312 [httpSSLWorkerThread-8080-1] DEBUG org.hibernate.jdbc.ConnectionManager - releasing JDBC connection [ (open PreparedStatements: 0, globally: 0) (open ResultSets: 0, globally: 0)]
2008-07-06 15:25:09,312 [httpSSLWorkerThread-8080-1] DEBUG org.hibernate.util.JDBCExceptionReporter - could not execute query [{call sp_createIndustrySnapshot(?, ?, ?)}]
java.sql.SQLException: Column 'industry1_227_0_' not found.
   at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:910)
   at com.mysql.jdbc.ResultSet.findColumn(ResultSet.java:955)
   at com.mysql.jdbc.ResultSet.getString(ResultSet.java:5436)
   at org.hibernate.type.StringType.get(StringType.java:18)
   at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:163)
   at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:154)
   at org.hibernate.loader.Loader.getKeyFromResultSet(Loader.java:1097)
   at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:565)
   at org.hibernate.loader.Loader.doQuery(Loader.java:701)
   at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:236)
   at org.hibernate.loader.Loader.doList(Loader.java:2220)
   at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2104)
   at org.hibernate.loader.Loader.list(Loader.java:2099)
   at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:289)
   at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1695)
   at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:142)
   at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:152)
   at org.hibernate.ejb.QueryImpl.getSingleResult(QueryImpl.java:82)

carlolf · **Posted:** Mon Jul 07, 2008 3:03 am

Hi,

I think you have no problem calling the stored procedure, but you have some mapping problem:

Quote:

java.sql.SQLException: Column 'industry1_227_0_' not found.

adam1jen · **Joined:** Mon Jul 03, 2006 4:28 pm **Posts:** 15

yep, understand it's a mapping problem, the question is why is hibernate trying to map a ficticious column called industry1_227_0_ ?

The entity mapping above should be correct because if I use the same columns as column mapping (as below), everything works fine

Code:

    @SqlResultSetMapping(
        name="createIndustrySnapshotMapping",
        columns={
            @ColumnResult(name="ONE_MONTH_GROWTH"),
            @ColumnResult(name="THREE_MONTH_GROWTH"),
            @ColumnResult(name="SIX_MONTH_GROWTH"),
            @ColumnResult(name="TWELVE_MONTH_GROWTH"),
            @ColumnResult(name="AVG_DAYS_OPEN")
        }
    )

Anyway, I'll use column mapping for now and translate the response myself which is an ok work around, but it would be good if anyone comes across a reason for this.