Executing HQL queries along with SQL Queries

[Ayyappan Selvaraj]

Friends, I have a requirement in my project like: I have a Stored Procedue which will return a set of tables and considering the result set as a table , I need to execute HQL query on it. How can this be done.

Can be better understood with the query which works fine in the SQL IDEs.
select * from (
select distinct PARTY_ID, ACCT_NO_1 , ROW_NUMBER() OVER (ORDER BY PARTY_ID) R from table(pax.p2a_services_pkg.get_by_cust_loc_func('G','327888475','AML'))
)where R between 10 and 18

pax.p2a_services_pkg.get_by_cust_loc_func('G','327888475','AML')) This is the stored procedure call and the remaining is the SQL query on the results it gives.

I want to know how this query can be executed using HQL and named sql query. Advice please...

[saranga rao]

Hi,

Please Go through the getNamedQuery() for this.....

which is very familiar for the stored procedure ...........only


regards
saranga

[saranga rao]

Hi

feel free to ask the code and say about the query you need ..........man



regards
saranga

[Ayyappan Selvaraj]

Hi thanks for replying.. I have got the code for executing simple named query from the javaranach forum only... now I require that an HQL query shud be run on the result sets that would be returned from the stored procedure..

[saranga rao]

Hi,

check the link below.............


http://swik.net/Hibernate/Using+SQL+From+Within+Hibernate


or else give some more details so that I can refer to this...

Regards
saranga

[Ayyappan Selvaraj]

Hey this is for running simple SQL queries. I want code for running Stored procedure..

[saranga rao]

Hi,

http://www.hibernate.org/hib_docs/v3/reference/en/html/querysql.html here
search for getNamedQuery() You will find the details.

<sql-query name="selectAllEmployees_SP" callable="true">
<return alias="emp" class="Employment">
<return-property name="employee" column="EMPLOYEE"/>
<return-property name="employer" column="EMPLOYER"/>
<return-property name="startDate" column="STARTDATE"/>
<return-property name="endDate" column="ENDDATE"/>
<return-property name="regionCode" column="REGIONCODE"/>
<return-property name="id" column="EID"/>
<return-property name="salary">
<return-column name="VALUE"/>
<return-column name="CURRENCY"/>
</return-property>
</return>
{ ? = call selectAllEmployments() }
</sql-query>



the named written like :

List people = sess.getNamedQuery("selectAllEmployees")
.setString("endDate", endDate)
.setMaxResults(50)
.list();


This will return the list you values