command builder generate update query missing bytea column

[jhkim]

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.

[StanislavK]

Thank you for your report, we have reproduced the problem. We will investigate it and inform you about the results.

[StanislavK]

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.