Devart Forums

Posted: **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
Indexes:
"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?

thanks.

regards,
jhkim.

Posted: **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.

Posted: **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 Forums

command builder generate update query missing bytea column

command builder generate update query missing bytea column