Invalid column name (looking up using different column name)

xserty · **Posted:** Fri Dec 15, 2006 10:52 am

Dear all,
I'm having a problem with a stored procedure call. Apparently, the S/P gets called, but when retrieving values from the ResultSet by column name, hibernate (or probably the Oracle Driver) looks for different column names.
In the exception below, you can see that the column name "NAME", which correctly is present in the ResultSet, is been accessed by name "NAME26_0_", thus generating a java.sql.SQLException: Invalid column name.
Any ideas of why this is happening and how to get it working correctly?
Kind regards,
Xserty

Hibernate version:

Code:

Hibernate 3.2.1
Hibernate EntityManager 3.2.1.GA
Hibernate Annotations 3.2.1.GA
hibernate.dialect: org.hibernate.dialect.Oracle9Dialect
also tryed
hibernate.dialect: org.hibernate.dialect.OracleDialect

Mapping documents:

Code:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
   <class name="sp.SpTest" >
      <id name="shipId" column="SHIP_ID" type="string">
         <generator class="native"/>
      </id>
      <property name="name" type="string">
         <column name="NAME"/>
      </property>
      <property name="imoNumber" type="string">
         <column name="IMO_NUMBER"/>
      </property>
   </class>

   <sql-query name="SP_TEST" callable="true">
      <return alias="spTest" class="sp.SpTest">
         <return-property name="shipId" column="SHIP_ID" />
         <return-property name="shipName" column="NAME" />
         <return-property name="shipImoNumber" column="IMO_NUMBER" />
      </return>
      { call SP_TEST(?, :p_ShipID) }
   </sql-query>
</hibernate-mapping>

Code between sessionFactory.openSession() and session.close():

Code:

Query query = hibernateSession.getNamedQuery("SP_TEST");
query.setParameter("p_ShipID", pShipId);
List list = query.list();

Full stack trace of any exception that occurs:

Code:

15 Dec 06 14:13:49, DEBUG  org.apache.commons.logging.impl.Log4JLogger:debug:84 Transaction already joined 
15 Dec 06 14:13:49, DEBUG  org.apache.commons.logging.impl.Log4JLogger:debug:84 about to open PreparedStatement (open PreparedStatements: 0, globally: 0) 
15 Dec 06 14:13:49, DEBUG  org.apache.commons.logging.impl.Log4JLogger:debug:84 opening JDBC connection 
15 Dec 06 14:13:49, DEBUG  org.apache.commons.logging.impl.Log4JLogger:debug:84 
    { call SP_TEST(?, ?) } 
15 Dec 06 14:13:49, DEBUG  org.apache.commons.logging.impl.Log4JLogger:debug:84 bindNamedParameters() 1 -> p_ShipID [2] 
15 Dec 06 14:13:49, DEBUG  org.apache.commons.logging.impl.Log4JLogger:debug:84 about to open ResultSet (open ResultSets: 0, globally: 0) 
15 Dec 06 14:13:49, DEBUG  org.apache.commons.logging.impl.Log4JLogger:debug:84 result row: EntityKey[sp.SpTest#1] 
15 Dec 06 14:13:49, INFO   org.apache.commons.logging.impl.Log4JLogger:info:94 could not read column value from result set: NAME26_0_; Invalid column name 
15 Dec 06 14:13:49, DEBUG  org.apache.commons.logging.impl.Log4JLogger:debug:84 about to close ResultSet (open ResultSets: 1, globally: 1) 
15 Dec 06 14:13:49, DEBUG  org.apache.commons.logging.impl.Log4JLogger:debug:84 about to close PreparedStatement (open PreparedStatements: 1, globally: 1) 
15 Dec 06 14:13:49, DEBUG  org.apache.commons.logging.impl.Log4JLogger:debug:84 aggressively releasing JDBC connection 
15 Dec 06 14:13:49, DEBUG  org.apache.commons.logging.impl.Log4JLogger:debug:84 releasing JDBC connection [ (open PreparedStatements: 0, globally: 0) (open ResultSets: 0, globally: 0)] 
15 Dec 06 14:13:49, DEBUG  org.apache.commons.logging.impl.Log4JLogger:debug:89 could not execute query [{ call SP_TEST(?, ?) }] 
java.sql.SQLException: Invalid column name
   at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
   at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146)
   at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:208)
   at oracle.jdbc.driver.OracleStatement.getColumnIndex(OracleStatement.java:3291)
   at oracle.jdbc.driver.OracleResultSetImpl.findColumn(OracleResultSetImpl.java:1914)
   at oracle.jdbc.driver.OracleResultSet.getString(OracleResultSet.java:1515)
   at org.hibernate.type.StringType.get(StringType.java:18)
   at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:113)
   at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:102)
   at org.hibernate.type.AbstractType.hydrate(AbstractType.java:81)
   at org.hibernate.persister.entity.AbstractEntityPersister.hydrate(AbstractEntityPersister.java:2046)
   at org.hibernate.loader.Loader.loadFromResultSet(Loader.java:1371)

