Hibernate Books

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 4 posts ] 
Author Message
 Post subject: Stored Procedure + Out Parameter
PostPosted: Wed Mar 07, 2007 4:42 pm 
Newbie

Joined: Thu Feb 15, 2007 12:48 pm
Posts: 11
Hi,
I just upgraded to NHIbernate 1.2, just for the Stored procedure capability. I am in need to execute a stored procedure with one input and one output parameter. I tried to look for examples but really was not able to find a good answer. If anyone could help me, how to invoke a stored procedure from nHibernate, it would be very helpful.

All I need is I pass an int input parameter and a string out parameter to the SP.
Thanks for any help,
Javid

Hibernate version:
1.2
Mapping documents:
Code:
<sql-query name="spCreateTemplateClause">
    <return-scalar column="where_clause" type="string"></return-scalar>
    call spCreateTemplateClause (:template_id, where_clause)
  </sql-query>

Code between sessionFactory.openSession() and session.close():
Code:
    IQuery query = Db.Session.GetNamedQuery("spCreateTemplateClause");
     query.SetInt32("template_id", 10);
     query.List();

Full stack trace of any exception that occurs:

Name and version of the database you are using:
SQL server 2005
The generated SQL (show_sql=true):

Debug level Hibernate log excerpt:


Problems with Session and transaction handling?

Read this: http://hibernate.org/42.html


Top
 Profile  
 
 Post subject:
PostPosted: Wed Mar 07, 2007 7:36 pm 
Newbie

Joined: Mon Mar 05, 2007 1:39 am
Posts: 10
Location: Brisbane, Australia
There was a whole bunch of stuff missing from your posting - the SQL, the error messages.... however, I did read stuff about this yesterday.

You mention that the procedure generates one output value (but we don't see the SQL so I'm not sure what this is, however I'm assuming something like:

Code:
SELECT COUNT(*) FROM MYTABLE


In this instance your Java call is then going to look something like (and I'm not talking specifics here):

Code:
    IQuery query = Db.Session.GetNamedQuery("spCreateTemplateClause");
     query.SetInt32("template_id", 10);
     query.uniqueResult();


Let me know if this works for you.

Cheers
mc

_________________
Murray Collingwood
Focus Computing
http://www.focus-computing.com.au


Top
 Profile  
 
 Post subject: Yee! I just search how to use produce with NH?
PostPosted: Wed Mar 07, 2007 11:30 pm 
Newbie

Joined: Wed Mar 07, 2007 10:59 pm
Posts: 6
Hi,javid
Can you give me a detail Demo?
My Email is roping.zong@erdc.com
I from china!


Top
 Profile  
 
 Post subject:
PostPosted: Thu Mar 08, 2007 10:57 am 
Newbie

Joined: Thu Feb 15, 2007 12:48 pm
Posts: 11
Focus,
Thanks for your reply. Sorry for not posting my SQL. Here it is:

Code:
ALTER PROCEDURE [dbo].[spCreateTemplateClause]
   @template_id int,
   @where_clause nvarchar(MAX) OUTPUT
AS
BEGIN
   -- SET NOCOUNT ON added to prevent extra result sets from
   -- interfering with SELECT statements.
   SET NOCOUNT ON;

DECLARE @clause AS nvarchar(MAX)
SET @clause = ''
DECLARE @type AS varchar(50)
DECLARE @value AS varchar(255)
DECLARE @combo_type AS varchar(50)
DECLARE @combo_value AS varchar(255)
DECLARE @current_type AS varchar(50)
SET @current_type = ''
DECLARE @current_combo_type AS varchar(50)
SET @current_combo_type = ''
DECLARE @join AS varchar(5)
SET @join = ''

-- Loop through all the values in the filter values table to create
-- a 'WHERE' clause for which items to include in a query.
DECLARE temp_cursor CURSOR FORWARD_ONLY STATIC FOR
  SELECT [TYPE], [VALUE], COMBO_TYPE, COMBO_VALUE
  FROM SCENARIO_TEMPLATE_VALUES
  WHERE TEMPLATE_ID = @template_id
  ORDER BY [TYPE], COMBO_TYPE

OPEN temp_cursor

-- Get the first value from the cursor
FETCH NEXT FROM temp_cursor
INTO @type, @value, @combo_type, @combo_value

-- Need to keep track of when the filter type changes. For the
-- filter, similar types should be OR'd and type changes
-- should be AND'd.
SET @current_type = @type

IF @@FETCH_STATUS = 0
    SET @clause = ' WHERE '

WHILE @@FETCH_STATUS = 0
BEGIN

-- eg. "(WAREHOUSE_NAME = 'Warehouse 1')"
SET @clause = @clause + '(' + @type + ' = ''' + @value + ''')'

-- Combo values are dependant criteria... they should be AND'd with
-- the main clause from above.
IF @combo_type IS NOT NULL
    SET @clause = @clause + ' AND (' + @combo_type + ' = ''' + @combo_value + ''')'

FETCH NEXT FROM temp_cursor
INTO @type, @value, @combo_type, @combo_value

IF @@FETCH_STATUS <> 0
   BREAK

-- Set the clause join, depending on whether the type changes or not.
IF @type = @current_type
    SET @join = ' OR '
ELSE
    SET @join = ' AND '

SET @clause = @clause + @join
SET @current_type = @type

END

CLOSE temp_cursor
DEALLOCATE temp_cursor

SET @where_clause = @clause

END


The stored procedure takes in an int ID and builds the where clause I am supposed to be using for another select statement.
I am not sure if this would work with hibernate as I read in the docs it needs a resultset as output to work.

Thanks for your help,
Javid


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 4 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.