nHibernate version: 1.0.2

I'm fairly new to nHibernate and am trying to figure out how to use it to return values from multiple tables at once. Here's my code (all 3 tables are mapped in .hbm.xml files):

String[] clss = { "Student", "Belt", "BeltDesc" };
Type[] types = {s.GetType(), b.GetType(), bc.GetType()};
IQuery iq = session.CreateSQLQuery("SELECT student.FirstName as {Student.FirstName}, student.LastName as {Student.LastName}, student.id as {Student.Id}, student.Number as {Student.Number}, belt.RankValue as {Belt.RankValue}, ( SELECT belt_descs.description FROM belt_descs WHERE belt_descs.rankvalue = belt.rankvalue) AS {BeltDesc.Description} FROM student JOIN belt ON belt.student = student.id GROUP BY student.firstname, student.lastname, student.id, student.atanumber, belt.rankvalue, belt_descs.description;", clss, types);

(s is a Student, b is a Belt, bc is a BeltDesc object).

When I run this code, i get the following exception:

error in FindBySQL

Inner exception text:
Field name not found.
Parameter name: fieldName
Actual value was id1_.

What am I doing wrong? I've searched the net and documentaiton on examples of how to do this. They are few. Any pointers would be greatly apprechiated.

---

Also, how do I set the option for show_sql="true"?

Thanks.

Seeing the SQL would help here (as you alluded to). Enabling "show_sql" is here:

http://www.hibernate.org/hib_docs/nhibe ... n-optional

my web.config:

I added this to my App.config, and it doesn't appear to have had any effect. I looked for the log file and it was no where in my application directory.

I initally had some errors after adding this, but got them all corrected (mostly it was due to not include a log4net reference).

This project is a windows app, not a web app.

I figured you already had log4net enabled, too. You will need to be sure to configure log4net for output. Check the doco. Here is an example, although, for your app, I would have the app place its log file in its installation directory:

Still not working, I tried your config, and the config in the nhibernate example nhibernate.test. Neither produced a log file. :(

Here's my current config:

<configSections>
<section name="nhibernate" type="System.Configuration.NameValueSectionHandler, System,
Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />
<section name="log4net" type="log4net.Config.Log4NetConfigurationSectionHandler,log4net" />
</configSections>
<nhibernate>
<add key="hibernate.show_sql" value="true" />
</nhibernate>
<log4net debug="false">

<!-- Define some output appenders -->
<appender name="trace" type="log4net.Appender.TraceAppender, log4net">
<layout type="log4net.Layout.PatternLayout,log4net">
<param name="ConversionPattern" value="%d [%t] %-5p %c - %m%n" />
</layout>
</appender>

<appender name="console" type="log4net.Appender.ConsoleAppender, log4net">
<layout type="log4net.Layout.PatternLayout,log4net">
<param name="ConversionPattern" value="%d [%t] %-5p %c - %m%n" />
</layout>
</appender>

<appender name="rollingFile" type="log4net.Appender.RollingFileAppender,log4net" >

<param name="File" value="C:\\log.txt" />
<param name="AppendToFile" value="false" />
<param name="RollingStyle" value="Date" />
<param name="DatePattern" value="yyyy.MM.dd" />
<param name="StaticLogFileName" value="true" />

<layout type="log4net.Layout.PatternLayout,log4net">
<param name="ConversionPattern" value="%d [%t] %-5p %c - %m%n" />
</layout>
</appender>

<!-- Setup the root category, add the appenders and set the default priority -->

<root>
<priority value="ALL" />
<appender-ref ref="console" />
</root>

<logger name="NHibernate.Cache">
<level value="ALL" />
</logger>

<logger name="NHibernate.Impl.BatcherImpl">
<level value="ALL" />
</logger>

<logger name="NHibernate.SQL">
<level value="ALL" />
</logger>
</log4net>

---------

I read through the documentation, but it really didn't make much sense to me. I'm going to read it again and try some things, any help would be apprechiated.

Also, the setups for the db connection are done at run time using this code:

cfg = new NHibernate.Cfg.Configuration();
// set provider & driver properties
cfg.Properties.Add(NHibernate.Cfg.Environment.ConnectionProvider,
"NHibernate.Connection.DriverConnectionProvider");
cfg.Properties.Add(NHibernate.Cfg.Environment.ConnectionDriver,
"NHibernate.Driver.NpgsqlDriver");
cfg.Properties.Add(NHibernate.Cfg.Environment.Dialect,
"NHibernate.Dialect.PostgreSQLDialect");
cfg.Properties.Add(NHibernate.Cfg.Environment.ConnectionString,
"SERVER=192.168.0.10;DATABASE=tkd;USER ID=pgsql;ENCODING=UNICODE;");




// here we add all the needed assemblies that contain mappings or objects
if (System.IO.File.Exists("DataClasses.dll"))
{
cfg.AddAssembly(Assembly.LoadFrom("DataClasses.dll"));
}
else
{
System.Windows.Forms.MessageBox.Show("Unable to load DataClasses.dll");
}


sessionFactory = cfg.BuildSessionFactory();

Thanks.

Thav,

Sorry about all of the troubles you're having. I know they can be frustrating. Personally, I really need to see the SQL first to help the diagnosis. In your config example, you have the "console" and "rollingFile" appender configured, but you've only selected the console appender to actually receive output on the <root></root> section. Try changing that to <appender-ref ref="rollingFile" /> and see if you get anything.

-devon

I dug around some more, and it seems that you need to call:

log4net.Config.XmlConfigurator.Configure();

in order to actually configure the logging.

Ok, here's the logs:

2006-05-09 14:52:07,674 [1980] INFO NHibernate.Impl.SessionFactoryObjectFactory - no name configured
2006-05-09 14:52:08,565 [1980] INFO NHibernate.Loader.Loader - SELECT Student.FirstName as firstname0_, Student.LastName as lastname0_, Student.id as id0_, Student.ATANumber as atanumber0_, Belt.RankValue as rankvalue1_, ( SELECT belt_descs.description FROM belt_descs WHERE belt_descs.rankvalue = belt.rankvalue) AS descript3_2_ FROM student Student JOIN belt Belt ON belt.student = student.id GROUP BY student.firstname, student.lastname, student.id, student.atanumber, belt.rankvalue, belt_descs.description;
2006-05-09 14:52:08,565 [1980] INFO NHibernate.Loader.Loader - SELECT Student.FirstName as firstname0_, Student.LastName as lastname0_, Student.id as id0_, Student.ATANumber as atanumber0_, Belt.RankValue as rankvalue1_, ( SELECT belt_descs.description FROM belt_descs WHERE belt_descs.rankvalue = belt.rankvalue) AS descript3_2_ FROM student Student JOIN belt Belt ON belt.student = student.id GROUP BY student.firstname, student.lastname, student.id, student.atanumber, belt.rankvalue, belt_descs.description;
2006-05-09 14:52:08,575 [1980] DEBUG NHibernate.SQL - SELECT Student.FirstName as firstname0_, Student.LastName as lastname0_, Student.id as id0_, Student.ATANumber as atanumber0_, Belt.RankValue as rankvalue1_, ( SELECT belt_descs.description FROM belt_descs WHERE belt_descs.rankvalue = belt.rankvalue) AS descript3_2_ FROM student Student JOIN belt Belt ON belt.student = student.id GROUP BY student.firstname, student.lastname, student.id, student.atanumber, belt.rankvalue, belt_descs.description;
2006-05-09 14:52:08,575 [1980] DEBUG NHibernate.SQL - SELECT Student.FirstName as firstname0_, Student.LastName as lastname0_, Student.id as id0_, Student.ATANumber as atanumber0_, Belt.RankValue as rankvalue1_, ( SELECT belt_descs.description FROM belt_descs WHERE belt_descs.rankvalue = belt.rankvalue) AS descript3_2_ FROM student Student JOIN belt Belt ON belt.student = student.id GROUP BY student.firstname, student.lastname, student.id, student.atanumber, belt.rankvalue, belt_descs.description;
2006-05-09 14:52:08,575 [1980] DEBUG NHibernate.SQL - SELECT Student.FirstName as firstname0_, Student.LastName as lastname0_, Student.id as id0_, Student.ATANumber as atanumber0_, Belt.RankValue as rankvalue1_, ( SELECT belt_descs.description FROM belt_descs WHERE belt_descs.rankvalue = belt.rankvalue) AS descript3_2_ FROM student Student JOIN belt Belt ON belt.student = student.id GROUP BY student.firstname, student.lastname, student.id, student.atanumber, belt.rankvalue, belt_descs.description;
2006-05-09 14:52:09,066 [1980] WARN NHibernate.Util.ADOExceptionReporter - System.ArgumentOutOfRangeException: Field name not found
Parameter name: fieldName
Actual value was id1_.

Looking through it, I definitely do not see an id1_, nor is there another "id" field in the query. Belt has an "Id" field, but it's not explicitly used in the query.

I do see examples with {}'d names behind some things, like the table name in from clauses, but I can't tell if that's supposed to be there, or if it's an oracle thing in the examples.

well there is actually and id field in the query at:



but that isn't id1_...

however, now I'm wondering about your mapping files. can you post the class and mapping files for both the student and the belt POCOs?

-devon

Student:

<class name="Student" table="student">

<id name="Id" column="id" type="Int64" >
<generator class="native">
<param name="sequence">student_id_seq</param>
</generator>
</id>
<property name="FirstName" column="firstname" />
<property name="LastName" column="lastname" />
<property name="ATANumber" column="atanumber" />
<property name="Address" column="address" />
<property name="Address2" column="address2" />
<property name="City" column="city" />
<property name="State" column="state" />
<property name="ZipCode" column="zipcode" />
<property name="HomePhone" column="homephone" />
<property name="WorkPhone" column="workphone" />
<property name="CellPhone" column="cellphone" />
<property name="inClass" column="inclass" />
<property name="schoolId" column="school_id" />
<property name="UniformType" column="uniform_type" />
<property name="UniformSize" column="uniform_size" />
<property name="BeltType" column="belt_type" />
<property name="BeltSize" column="belt_size" />
<property name="DOB" column="dob" />
<property name="ATAExpiration" column="expiration" />

<property name="familyId" column="family_id" />
<property name="Email" column="email" />
<property name="MiddleName" column="middlename" />
<property name="EmgContact" column="emgcontact" />
<property name="EmgPhone" column="emgphone" />
<property name="Active" column="active" />

<!--
<set name="Belts" order-by="RankValue desc">
<key column="student" />
<one-to-many class="Belt" />
</set>
-->

</class>

----

Belt:

<class name="Belt" table="belt">

<id name="Id" column="id" type="Int64" >
<generator class="native">
<param name="sequence">belt_id_seq</param>
</generator>
</id>
<property name="Received" column="received" />
<property name="RankValue" column="rankvalue" />
<property name="StudentID" column="student" />
<property name="Points" column="points" />
<property name="Instructor" column="instructor" />
<property name="Comments" column="comments" />
<property name="Location" column="location" />

<one-to-one name="Descrip" class="BeltDesc" property-ref="RankVal" />


</class>

---

BeltDesc:

<class name="BeltDesc" table="belt_descs">

<id name="Id" column="id" type="Int64" >
<generator class="native">
<param name="sequence">belt_descs_id_seq</param>
</generator>
</id>
<property name="RankVal" column="rankvalue" />
<property name="Description" column="description" />
<property name="ShortDesc" column="shortdesc" />
</class>

---

And what's POCOs?

-Thav

As I'm reading through these logs, I think the trouble begins by using the full objects. I added the id's from belt and beltdesc to the query, and got an error on state0_ (which is a part of student).

In the logs I see:

DEBUG NHibernate.Loader.Loader - processing result set
[2100] DEBUG NHibernate.Type.Int64Type - returning '1' as column: id0_
[2100] DEBUG NHibernate.Type.Int64Type - returning '13' as column: id1_
[2100] DEBUG NHibernate.Type.Int64Type - returning '16' as column: id2_
[2100] DEBUG NHibernate.Loader.Loader - result row: 1, 13, 16
[2100] DEBUG NHibernate.Loader.Loader - Initializing object from DataReader: 1
[2100] DEBUG NHibernate.Loader.Loader - Hydrating entity: TKD.Student#1
[2100] DEBUG NHibernate.Util.ADOExceptionReporter - SQL Exception

I'm assuming "Hydrating" means "Filling values"

Based on that assumption, it seems that nhibernate is trying to fill values that aren't returned by the query.

Am I going in the right direction here?

POCO = plain ol' C# object. taken from the java camp (POJO)

Hydrating means reading values from the database and instantiating the class with those values.

I think you should focus on setting up your associations more clearly. Knowing your table structure would help. In lieu, I will try and intuit based on your mapping files:



should probably be a



uncomment your sets as you had originally:



and make sure the belt class has the other side of the relationship:



becomes



I think there might be an issue with the mapping of belt 1-1 to BeltDescription. I've not done it the way you are attempting so I'm sort of at a loss on that one. Unless you have some sort of database UNIQUE index on there wouldn't you end up with more than one description for a rankvalue?

In a nutshell, though, what I would do is comment out all of your relationships in the Student class. Make sure you can load a student, then add the relationship from student to belt commenting all relationships out of belt (except for student) and get that working, then add the final 1-1 to belt description.

-devon

Thanks for the help,

I had the <set> tag uncommented before, and commented it as a test to solve my _id1 problem. I uncommented it and had the same effect.

the school_id linked table hasn't been created yet. you are correct in that should be mapped as you stated, but it's kinda hard to map to a non-existent database table. :)

Before I tried the query that's causing me issues, I was able to get a list of students no problem.

I'll dig some more into the docs to see if I can find a resolution.[/quote]

It appears that if you use CreateSQLQuery, and specify properties individually, you must use ALL the properties from every object in the query. This isn't what I want to do, so CreateSQLQuery won't work for me.

Time to move on to other things....

That makes sense since you are expected to write the entire SQL query yourself. In general, I try not to use SQL Queries. I stick with HQL and ICriteria. The general rules are:

Use HQL first,as it is the easiest to understand. Then go with ICriteria which allows you to query by example, and last, in rare cases where the other two won't suffice, use SQL queries.

-devon