Help - what could cause this problem?

[Merlin]

Hello,

I am struggling with the following situation. I have a TIBCQuery component, quPosition, that accesses a table. I am performing this logic (Delphi 5, IBDAC 2.00, Firebird 2.01):

if quPosition.Locate('SYMBOL', pSymbol, []) then begin
... do something with existing record
end else begin
... insert a new record for this pSymbol value.
end;

Note that SYMBOL is the primary key on the table.
I am experiencing a problem that always occurs on the same pSymbol value, 'AHG'. This logic is called from a loop, repeatedly, for the same set of pSymbol values. The first time the logic is called for 'AHG', the locate succeeds, because there is already a record in the table for this value. The second time the logic is called with pSymbol = 'AHG', the Locate fails - even though the record still exists in the table! (no deletes are performed on this table at all). Then when the 'end else' branch is followed, the logic attempts to insert a new record for AHG and fails with 'violation of PRIMARY or UNIQUE KEY constraint'.

The strangest thing is that I can go into the Delphi debugger Evaluate/Modify window, do First on the quPosition component, and scroll through it, and I can actually see that there is a record there for SYMBOL = 'AHG'. What can possibly be causing this Locate to fail, when it has already succeeded once with the same record, and I can see that the record is there??

Thanks for any light you can shed. This is part of a very large project, I'm not sure I'll be able to duplicate the problem in a demonstration project. The query is attached to a TIBCTransaction object with ReadCommitted isolation level. i have experimented with various Refresh settings for the TIBCQuery, but so far this problem reoccurs no matter what I do. I have even done a complete backup and restore of the Firebird database - that didn't help either.

Thanks for any suggestions you can give. This appears to me to be a bug in the Locate logic - how can it be failing when the record is visibly there and can be seen by scrolling through the dataset with Next ???

[Merlin]

Further information on this problem: I tried deleting that particular record. The problem then occurs on the very next record in the list. Again, this is a case where scrolling through with Next shows that the record is in the local dataset, but Locate says it isn't there!

[Alex]

We can't reproduce this problem. If it is possible, send us a small demo project that demonstrates this problem.

It looks like the Locate method continues searching from the curent DataSet position, instead of searching from the first record. Try to call the IBCQuery.First method before IBCQuery.Locate method. Also specify if you are using filtering or sorting (TIBCQuery.Filter, TIBCQuery.IndexFieldNames, etc), cached updates or any other not default settings.

[Merlin]

Yes, I am using IndexFieldNames with the same field that I am Locating on, this is what you recommend. Will try the First method and let you know.

[Merlin]

Once again, I am working with Delphi 5, Firebird 2.01, and the latest IBDAC 2.00 release. I am now working around the problem with this code:

Code: Select all

var
  PosRecFound : boolean;
begin
  ...

      with quPosition do begin
        first;
        PosRecFound := Locate('SYMBOL', pSym, []);
        if not PosRecFound then begin
          // I don't believe you - do a full scan to find the record
          first;
          while not EOF do begin
            if pSym = quPositionSYMBOL.asString then begin
              // this should never be true, if Locate failed!  But it is true frequently
              PosRecFound := true;
              BREAK;
            end;
            next;
          end;
        end;
      end;
      if PosRecFound then begin

Here is the table definition:

Code: Select all

CREATE TABLE CURRENT_POSITION
(
  RUN_ID                             INTEGER         NOT NULL,
  SYMBOL                                 SYM ,
  OPEN_TRADE_ID                      INTEGER,
  PENDING_LIVE_ORDER_ID              INTEGER,
 PRIMARY KEY (RUN_ID, SYMBOL)
);
update rdb$relations set rdb$description = 'Holds current open positions for each stock for each system' where rdb$relation_name = 'CURRENT_POSITION';

ALTER TABLE CURRENT_POSITION ADD CONSTRAINT FK_POSITION_RUN
  FOREIGN KEY (RUN_ID) REFERENCES RUN
  (RUN_ID)
  ON DELETE CASCADE
  ON UPDATE CASCADE
;

Here is the SQL for the query:

Code: Select all

SELECT RUN_ID, SYMBOL, OPEN_TRADE_ID, PENDING_LIVE_ORDER_ID
FROM CURRENT_POSITION
WHERE Run_ID = :p_RUN_ID

