CODEWITHSUNDEEP
c#postgresqlnpgsql
Răzvan Flavius Panda
Răzvan Flavius Panda

Reputation: 22116

How to use NpgsqlCopyIn with NpgsqlCopySerializer?

If I understand correctly NpgsqlCopyIn with NpgsqlCopySerializer should work something like this:

var conn = new NpgsqlConnection(ConfigurationManager.ConnectionStrings["PostgreSqlDb"].ConnectionString);
conn.Open();
var tran = conn.BeginTransaction();
var cmd = new NpgsqlCommand("COPY address (id, employee, value) FROM STDIN", conn);
var npgsqlCopySerializer = new NpgsqlCopySerializer(conn);
var npgsqlCopyIn = new NpgsqlCopyIn(cmd, conn, npgsqlCopySerializer.ToStream);

try
{
    npgsqlCopyIn.Start();
    npgsqlCopySerializer.AddInt32(300);
    npgsqlCopySerializer.AddInt32(1);
    npgsqlCopySerializer.AddString("address");
    npgsqlCopySerializer.EndRow();
    npgsqlCopySerializer.Flush();
    npgsqlCopySerializer.AddInt32(301);
    npgsqlCopySerializer.AddInt32(1);
    npgsqlCopySerializer.AddString("another\r\naddress");
    npgsqlCopySerializer.EndRow();
    npgsqlCopySerializer.Flush();
    npgsqlCopyIn.End();

    tran.Commit();
}
//catch (Exception e)
//{
//    tran.Rollback();
//    throw;
//}
finally
{
    conn.Close();
}

Problem is that each time there are not allowed characters in AddString() it throws an ArgumentOutOfRangeException inside that method, otherwise it works.

For example:

npgsqlCopySerializer.AddString("another\r\naddress");

will throw the exception cause it contains a newline which has a special meaning in copy from text format (row separator) and should be escaped.

Anyone know what I can do to make it work? I searched for examples on the internet but I couldn't find anything.

Thanks for your help!

Upvotes: 0

Views: 4505

Answers (2)

Jelmer
Jelmer

Reputation: 61

I'm the person who reported the bug, coincidentally only a few days ago.

Would you mind posting the XML you're trying to insert? I have tried it with an OSM result: http://nominatim.openstreetmap.org/search.php?q=A7&viewbox=3.96%2C52.82%2C7.93%2C51.11&format=xml

And a rather large web.config file, both work flawlessly. So either I made a copy / paste error while submitting my fix, or the XML you're trying to insert is different than the XML I tried.

Also: do you get the exact same error as before (i.e., argument out of range)? The default buffer size is only 8k, which wasn't enough in my case.

I don't know if it makes any difference, but I didn't use flush at all:

NpgsqlConnection Conn = new NpgsqlConnection(getPostgresConnString());
Conn.Open();
NpgsqlCopyIn copyIn = new NpgsqlCopyIn("COPY table  (col1,col2,col2)   FROM STDIN;", Conn);
copyIn.Start();
NpgsqlCopySerializer cs1 = new NpgsqlCopySerializer(pConn2);
cs1.AddString(System.IO.File.ReadAllText("C:\\test\\Web.config"));
[...]
cs1.EndRow();
cs1.Close();
copyIn.End();

Upvotes: 2

twoflower
twoflower

Reputation: 6830

To quote from the documentation:

It is strongly recommended that applications generating COPY data convert data newlines and carriage returns to the \n and \r sequences respectively. At present it is possible to represent a data carriage return by a backslash and carriage return, and to represent a data newline by a backslash and newline.

So I would say the following will work:

npgsqlCopySerializer.AddString("another\\\r\\\naddress");

EDIT: I tried it for myself and there seems to be a bug in NpgsqlCopySerializer.AddString():

if (escapeAt > bufferedUpto)
{
    int encodedLength = ENCODING_UTF8.GetByteCount(fieldValue.ToCharArray(bufferedUpto, escapeAt));
    MakeRoomForBytes(encodedLength);
    _sendBufferAt += ENCODING_UTF8.GetBytes(fieldValue, bufferedUpto, escapeAt, _sendBuffer, _sendBufferAt);
    bufferedUpto = escapeAt;
}

The second line throws the ArgumentOutOfRangeException after the two escape sequences (\r and \n) are processed and added to the output buffer, since at that moment bufferedUpto == 9 and escapeAt == "another\r\naddress".Length == 16

Now I even found this bug reported at the Npgsql tracker.

Upvotes: 2

Related Questions