Informix with NHibernate

I am trying to get Informix working with NHibernate on windows 7. I have a connection string that works fine with informix now, it is this,

Database=db;Server=server:port;uid=username;password=password;pooling=false

I am using the IBM.Data.Informix .NET provider version 9.0.0.2.

We have a number of different applications that work fine using this provider with the Informix servers that we are running.

My nhibernate application is connecting to the informix server now, but the problem is the form of the SQL that it is producing.

If my nhibernate code looks like this,

using (ISession session = Config.SessionFactor开发者_C百科y.OpenSession())
{
    return session
        .CreateCriteria<DBTable>()
        .Add(Restrictions.Eq("FieldValue", true))
        .List<DBTable>();
}

I am new to Informix, but if I am not wrong the correct SQL would be this,

select * from DBTable where fieldValue = 'T'

But instead the SQL is it producing is,

select * from DBTable where fieldValue = True

Which is not working. I tried adding stuff like this to the nhibernate config file,

<property name="query.substitutions">True=T,False=F</property>
<property name="query.substitutions">True 'T',False 'F'</property>
<property name="query.substitutions">True='T',False='F'</property>
<property name="query.substitutions">True T,False F</property>

but that just doesn't seem to work. I couldn't find consistent documentation as to how to use the query.substitutions, and it seemed to differ depending on what database type you are using.

What version of NHibernate are you using?

What is the property type of FieldValue?

I'm using NHibernate with Informix and queries with boolean restrictions work fine. These are the relevant configuration values:

<property name="connection.provider">NHibernate.Connection.DriverConnectionProvider</property>
<property name="connection.driver_class">NHibernate.Driver.IfxDriver</property>
<property name="dialect">NHibernate.Dialect.InformixDialect1000</property>

Commentary on original version of question

To answer this is going to require some more information - and more information requests than fits into a comment.

First, I assume from the discussion that you are using IBM Informix Dynamic Server (IDS) on some variant of Windows (but which one?). Which version of IDS are you using, please? Also, which version of the ClientSDK are you using?

Next, I assume that you can connect to the IDS database using some program - the problem is related to access via NHibernate, rather than access at all. If your problem is that you have not yet been able to access IDS at all, then the debugging steps are rather different from 'not via an alternative access method'.

When you connect via the other method, (a) what is that other method, and (b) what is the connection string you use? I would like to see the 'structural details' of string values. For example, if you specify the database as 'sensitive@secret', I'd like to see the notation 'xxxxxxxxx@yyyyyy' because although we don't necessarily need to know that the names are sensitive and secret, we do need to know roughly what the names look like. Similarly for any other value in the strings. You said you removed the sensitive information, but you've done it so thoroughly that I can't tell whether what you supplied was sensible.

Have you used SETNET32 to set any Informix environment values - a question that only applies to Windows platforms (Unix uses regular environment variables and not a central registry)? If so, what have you set?

How do the working connection strings for the non-NHibernate package compare with the non-working strings for NHibernate?

Lastly (for now) you showed that you had tried to use both the IBM DB2 .NET driver and the Informix OLEDB driver. You need to be aware that the DB2 .NET driver uses the DRDA protocol to talk to IDS, whereas the Informix driver uses the SQLI protocol. By default, IDS only listens for SQLI connections - you would have to configure IDS to accept the DRDA connections. Fixing that gets into some of the details of IDS administration - which I hope we don't need to deal with, but we may.

How to get the information onto SO? I suggest you edit your question, adding the extra information to the question so other people can easily see the issues. (I'm not an expert with IDS on Windows; my backyard is Unix-based. I will likely have to get other people to assist in providing the answer, but the necessary information isn't yet available.)

Commentary on question as amended

