delphi+MySQL问题

Question

主要代码：QueryDataset := TADODataSet.Create(nil);
with QueryDataset do
begin
    Active := False;
    AutoCalcFields := True;
    CommandType := cmdText;
    CommandTimeout := 30;
    CacheSize := 1;
    AutoCalcFields := True;
    CursorLocation := clUseClient;
    CursorType := ctKeyset;
    EnableBCD := True;
    Filtered := False;
    Prepared := False;
    LockType := ltOptimistic;
    MarshalOptions := moMarshalAll;
    StoreDefs := False;
    Tag := 0;
    connectionstring := '';
    try
      Connection := Conn;
    except
      on e:Exception do begin
        Result := False;
      end;
  CommandText := 'Select * from table order by idx';    
end;
while not QueryDataset.Eof do
begin
  Inc(NumberOfQueryResult);
  QueryDataset.Next();
end;
Freeandnil(QueryDataset);
如果table不做insert操作，没有问题，但如果insert 几条数据到table里面，
然后再运行以上程序，QueryDataset.Next();这里报错，好像AdoDataset指针不对了MySQL版本：
mysql> select @@version;
+---------------------+
| @@version           |
+---------------------+
| 5.0.81-community-nt |
+---------------------+
1 row in set (0.00 sec)没见过的毛病，维护老产品用的delphi6.0开发。

Answer 1

你 insert 时的代码是什么样？

Answer 2

QueryDataset.Next();这里报错：什么错误信息，表是否有主键

Answer 3

mysql> desc iv_alert;
+--------------------------+---------------------+------+-----+---------------------+-----
| Field                    | Type                | Null | Key | Default             | Extr
+--------------------------+---------------------+------+-----+---------------------+-----
| uuid                     | bigint(20)          | NO   | UNI | NULL                |
| state                    | smallint(6)         | YES  | MUL | NULL                |
| ForDelete            | char(1)             | YES  |     | NULL                |
| lastmodtime              | timestamp           | NO   |     | CURRENT_TIMESTAMP   |
| lastModUserRef           | char(32)            | YES  |     | NULL                |
| assignedUserRef          | char(32)            | YES  |     | NULL                |
| sensorId                 | int(11)             | NO   | PRI | 0                   |
| vsaId                    | int(11)             | YES  |     | -1                  |
| vidsId                   | int(11)             | YES  |     | NULL                |
| liId                     | int(11)             | YES  |     | -1                  |
| subscriberId1            | int(11)             | YES  |     | NULL                |
| subscriberId2            | int(11)             | YES  |     | NULL                |
| subscriberId3            | int(11)             | YES  |     | NULL                |
| subscriberId4            | int(11)             | YES  |     | NULL                |
| alertType                | smallint(6)         | NO   |     | NULL                |
| categoryId               | int(11)             | YES  |     | NULL                |
| subCategoryId            | int(11)             | YES  |     | NULL                |
| detectionMechanism       | int(11)             | YES  |     | NULL                |
| attackId                 | int(11)             | NO   |     | NULL                |
| creationTime             | timestamp           | NO   | MUL | 0000-00-00 00:00:00 |
| emsReceivedTime          | timestamp           | NO   |     | 0000-00-00 00:00:00 |
| severity                 | tinyint(4)          | NO   |     | NULL                |
| alertDuration            | int(11)             | YES  |     | NULL                |
| slotId                   | smallint(6)         | NO   |     | NULL                |
| portId                   | smallint(6)         | NO   |     | NULL                |
| alertCount               | int(11)             | YES  |     | NULL                |
| packetLogId              | bigint(20)          | YES  |     | NULL                |
| packetLogGrpId           | bigint(20)          | NO   |     | NULL                |
| packetLogSeq             | int(11)             | YES  |     | NULL                |
| lastByteReqStreamOffset  | int(11)             | YES  |     | NULL                |
| lastByteRespStreamOffset | int(11)             | YES  |     | NULL                |
| hasPreviousBuffer        | char(1)             | YES  |     | NULL                |
| signatureId              | smallint(6)         | YES  |     | NULL                |
| ivProtocolId             | int(11)             | YES  |     | NULL                |
| networkProtocolId        | smallint(6)         | YES  |     | NULL                |
| sourceIPAddr             | char(32)            | YES  |     | NULL                |
| sourcePort               | int(11)             | YES  |     | NULL                |
| targetIPAddr             | char(32)            | YES  |     | NULL                |
| targetPort               | int(11)             | YES  |     | NULL                |
| confidence               | tinyint(4)          | YES  |     | NULL                |
| protoQual1               | int(11)             | YES  |     | NULL                |
| protoQual2               | int(11)             | YES  |     | NULL                |
| protoParsingState        | int(11)             | YES  |     | NULL                |
| direction                | tinyint(4)          | YES  |     | NULL                |
| suppressedSigIds         | int(11)             | YES  |     | NULL                |
| nidId                    | int(11)             | YES  |     | NULL                |
| firstAlarmTime           | timestamp           | NO   |     | 0000-00-00 00:00:00 |
| accumulateTime           | int(11)             | YES  |     | NULL                |
| thresholdId              | int(11)             | YES  |     | NULL                |
| observedValue            | bigint(20)          | YES  |     | NULL                |
| thresholdValue           | int(11)             | YES  |     | NULL                |
| thresholdDuration        | int(11)             | YES  |     | NULL                |
| attackIdRef              | char(20)            | YES  |     | NULL                |
| resultSetValue           | int(11)             | YES  |     | NULL                |
| inlineDropAction         | int(11)             | YES  |     | NULL                |
| relevance                | char(1)             | YES  |     | NULL                |
| VLANId                   | int(11)             | YES  |     | NULL                |
| policyid                 | char(20)            | YES  |     | NULL                |
| hostIsolationState       | tinyint(4)          | NO   |     | NULL                |
| mpeForwardState          | tinyint(4)          | NO   |     | NULL                |
| mpeManagedStatus         | tinyint(4)          | YES  |     | NULL                |
| mpeActiontStatus         | tinyint(4)          | YES  |     | NULL                |
| mpeErrorcode             | tinyint(4)          | YES  |     | NULL                |
| sensorAlertId            | bigint(20)          | NO   |     | NULL                |
| sensorAlertUUID          | bigint(20)          | NO   | PRI | NULL                |
| sourceUserId             | int(11)             | YES  |     | NULL                |
| destinationUserId        | int(11)             | YES  |     | NULL                |
| sourceOSId               | int(11)             | YES  |     | NULL                |
| destinationOSId          | int(11)             | YES  |     | NULL                |
| sourceOSId1              | tinyint(3) unsigned | YES  |     | NULL                |
| sourceOSId2              | tinyint(3) unsigned | YES  |     | NULL                |
| sourceOSId3              | tinyint(3) unsigned | YES  |     | NULL                |
| sourceOSId4              | tinyint(3) unsigned | YES  |     | NULL                |
| destinationOSId1         | tinyint(3) unsigned | YES  |     | NULL                |
| destinationOSId2         | tinyint(3) unsigned | YES  |     | NULL                |
| destinationOSId3         | tinyint(3) unsigned | YES  |     | NULL                |
| destinationOSId4         | tinyint(3) unsigned | YES  |     | NULL                |
+--------------------------+---------------------+------+-----+---------------------+-----
77 rows in set (0.03 sec)
insert Code:insert into iv_alert(uuid,state,sensorid,alerttype,attackid,severity,slotid,portid,packetLogGrpId,hostIsolationState,mpeForwardState,sensorAlertId,sensorAlertUUId) values(5409654708970783742,1,1004,1,4202751,1,1,2,0,0,0,0,501)
联合主键：
sensorid
sensoralertid

