Cannot locate record in delphi ADO query

Question

I can't locate any record in TADOQuery using PK. First, I was trying to use standard Locate method:

PPUQuery.Locate('ID', SpPlansQuery['PPONREC'], []);

It always returns False, but manual search (passing the whole query matching ID with given PPONREC which is really slow) finds the desired row. I tried using loPartialKey and switched CursorLocation of query to clUseServer, but it didn't help.

Next, I tried to filter my PPUQuery:

PPUQuery.Filter := 'ID = ' + VarToStr(SpPlansQuery['PPONREC']);
PPUQuery.Filtered := True;
PPUQuery.First;

But after that the PPUQuery.Eof is True and PPUQuery.RecordCount equals 0.

PPUQuery fields list:

object PPUQueryNUM: TFMTBCDField
  DisplayLabel = #1053#1086#1084#1077#1088
  FieldName = 'NUM'
  ReadOnly = True
  Precision = 38
  Size = 0
end
object PPUQueryINUM: TFMTBCDField
  DisplayLabel = #1053#1086#1084#1077#1088' '#1062#1052#1050
  FieldName = 'INUM'
  ReadOnly = True
  Precision = 38
  Size = 0
end
object PPUQueryONUM: TFMTBCDField
  DisplayLabel = #1055#1086#1088#1103#1076#1082#1086#1074#1099#1081' '#1085#1086#1084#1077#1088
  FieldName = 'ONUM'
  ReadOnly = True
  Precision = 38
  Size = 0
end
object PPUQueryDRAWING: TStringField
  DisplayLabel = #1053#1086#1084#1077#1088' '#1095#1077#1088#1090#1077#1078#1072
  FieldName = 'DRAWING'
  ReadOnly = True
  Size = 50
end
object PPUQueryWEIGHT: TFloatField
  DisplayLabel = #1042#1077#1089
  FieldName = 'WEIGHT'
  ReadOnly = True
end
object PPUQueryTITLE: TStringField
  DisplayLabel = #1047#1072#1075#1086#1083#1086#1074#1086#1082
  FieldName = 'TITLE'
  ReadOnly = True
  Size = 200
end
object PPUQueryPRODUCER: TFMTBCDField
  DisplayLabel = #1055#1088#1086#1080#1079#1074#1086#1076#1080#1090#1077#1083#1100
  FieldName = 'PRODUCER'
  ReadOnly = True
  Precision = 38
  Size = 0
end
object PPUQueryGRAPH: TStringField
  DisplayLabel = #1043#1088#1072#1092#1080#1082
  FieldName = 'GRAPH'
  ReadOnly = True
  Size = 200
end
object PPUQueryNOTE: TStringField
  DisplayLabel = #1055#1088#1080#1084#1077#1095#1072#1085#1080#1077
  FieldName = 'NOTE'
  ReadOnly = True
  Size = 1024
end
object PPUQueryUPDATED: TDateTimeField
  DisplayLabel = #1054#1073#1085#1086#1074#1083#1105#1085
  FieldName = 'UPDATED'
  ReadOnly = True
end
object PPUQueryRELDATE: TDateTimeField
  DisplayLabel = #1044#1072#1090#1072' '#1086#1082#1086#1085#1095#1072#1085#1080#1103' '#1080#1079#1075#1086#1090#1086#1074#1083#1077#1085#1080#1103
  FieldName = 'RELDATE'
  ReadOnly = True
end
object PPUQueryID: TFMTBCDField
  FieldName = 'ID'
  ReadOnly = True
  Visible = False
  Precision = 38
  Size = 0
end
object PPUQueryNUM_OF: TFMTBCDField
  FieldName = 'NUM_OF'
  ReadOnly = True
  Visible = False
  Precision = 38
  Size = 0
end
object PPUQueryORDER_ID: TFMTBCDField
  FieldName = 'ORDER_ID'
  Precision = 38
  Size = 0
end
object PPUQueryPLAN_ID: TFMTBCDField
  FieldName = 'PLAN_ID'
  Precision = 38
  Size = 0
end
object PPUQueryCNUM: TStringField
  FieldName = 'CNUM'
  Size = 50
end
object PPUQueryCADEP: TFMTBCDField
  FieldName = 'CADEP'
  Precision = 38
  Size = 0
end
object PPUQueryMATERIAL: TFMTBCDField
  FieldName = 'MATERIAL'
  Precision = 38
  Size = 0
end
object PPUQueryARTICLE: TFMTBCDField
  DisplayLabel = #1057#1090#1072#1090#1100#1103
  FieldName = 'ARTICLE'
  Precision = 38
  Size = 0
end
object PPUQueryDEPTH: TFloatField
  FieldName = 'DEPTH'
