I have been using C# to write a concrete provider implementation for our product for different databases. W/out getting into details, one of the columns is of byte array type (bytea in postgres - due to the preferences bytea was chosen over blob). The only problem, is that it does not return same value that was inserted. When I insert Int32 ("0") I get 8 [92 and 8x 48] (instead of [0,0,0,0]). I need a performance wise solution, that will return pure bytes I have inserted, instead of ASCII representation of value "0" on 8 bytes.
I am using Npgsql to retrive data. If someone knows solution for c# I will be happy to learn it as well.
Edit: Postgres 9.0, .Net 3.5
Simplification
Command query: - inside it only does an insert statment
select InsertOrUpdateEntry(:nodeId, :timeStamp, :data)
Data parameter:
byte [] value = BitConverter.GetBytes((int)someValue);
Parameter is assigned as below
command.Parameters.Add(new NpgsqlParameter("data", NpgsqlDbType.Bytea)
{ Value = value });
Select statments:
select * from Entries
Same byte array I have entered, I want to get back. I would really appreciate your help.
Input: 0 0 0 0
Current Output: 92 48 48 48 48 48 48 48 48
Expected Output: 0 0 0 0
In Npgsql there is NpgsqlDataReader
class to retrieve inserted rows, e.g:
NpgsqlConnection conn = new NpgsqlConnection(connStr);
conn.Open();
NpgsqlCommand insertCmd =
new NpgsqlCommand("INSERT INTO binaryData (data) VALUES(:dataParam)", conn);
NpgsqlParameter param = new NpgsqlParameter("dataParam", NpgsqlDbType.Bytea);
byte[] inputBytes = BitConverter.GetBytes((int)0);
Console.Write("Input:");
foreach (byte b in inputBytes)
Console.Write(" {0}", b);
Console.WriteLine();
param.Value = inputBytes;
insertCmd.Parameters.Add(param);
insertCmd.ExecuteNonQuery();
NpgsqlCommand selectCmd = new NpgsqlCommand("SELECT data FROM binaryData", conn);
NpgsqlDataReader dr = selectCmd.ExecuteReader();
if(dr.Read())
{
Console.Write("Output:");
byte[] result = (byte[])dr[0];
foreach(byte b in result)
Console.Write(" {0}", b);
Console.WriteLine();
}
conn.Close();
Result from C# app:
Input: 0 0 0 0
Output: 0 0 0 0
Result from pgAdmin:
"\000\000\000\000"
EDIT:
I found explanation why you getting:
92 48 48 48 48 48 48 48 48
I checked my code with previous version Npgsql2.0.10-bin-ms.net3.5sp1.zip
and get above result (of course pgAdmin returns \000\000\000\000
), so I think that best what you can do is to use another version without this bug.
ANSWER: User higher version of Npgsql than 2.0.10