Answer 4

QueryDataset.Next();本来应该有10条返回结果，但到第3条就跳出循环。

Answer 5

CursorType := ctDynamic
试试

Answer 6

你是单机还是网络？在插入记录后，再执行你上述代码出问题？
你在插入后，用ADOQUERY连接MYSQL，执行Select * from table order by idx，
用dbGRID显示
看看结果是多少条记录，

Answer 7

在插入记录后，再执行上述代码会出问题，
如果用MySQL Command 窗口直接查询，数目对头
mysql> select count(1) from iv_alert;
+----------+
| count(1) |
+----------+
|     1003 |
+----------+
1 row in set (0.00 sec)
第1003条是用上面insert插入的，
运行后到1002条跳出
看日志后几行：
1/21/2010 4:27:46 PM Before1000|5409654708970783719
1/21/2010 4:27:46 PM Before1001|5409654708970783720
1/21/2010 4:27:46 PM Before1002|5409654708970783721到1002行后就不去1003条，反复运行代码都是这个结果

Answer 8

有什么方法可以查看iv_alert这张表的详细情况？是不是标有什么属性设置不对？

Answer 9

重新编制一个程序，
运行完INSERT 后，用dbGRID显示 
看看结果是多少条记录，数据源为Select * from table order by idx，用ADOQUERY

Answer 10

如果用DBGrid 显示select * from iv_alert order by uuid报错：Data Provider or other service returned an E_FAIL status

Answer 11

代码是什么，奇怪，去掉ORDER BY 试试，
SELECT COUNT(*) FROM iv_alert，结果是多少，用DBGRID显示

Answer 12

the same problem and it's Solution
Posted by: Aleksej Timohin () 
Date: November 11, 2005 04:59AM
Hi all, 
i have similar bug in Delphi, using mysql-connector-odbc-3.51.12-win32 and MySQL 4.1.12. 
I receive "Data Provider or other service returned an E_FAIL status Error" while trying to get data from a "date"-typed field, which contain default date-value(0000-00-00). I've Fixed this, by changing default value to existing date, f.e. 1980-01-01... I also tried to change this field settings in mysql, by allowing to store NULL in this field, and making default value NULL..Edited 3 time(s). Last edit at 11/16/2005 04:12AM by Aleksej Timohin.
kao,SQL bug,日期类型字段问题，如果没有日期字段貌似就可以了
我的Driver 已经是：mysql-connector-odbc-3.51.27-win32

Answer 13

你下载MYSQL ODBC 5。X版本安装试试，看来问题开始没有讲清楚

Answer 14

http://dev.mysql.com/downloads/connector/odbc/

Answer 15

怎么都没有回复我也遇到了E_FAIL的问题，在mysql中执行操作可以返回正确结果集，但是通过delphi的ADO则无法返回结果，而是直接报E_FAIL错误！！！