Hibernate Books

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 8 posts ] 
Author Message
 Post subject: Testing Stored Procedure
PostPosted: Wed Feb 24, 2010 10:44 am 
Newbie

Joined: Wed Feb 24, 2010 10:29 am
Posts: 4
Hi, I´m new in NHibernate, and I´m trying to run this store procedure:

CREATE PROCEDURE mapping
@IDMAP int
AS
SELECT no_mapeo, nombre
FROM tmapeo
WHERE id = @IDMAP
go

here is my NHibernate.hbm.xml:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" auto-import="true" >
<class name="SiseHibernate.mapeo, SiseHibernate" table="tmapeo" lazy="false" >
<id name="Id" column="id" type="int">
<generator class="native"/>
</id>
<property name="no_mapeo" column="no_mapeo" type="int"/>
<property name="nombre" column="nombre" type="string" length="40"/>
<loader query-ref="mapping"/>
</class>
<sql-query name="mapping" >
<return alias="mapping" class="SiseHibernate.mapeo, SiseHibernate">
<!--<return-property name="Id" column="id"/>-->
<return-property name="no_mapeo" column="no_mapeo"/>
<return-property name="nombre" column="nombre"/>
</return>
exec dbo.mapping IDMAP
</sql-query>

</hibernate-mapping>

and my C# code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Windows.Forms;
using NHibernate;
using NHibernate.Cfg;

namespace SiseHibernate
{
static class Program
{
/// <summary>
/// Punto de entrada principal para la aplicación.
/// </summary>
[STAThread]
static void Main()
{
//OpenSession();

using (ISession session = OpenSession())
{
using (ITransaction transaction = session.BeginTransaction())
{
try
{
//mapeo NuevoReg = new mapeo();
//NuevoReg.no_mapeo = 2;
//NuevoReg.nombre = "fernando";
//session.Save(NuevoReg);
//transaction.Commit();
// NuevoReg.Id = 2;

IQuery query = (IQuery)session.GetNamedQuery("MAPPING");
query.SetParameter("IDMAP", idmap); <--- I don´t understand here.
System.Collections.IList result = query.List();




Console.WriteLine("FIN");
}
catch (Exception)
{
transaction.Rollback();
throw;
}
finally
{
transaction.Dispose();
}
}
}
}



static ISession OpenSession()
{
Configuration cfg = new Configuration();
cfg.AddAssembly("SiseHibernate");
ISessionFactory factory = cfg.BuildSessionFactory();
ISession session = factory.OpenSession();
ITransaction transaction = session.BeginTransaction();
//Console.ReadKey();
return factory.OpenSession();
}

}
}

How can I run the store procedure????, it´s the same if I use a store Procedure with updates and inserts?


Top
 Profile  
 
 Post subject: Re: Testing Stored Procedure
PostPosted: Thu Feb 25, 2010 3:53 am 
Beginner
Beginner

Joined: Sat Jan 26, 2008 6:33 am
Posts: 24
Hmm.... I guess it would help if you wrote the error message you got. But what attracted my attention was your sql:
Code:
exec dbo.mapping IDMAP

I guess this should be:
Code:
exec dbo.mapping :IDMAP

The colon is required to identify a parameter name, wich can later be passed to the Database:
Quote:
query.SetParameter("IDMAP", idmap); <--- I don´t understand here.

Im not sure what it is, you dont understand. Please substantiate your question.
Query-Parameters are generally directly supported by the Database.
The application prepares your query against the Database Server and parameters can be set for each call to it.
Like this you avoid SQL-Injection and increase performance, if you have to call the same query with different parameters multiple times.

Greetings!
Zorgoban


Top
 Profile  
 
 Post subject: Re: Testing Stored Procedure
PostPosted: Thu Feb 25, 2010 11:17 am 
Newbie

Joined: Wed Feb 24, 2010 10:29 am
Posts: 4
Hi, thanks for answer me, it send me a message error that said:

Parameter IDEM does not exist as a named parameter in [exec dbo.mapping :IDMAP]

I don´t understand well what kind of values I have to set in the query parameter
query.SetParameter("name of my parameter", "name of column?";

Thanks !


Top
 Profile  
 
 Post subject: Re: Testing Stored Procedure
PostPosted: Thu Feb 25, 2010 12:18 pm 
Beginner
Beginner

Joined: Sat Jan 26, 2008 6:33 am
Posts: 24
Yes, thats what I thought. You need to put the colon in front of the parameter name inside your query, like I wrote it in my last post.

And the function means:
Code:
query.SetParameter("name of parameter", "value of parameter");

As far as I can see, the rest of your code looks good.

Greetings!
Zorgoban


Top
 Profile  
 
 Post subject: Re: Testing Stored Procedure
PostPosted: Thu Feb 25, 2010 1:48 pm 
Newbie

Joined: Wed Feb 24, 2010 10:29 am
Posts: 4
Hi thanks, I do it but i have this error message:
could not locate named parameter [IDEM]
I set the parameter as you said me, but it doesn´t works.
Thanks !

I´m using Visual Studio 2008, and I´m trying to make it work over a c# application.
Thanks


Top
 Profile  
 
 Post subject: Re: Testing Stored Procedure
PostPosted: Thu Feb 25, 2010 3:07 pm 
Beginner
Beginner

Joined: Sat Jan 26, 2008 6:33 am
Posts: 24
Yes, thats right, according to the code you posted here, you dont even use a parameter named IDEM anywhere. Its probably just a typo.


Top
 Profile  
 
 Post subject: Re: Testing Stored Procedure
PostPosted: Fri Feb 26, 2010 6:29 pm 
Newbie

Joined: Wed Feb 24, 2010 10:29 am
Posts: 4
I wrote the correct parameter and the application is running, but when I set :
query.list()
after
query.SetParameter("IDMAP",2);
it appears the following error :

could not execute query
[ exec dbo.mapping @p0 ]
Name:IDMAP - Value:2
[SQL: exec dbo.mapping @p0]

{"could not execute query\r\n[ exec dbo.mapping @p0 ]\r\n Name:IDMAP - Value:2\r\n[SQL: exec dbo.mapping @p0]"}

Thanks!!!


Top
 Profile  
 
 Post subject: Re: Testing Stored Procedure
PostPosted: Sun Feb 28, 2010 10:59 am 
Beginner
Beginner

Joined: Sat Jan 26, 2008 6:33 am
Posts: 24
How could you write the correct parameter without query.SetParameter("IDMAP",2)? And when exactly is the application running? Please send the whole Stack-Trace. The reason is missing, why it couldnt execute the query.

I checked you code again, and neither your SP, nor the query, seemed to have an error. Is the user you use to connect to the database having the right to execute this SP?

Greetings
Zorgoban


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 8 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.