Hi,
I've a problem with Mapping SQL native with AS/400:
 
File PCKLft.hbm.xml

<?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 schema="IB_DAT1">

<class name="prove.PCKLft" table="PCKLFT">

<composite-id>

<key-property name="idn" column="IDN" type="integer" />

<key-property name="vrs" column="VRS" type="short" />

</composite-id>

<property name="crtpot" column="CRTPOT" type="timestamp" />

<property name="crtsbj" column="CRTSBJ" type="integer" />

<property name="updpot" column="UPDPOT" type="timestamp" />

<property name="updsbj" column="UPDSBJ" type="integer" />

<property name="dsc" column="DSC" type="string" length="50" />

<loader query-ref="avanti" />

</class>

<sql-query name="avanti">

<return alias="p" class="prove.PCKLft" />

SELECT DSC AS {p.dsc} FROM IB_DAT1.PCKLFT p

</sql-query>

</hibernate-mapping>

File hibernate.cfg.xml

<?xml version='1.0' encoding='utf-8'?>

<!DOCTYPE hibernate-configuration PUBLIC

"-//Hibernate/Hibernate Configuration DTD 3.0//EN"

"http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">

<hibernate-configuration>

<session-factory>

<property name="hibernate.connection.driver_class">com.ibm.as400.access.AS400JDBCDriver</property>

<property name="hibernate.connection.url">jdbc:as400://192.168.101.4</property>

<property name="hibernate.connection.username">qsecofr</property>

<property name="hibernate.connection.password">u348ds</property>

<property name="dialect">org.hibernate.dialect.DB2400Dialect</property>

<property name="show_sql">true</property>

<property name="transaction.factory_class">

org.hibernate.transaction.JDBCTransactionFactory

</property>

<property name="hibernate.cache.provider_class">

org.hibernate.cache.HashtableCacheProvider

</property>

<property name="hibernate.hbm2ddl.auto">update</property>

<mapping resource="prove/PCKLft.hbm.xml"/>

</session-factory>

</hibernate-configuration>

And the code for the Sql instruction is:
 
List result = session.getNamedQuery("avanti")
    .setMaxResults(6)    
    .list();
 
But the result is:
 
Hibernate: SELECT DSC FROM IB_DAT1.PCKLFT fetch first 5 rows only
WARN  - SQL Error: -99999, SQLState: 42703
ERROR - An undefined column name was detected.
 
The file PCKLft in the library IB_DAT1 was created with:
 
CREATE TABLE IB_DAT1/PCKLFT (  
IDN INT NOT NULL ,             
VRS SMALLINT NOT NULL ,        
CRTPOT TIMESTAMP ,             
CRTSBJ INT ,                   
UPDPOT TIMESTAMP ,             
UPDSBJ INT ,                   
DSC VARCHAR(50) ,              
PRIMARY KEY (IDN, VRS))        
 
And the SQL with the code:
 
Query q = session.createQuery("from PCKLft");     
q.setMaxResults(25);
List result = q.list();  
 
runs very well. The class runs in a PC with hibernate3.jar
I've used 2 AS/400, both with OS version V5R2.
It's the first time I don't see the solution with java and AS/400.
I hope you give to me some solutions.
THANK YOU!
 
Giovanni Battista Savazzini
Idrosanitaria Beltrami S.p.A.
-----------------------------------------------------
Tel.  0522/221729 - 0522/221711
Fax  0522/221730
areaelaborazionedati@beltrami.sm