Обсуждение: Error When Trying to Use Npgsql to COPY into a PostgreSQL Database
I am currently in the process of using an ODBC Connection and a Npgsql
Connection to transfer a database from a Sybase/Advantage platform to a
PostgreSQL platform using C# and the .NET framework.
Below is the code I have come up with to transfer the data itself...
NpgsqlCommand copyCommand = new NpgsqlCommand("COPY \"2009info.adt\" FROM
STDIN", connectionTest);
string query = "SELECT * FROM \"2009info.adt\"";
OdbcCommand test = new OdbcCommand(query, myConnection);
string dataEntry = "";
NpgsqlCopyIn copy = new NpgsqlCopyIn(copyCommand,
connectionTest);
copy.Start();
OdbcDataReader reader = test.ExecuteReader();
int rowCount = reader.FieldCount;
while (reader.Read())
{
for (int i = 0; i < rowCount; i++)
{
dataEntry = dataEntry + reader[i].ToString() + "|";
}
dataEntry = dataEntry.Trim().Substring(0, dataEntry.Length -
1);
dataEntry = dataEntry.Replace("\r",
string.Empty).Replace("\n", string.Empty);
var raw = Encoding.UTF8.GetBytes(dataEntry);
copy.CopyStream.Write(raw, 0, raw.Length);
dataEntry = "";
}
copy.End();
However, nothing happens when this code compiles. And when I look at the log
files I get the following errors.
2014-06-24 13:22:58 EDT CONTEXT: COPY 2009info.adt, line 1
2014-06-24 13:22:58 EDT STATEMENT: COPY "2009info.adt" FROM STDIN
2014-06-24 13:22:58 EDT ERROR: unexpected EOF on client connection with an
open transaction
2014-06-24 13:22:58 EDT CONTEXT: COPY 2009info.adt, line 1
2014-06-24 13:22:58 EDT STATEMENT: COPY "2009info.adt" FROM STDIN
2014-06-24 13:22:58 EDT LOG: could not send data to client: No connection
could be made because the target machine actively refused it.
Anyone have any ideas why this is happening?
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Error-When-Trying-to-Use-Npgsql-to-COPY-into-a-PostgreSQL-Database-tp5808954.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On 06/24/2014 10:47 AM, Altec103 wrote:
> I am currently in the process of using an ODBC Connection and a Npgsql
> Connection to transfer a database from a Sybase/Advantage platform to a
> PostgreSQL platform using C# and the .NET framework.
>
> Below is the code I have come up with to transfer the data itself...
>
> NpgsqlCommand copyCommand = new NpgsqlCommand("COPY \"2009info.adt\" FROM
> STDIN", connectionTest);
>
> string query = "SELECT * FROM \"2009info.adt\"";
> OdbcCommand test = new OdbcCommand(query, myConnection);
> string dataEntry = "";
>
> NpgsqlCopyIn copy = new NpgsqlCopyIn(copyCommand,
> connectionTest);
>
> copy.Start();
>
> OdbcDataReader reader = test.ExecuteReader();
>
> int rowCount = reader.FieldCount;
>
> while (reader.Read())
> {
>
>
> for (int i = 0; i < rowCount; i++)
> {
> dataEntry = dataEntry + reader[i].ToString() + "|";
> }
>
> dataEntry = dataEntry.Trim().Substring(0, dataEntry.Length -
> 1);
> dataEntry = dataEntry.Replace("\r",
> string.Empty).Replace("\n", string.Empty);
>
> var raw = Encoding.UTF8.GetBytes(dataEntry);
> copy.CopyStream.Write(raw, 0, raw.Length);
>
> dataEntry = "";
> }
>
> copy.End();
>
> However, nothing happens when this code compiles. And when I look at the log
> files I get the following errors.
>
> 2014-06-24 13:22:58 EDT CONTEXT: COPY 2009info.adt, line 1
> 2014-06-24 13:22:58 EDT STATEMENT: COPY "2009info.adt" FROM STDIN
> 2014-06-24 13:22:58 EDT ERROR: unexpected EOF on client connection with an
> open transaction
> 2014-06-24 13:22:58 EDT CONTEXT: COPY 2009info.adt, line 1
> 2014-06-24 13:22:58 EDT STATEMENT: COPY "2009info.adt" FROM STDIN
> 2014-06-24 13:22:58 EDT LOG: could not send data to client: No connection
> could be made because the target machine actively refused it.
>
> Anyone have any ideas why this is happening?
AFAICT this:
COPY \"2009info.adt\" FROM STDIN"
Is 2009info.adt supposed to be a table name or a file?
It is in the table position in the command.
If it is a file you cannot COPY from both a file and STDIN at the same
time.
Otherwise where is the data for STDIN coming from?
For more information see here:
http://www.postgresql.org/docs/9.3/interactive/sql-copy.html
>
>
>
>
>
> --
> View this message in context:
http://postgresql.1045698.n5.nabble.com/Error-When-Trying-to-Use-Npgsql-to-COPY-into-a-PostgreSQL-Database-tp5808954.html
> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
>
>
--
Adrian Klaver
adrian.klaver@aklaver.com
Adrian, I actually figured it out. It was a really silly mistake - basically I was testing something earlier and edited my ODBC connection string. I was actually connecting to the wrong database and it was an empty database, so ODBCDataReader was trying to pull data from an empty table. Anyways, this is fixed. However, I have a new problem. I occasionally get an ERROR: 22021: invalid byte sequence for encoding "UTF8": 0x92 when using the COPY query and this stops the whole process. It occurs here in my code... var raw = Encoding.UTF8.GetBytes(string.Concat(dataEntry, "\n")); copy.CopyStream.Write(raw, 0, raw.Length); dataEntry is a string. I have tried a few things to try and remove any non-UTF8 characters from the string, but it has failed so far. Any ideas? -- View this message in context: http://postgresql.1045698.n5.nabble.com/Error-When-Trying-to-Use-Npgsql-to-COPY-into-a-PostgreSQL-Database-tp5808954p5808982.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On 06/24/2014 01:37 PM, Altec103 wrote: > Adrian, I actually figured it out. It was a really silly mistake - basically > I was testing something earlier and edited my ODBC connection string. I was > actually connecting to the wrong database and it was an empty database, so > ODBCDataReader was trying to pull data from an empty table. Anyways, this is > fixed. However, I have a new problem. > > I occasionally get an ERROR: 22021: invalid byte sequence for encoding > "UTF8": 0x92 when using the COPY query and this stops the whole process. It > occurs here in my code... > > var raw = Encoding.UTF8.GetBytes(string.Concat(dataEntry, "\n")); > copy.CopyStream.Write(raw, 0, raw.Length); > > dataEntry is a string. I have tried a few things to try and remove any > non-UTF8 characters from the string, but it has failed so far. Any ideas? Look at this page: http://www.postgresql.org/docs/9.3/interactive/multibyte.html Postgres recognizes and can convert quite a few character sets. If you know what character set you are working with you can use SET CLIENT_ENCODING. See bottom of above page for examples. > > > -- Adrian Klaver adrian.klaver@aklaver.com
On 06/24/2014 01:37 PM, Altec103 wrote: > Adrian, I actually figured it out. It was a really silly mistake - basically > I was testing something earlier and edited my ODBC connection string. I was > actually connecting to the wrong database and it was an empty database, so > ODBCDataReader was trying to pull data from an empty table. Anyways, this is > fixed. However, I have a new problem. > > I occasionally get an ERROR: 22021: invalid byte sequence for encoding > "UTF8": 0x92 when using the COPY query and this stops the whole process. It > occurs here in my code... > > var raw = Encoding.UTF8.GetBytes(string.Concat(dataEntry, "\n")); > copy.CopyStream.Write(raw, 0, raw.Length); > > dataEntry is a string. I have tried a few things to try and remove any > non-UTF8 characters from the string, but it has failed so far. Any ideas? > > Some further digging found this: https://github.com/npgsql/Npgsql/issues/124 Seems to address the same issue. -- Adrian Klaver adrian.klaver@aklaver.com
On 06/24/2014 01:37 PM, Altec103 wrote: > Adrian, I actually figured it out. It was a really silly mistake - basically > I was testing something earlier and edited my ODBC connection string. I was > actually connecting to the wrong database and it was an empty database, so > ODBCDataReader was trying to pull data from an empty table. Anyways, this is > fixed. However, I have a new problem. > > I occasionally get an ERROR: 22021: invalid byte sequence for encoding > "UTF8": 0x92 when using the COPY query and this stops the whole process. It > occurs here in my code... > > var raw = Encoding.UTF8.GetBytes(string.Concat(dataEntry, "\n")); > copy.CopyStream.Write(raw, 0, raw.Length); > > dataEntry is a string. I have tried a few things to try and remove any > non-UTF8 characters from the string, but it has failed so far. Any ideas? > A more on point example: http://npgsql.projects.pgfoundry.org/docs/manual/UserManual.html Fast bulk data copy into a table The second example show how to deal with different client/server encodings. > > > -- > View this message in context: http://postgresql.1045698.n5.nabble.com/Error-When-Trying-to-Use-Npgsql-to-COPY-into-a-PostgreSQL-Database-tp5808954p5808982.html > Sent from the PostgreSQL - general mailing list archive at Nabble.com. > > -- Adrian Klaver adrian.klaver@aklaver.com