Name and version of the database you are using:

Code:

Oracle9i Enterprise Edition Release 9.2.0.4.0
jdk1.5.0_09
jboss-4.0.5.GA
ojdbc14.jar: Implementation-Version: Oracle JDBC Driver version -  10.2.0.2.0"

The generated SQL (show_sql=true):

Code:

call SP_TEST(?, ?)

Stored Procedure:

Code:

PROCEDURE SP_TEST ( p_recordset OUT UTILS.type_cursor, p_ShipID IN SVD_SHIPS.SHIP_ID%TYPE ) IS

BEGIN
    dbms_output.put_line ( 'START SP_TEST' );
     OPEN p_recordset
      FOR SELECT SVD_SHIPS.SHIP_ID,
     SVD_SHIPS.NAME,
     SVD_SHIPS.IMO_NUMBER
     FROM SVD_SHIPS
    WHERE SVD_SHIPS.SHIP_ID = p_ShipID;
    dbms_output.put_line ( 'END SP_TEST' );
END SP_TEST;

Debug level Hibernate log excerpt:
Please see Full stack trace of any exception that occurs above.

sneal · **Posted:** Sun Dec 17, 2006 1:11 pm

Have you tried using SQL92 call syntax in your mapping file?

From 16.2.2.1. Rules/limitations for using stored procedures
http://www.hibernate.org/hib_docs/v3/reference/en/html/querysql.html#querysql-limits-storedprocedures

Quote:

