[AnyDac][DApt]-400 But my tables does have a PK

Question

[anydac][DApt]-400.Fetch command fetched[0] instead of [1] record, Possible reasons:update table does not have PK or row identifier,record has been changed/deleted by another user,

when executing

SingleTestRunADQuery.Append();
SingleTestRunADQuery.FieldByName('run_id').Value := StartRecordingButton.Tag;
SingleTestRunADQuery.FieldByName('ph_value').Value := FloatToStr(ph_reading);
SingleTestRunADQuery.FieldByName('conductivity_value').Value := conductivity_reading;
SingleTestRunADQuery.FieldByName('cod_value').Value := cod_reading;
SingleTestRunADQuery.Post();

on

mysql> describe measurements;
+------------------------+-----------+------+-----+-------------------+-------+
| Field                  | Type      | Null | Key | Default           | Extra |
+------------------------+-----------+------+-----+-------------------+-------+
| run_id                 | int(11)   | NO   | MUL | NULL              |       |
| measurement_time_stamp | timestamp | NO   | PRI | CURRENT_TIMESTAMP |       |
| ph                     | float     | NO   |     | NULL              |       |
| conductivity           | float     | NO   |     | NULL              |       |
| cod                    | float     | NO   |     | NULL              |       |
+------------------------+-----------+------+-----+-------------------+-------+
5 rows in set (0.03 sec)

as you can see, the table does have a PK. Also, the program is single-threaded and only one copy is running, so no one else is updating.

I set SingleTestRunADQuery.MasterFields=run_id and IndexFieldNames=run_id as that is the PK of table which holds a summary of all test runs. The second table hold the measurements taken during tests, with run_id giving all the measurements for one test run (I only added PK on tiemstamp to get rid of this error, but it didn't work and can be removed, I guess).

In case it helps, here's the master data source:

mysql> describe test_runs;
+------------------+-------------+------+-----+-------------------+----------------+
| Field            | Type        | Null | Key | Default           | Extra          |
+------------------+-------------+------+-----+-------------------+----------------+
| run_id           | int(11)     | NO   | PRI | NULL              | auto_increment |
| start_time_stamp | timestamp   | NO   |     | CURRENT_TIMESTAMP |                |
| end_time_stamp   | timestamp   | YES  |     | NULL              |                |
| description      | varchar(64) | YES  |     | NULL              |                |
+------------------+-------------+------+-----+-------------------+----------------+
4 rows in set (0.05 sec)

Any idea what's wrong?

[Update] @ mj2008 points out that some fields have different names. This is for historical reasons (I am still trying something out & don't want to change yet), hoever these are adapted by the query:

SELECT run_id,
       measurement_time_stamp, 
       ph as ph_value, 
       conductivity as conductivity_value, 
       cod as cod_value

FROM photo_catalytic.measurements

ORDER BY measurement_time_stamp DESC

The code has field names that don't match the table output. Fix that, then tell us if it still fails. — mj2008
oic, yes, but the query associates those names e.g SELECT ph AS ph_value - for historical reasons. Can that be causing it? — Mawg says reinstate Monica

DiGi DiGi · Accepted Answer · 2013-03-11T09:35:23

I'm not sure that is correct to have TIMESTAMP field as PRIMARY KEY. It will automatically change on every UPDATE.

[AnyDac][DApt]-400 But my tables does have a PK

2 Answers