Post 更新为什么出错？

qqqqq

原处为：打开一个表编历，取出一个值赋予另一个表，嵌套中更新成功，但提示出错。
所以试着用一个表试验，多个更新，问题一样。
数据库控件为ZEOS7.0，库为SQLITE3.
（此问题己解决，我现将无用的信息删除，留下有用的帮助后来人）
   with zquery1 do
   begin
    if RecordCount>0 then
    begin
      while not eof do
      begin
        edit ;
        FieldByName('A0001').Value:='3';
        Post ;
        //ApplyUpdates ;
        next;
      end;
    end;
   end;
              

qqqqq

将后面的总结提到前面，后面是学习的过程。

作一下总结：
在用ZConnection1连接数据库、ZQuery1打开表时，EDIT...POST时提示出错，百思不得其解，探索再三，原来是ZEOS7.0.0 beta的BUG，换装6.6后，不再提示出错，但对ZEOS使用留下了阴影，遂终定就用LA自带的 SQLTransaction1（ TSQLTransaction）、 ZConnection1（ TZConnection）、 ZQuery1（TZQuery）组合，结果发现对个别表全部更新，再检查发现没有主键，设置主键后完全正常。

qqqqq

总遇到奇怪的问题.在
http://zeos.firmos.at/viewtopic.php?t=3449找到,说ZEOS7.0的BUG,原文
If you write well, then there are no errors:
Q_Name.Edit;
Q_Name.FieldByName('PR_Time').AsString := TimeToStr(Time);
Q_Name.Post;

Q_Name.Edit;
Q_Name.FieldByName('PR_Time').AsString := TimeToStr(Time);
Q_Name.Post;

ZEOS 6.6.6-stable - no errors.

Еried at the last updated ZEOS 7.0.0

qqqqq

卸载7.0.安装6.6,是不出提示了,但又奇怪了,EDIT ....POST,不是改一条记录吧,怎么全部记录都改了?继续探索.

qqqqq

（此栏信息无用）

qqqqq

将看到的解释贴下http://www.lazarus.freepascal.org/index.php/topic,19222.0.html?PHPSESSID=29129d460f1495d6561b433cd65f5b05

Re: Problem with Zeos 7 beta
« Reply #2 on: December 14, 2012, 11:42:58 pm »The reason is quiet simple:

Zeos is able to determine default-values, not-null fields etc..

Now you use some joins with some more tybles and each check of metainformations will fail. Zeos is able to read the metainformations of one table but joins can be very complex and Zeos determines the Field-Names,Types from ResultSet informations the Drivers do return. but these informations do not tell Zeos if a field is Writeable or not...

So you won't be able to update a complex query in your custom way. You need the TZUpdateSQL Component for this case. Then it's on you to tell Zeos which field can be written and how.

Best regards, Michael

qqqqq

先贴过来SqlDBHowto
Program ConnectDB

var AConnection : TSQLConnection;

Procedure CreateConnection;
begin
  AConnection := TIBConnection.Create(nil);
  AConnection.Hostname := 'localhost';
  AConnection.DatabaseName := '/opt/firebird/examples/employee.fdb';
  AConnection.UserName := 'sysdba';
  AConnection.Password := 'masterkey';
end;

begin
  CreateConnection;
  AConnection.Open;
  if Aconnection.Connected then
    writeln('Succesful connect!')
  else
    writeln('This is not possible, because if the connection failed, ' +
            'an exception should be raised, so this code would not ' +
            'be executed');
  AConnection.Close;
  AConnection.Free;
end.

Program EditData;

var
  AConnection : TSQLConnection;
  ATransaction : TSQLTransaction;
  Query : TSQLQuery;

begin
  CreateConnection;
  CreateTransaction;
  AConnection.Transaction := ATransaction;
  Query := GetQuery;
  Query.SQL.Text := 'select * from tblNames';
  Query.Open;
  Query.Edit;
  Query.FieldByName('NAME').AsString := 'Edited name';
  Query.Post;
  Query.UpdateMode := upWhereAll;
  Query.ApplyUpdates;
  AConnection.Transaction.Commit;
  Query.Free;
  ATransaction.Free;
  AConnection.Free;
end.
上面无非是修改的用法，存贴留念。

qqqqq

To send a change to the database server, Sqldb must assemble an update query. To assemble the query, three things are needed:

The name of the table
The table name is retrieved from parsing the select query, although this doesn't always work.
UPDATE or INSERT clause
These contain the fields that must be changed.
WHERE clause
This contains the fields that determine which records should be changed.
Every field (each TField in Fields) has a ProviderFlags property. Only fields with pfInUpdate in ProviderFlags will be used in the update or insert cluase of a query. By default all fields have pfInUpdate set in their ProviderFlags property.

Which fields are used in the WHERE clause depends on the UpdateMode property of the query and the ProviderFlags property of the fields. Fields with pfInkey in their ProviderFlags are always used in the WHERE clause. A field will have the pfInKey flag set automatically if the field is part of the primary key of the table and 'TSQLQuery.UsePrimaryKeyAsKey' returns 'True'.

The default value for UpdateMode of the query is upWhereKeyOnly. In this update mode only fields with pfInkey in their ProviderFlags property are used in the WHERE clause. If none of the fields have their pfInKey flag set, then no fields are available for the WHERE clause and the error message from the beginning of this section will be returned. You can solve the issue by:

Adding a primary key to the table and set TSQLQuery.UsePrimaryKeyAsKey to 'True', or
Setting the pfInkey flag for one or more fields in code.
The UpdateMode property knows two more possible values. 'upWhereAll' can be used to add all fields with the 'pfInWhere' flag set to the WHERE clause. By default all fields have this flag set. 'upWhereChanged' can be used to add only those fields that have the 'pfInWhere' flag set and that are changed in the current record.

qqqqq

Applying changes to the underlying datafile

To use the ApplyUpdates function, the dataset must contain at least one field that fills the requirements for a Primary Key (values must be UNIQUE and not NULL)

It's possible to do that in two ways:

Set PrimaryKey property to the name of a Primary Key field
Add an AutoInc field (This is easier since the TSqliteDataSet automatically handles it as a Primary Key) (ftAutoInc: an autoincrementing integer field).

If one of the two conditions is set then just call
大致翻译如下：
要将编辑变化保存到数据库中，表中必须有一个主键（非空且唯一）。
有两个方法：
一是设置其中一个字段为主键。
二是增加一个自增字段。

bugxiong

自问自答，互相学习
社区有你更精彩