Recommended call form is standard SQL92: { ? = call functionName(<parameters>) } or { ? = call procedureName(<parameters>}. Native call syntax is not supported.

xserty · **Posted:** Thu Dec 21, 2006 6:54 am

sneal wrote:

Have you tried using SQL92 call syntax in your mapping file?

From 16.2.2.1. Rules/limitations for using stored procedures
http://www.hibernate.org/hib_docs/v3/reference/en/html/querysql.html#querysql-limits-storedprocedures

Quote:

Recommended call form is standard SQL92: { ? = call functionName(<parameters>) } or { ? = call procedureName(<parameters>}. Native call syntax is not supported.

Hi sneal,
thanks for your reply, but I tried and got the following error:

Code:

10:20:59,728 WARN  [JDBCExceptionReporter] SQL Error: 6550, SQLState: 65000
10:20:59,732 ERROR [JDBCExceptionReporter] ORA-06550: line 1, column 13:
PLS-00306: wrong number or types of arguments in call to 'SP_TEST'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

IMHO, I'm calling the S/P the correct way (even though the docs suggest what you are saying). I say this because I am able to debug and change the name that hibernate uses to access the result set columns. In fact, if I change the (randomly generated) access names of hibernate (e.g. from "NAME26_0_" to "NAME"), I am able to fill the mapping bean associated with that S/P.
The issue (or BUG) here is that hibernate tries to access result set columns with randomly generated names!

For sake of clarity, I'll try to explain the problem with an example:
Say the result set contains a column called "NAME" of string type (VARCHAR2), then hibernate uses the following method of org.hibernate.type.StringType class to access the result set column:

Code:

public Object get(ResultSet rs, String name) throws SQLException {
   return rs.getString(name);
}

The "name" parameter value should be "NAME", but "NAME26_0_" is used instead! This generates an exception:

Code:

ERROR [JDBCExceptionReporter] Invalid column name

I can't understand how, why and where this value is changed, but it sure looks like a bug to me.
Has nobody ever come across this issue?
Hope somebody can help me...
Kind regards,
Xserty

martin.capote · **Joined:** Wed Jan 10, 2007 8:06 am **Posts:** 7

Hi, i have EXACTLY the same problem. The stored procedure follows the rules ("The first parameter of a procedure must be an OUT that returns a result set."). The org.hibernate.type.LongType class incorrectly rename the parameter "IDENT" as "IDENT0_0_".

I can't convert the procedure in a function (legacy problem).
The header is

Code:

PROCEDURE OBTENER(LIS OUT SYS_REFCURSOR,VDEP IN VARCHAR2, VPD IN NUMBER, 
                        VLC IN VARCHAR2, VB IN VARCHAR, 
                        VNV IN VARCHAR2, VUI IN VARCHAR2
                        ) IS

BEGIN .....

Oracle (off topic) question: I can specify an 'Alias' in the stored procedure call ?

Has nobody ever come across this issue?
Thanks in advance

xserty · **Posted:** Wed Jan 10, 2007 9:46 am

martin.capote wrote:

[snip...]

Oracle (off topic) question: I can specify an 'Alias' in the stored procedure call ?

Has nobody ever come across this issue?
Thanks in advance

Yes, you can specify aliases in the S/P, but the problem still persists!

Regards,
Xserty

martin.capote · **Joined:** Wed Jan 10, 2007 8:06 am **Posts:** 7

Hi. The object that I want to return is a "Key Wrapper":

<class name="TableKey" table="MY_TABLE">
<composite-id>
<key-property name="ident" column="IDENT" type="long"/>
</composite-id>
</class>

The stored procedure returns a cursor with the IDENT column only.

But, if I intent to return the column like a scalar value (with the same stored procedure), all works fine!

This solution work

Code:

    
<sql-query name="obtenerIds"  callable="true">
        <return-scalar column="IDENT" type="long"/>
        { ?=call Obtener(:dep,:pad,:loc,:b,:niv,:un) }
</sql-query>

This solution doesn't (raise the IDENT0_0_ error)

Code:

    
<sql-query name="obtenerIds"  callable="true">
        <return class="TableKey">
            <return-property name="ident" column="IDENT"/>
        </return>
        { ?=call Obtener(:dep,:pad,:loc,:b,:niv,:un) }
</sql-query>

Some idea? Is a bug? Thanks

xserty · **Posted:** Mon Jan 15, 2007 4:57 am

martin.capote wrote:

Hi. The object that I want to return is a "Key Wrapper":

<class name="TableKey" table="MY_TABLE">
<composite-id>
<key-property name="ident" column="IDENT" type="long"/>
</composite-id>
</class>

The stored procedure returns a cursor with the IDENT column only.

But, if I intent to return the column like a scalar value (with the same stored procedure), all works fine!

This solution work

Code:

    
<sql-query name="obtenerIds"  callable="true">
        <return-scalar column="IDENT" type="long"/>
        { ?=call Obtener(:dep,:pad,:loc,:b,:niv,:un) }
</sql-query>

This solution doesn't (raise the IDENT0_0_ error)

Code:

    
<sql-query name="obtenerIds"  callable="true">
        <return class="TableKey">
            <return-property name="ident" column="IDENT"/>
        </return>
        { ?=call Obtener(:dep,:pad,:loc,:b,:niv,:un) }
</sql-query>

Some idea? Is a bug? Thanks

When returning a cursor, did you try something defining the query as follows:

Code:

    
<sql-query name="obtenerIds"  callable="true">
        <return class="TableKey">
            <return-property name="ident" column="IDENT"/>
        </return>
        { call Obtener(?, :dep,:pad,:loc,:b,:niv,:un) }
</sql-query>

martin.capote · **Joined:** Wed Jan 10, 2007 8:06 am **Posts:** 7

Yes. In fact, is the original situation. Searching the solution, I made the function 'Obtener', which calls the (untocheable) procedure ObtenerId.

Code:

function Obtener(vdp in varchar2, vpd in number, 
                        vlc in varchar2, vbo in varchar, 
                        vnv in varchar2, vui in varchar2

                        ) RETURN t_pd is
 Lis t_pd;                        
begin
   ObtenerId(Lis, vdp,vpd,vlc,vbo,vnv,vui);
   return Lis;
end Obtener;                        

none of these options worked, same error in both (call the function, or call directly the procedure)

max · **Posted:** Mon Jan 15, 2007 7:05 am

it complains because your procedure does not return all columns needed to build up the entity.

If you only have the id then you need to use the scalar query and look up the entity with session.get(id, Obtener) afterwards to get a reference to the entity object if you need that.

martin.capote · **Joined:** Wed Jan 10, 2007 8:06 am **Posts:** 7

Hi. I run the hibernate test, and I found some distinct with my code. In the Oracle Stored procedure tests, the logs show the following lines:

Code:

46:41,296 DEBUG SessionFactoryImpl:390 - Checking 0 named HQL queries
46:41,296 DEBUG SessionFactoryImpl:410 - Checking 10 named SQL queries
46:41,296 DEBUG SessionFactoryImpl:418 - Checking named SQL query: selectAllEmployments
46:41,296 DEBUG QueryPlanCache:112 - unable to locate native-sql query plan in cache; generating ({ ? = call allEmployments() })
46:41,296 DEBUG SQLCustomQuery:62 - starting processing of sql query [{ ? = call allEmployments() }]
46:41,312 DEBUG SQLQueryReturnProcessor:364 - mapping alias [emp] to entity-suffix [0_]
....

But, in my case, I don't have the last tree lines (with the "mapping alias")

Code:

2007-01-15 11:54:48,968 DEBUG [org.hibernate.impl.SessionFactoryImpl] Checking 0 named HQL queries
2007-01-15 11:54:48,968 DEBUG [org.hibernate.impl.SessionFactoryImpl] Checking 1 named SQL queries
2007-01-15 11:54:48,968 DEBUG [org.hibernate.impl.SessionFactoryImpl] Checking named SQL query: obtenerIds
2007-01-15 11:54:48,968 DEBUG [org.springframework.beans.factory.support.DefaultListableBeanFactory] Invoking BeanPostProcessors after initialization of bean 'sessionFactory'
....

Maybe the missed "mapping alias" is the problem. Some idea why don't execute these lines of code? Thanks

max · **Posted:** Mon Jan 15, 2007 2:02 pm

no your problem is what I just answered.

martin.capote · **Joined:** Wed Jan 10, 2007 8:06 am **Posts:** 7

Hi. I found a solution !!!

This mapping doesn't work:

Code:

<class name="TableKey" table="MY_TABLE">
    <composite-id>
        <key-property name="ident" column="IDENT" type="long"/>
    </composite-id>
</class> 

But this mapping works!

Code:

<class name="TableKey" table="MY_TABLE">
        <id name="ident" unsaved-value="0" column="IDENT">
            <generator class="assigned"/>
        </id>
</class> 

I don`t understand why this change solves the problem (conceptually, both are very similar).
Somebody can explain to me as it is the conceptual difference, that cause that one of the examples works, and other no?

max · **Posted:** Wed Jan 17, 2007 11:01 am

does your class only have one single property which is its id ?

...did you read my previous answers at all ?

martin.capote · **Joined:** Wed Jan 10, 2007 8:06 am **Posts:** 7

Hi Max, thanks for your answers. I must admit that not understand at 100%.
Please, clarify your answer with my example: Why the mapping with the <id> works, but with the <composite-id> with the same field doesn't work?

In this case, the class only have one single property which is its id. I have more than one PL\SQL (all of them legacies, with composites ids), I just start with the 'easy' case :)

max · **Posted:** Thu Jan 18, 2007 7:45 am

well I do not understand why you use composite-id mapping when you clearly only have one single column id!

Furthermore your composite-id mapping does not have a name which is legal, but very seldomly used and i'm not sure if our native sql implementation support this combination.

why don't you just use the <id> mapping which is clearly working and enough for your case ?

btw. I have never heard about an entity with just a single column ;)