C# NHibernate.Exceptions.GenericADOException：无法执行查询

NHibernate.Exceptions.GenericADOException : could not execute query

提问by P Hemans

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.

我有一个遗留应用程序 (vfp 8)，我需要从中提取数据（无插入）。我使用 Accnum 字段作为主键，它在表中定义为字符 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:

运行 NHibernate Profiler 它显示：

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.

回答by Rippo

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

我会尝试的第一件事是在 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 = '00059337444'

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?

Accnum 列是否在您的数据库中定义为字符串类型（varchar、nvarchar 等）？

EditOK 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.

EditOK 下一步是实际确认发送到 FoxPro 的 SQL。您需要设置日志记录（或下载NHProf的试用版）来确定 SQL 是否正确。您的设置和代码看起来正确，但是我不是 100% 确定方言的选择，因为这可能会给您带来问题。

I take it you have seen thisand this.

我想你已经看到了这个和这个。

Edit2The 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).

Edit2在我看来，NHProf 错误认为您的 Id 应该是 Int32，因为它看起来像是在调用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"

注意额外的 type="string"

回答by BDors

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.

每当我向它们传递参数时，我的 linq 查询都会抛出相同的错误。如果我没有传递任何参数并执行 session.Query() 他们会正常工作。

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.

我为此苦苦挣扎了好几天，但我在这里找到了这张 Nhibernate jira 票。它解释了 SQLParameters 和 Iseries Db2 提供程序的一个明显问题。

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.

我知道您使用的是不同的提供程序，但您可能会从下载最新的 Nhibernate 核心源代码、构建它并在您的项目中引用最新版本中受益。它解决了我的问题。