end
object PPUQueryPAINT: TFMTBCDField
  FieldName = 'PAINT'
  Precision = 38
  Size = 0
end
object PPUQueryBSS: TFMTBCDField
  FieldName = 'BSS'
  Precision = 38
  Size = 0
end
object PPUQueryQNUM: TFMTBCDField
  DisplayLabel = #1054#1095#1077#1088#1077#1076#1100
  FieldName = 'QNUM'
  Precision = 38
  Size = 0
end
object PPUQuerySERVICE: TFMTBCDField
  FieldName = 'SERVICE'
  Precision = 38
  Size = 0
end
object PPUQueryCONTINUE: TFMTBCDField
  FieldName = 'CONTINUE'
  Precision = 38
  Size = 0
end
object PPUQueryADVANCED: TFMTBCDField
  FieldName = 'ADVANCED'
  Precision = 38
  Size = 0
end
object PPUQueryMETIZ: TFMTBCDField
  FieldName = 'METIZ'
  Precision = 38
  Size = 0
end
object PPUQueryID_1: TFMTBCDField
  FieldName = 'ID_1'
  Precision = 38
  Size = 0
end
object PPUQueryTITLE_1: TStringField
  DisplayLabel = #1057#1090#1072#1090#1100#1103
  FieldName = 'TITLE_1'
end
object PPUQueryORD: TFMTBCDField
  FieldName = 'ORD'
  Precision = 38
  Size = 0
end
object PPUQueryID_2: TFMTBCDField
  FieldName = 'ID_2'
  Precision = 38
  Size = 0
end
object PPUQueryTITLE_2: TStringField
  DisplayLabel = #1052#1072#1090#1077#1088#1080#1072#1083
  FieldName = 'TITLE_2'
  Size = 100
end
object PPUQueryID_3: TFMTBCDField
  FieldName = 'ID_3'
  Precision = 38
  Size = 0
end
object PPUQueryCA: TFMTBCDField
  FieldName = 'CA'
  Precision = 38
  Size = 0
end
object PPUQueryTITLE_3: TStringField
  DisplayLabel = #1047#1072#1082#1072#1079#1095#1080#1082
  FieldName = 'TITLE_3'
  Size = 100
end
object PPUQueryID_4: TFMTBCDField
  FieldName = 'ID_4'
  Precision = 38
  Size = 0
end
object PPUQueryCA_1: TFMTBCDField
  FieldName = 'CA_1'
  Precision = 38
  Size = 0
end
object PPUQueryTITLE_4: TStringField
  DisplayLabel = #1055#1088#1086#1080#1079#1074#1086#1076#1080#1090#1077#1083#1100
  FieldName = 'TITLE_4'
  Size = 100
end
object PPUQueryID_5: TFMTBCDField
  FieldName = 'ID_5'
  Precision = 38
  Size = 0
end
object PPUQueryARTICLE_1: TFMTBCDField
  FieldName = 'ARTICLE_1'
  Precision = 38
  Size = 0
end
object PPUQueryMATERIAL_1: TFMTBCDField
  FieldName = 'MATERIAL_1'
  Precision = 38
  Size = 0
end
object PPUQueryPRODUCER_1: TFMTBCDField
  FieldName = 'PRODUCER_1'
  Precision = 38
  Size = 0
end
object PPUQueryWEIGHT_1: TFloatField
  FieldName = 'WEIGHT_1'
end
object PPUQueryINUM_1: TFMTBCDField
  FieldName = 'INUM_1'
  Precision = 38
  Size = 0
end
object PPUQueryQNUM_1: TFMTBCDField
  FieldName = 'QNUM_1'
  Precision = 38
  Size = 0
end

Underlying database is Oracle 9 and the ID is of type INTEGER and is PK of table TPORDER_CMK. PPUQuery.SQL is:

SELECT tp.*, la.*, lm.*, ld.*, ld1.*, to_cmk.*
  FROM ppu_plan.tporder_cmk tp
       JOIN PPU_PLAN.LARTICLES la
          ON TP.ARTICLE = LA.ID
       JOIN PPU_PLAN.LMATERIAL lm
          ON TP.MATERIAL = lm.id
       JOIN PPU_PLAN.LCADEP ld
          ON TP.CADEP = LD.ID
       JOIN PPU_PLAN.LCADEP ld1
          ON TP.PRODUCER = LD1.ID
       JOIN PPU_PLAN.TORDER_CMK to_cmk
          ON TP.order_id=TO_cmk.ID
WHERE TP.PLAN_ID = :pplan_id

DDL script for TPORDER_CMK:

CREATE TABLE PPU_PLAN.TPORDER_CMK
(
  ID        INTEGER                             NOT NULL,
  ORDER_ID  INTEGER                             NOT NULL,
  PLAN_ID   INTEGER                             NOT NULL,
  NUM       INTEGER,
  ONUM      INTEGER,
  CNUM      VARCHAR2(50 BYTE),
  DRAWING   VARCHAR2(50 BYTE),
  TITLE     VARCHAR2(200 BYTE),
  NUM_OF    INTEGER                             DEFAULT 0,
  CADEP     INTEGER,
  WEIGHT    FLOAT(126),
  MATERIAL  INTEGER,
  ARTICLE   INTEGER,
  DEPTH     FLOAT(126),
  PRODUCER  INTEGER                             NOT NULL,
  INUM      INTEGER,
  PAINT     INTEGER                             NOT NULL,
  BSS       INTEGER                             NOT NULL,
  QNUM      INTEGER                             DEFAULT NULL,
  SERVICE   INTEGER                             NOT NULL,
  CONTINUE  INTEGER                             NOT NULL,
  GRAPH     VARCHAR2(200 BYTE),
  ADVANCED  INTEGER                             NOT NULL,
  NOTE      VARCHAR2(1024 BYTE),
  UPDATED   DATE                                DEFAULT SYSDATE               NOT NULL,
  RELDATE   DATE,
  METIZ     INTEGER                             DEFAULT 0                     NOT NULL
)
TABLESPACE WEB
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          80K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOLOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
NOMONITORING;


CREATE INDEX PPU_PLAN.TPORDER_CMK_IDX ON PPU_PLAN.TPORDER_CMK
(ORDER_ID, PLAN_ID, NUM, ONUM, CADEP, 
MATERIAL, ARTICLE, PRODUCER, PAINT, BSS, 
SERVICE, CONTINUE, GRAPH, ADVANCED, UPDATED, 
RELDATE, METIZ)
LOGGING
TABLESPACE WEB
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


CREATE UNIQUE INDEX PPU_PLAN.TPORDER_CMK_PK ON PPU_PLAN.TPORDER_CMK
(ID)
NOLOGGING
TABLESPACE WEB
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          80K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


CREATE OR REPLACE TRIGGER PPU_PLAN.TRPORDER_CMK
    BEFORE INSERT ON PPU_PLAN.TPORDER_CMK     FOR EACH ROW
WHEN (
NEW.ID IS NULL
      )
DECLARE NID INTEGER;
    BEGIN
        IF (:new.ID = NULL) THEN
        BEGIN
            SELECT SPORDER_CMK.NEXTVAL INTO NID FROM DUAL;
            :new.ID := NID;
        END;
        END IF;
    END;
/


ALTER TABLE PPU_PLAN.TPORDER_CMK ADD (
  CONSTRAINT TPORDER_CMK_PK
  PRIMARY KEY
  (ID)
  USING INDEX PPU_PLAN.TPORDER_CMK_PK
  ENABLE VALIDATE);

SpPlansQuery['PPONREC'] is a TFmtBcdField, but even specifying value literally (e.g. 18323) doesn't help.

My previous question (Cannot assign data to client dataset) contains information that may help in finding out what am I trying to achieve.

What should I try next and how to solve this problem?