And here are all the properties of the query (from the data module, with 'View as Text':

Code: Select all

  object quPosition: TIBCQuery
    KeyFields = 'RUN_ID;SYMBOL'
    SQLInsert.Strings = (
      'INSERT INTO CURRENT_POSITION'
      '  (RUN_ID, SYMBOL, OPEN_TRADE_ID, PENDING_LIVE_ORDER_ID)'
      'VALUES'
      '  (:RUN_ID, :SYMBOL, :OPEN_TRADE_ID, :PENDING_LIVE_ORDER_ID)')
    SQLDelete.Strings = (
      'DELETE FROM CURRENT_POSITION'
      'WHERE'
      '  RUN_ID = :Old_RUN_ID AND SYMBOL = :Old_SYMBOL')
    SQLUpdate.Strings = (
      'UPDATE CURRENT_POSITION'
      'SET'

        '  RUN_ID = :RUN_ID, SYMBOL = :SYMBOL, OPEN_TRADE_ID = :OPEN_TRAD' +
        'E_ID, PENDING_LIVE_ORDER_ID = :PENDING_LIVE_ORDER_ID'
      'WHERE'
      '  RUN_ID = :Old_RUN_ID AND SYMBOL = :Old_SYMBOL')
    SQLRefresh.Strings = (

        'SELECT CURRENT_POSITION.RUN_ID, CURRENT_POSITION.SYMBOL, CURRENT' +
        '_POSITION.OPEN_TRADE_ID, CURRENT_POSITION.PENDING_LIVE_ORDER_ID ' +
        'FROM CURRENT_POSITION'
      'WHERE'
      '  RUN_ID = :RUN_ID AND SYMBOL = :SYMBOL')
    SQLLock.Strings = (
      'SELECT * FROM CURRENT_POSITION'
      'WHERE'
      'RUN_ID = :Old_RUN_ID AND SYMBOL = :Old_SYMBOL'
      'FOR UPDATE WITH LOCK')
    Connection = TradeConnection
    Transaction = TradeReadTransaction
    UpdateTransaction = TradeUpdateTransaction
    SQL.Strings = (
      'SELECT RUN_ID, SYMBOL, OPEN_TRADE_ID, PENDING_LIVE_ORDER_ID'
      'FROM CURRENT_POSITION'
      'WHERE Run_ID = :p_RUN_ID')
    FetchAll = True
    RefreshOptions = [roAfterInsert, roAfterUpdate]
    Options.StrictUpdate = False
    Options.AutoPrepare = True
    Options.FullRefresh = True
    IndexFieldNames = 'RUN_ID;SYMBOL'
    Left = 36
    Top = 192
    ParamData = 
    object quPositionRUN_ID: TIntegerField
      FieldName = 'RUN_ID'
      Required = True
    end
    object quPositionSYMBOL: TStringField
      FieldName = 'SYMBOL'
      Required = True
      FixedChar = True
      Size = 6
    end
    object quPositionOPEN_TRADE_ID: TIntegerField
      FieldName = 'OPEN_TRADE_ID'
    end
    object quPositionPENDING_LIVE_ORDER_ID: TIntegerField
      FieldName = 'PENDING_LIVE_ORDER_ID'
    end
  end

Lastly, here are the properties of the connection and the 2 transaction objects:

Code: Select all

  object TradeConnection: TIBCConnection
    Database = 'F:\AA TSoft\Ian\data\ZANSHIN.FDB'
    DefaultTransaction = TradeReadTransaction
    ClientLibrary = 'gds32.dll'
    Username = 'SYSDBA'
    Password = '--------'
    AutoCommit = False
    Connected = True
    LoginPrompt = False
    Left = 36
    Top = 20
  end
  object TradeReadTransaction: TIBCTransaction
    DefaultConnection = TradeConnection
    DefaultCloseAction = taCommit
    IsolationLevel = iblReadOnlyReadCommitted
    Left = 136
    Top = 20
  end
  object TradeUpdateTransaction: TIBCTransaction
    DefaultConnection = TradeConnection
    DefaultCloseAction = taCommit
    Left = 256
    Top = 20
  end

Note that Object Inspector shows the Isolation Level for TradeUpdateTransaction as "iblReadCommitted", apparently this is the default.

The program logic opens transactions for both TradeReadTransaction and TradeUpdateTransaction, and does a CommitRetaining on TradeUpdateTransaction after each full loop.

Also note that the table Primary key is "RUN_ID;SYMBOL", but in this case the run_id is always the same. I am getting the exact same problem whether I use:

Code: Select all

Locate('SYMBOL', pSym, [])

or

Code: Select all

Locate('RUN_ID;SYMBOL', VarArrayOf([RunID, pSym]), [])

so that is not the problem. I have also tried using LocateEx - the result is exactly the same, Locate fails to find records that are then found by the sequential scan through the table.

I hope this will help you find the problem, or at least allow you to tell me a more efficient workaround then manually scanning through each table because Locate cannot be trusted.

Best regards,

[Merlin]

I noticed that I had AutoCommit set to false for the Connection. I am explicitly starting and committing the transaction on a regular basis - I am not currently using cached updates.

Since I am handling transactions in my code, I did not think it was necessary to also have AutoCommit = true - and I am not sure what the consequences of this setting would be - does this mean that extra commits are occurring?? (both my manual commits and your component's Autocommits?).

BUT, when I set AutoCommit to true, the Locate now works properly !!

I would appreciation some explanation for why this should work this way, and what settings I should be using when managing transactions explicitly.

Thank you.

[Merlin]

Hi. It appears I spoke to soon. I have made further minor changes to my program - including adding one TIBCScript component to the datamodule. The Locate is now failing again. By failing, I mean that it is incorrectly returning false when the target record is in fact present in the dataset (and can be found by a sequential scan).

The failure is occurring now, even though AutoCommit is set to true for the connection (and for the query dataset). So apparently that is not the solution after all. I'm afraid I have no idea at this point what change caused the Locate to magically start working, and what change caused it to stop working again.

At the moment, I am moving forward with my project by using the 'sequential scan' workaround, but of course I would like this resolved. Note that I do have a number of other IBCQuery objects in the project, and this appears to be the only one where Locate is failing.

[Alex]

Thank you for assistance.
We have reproduced and fixed this problem. This fix will be included in the next IBDAC build.

As a temporary solution, you can remove locate field from IndexFieldNames property of TIBCQuery.

[Merlin]

Thank you for the quick support. Please get the new build out quickly!

Best regards,

Merlin