bytea fields treated as strings?

[bozr]

Hi,

First of all, thanks a bunch for your efforts on dotConnect for PostgreSQL!
It really improves a lot on each release.

However, I'm running into trouble when I configure my PostgreSQL server to use the "standard_conforming_strings = on" setting. It seems that when I insert data into a bytea field using a DbParameter of type DbType.Binary this data is handled as if it was a string, e.g. backslashes are automatically escaped. This kind of defeats the purpose of binary fields, right?

I'm using dotConnect for PostgreSQL version 4.95.190.

Regards,
Ruud

[Shalex]

Thank you for your kind words.

I have tried to reproduce the wrong behaviour of dotConnect for PostgreSQL v 4.95.190 with enabled "standard_conforming_strings = on" setting at PostgreSQL server v 9.0 using the sample from the description of the PgSqlBlob class: http://www.devart.com/dotconnect/postgr ... lBlob.html. The corresponding script (we will add it to our documentation):

Code: Select all

CREATE TABLE "Pictures"
(
  "ID" integer NOT NULL,
  "PicName" character varying,
  "Picture" bytea,
  CONSTRAINT "Pictures_pkey" PRIMARY KEY ("ID")
);

It works as expected. Tell us how we should modify this sample or send us your small test project with the corresponding DDL/DML script to reproduce the problem in our environment. What PostgreSQL server version are you working with?

[bozr]

My test program looks like this:

Code: Select all

using System;
using System.Data;
using System.Data.Common;
using System.Linq;

namespace ProveDotConnectBug
{
    class Program
    {
        static void Main(string[] args)
        {
            byte[] data = { 0x01, 0x02, 0x03, 0x04, 0x05 };

            DbProviderFactory factory = DbProviderFactories.GetFactory(@"Devart.Data.PostgreSql");
            DbConnection connection = factory.CreateConnection();
            connection.ConnectionString = @"Host=localhost;Database=mydb;User Id=myuser;Password=secret;Protocol=2;Unicode=true;";
            connection.Open();

            DbCommand insert = connection.CreateCommand();
            insert.CommandText = @"myfunction";
            insert.CommandType = CommandType.StoredProcedure;
            
            DbParameter parameter = insert.CreateParameter();
            parameter.ParameterName = @"arg";
            parameter.DbType = DbType.Binary;
            parameter.Value = data;
            insert.Parameters.Add(parameter);

            insert.ExecuteNonQuery();

            DbCommand select = connection.CreateCommand();
            select.CommandText = @"SELECT data FROM mytable;";
            byte[] result = select.ExecuteScalar() as byte[];

            if (!data.SequenceEqual(result))
                Console.WriteLine("Not OK!");
            else
                Console.WriteLine("OK!");
        }
    }
}

and the DDL looks like this:

Code: Select all

CREATE TABLE mytable(data bytea);

CREATE FUNCTION myfunction(arg bytea)
  RETURNS void AS $$
  BEGIN
    INSERT INTO mytable(data) VALUES(arg);
  END;
$$ LANGUAGE plpgsql VOLATILE;

When standard_conforming_strings = off "\001\002\003\004\005" is stored in mytable. In the case standard_conforming_strings is switched on I get "\001\002\003\004\005". I'm using PostgreSQL version 8.4.7.

Regards,
Ruud

[Shalex]

You are using Protocol=2 (text protocol) in your connection string. We recommend you using Protocol=3 (binary protocol, it is set by default for PostgreSQL servers 7.4 and higher). I have tried the Protocol=3; connection string parameter with your code - it works OK for both standard_conforming_strings = on/off. Please try Protocol=3; in your environment and notify us about the results.

[bozr]

I changed my application to use protocol v3 and the problem doesn't exist anymore.

Thanks,
Ruud