VFP insert, index updating

Question

So the main program is in C#. Inserting new records into a VFP database table. It was taking too long to generate the next ID for the record via

select max(id)+1 from table

, so I put that code into a compile dll in VFP and am calling that COM object through C#.

The COM object returns the new ID in about 250ms. I then just do an update through OLEDB. The problem I am having is that after the COM object returns the newly inserted ID, I cannot immediately find it from C# via the OLEDB

select id form  table where id = *newlyReturnedID*

returns 0 rows back. If I wait an unknown time period the query will return 1 row. I can only assume it returns 0 rows immediately because it has yet to add the newly minted ID into the index and therefore the select cannot find it.

Has anyone else ever run into something similar? If so, how did you handle it?

DD

Answer 1

VFP needs to FLUSH its workareas.

Answer 2

Warning: your code is flawed in a multi-user environment. Two people could run the query at the same time and get the same ID. One of them will fail on the INSERT if the column has a primary or candidate key, which is a best practice for key fields.

My recommendation is to either have the ID be a auto-incrementing integer field (I'm not a fan of them), or even better, create a table of keys. Each record in the table is for a table that gets keys assigned. I use the a structure similar to this:

       Structure for: countergenerator.dbf
       Database Name: conferencereg.dbc
     Long table name: countergenerator
   Number of records: 0
        Last updated: 11/08/2008
Memo file block size: 64
           Code Page: 1252
          Table Type: Visual FoxPro Table

Field  Name                  Type                 Size   Nulls       Next       Step  Default  
----------------------------------------------------------------------------------------------------------------
    1  ccountergenerator_pk  Character            36         N                        guid(36)  
    2  ckey                  Character (Binary)   50         Y                          
    3  ivalue                Integer               4         Y                          
    4  mnote                 Memo                  4         Y                        "Automatically created"  
    5  cuserid               Character            30         Y                        
    6  tupdated              DateTime              8         Y                        DATETIME()  

Index Tags: 
1. Tag Name: PRIMARY
 - Type: primary
 - Key Expression: ccountergenerator_pk
 - Filter: (nothing)
 - Order: ascending
 - Collate Sequence: machine

2. Tag Name: CKEY
 - Type: regular
 - Key Expression: lower(ckey)
 - Filter: (nothing)
 - Order: ascending
 - Collate Sequence: machine

Now the code for the stored procedure in the DBC (or in another program) is this:

FUNCTION NextCounter(tcAlias)

LOCAL lcAlias, ; lnNextValue, ; lnOldReprocess, ; lnOldArea

lnOldArea = SELECT()

IF PARAMETERS() < 1 lcAlias = ALIAS()

IF CURSORGETPROP("SOURCETYPE") = DB_SRCLOCALVIEW *-- Attempt to get base table lcAlias = LOWER(CURSORGETPROP("TABLES")) lcAlias = SUBSTR(lcAlias, AT("!", lcAlias) + 1) ENDIF ELSE lcAlias = LOWER(tcAlias) ENDIF

lnOrderNumber = 0 lnOldReprocess = SET('REPROCESS')

*-- Lock until user presses Esc SET REPROCESS TO AUTOMATIC

IF !USED("countergenerator") USE EventManagement!countergenerator IN 0 SHARED ALIAS countergenerator ENDIF

SELECT countergenerator

IF SEEK(LOWER(lcAlias), "countergenerator", "ckey") IF RLOCK() lnNextValue = countergenerator.iValue REPLACE countergenerator.iValue WITH countergenerator.iValue + 1 UNLOCK ENDIF ELSE * Create the new record with the starting value. APPEND BLANK IN countergenerator SCATTER MEMVAR MEMO m.cKey = LOWER(lcAlias) m.iValue = 1 m.mNote = "Automatically created by stored procedure." m.tUpdated = DATETIME() GATHER MEMVAR MEMO

IF RLOCK() lnNextValue = countergenerator.iValue REPLACE countergenerator.iValue WITH countergenerator.iValue + 1 UNLOCK ENDIF ENDIF

SELECT (lnOldArea) SET REPROCESS TO lnOldReprocess

RETURN lnNextValue ENDFUNC

The RLOCK() ensures there is no contention for the records and is fast enough to not have bottleneck the process. This is way safer than the approach you are currently taking.

Rick Schummer
VFP MVP