SCM

[#1010714] using 'DbType.Binary' instead of 'NpgsqlTypes.NpgsqlDbType.Bytea' for updating bytea column fails

View Trackers | Bugs | Download .csv | Monitor

Date:
2009-10-07 10:09
Priority:
3
State:
Closed
Submitted by:
Aleksandar Marković (alemarko)
Assigned to:
Nobody (None)
Npgsql Version:
2.0
Category:
Group:
Resolution:
Accepted
Summary:
using 'DbType.Binary' instead of 'NpgsqlTypes.NpgsqlDbType.Bytea' for updating bytea column fails

Detailed description
My code is based on example from your documentation page and it's not working.
C:\....\Npgsql2.0.6-bin-ms.net3.5sp1\docs\UserManual.html


Your example:
>>
NpgsqlCommand command = new NpgsqlCommand("insert into tableBytea(field_bytea) values(:bytesData)", conn);
NpgsqlParameter param = new NpgsqlParameter(":bytesData", DbType.Binary);
param.Value = bytes;
>>


My code:
>>
NpgsqlCommand cmd = new NpgsqlCommand(
"update test1 set field_bytea = :field_bytea where id = 1");
cmd.Parameters.Add(":field_bytea", System.Data.DbType.Binary).Value = imageBytes;
>>


This is the exception thrown:

System.InvalidCastException: Can't cast System.Data.DbType into any valid DbType.
at Npgsql.NpgsqlParameter..ctor(String parameterName, Object value)
at Npgsql.NpgsqlParameterCollection.Add(String parameterName, Object value)


Only way to make this work is by using 'NpgsqlTypes.NpgsqlDbType.Bytea' instead of 'System.Data.DbType.Binary'.

Followup

Message
Date: 2009-12-19 15:13
Sender: Francisco Figueiredo jr.


Hi!

I added a method called AddWithValue just like SqlParameterCollection did in order to avoid the ambiguity of this type.

Also, you should achieve the same thing by using the Add method, or better, the AddWithValue method, without needing to specify the dbtype. Npgsql can and should figure it out by itself.

So, you code could be just:
cmd.Parameters.Add(":field_bytea", bytes);

I just added this modification of AddWithValue and also created a test case to verify that Npgsql indeed recognizes correctly the dbtype when passing only the name and bytes value.

Please, let me know what do you think.

I hope it helps.
Date: 2009-10-12 07:09
Sender: Aleksandar Marković

Hi, Gildas.
I was trying too hard to shorten the code :-)
I'll just decompose the calls like you suggested.
Thanks for your help,
Aleksandar
Date: 2009-10-08 23:11
Sender: Gildas

It cames beacause you use :
cmd.Parameters.Add(":field_bytea", System.Data.DbType.Binary).Value = bytes;

instead of decomposing like :
NpgsqlParameter param = new NpgsqlParameter(":bytesData", DbType.Binary);
param.Value = bytes;

So in this case the NpgsqlParameter constructor used is the
NpgsqlParameter(String parameterName, object value) instead of this one NpgsqlParameter(String parameterName, DbType parameterType)

Two solutions are possible to resolve it, i have posted them here http://pgfoundry.org/tracker/index.php?func=detail&aid=1010715&group_id=1000140&atid=592

Attached Files:

Changes:

Field Old Value Date By
close_date2009-12-19 15:132009-12-19 15:13fxjr
status_idOpen2009-10-12 07:09alemarko
ResolutionNone2009-10-12 07:09alemarko
close_date2009-10-12 07:092009-10-12 07:09alemarko
Powered By FusionForge