IDS supports BOOLEAN types in an unorthodox way - it does not recognize true or false (or unknown) as boolean values; it uses 't' and 'f' instead. Consequently, the code generated by NHibernate is not valid for IDS (though I'd accept arguments to the effect that it should be). I'm not clear whether there's a good way to resolve the problem. If you can persuade NHibernate to pass the quoted characters instead of true and false, then you're in with a fighting chance.

Because of this: fieldValue = 'T' I'm drawing conclusion that fieldValue have Char(1) type in database, not boolean. If so you should use:

Restrictions.Eq("FieldValue", "T")

I haven't tested this but I think that your issue is not Informix - specific. You'll get the same error with different database.

This is because every NHibernate dialect provides ToBooleanValueString method. By default boolean values are mapped to "0" and "1" (so you can't expect "T" in other databases) and for Informix it is mapped to "t" and "f". Apparently this method is not used by Restrictions.Eq which is not Informix fault.

Probably Restrictions.Eq is using internally boolean variable (because your argument is boolean) and calls its "ToString" method because database column is of character type. Result of "ToString" for true value is just "true".

The problem is happening because the newer Informix drivers are using the different connections, and it is misunderstanding the booleans.

The solution that I found is to create a new NHibernate Driver, inherited from NHibernate.Driver.IfxDriver. Treating the parameters just before the query execution.

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using NHibernate;
using NHibernate.Driver;
using NHibernate.SqlCommand;
using NHibernate.SqlTypes;

namespace DataAccess
{
    public class NHibernateCustomDriver : NHibernate.Driver.IfxDriver
    {
        public override IDbCommand GenerateCommand(CommandType type, SqlString sqlString, SqlType[] parameterTypes)
        {
            IDbCommand cmd = CreateCommand();
            cmd.CommandType = type;

            SetCommandTimeout(cmd);
            SetCommandText(cmd, sqlString, parameterTypes);
            SetCommandParameters(cmd, parameterTypes);
            return cmd;
        }

        private void SetCommandText(IDbCommand cmd, SqlString sqlString, SqlType[] parameterTypes)
        {
            SqlStringFormatter formatter = GetSqlStringFormatter();
            formatter.Format(sqlString);

            int index = 0;
            int count = 0;
            string newCommand = formatter.GetFormattedText();
            index = newCommand.IndexOf("?");
            while (index >= 0)
            {
                if (parameterTypes[count].DbType == DbType.Boolean)
                {
                    newCommand = newCommand.Substring(0, index) + "CAST(?  AS BOOLEAN)" + newCommand.Substring(index + 1);
                    index = newCommand.IndexOf("?", index + 1);
                }
                count++;
                index = newCommand.IndexOf("?", index + 1);

            }

            cmd.CommandText = newCommand;

        }

        private void SetCommandParameters(IDbCommand cmd, SqlType[] sqlTypes)
        {
            for (int i = 0; i < sqlTypes.Length; i++)
            {
                string paramName = ToParameterName(i);
                IDbDataParameter dbParam = GenerateParameter(cmd, paramName, sqlTypes[i]);
                cmd.Parameters.Add(dbParam);
            }
        }

        private static string ToParameterName(int index)
        {
            return "p" + index;
        }
    }
}

The NHibernate configuration must be set to the new class.

cfg.SetProperty("connection.driver_class", "DataAccess.NHibernateCustomDriver, DataAccess");

You also have to create a custom type, to handle those booleans:

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using NHibernate.Type;

namespace DataAccess
{
    public class NHibernateUnixBooleanType : BooleanType
    {
        public override void Set(IDbCommand cmd, object value, int index)
        {
            cmd.Parameters[index] = CloneParameter(cmd, cmd.Parameters[index] as IDbDataParameter, value as bool?);
        }
        private IDbDataParameter CloneParameter(IDbCommand cmd, IDbDataParameter originalParameter, bool? value)
        {
            var clone = cmd.CreateParameter();
            clone.DbType = DbType.String;
            clone.Value = value.Value ? "t" : "f";
            clone.ParameterName = originalParameter.ParameterName;
            return clone;
        }
    }
}

And set this type on the mapping file:

<property name="Property" column="column" type="DataAccess.NHibernateUnixBooleanType, DataAccess"></property>