Hi,
It is not short of time to use postgresql for me, but recently got a problem about record update as the title says.
I have encountered the same error before and all been resolved, but this time it is a bit specific, the Internet couldn't help me to confirm the problem.
I record the environment and the process of validation as follows.
Hope to get help.
1.Enviroment:
.PostgreSQL:9.3
.PostgreSQL ANSI 8.04.02.00
.client_encoding: GBK
2.Test Table and Data:
Create t_test_001 and insert 4 records for test.
The script of create as follow:
CREATE TABLE t_test_001
(
id integer NOT NULL,
name character varying(4),
CONSTRAINT pk_test_001 PRIMARY KEY (id)
);
Insert data.
(1)insert into t_test_001(id,name) values(1,'大'); --- without trailling blank char_length=1
(2)insert into t_test_001(id,name) values(2,'大 '); --- with 1 trailling blank char_length=2
(3)insert into t_test_001(id,name) values(3,'大 '); --- with 2 trailling blank char_length=3
(4)insert into t_test_001(id,name) values(4,'大 '); --- with 3 trailling blank char_length=4
notice the length of column id is 4.
3.Testing
Using ADO Recordset API such as Open,PutCollet,Update to update a specific record.
The following code uses an ado wrapped class, but can be corresponding to the original method.
string sql = "select name from t_test_001 where id=1"; ///< id=1 locate the record want to update,replace 1 with the one want to update.
string name = "a";
pRecordset = pdbor->Query(sql.c_str(),adOpenStatic,adLockOptimistic);
if (!pRecordset->PutCollect("name",name.c_str()))
return -1;
if (!pRecordset->Update())
return -1;
change id in code above,execute for update every record in sequence.
the results showed that it is ok for records with id=1,2,3,but the record with id=4 raise error as follow:
无法为更新定位行。一些值可能已在最后一次读取后已更改.
(Row cannot be located for updating. some values may have been changed since it was last read.)
i found serveral factors to bring this error:
.varchar column contains Chinese char
.with trailling blank
.length of data is width of the column
how to explain and resolve it?
4.Additions
.when i replace with PostgreSQL Unicode Driver,it's ok.
.i would not set Update Criteria to adCriteriaKey to work around this,because it's not sure that all query always contains a primary key column.
Thank you,
whereh
