command builder generate update query missing bytea column

command builder generate update query missing bytea column

Postby jhkim » Tue 14 Dec 2010 15:11

first. sorry for poor english.

I using 'dotConnect for PostgreSQL 5.00 Professional' on C# application.
but, generated by PgSqlCommandBuilder query raise exception on table using bytea column.

here is information.

DB schema:

Code: Select all

Table "public.web_site_defacement"
Column          | Type    | Modifiers
virtual_host_id | integer | not null
uri             | bytea   | not null
pattern         | bytea   |
replace_page    | bytea   |
code_page       | integer |
match_full      | bytea   |
group_id        | integer | not null default -1
"web_site_defacement_pkey" PRIMARY KEY, btree (virtual_host_id, uri, group_id)

Using C# code.

Code: Select all
this.command = new Devart.Data.PostgreSql.PgSqlCommand();

this.command.CommandText = "select uri, virtual_host_id, group_id, pattern,replace_page, code_page, match_full from test where group_id = 86";

this.command.Name = "command";
this.command.Owner = this;

adt = new PgSqlDataAdapter(this.command.CommandText, connection);

this.pgSqlCommandBuilder = new Devart.Data.PostgreSql.PgSqlCommandBuilder(adt);

this.pgSqlCommandBuilder.Quoted = true;
this.pgSqlCommandBuilder.UpdatingFields = "";

pgSqlCommandBuilder.ConflictOption = ConflictOption.OverwriteChanges;
pgSqlCommandBuilder.KeyFields = "";

adt.Fill(myDataSet, "web");

myDataSet.EnforceConstraints = true;

DataTable table = myDataSet.Tables["web"];
DataRow[] rows = table.Select();

rows[0]["uri"] = "modifyName";
rows[0]["pattern"] = "modifyValueaaaaaaaaaaaaaaa";
rows[0]["code_page"] = 333;
rows[0]["group_id"] = 86;

int result = adt.Update(myDataSet, "web");

command builder generated where clause missing bytea(uri) column on primary key.
so query raise duplicate error.

here is 'pgSqlCommandBuilder.GetUpdateCommand().CommandText'
Code: Select all
"UPDATE \"sphere\".\"public\".\"test\" SET \"uri\" = :p1, \"virtual_host_id\" = :p2, \"group_id\" = :p3, \"pattern\" = :p4, \"replace_page\" = :p5, \"code_page\" = :p6, \"match_full\" = :p7 WHERE (((:p8 = 1 AND \"uri\" IS NULL) OR (\"uri\" = :p9)) AND ((:p10 = 1 AND \"virtual_host_id\" IS NULL) OR (\"virtual_host_id\" = :p11)) AND ((:p12 = 1 AND \"group_id\" IS NULL) OR (\"group_id\" = :p13)) AND ((:p14 = 1 AND \"match_full\" IS NULL) OR (\"match_full\" = :p15)))"

same problem on insert/pdate/delete query generated by PgSqlCommandBuilder.

how do I resolve this problem?
would I get more information?


Posts: 1
Joined: Tue 14 Dec 2010 14:46

Postby StanislavK » Wed 15 Dec 2010 18:04

Thank you for your report, we have reproduced the problem. We will investigate it and inform you about the results.
Devart Team
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Postby StanislavK » Mon 20 Dec 2010 14:42

We've analyzed the situation, this is the designed behaviour. The thing is that bytea columns are marked as long (i.e., the corresponding SchemaTableColumn.IsLong property is true), and the DbCommandBuilder class does not include long fields into the WHERE clause for performance reasons.

A possible workaround is to explicitly set the update command for this table.
Devart Team
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Return to dotConnect for PostgreSQL