Sorry, can't chat now. Have you tried `PPUQuery.Locate('ID', SpPlansQuery['PPONREC'].AsInteger, []);` ? — David A, May 29 '14 at 04:56
Yes, as well as `Integer(SpPlansQuery['PPONREC'])` and `VarToInt(SpPlansQuery['PPONREC'])`. — Danatela, May 29 '14 at 05:01
I guess the column `ID` is not the column you want to ask, because you have 6 columns which name is `ID`. Change your query to `SELECT tp.* FROM ...` and try `Locate`. Now you should get a match. — Sir Rufo, May 29 '14 at 05:41
Use the [Fields Editor](http://docwiki.embarcadero.com/RADStudio/en/Fields_Editor) and *Add all Fields*. You should see all fields of the query and the names. Please add this field name list and the classtype (OI will tell you) of that ID field to your question — Sir Rufo, May 29 '14 at 07:08
I would have expected to see a `TIntegerField` for `PPUQueryID` and that seems to be the main problem with `Locate` and `Filter` — Sir Rufo, May 29 '14 at 07:41
Since you have multiple ID fields, you must supply Aliases for your ID fields in your query.. — whosrdaddy, May 29 '14 at 12:38
@Danatela: Please stop requesting chat to discuss details. The details for your question belong here in the post. Having them in chat won't benefit anyone in the future who finds this question in a search result. Please [edit] your question and add the details to it, instead of in comments or chat, where they can be readily seen. — Ken White, May 29 '14 at 13:05
@KenWhite I always add relevant information inside the body of the question. The reason it also exists in comments is because it is reply to concrete person and I don't want to force them to search updates there. — Danatela, May 30 '14 at 02:26
@whosrdaddy I thought so, but the problem is not there since aliases are made automatically. I have ID_1, ID_2 fields and so long. — Danatela, May 30 '14 at 02:29
@Danatela: If you are being asked to provide more details, it's because they are not in "the body of your question", and you need to **add them there** instead of asking to discuss it in chat. Chat is not available to future users who find the question in a search, as I've previously said. **Put the details in your question instead.**. There's no need then to "search updates", because **they are in the question itself**. "For details I'd prefer chat" is nonsense - if you want help, [edit] your question and provide details in the question itself. If you want to chat, you're on the wrong site. — Ken White, May 30 '14 at 02:48
@KenWhite oops sorry I said wrong. I meant 'for discussion I prefer chat'. And I added all details that were requested. Also, the engine told me to use chat. Anyway, I found the solution and working on it now. Thanks for the tips. — Danatela, May 30 '14 at 02:59

score 1 · Accepted Answer · answered May 30 '14 at 03:28

I have found temporary solution that I hate since it is memory consuming and 3 times slower than I want. Also, I apologise for answering my own questions too much, but it's the best solution I found.

I have to pass PPUQuery and build TDictionary<Integer, Variant> with values that I want. Next, I'm querying the dictionary instead of using TADOQuery.Locate:

uses Generics.Collections;

procedure ReFillDataSet;
const
  // IMPORTANT: check that fields count in next 2 lines would be the same
  FieldsStr1 = 'FIELD_1_PPU;FIELD_2_PPU;FIELD_3_PPU';
  FieldsStr2 = 'FLD_1;FLD_2;FLD_3';
var
  Deleted, Changed: Boolean;
  FieldValues1, FieldValues2: Variant;
  Idx1, Idx2: TDictionary<Integer, Variant>
begin
  ComparisonDataSet.DisableControls;
  ComparisonDataSet.EmptyDataSet;
  // building indexes
  Idx1 := TDictionary<Integer, Variant>.Create;
  Idx1.Clear;
  PPUQuery.First;
  while not PPUQuery.Eof do begin
    Idx1.Add(PPUQuery['ID'], PPUQuery[FieldsStr2]);
    PPUQuery.Next;
  end;
  Idx2 := TDictionary<Integer, Variant>.Create;
  Idx2.Clear;
  SpPlansQuery.First;
  while not SpPlansQuery.Eof do begin
    Idx2.Add(SpPlansQuery['PPONREC'], Null);
    SpPlansQuery.Next;
  end;
  // deleted and changed records
  SpPlansQuery.First;
  while not SpPlansQuery.Eof do
  begin
    FieldValues1 := SpPlansQuery[ReplaceStr(FieldsStr1, '_PPU', '')];
    //Deleted := not PPUQuery.Locate('ID', SpPlansQuery['PPONREC'], []);
    Deleted := not Idx1.ContainsKey(SpPlansQuery['PPONREC']);
    if not Deleted then
    begin
      FieldValues2 := Idx1[SpPlansQuery['PPONREC']];
      Changed := False;
      // count fields
      Fields := TList.Create;
      try
        ComparisonDataSet.GetFieldList(Fields, FieldsStr1);
        for J := 0 to Fields.Count - 1 do
        begin
          if Changed then
            System.Break;
          Changed := Changed or (FieldValues1[J] <> FieldValues2[J]);
        end;
      finally
        Fields.Free;
      end;
    end;
    if Deleted or Changed then
    begin
      ComparisonDataSet.Append;
      ComparisonDataSet[ReplaceStr(FieldsStr1, '_PPU', '')] := FieldValues1;
      if Deleted then
        ComparisonDataSet['Oper'] := 2
      else begin
        ComparisonDataSet['Oper'] := 1;
        ComparisonDataSet[FieldsStr1] := FieldValues2;
      end;
      ComparisonDataSet.Post;
    end;
    SpPlansQuery.Next;
  end;
  // added records
  PPUQuery.First;
  while not PPUQuery.Eof do begin
    if not {SpPlansQuery.Locate('PPONREC', PPUQuery['ID'], [])} Idx2.ContainsKey(PPUQuery['ID']) then begin
      ComparisonDataSet.Append;
      ComparisonDataSet[FieldsStr1] := PPUQuery[FieldsStr2];
      ComparisonDataSet['Oper'] := 0;
      ComparisonDataSet.Post;
    end;
    PPUQuery.Next;
  end;
  Idx1.Free;
  Idx2.Free;
  ComparisonDataSet.First;
  ComparisonDataSet.EnableControls;
end;

Also see http://stackoverflow.com/questions/28231727/delphi-ado-locate-with-dataset-filter-on-bug. — Tom, Jun 26 '15 at 11:27

Cannot locate record in delphi ADO query

1 Answers1