NHibernate and DB2 zOS v9

[DBLWizard]

Howdy,

I am trying to get DB2 zOS 9 to work with NHibernate but I can not
find clear documenation on how to setup the app.config and the
hibernate.cfg.xml file. I'm getting an error when its trying to build
the SQL statements a substituting parameters. So I want to make sure
I've got my config file setup. I have tried both of these to
configurations and both get the same error.

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-configuration xmlns="urn:nhibernate-configuration-2.2">
<session-factory name="Test">
<property
name="connection.provider">NHibernate.Connection.DriverConnectionProvider</
property>
<property name="dialect">NHibernate.Dialect.DB2Dialect</property>
<property
name="connection.driver_class">NHibernate.Driver.DB2Driver</property>
<property
name="connection.connection_string">Database=dbalias;Uid=myUid;Pwd=myPass</
property>
<property name="show_sql">true</property>
</session-factory>
</hibernate-configuration>

This is an ODBC example I found:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-configuration xmlns="urn:nhibernate-configuration-2.0" >
<session-factory name="session">
<property
name="connection.provider">NHibernate.Connection.DriverConnectionProvider</
property>
<property
name="connection.driver_class">NHibernate.Driver.OdbcDriver</property>
<property name="connection.connection_string">driver={IBM DB2
ODBC
DRIVER};Database=db;hostname=host;port=port;protocol=TCPIP;uid=uid;
pwd=pwd</property>
<property name="show_sql">true</property>
<property name="dialect">NHibernate.Dialect.DB2Dialect</
property>
</session-factory>
</hibernate-configuration>

As I said I've tried both of these and get the same error and it
revolves around the false return of the UseNamedPrefixInSql function.
The Error I'm getting is:

could not load an entity: [ADRPData.Domain.DocType#ArbAssocRuling]
[SQL: SELECT doctype0_.SDOCTYPEID as SDOCTYPEID1_0_,doctype0_.
SDOCTYPECODE as SDOCTYPE2_1_0_ FROM DocType doctype0_ WHERE
doctype0_.SDOCTYPEID=?]

Can anybody point me to a good resource for using NHibernate with DB2
zOS or how this config file should be set to make things work
correctly.

Thanks

dbl

[MattO]

Dbl,

Welcome to the minority of users who are still using DB2!

The first XML example is pretty much what I use for DB2 on IBM i
(AS400).

I don't think your error however is due to improper XML
configuration. I think it is something else. I think we need more
code to figure it out. Can you post your code for the DocType class,
the code that is trying to load the domain, and the exception (and
inner exception detail) from the query you are trying to perform?

Matt

[DBLWizard]

Matt,

Here is all the information minus the using statements in the source
code.

Thanks

David

Exception:
{"could not load an entity: [ADRPData.Domain.DocType#ArbAssocRuling]

[SQL: SELECT doctype0_.SDOCTYPEID as SDOCTYPEID1_0_,

doctype0_.SDOCTYPECODE as SDOCTYPE2_1_0_, doctype0_.sSettings as
sSettings1_0_, doctype0_.sTemplate as sTemplate1_0_,
doctype0_.sArchiveOnly as sArchive5_1_0_, doctype0_.sDocTypeDescriptio
as sDocType6_1_0_, doctype0_.sPrinterName as sPrinter7_1_0_,
doctype0_.sViewableInMCare as sViewabl8_1_0_,
doctype0_.sMCareDescription as sMCareDe9_1_0_, doctype0_.sDocGroup as
sDocGroup1_0_ FROM DocType doctype0_ WHERE doctype0_.SDOCTYPEID=?]"}

Inner Exception:
{"Invalid index 0 for this DB2ParameterCollection with Count=0."}

DocType.hbm.xml:

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

<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
assembly="ADRPData"
namespace="ADRPData.Domain">
<class name="DocType">
<id name="sDocTypeID" column="SDOCTYPEID" type="String">
<generator class="assigned" />
</id>
<property name="sDocTypeCode" column="SDOCTYPECODE" />
<property name="sSettings" />
<property name="sTemplate" />
<property name="sArchiveOnly" />
<property name="sDocTypeDescriptio" />
<property name="sPrinterName" />
<property name="sViewableInMCare" />
<property name="sMCareDescription" />
<property name="sDocGroup" />
</class>
</hibernate-mapping>

DocType.cs:

namespace ADRPData.Domain
{
public class DocType
{
public virtual string sDocTypeID { get; set; }
public virtual string sDocTypeCode { get; set; }
public virtual string sSettings { get; set; }
public virtual string sTemplate { get; set; }
public virtual string sArchiveOnly { get; set; }
public virtual string sDocTypeDescriptio { get; set; }
public virtual string sPrinterName { get; set; }
public virtual string sViewableInMCare { get; set; }
public virtual string sMCareDescription { get; set; }
public virtual string sDocGroup { get; set; }
}
}

Program.cs - ConsoleApplication:

namespace ADRPConsole
{
class Program
{
private static ISessionFactory _sessionFactory;
private static Configuration _configuration;

static void Main(string[] args)
{
_configuration = new Configuration();
_configuration.Configure();
_configuration.AddAssembly(typeof(DocType).Assembly);
_sessionFactory = _configuration.BuildSessionFactory();

IDocTypeRepository repository = new DocTypeRepository();
var fromDb = repository.GetById("ArbAssocRuling");

Console.WriteLine("DocTypeCode:" + fromDb.sDocTypeCode);

[MattO]

David,

Things actually look OK. Nothing too different from what I am doing
with DB2 on the AS400. I guess I only have a few suggestions at this
point:

1. Remove all columns except for maybe two columns and try again.
2. Try to "GetAll" or whatever it is called in your domain layer that
retrieves all objects from the database without a parameter and see if
that works, as the error seems to indicate you are missing a parameter
(in your example the "ArbAssocRuling" parameter.

Additionally, zOS DB2 may require the fully qualified table name. In
my system, if I don't specify "ARRTFLIB.CONSULTS", which is the
libraryname.tablename format I get a "could not load an entity error",
but the inner exception is not the same.

Here is the example:

<class name="AS400_CONSULTS" table="ARRTFLIB.CONSULTS"
optimistic-lock="all" dynamic-update="true" lazy="true">
<id name="Id" column="COID#" >

<generator class="assigned" />
</id>

Lastly atleast in the case of AS400, it requires special nHibernate
settings as follows:

<property
name="connection.provider">NHibernate.Connection.DriverConnectionProvider</
property>

<property name="dialect">NHibernate.Dialect.DB2400Dialect</
property>
<property
name="connection.driver_class">NHibernate.Driver.DB2400Driver</
property>

I didn't however find a specialized version for zOS, so I'm going to
assume the standard DB2 settings you have already specified are
correct.

Sorry I couldn't be of more help!

> > > I am trying to get DB2zOS9 to work with NHibernate but I can not

> > >zOSor how this config file should be set to make things work
> > > correctly.
>
> > > Thanks
>
> > > dbl

[DBLWizard]

Matt,

Hey, I appreciate your response but still need a little help. Being
new to NHibernate I'm still not sure how all this works.

I had only implemented the GetByID in my domain .. what session call
in NHibernate will return all of the objects for a given object? Get
and Load seem to do the same thing and they all take an "objectID" as
a parameter. I don't see another call that returns any kind of
collection. What am I missing?

Thanks

dbl

[MattO]

David,

I would recommend looking at the s#arp project as they've pretty much
done all of this kind of stuff for you and you can add on to it within
your own project.

Anyways, here is how you would typically return all results from a
table without a "WHERE clause"

ICriteria criteria = Session.CreateCritera(DocType);
return criteria.List<DocType>();

Or all results with a limit (100 records):

ICriteria criteria =
Session.CreateCritera(DocType).SetMaxResults(100);
return criteria.List<DocType>();

Matt

On Sep 6, 1:34 pm, DBLWizard <ibflyfis...@yahoo.com> wrote:
> Matt,
>
> Hey, I appreciate your response but still need a little help.  Being
> new to NHibernate I'm still not sure how all this works.
>
> I had only implemented the GetByID in my domain .. what session call
> in NHibernate will return all of the objects for a given object?  Get
> and Load seem to do the same thing and they all take an "objectID" as
> a parameter.  I don't see another call that returns any kind of
> collection.  What am I missing?
>
> Thanks
>
> dbl
>
> On Sep 6, 12:07 pm, MattO <xant...@gmail.com> wrote:
>
>
>
> > David,
>
> > Things actually look OK.  Nothing too different from what I am doing
> > with DB2 on the AS400.  I guess I only have a few suggestions at this
> > point:
>
> > 1.  Remove all columns except for maybe two columns and try again.
> > 2.  Try to "GetAll" or whatever it is called in your domain layer that
> > retrieves all objects from the database without a parameter and see if
> > that works, as the error seems to indicate you are missing a parameter
> > (in your example the "ArbAssocRuling" parameter.
>

> > Additionally,zOSDB2 may require the fully qualified table name.  In

> > my system, if I don't specify "ARRTFLIB.CONSULTS", which is the
> > libraryname.tablename format I get a "could not load an entity error",
> > but the inner exception is not the same.
>
> > Here is the example:
>
> >   <class name="AS400_CONSULTS" table="ARRTFLIB.CONSULTS"
> >          optimistic-lock="all" dynamic-update="true" lazy="true">
> >     <id name="Id" column="COID#" >
> >       <generator class="assigned" />
> >     </id>
>
> > Lastly atleast in the case of AS400, it requires special nHibernate
> > settings as follows:
>
> >       <property
> > name="connection.provider">NHibernate.Connection.DriverConnectionProvider</
> > property>
> >       <property name="dialect">NHibernate.Dialect.DB2400Dialect</
> > property>
> >       <property
> > name="connection.driver_class">NHibernate.Driver.DB2400Driver</
> > property>
>

> > I didn't however find a specialized version forzOS, so I'm going to

[DBLWizard]

Matt,

That worked! so that's progress. I will continue experimenting and
see what works and what doesn't. I'm guessing I'm missing something
in the config file that better defines the ID field and that's what's
causing the error. We'll just keep taking steps and see what
happens. I'm also looking at CodeSmith but it doesn't have real good
support for DB2 either.

I'm not sure what s#harp project you are referring to. I've gone
through the nhibernate getting started project that is listed on their
website and couple different version of that that I have found on line
but I don't have ... that I know of ... a sample nhibernate project to
look at. Did I miss something somewhere?

Thanks for all the help!

dbl

> ...
>
> read more »

[DBLWizard]

Matt,

That worked! so that's progress. I will continue experimenting and
see what works and what doesn't. I'm guessing I'm missing something
in the config file that better defines the ID field and that's what's
causing the error. We'll just keep taking steps and see what
happens. I'm also looking at CodeSmith but it doesn't have real good
support for DB2 either.

I'm not sure what s#harp project you are referring to. I've gone
through the nhibernate getting started project that is listed on their
website and couple different version of that that I have found on line
but I don't have ... that I know of ... a sample nhibernate project to
look at. Did I miss something somewhere?

Thanks for all the help!

dbl

On Sep 7, 7:13 am, MattO <xant...@gmail.com> wrote:

> ...
>
> read more »

[MattO]

s#harp: http://www.sharparchitecture.net/

Basically they have the architectural foundations for your domain
layer already built. It's geared towards MVC, but I retrofitted it
for ASP .NET. But the same domain layer stuff they do in there would
probably help you.

Matt

> ...
>
> read more »- Hide quoted text -
>
> - Show quoted text -

[DBLWizard]

Matt,

 

Have you had to deal with properly handling the Db2 datatypes Date, Time, and TimeStmp?

 

dbl