Board index » delphi » TADOTable.Post throws "Row cannot be located for updating"

2007-03-01 06:32:13 PM
delphi185
I am trying to store binary data in a BLOB field on a MySQL datasource.
These MySQL blobs appear as TVarBytesField, and not as TBlobField in VCL. I
connect to the datasource using MyODBC 3.51. The data I want to store is
passed to a procedure in a stream. Below is the code, written in Delphi 7.
What happens is that the call adoTbl.Post throws the exception EOleException
: "Row cannot be located for updating. Some values may have been changed
since it was last read.". I have tried setting CursorType to dtDynamic, but
that doesn't make any difference. Any clue what might be wrong? If there is
a much better scheme for achieving what I want to do, I am of course
interested as well.
Cheers,
Guy
procedure SetBinaryValue(nRecID: Integer; pStream: TStream);
type
// Upper bound not relevant
LTCharArr = array [0 .. 2] of Char;
var
adoTbl: TADOTable;
fld: TField;
buffer: ^ LTCharArr;
sze: Integer;
pw: ^ Word;
begin
adoTbl := TADOTable.Create(Application);
with adoTbl do
begin
TableName := 'Tbl_Test';
// Actual names in ConnectionString replaced by dummies for posting in
public newsgroup
ConnectionString := 'Driver={mySQL ODBC 3.51
Driver};Server=XXX;Port=3306;Database=YYY;UID=ZZZ;Pwd=PPP;';
try
Open;
// Fld_ID is an integer field set as primary key
if Locate('Fld_ID', nRecID, [])
then
begin
// Fld_BinData is a MySQL BLOB field
fld := FieldByName('Fld_BinData');
Edit;
if fld is TVarBytesField
then
begin
try
if (pStream = nil) or (pStream.Size = 0)
then
sze := 2
else
sze := pStream.Size + 2;
GetMem(buffer, sze);
ZeroMemory(buffer, sze);
pw := Pointer(buffer);
// First 2 bytes of TVarBytesField data contain data length
pw ^ := sze - 2;
if sze>2
then
pStream.Read(buffer[2], sze - 2);
fld.SetData(buffer);
FreeMem(buffer, sze)
finally
adoTbl.Post
end
end
end
finally
Close
end
end;
adoTbl.Free
end { SetBinaryValue };
 

T 55 writes:
<snip text>
see thread "TADOTable exception when post second time" on the
03/08/2005.
--
Best regards :-)
Guillem Vicens Meier
Dep. Informática Green Service S.A.
www.clubgreenoasis.com
Contribute to the Indy Docs project: docs.indyproject.org
In order to contact me remove the -nospam
 

"Guillem" <XXXX@XXXXX.COM>writes
- Change cursor location to server side. This is apparently not supported by
the database or driver.
- Set adoTbl.Properties['Update Criteria'].Value := 0. This doesn't make a
difference, I still get the same exception.
Meanwhile I noticed that I only get the exception once the blob field is
filled in. When its value is still NULL, I don't get the exception. But
setting it to NULL and then setting the real data, does not work. I still
get the exception.
Guy
 

On Thu, 1 Mar 2007 11:32:13 +0100, "T 55" <XXXX@XXXXX.COM>writes:
statement and you have to.
Let's say you have a table:
Table: SalesGuys
SalesID autoinc
SalesName text/string
and you use this table in a grid where you writes:
select SalesName from SalesGuys order by SalesGuys
If you allow editing and attempt to make a correction/edit, you get
the error you describe.
solution
change your SQL to include the unique record id:
select SalesID, SalesName from SalesGuys order by SalesGuys.
If your tables don't have unique record identifiers, even if you never
plan to use them, always include them.
 

I have the impression that the problem might be with the MySQL ODBC client.
I tried the same using TADOQuery instead of TADOTable. In this case VCL sees
the MySQL BLOB as a TBlobField and not as a TVarBytes field. Advantage is
that I can create a blob stream and use that to do the update. This works
perfectly when tested on a SQL Server database, but not on MySQL. I get the
same error "Row cannot be located for updating. Some values may have been
changed since it was last read". Setting the data set's Properties['Update
Criteria'].value to adCriteriaKey doesn't change anything. Back to square 1.
Guy
 

I finally got it to work! This solves the problem for me:
Add "Option=2;" to the ADO connect string (that is, 2 or-ed with possible
other Option values that might be of interest to you). The explanation of
Option=2 is: "The client can not handle that MySQL returns the true value of
affected rows. If this flag is set then MySQL returns 'found rows' instead.
One must have MySQL 3.21.14 or newer to get this to work."
Guy