CODEWITHSUNDEEP
sqloracle-databasedelphi
Danatela
Danatela

Reputation: 349

Cannot locate record in delphi ADO query

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?

Upvotes: 1

Views: 3037

Answers (1)

Danatela
Danatela

Reputation: 349

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;

Upvotes: 1

Related Questions