Illegal attempt to use Text/Byte host variable - I

问题:

Trying to insert into a table (Text Column) via Dapper, and getting the error from Informix:

Illegal attempt to use Text/Byte host variable

I have written a small program to simulate this, and I am still up against problems.

We cannot currently use the Informix drivers, as they do not suit our needs.

using Dapper;
using System;
using System.Data.Odbc;
namespace DapperParamsTest
{
    class Program
    {
        static void Main(string[] args)
        {
            OdbcConnection conn = new System.Data.Odbc.OdbcConnection("Driver={IBM INFORMIX ODBC DRIVER (64-bit)}; Host=bylgia; Server=bylgia; Service=sqlexec; Protocol=onsoctcp; Database=DATABASE; Client_Locale=en_US.CP1252; DB_LOCALE=en_GB.1252");

            var dynParams = new DynamicParameters();

            dynParams.Add("np_c_ref",-1);
            dynParams.Add("np_np_type","T");
            dynParams.Add("np_text", System.Text.Encoding.Default.GetBytes("TEXT INPUT"), System.Data.DbType.Binary);

            conn.Execute("INSERT INTO notepads (np_c_ref, np_type,np_text) VALUES (?,?,?)",dynParams);

            Console.WriteLine("Written");
            Console.ReadLine();
        }
    }
}

Table Structure:

CREATE TABLE notepad
(
    np_c_ref int,
    np_type char(1),
    np_text TEXT
)

回答1:

This question might actually be a lot simpler than my other answer presumes; I've just noticed you say the column is TEXT, yet you are passing it a byte[]. If you want that, you should probably be using the BYTE data type. If you want to use TEXT - just pass it the string (i.e. "TEXT INPUT") and forget about Encoding.

回答2:

I'm not sure I know what setup I would need to repro this... However, here's something to try:

It looks like Informix wants positional parameters. Recent builds of dapper actually support a modified syntax to make this more convenient. Can you try:

conn.Execute("INSERT INTO notepads (np_c_ref, np_type,np_text) VALUES (?np_c_ref?,?np_np_type?,?np_text?)",dynParams);

Also: try using 'T' instead of "T" (character versus string).

This might help if the issue is parameter order. Note also: if this works, you can possibly also use the more-convenient anonymous type approach to specify the parameters:

conn.Execute("INSERT INTO notepads (np_c_ref, np_type,np_text) VALUES (?np_c_ref?,?np_np_type?,?np_text?)",
new { np_c_ref = -1, np_np_type = "T",
    np_text = System.Text.Encoding.Default.GetBytes("TEXT INPUT")
});

Final final thought: there are virtually no scenarios in which Encoding.Default is either correct or appropriate. It is rarely used, except by mistake.