CODEWITHSUNDEEP
c#npgsql
Tejal Barot
Tejal Barot

Reputation: 80

08P01: insufficient data left in message for Nullable DateTime

I have database table having one column defined as timestamp without time zone. Now from my c# application, when I try to insert null value in that column using NpgSql BeginBinaryImport it gives error message as mentioned below:

08P01: insufficient data left in message

Below is the code which I am trying to execute: 

static void Main(string[] args)
{
    BulkInsert();
}

private static void BulkInsert()
{

    DataTable table = new DataTable();
    table.Columns.Add("firstname", typeof(String));
    table.Columns.Add("lastname", typeof(String));
    table.Columns.Add("logdatetime", typeof(DateTime));
    table.Columns.Add("status", typeof(int));
    table.Columns.Add("id", typeof(long));

    var dataRow = table.NewRow();
    dataRow["firstname"] = "MyFirstName";
    dataRow["lastname"] = "MyLastName";
    dataRow["logdatetime"] = DBNull.Value;
    dataRow["status"] = 1;
    dataRow["id"] = 10;
    table.Rows.Add(dataRow);

    var data = new DataAccess();

    using (var npgsqlConn = new NpgsqlConnection([ConnectionString]))
    {
        npgsqlConn.Open();
        var commandFormat = string.Format(CultureInfo.InvariantCulture, "COPY {0} {1} FROM STDIN BINARY", "logging.testtable", "(firstName,LastName,logdatetime,status,id)");
        using (var writer = npgsqlConn.BeginBinaryImport(commandFormat))
        {
            foreach (DataRow item in dataTable.Rows)
            {
                writer.StartRow();
                foreach (var item1 in item.ItemArray)
                {
                     writer.Write(item1);
                }

            }
        }

        npgsqlConn.Close();
}

Upvotes: 2

Views: 9354

Answers (2)

prashant
prashant

Reputation: 2209

I faced same issue while bulk copying data in table. To solve this I have created an extension method so you dont have to null check on all fields

public static void WriteWithNullCheck(this NpgsqlBinaryImporter writer, string value)
        {
            if (string.IsNullOrEmpty(value))
            {
                writer.WriteNull();
            }
            else
            {
                writer.Write(value);
            }
        }

this can be made generic by 

public static void WriteWithNullCheck<T>(this NpgsqlBinaryImporter writer, T value,NpgsqlDbType type)
        {
            if (value == null)
            {
                writer.WriteNull();
            }
            else
            {
                writer.Write(value, type);
            }
        }

Upvotes: 1

Shay Rojansky
Shay Rojansky

Reputation: 16722

The issue is the DBNull.Value you're trying to write - Npgsql doesn't support writing nulls this way. To write a null you need to use the WriteNull() method instead.

I can make Npgsql accept DBNull.Value, but only for the overload of Write() which also accepts an NpgsqlDbType (because Npgsql has to write the data type, and with DBNull.Value we have no idea what that is).

EDIT: Have done this, see https://github.com/npgsql/npgsql/issues/1122.

Upvotes: 4

Related Questions