NHibernate.Exceptions.GenericADOException : could not execute query

Question

I have a legacy application (vfp 8) that I need to pull data from (no inserts). I am using the Accnum field as the primary key, it is defined in the table as character 11.

Factory configuration:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-configuration xmlns="urn:nhibernate-configuration-2.2">
<reflection-optimizer use="false" />
<session-factory>
<property name="connection.provider">NHibernate.Connection.DriverConnectionProvider</property>
<property name="dialect">NHibernate.Dialect.GenericDialect</property>
<property name="connection.driver_class">NHibernate.Driver.OleDbDriver</property>
<property name="connection.connection_string">Provider=VFPOLEDB.1;Data Source=C:\Analysis\Quantium\development\RD warehouse\_RDAUWH\Data;Collating Sequence=MACHINE</property>
<property name="show_sql">false</property>
</session-factory>
</hibernate-configuration>

This is my mapping file:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
               assembly="RDLabels"
               namespace="RDLabels.Domain">

  <class name="CustMast">
    <id name="Accnum" column="Accnum" type="string">
    <generator class="assigned"/>
    </id>
    <property name="Fullname" />
    <property name="Add" />
    <property name="State" />
  </class>  
</hibernate-mapping>

The class:

public class CustMast
{
    private string _accnum;
    public virtual string Accnum
    {
        get { return _accnum; }
        set { _accnum = value; }
    }
    private string _fullname;
    public virtual string Fullname
    {
        get { return _fullname; }
        set { _fullname = value; }
    }
    private string _add;
    public virtual string Add
    {
        get { return _add; }
        set { _add = value; }
    }
    private string _state;
    public virtual string State
    {
        get { return _state; }
        set { _state = value; }
    }
}

Here is the code that gets the record:

public CustMast GetByAccnum(String accnum)
{
        using (ISession session = NHibernateHelper.OpenSession())
        {
            CustMast custMast = session
                                .CreateCriteria(typeof(CustMast))
                                .Add(Restrictions.Eq("Accnum", accnum))
                                .UniqueResult<CustMast>();
            return custMast;
        }
}

The full error is:

NHibernate.Exceptions.GenericADOException : could not execute query
[ SELECT this_.Accnum as Accnum0_0_, this_.Fullname as Fullname0_0_, this_.Add as Add0_0_, this_.State as State0_0_ FROM CustMast this_ WHERE this_.Accnum = ? ]
Name:cp0 - Value:00059337444
[SQL: SELECT this_.Accnum as Accnum0_0_, this_.Fullname as Fullname0_0_, this_.Add as Add0_0_, this_.State as State0_0_ FROM CustMast this_ WHERE this_.Accnum = ?]
----> System.IndexOutOfRangeException : Invalid index 0 for this OleDbParameterCollection with Count=0. - d:\CSharp\NH\NH\nhibernate\src\NHibernate\Loader\Loader.cs:1590

Running NHibernate Profiler it shows:

WARN: 
reflection-optimizer property is ignored out of application configuration file.


WARN: 
System.IndexOutOfRangeException: Invalid index 0 for this OleDbParameterCollection with Count=0.
at System.Data.OleDb.OleDbParameterCollection.RangeCheck(Int32 index)
at System.Data.OleDb.OleDbParameterCollection.GetParameter(Int32 index)
at System.Data.Common.DbParameterCollection.System.Collections.IList.get_Item(Int32 index)
at NHibernate.Driver.DriverBase.ExpandQueryParameters(IDbCommand cmd, SqlString sqlString) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Driver\DriverBase.cs:line 235
at NHibernate.AdoNet.AbstractBatcher.ExpandQueryParameters(IDbCommand cmd, SqlString sqlString) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\AdoNet\AbstractBatcher.cs:line 232
at NHibernate.Loader.Loader.PrepareQueryCommand(QueryParameters queryParameters, Boolean scroll, ISessionImplementor session) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Loader\Loader.cs:line 1152

ERROR: 
Invalid index 0 for this OleDbParameterCollection with Count=0.

Answer 1

I was struggling with my linq queries throwing the same error whenever i passed them a parameter. If i didn't pass any parameters and did a session.Query() they would work fine.

I struggled with this for days but i found this Nhibernate jira ticket here . It explains an apparent problem with SQLParameters and the Iseries Db2 provider.

I understand you're using a different provider but you might benefit from just downloading the latest Nhibernate core source code, building it, and referencing the latest version in your project. It fixed my issue.

Answer 2

If you use ODP.Net then you need an oracle client installed and configured, on your target machine, as well as your development machine.

The error message you post might be caused by having more than one Oracle client installed, or possibly trying to use something like a later version of odp.net with an earlier client version.

Answer 3

First thing I would try is to run this in SQL and see what happens as it might be a data issue.

SELECT this_.Accnum as Accnum0_0_, this_.Fullname as Fullname0_0_, this_.Add as Add0_0_, this_.State as State0_0_ FROM CustMast this_ WHERE this_.Accnum = '00059337444'

Is Accnum column defined as a string type (varchar, nvarchar etc) in your database?

Edit OK the next step is to actually confirm the SQL being sent to FoxPro. You will need to set up logging (or download a trial copy of NHProf) to find out if the SQL is correct. Your setup and code looks correct however I am not 100% sure of the choice of dialect as this may be causing you problems.

I take it you have seen this and this.

Edit2 The NHProf error seems to me that it thinks your Id should be a Int32 as it looks like it is calling at System.Data.OleDb.OleDbParameterCollection.GetParameter(Int32 index).

I think you need to add this to your mappings:-

<id name="Accnum" column="Accnum" type="string" >

Note the additional type="string"