VFP Database contention issue

Question

I have a very large VFP app. I use the VFE framework. My app uses the native VFP database. App is 20 years old and grows daily. For 10 years I very intermittently get crashes. It can occur multiples times in same day. It may not occur for a week or 2 and then twice in a single day. I have 20+ customer sites with anywhere from 5 to 30 users concurrently per site. The busier the site, the more often the crashes. I have always thought it was due to the VFP DBC contention issue but have never proven. Over past 10+ years I have tried many times to resolve but have never been able to do so. I'm ready to try again. I have a DBC that has approx 125 tables. All views are in a separate "views" DBC that contains about 1500 views.

I receive a 1925 error "unknown member PARAMETERS" when the crashes occur. I will gladly supply a lot more info but first I would like to just get a better understanding of the locking/contention issue with my views database. Can someone please help me understand exactly what that issue is?

This is the code where the error is occurring. This can occur in several other places but this is probably the simplest example:

1. I have multiple apps. I have an app that is used as the user interface. That is the app my users are using all day every day. The error we are discussing never occurs in that app. I have an app that is used to do "background stuff". It has no user interface and I will not say this exactly right but it is a "COM object" meaning it is OLE Public. I call this program ITFCOM001. It is an EXE. ITFCOM001.EXE is where these errors ALWAYS occur. ITFCOM001 is basically a "endless loop" program that does things like imports from vendors, exports to vendors, generating reports data, etc. Both my user interface app and ITFCOM001 make use of the same tables DBC and views DBC.
2. The errors occur when ITFCOM001 goes to do something like a vendor export.

Code from ITFCOM001 where the error occurs:

lojpayexportsbo=CREATEOBJECT("v_jpayexportsbizobj")
&& 11/13/2017 - add cd to try stop com001 crashes
IF NOT VARTYPE(lojpayexportsbo)='O'
   lojpayexportsbo = NULL
   RELEASE lojpayexportsbo
   RETURN .f.
ENDIF
lojpayexportsbo.setparameter("vp_ctransfer_acct_id",tctransfer_acct_id)
lojpayexportsbo.requery()
lnretval=lojpayexportsbo.navigate("FIRST")
&& 11/30/2017 added lnretval= to above statement and then checking to ensure = FILE_OK below here
IF NOT lnretval>=-7
   IF VARTYPE(lojpayexportsbo)="O"
      lojpayexportsbo.release()
   ENDIF
   lojpayexportsbo = NULL
   RELEASE lojpayexportsbo
   RETURN .f.
ENDIF
lojpayexportsrec=lojpayexportsbo.getvalues()

In the above code, the setparameter is where the error is occurring. Looking at the VFE class libraries The error is actually occurring on line 41 of the FindViewParameter method.

*==============================================================================
* Method:           FindViewParameter
* Purpose:          Finds the Parameter object associated with the passed 
*                   view parameter name.
* Author:           F1 Technologies
* Parameters:       tcParameterName, The name of the view parameter.
* Returns:          Object, The Parameter object.
* Modifications:
* 12/03/1999        Initialized loParameter to .NULL.
*==============================================================================
LPARAMETERS ;
    tcParameter
    
LOCAL ;
    loParameter, ;
    loCursor, ;
    lcCursor, ;
    lcParameter

loParameter = .NULL.

WITH This   
    IF "." $ tcParameter
        lcParameter = TRIM(SUBSTR(tcParameter, AT(".", tcParameter) + 1))
        lcCursor = LEFT(tcParameter,AT(".", tcParameter) - 1)
    ELSE
        lcParameter = tcParameter
        lcCursor = ""
    ENDIF
    IF "!" $ lcCursor
        lcCursor = SUBSTR(lcCursor,AT("!", lcCursor) + 1)
    ENDIF

    IF NOT EMPTY(lcCursor)
        loCursor = .FindCursor(lcCursor)
    ELSE
        loCursor = .oCursor
    ENDIF

    IF VARTYPE(loCursor) = T_OBJECT
        loParameter = loCursor.Parameters.Item(lcParameter)
    ENDIF
    * If the view parameter could not be found in the business object, check
    * its parent.
    IF VARTYPE(loParameter) <> T_OBJECT AND VARTYPE(.oParentBizObj) = T_OBJECT
        loParameter = .oParentBizObj.FindViewParameter(tcParameter)
    ENDIF
ENDWITH
RETURN loParameter

line 41 is "loParameter = loCursor.Parameters.Item(lcParameter)"

This is the value from error logging. This is the call stack:

0000KF7T0193
ITFCOM001APPLICATIONOBJECT.INIT
ITFCOM001APPLICATIONOBJECT.INIT_POST
ITFCOM001APPLICATIONOBJECT.EXPORTTOEDV
V_JPAYEXPORTSBIZOBJ.SETPARAMETER
V_JPAYEXPORTSBIZOBJ.FINDVIEWPARAMETER
V_JPAYEXPORTSBIZOBJ.ERROR

I should probably add one more thing: The code shown above executes every 15 minutes 24/7 on all my customer sites. The error/crashes only occur very intermittently across all sites.

Stefan, I have never used ASSERTS but I did look it up in VFP help. Use of ASSERTS will not help because this is a COM object and it has no user interface so even if the debug dialog would display(and I don't believe it would), ASSERTS would only work in my development environment and NOT in the live system. Also, this error would never occur in my development environment unless I also set up a very complex test that would simulate users doing data entry while the COM object is also running and doing that would be unbelievably complex. BUT, conceptually you have given me an idea that I believe could work. I could modify the FindViewParameter method to write additional information out to a text file for better debugging information or perhaps I could do that in the ON ERROR routine when an error gets logged. I will try to do that today and see what I get.

OK, I set out to do as described above. I decided to modify my ON ERROR routine to write out additional debugging info when the error 1925 occurs. I found out that I already have code in my ON ERROR routine to do that but that code is not working properly. I need some help with determining why. Here is the code in my ON ERROR routine:

LPARAMETERS     tnError,    tcMethod,   tnLine

LOCAL lcfacility,lcsubject,lctmpfilename,lntmpfilehndl

&& 01/06/2016 - added property and adding line here to set property true.
this.lerrorhasoccurred=.t.
&& will check in code where i suspect an untrapped error is occurring

&& 01/06/2016 - I should see one of these files everytime com001 errors.
lctmpfilename=ADDBS(ALLTRIM(this.capphomeonserver))+"TEMP\ITFCOM001_Log_errors_"+TTOC(DATETIME(),1)+".txt"
lntmpfilehndl=FCREATE(lctmpfilename)
FPUTS(lntmpfilehndl,"in oappcom001.error. errno="+ALLTRIM(STR(tnError,6,0)))
FFLUSH(lntmpfilehndl,.t.)
FCLOSE(lntmpfilehndl)

DO CASE
   && Ignore error caused by issuing a CLEAR ALL in the init of this class.
   CASE tnError = 1951 AND UPPER(ALLTRIM(tcMethod)) == "INIT"
      RETURN
    
   && Ignore errors causes by issuing SET DATASESSION TO an invalid data session.
   CASE tnError = 1540
      RETURN

   && Ignore errors opening the project as a table. GetProjectData will use the
   && last copy of the meta data table if the project can't be opened. This allows 
   && VFE to run in one instance of VFP and a second VFP instance to be used to
   && run the project.
   CASE tnError = 1705 AND UPPER(ALLTRIM(tcMethod)) == "GETPROJECTDATA"
      RETURN    
    
   && Handle any other errors
   OTHERWISE
      && send me a text
      && I took all the code out that sends me a text via Twilio to make this more readable.
      DoDefault(tnError, tcMethod, tnLine)
ENDCASE

RETURN

About the above code: VFE always generates an error 1951 when any VFE app starts. It is just always been that way. So, anytime I start this app, an error 1951 occurs, this ON ERROR routine gets executed, I see the text file in my temp folder and it contains the text, "in oappcom001.error. errno=1951". The error is ignored and the program runs.

When an error 1925 occurs this ON ERROR routine is NOT getting executed. How do I know that? The text file is NOT being created. That tells me something some where in my app or maybe in some class library that I am using, is changing ON ERROR and causing it to maybe use the default VFP error handling routine rather than this ON ERROR routine. I am going to need to find that first.

So, I am attempting to understand what could be changing which ON ERROR routine gets executed and it does not take long to realize I have done this sooooo many times and has always proven to be a dead end. Just reading the VFE help topic for how VFE handles errors is so complex it would take a me a month of doing nothing but trying to understand the help topic. I can't do that.

This needs to go back to my original question. I did not do a very good job of asking that original question. Let me try again:

Years and years of dealing with and studying what might cause these 1925 errors, I have come to the conclusion that this is somehow related to the VFP database contention issue where the database gets locked when a view is created. As wrong as it may sound, I have given up on ever determining WHY the 1925 errors occur and instead would prefer a workaround. So back to the basics:

1. My app that has the user interface is named INMATETRUSTFUND. It is compiled to an EXE. It has a tables database with 125 tables. It has a "views" database with 1200 views.
2. My app that is getting the 1925 errors is named ITFCOM001. It is a COM object compiled to an EXE. It has no display and no user interface. It just runs in the background. It uses the same tables and views databases.

I suspect something like the following is occurring and that is what is causing the 1925 errors:

A user in INMATETRUSTFUND is doing something that opens a view so the views database gets locked while the view is generated. At the same exact moment, ITFCOM001 determines it is time to do an export to a vendor and executes the code that creates the v_jpayexportsbizobj. Doing that would access the views database. VFP would attempt to lock the views database but it is already locked because INMATETRUSTFUND locked it to open some other view. Everything goes awry (and there is a lot of unknown stuff in that statement), and ITFCOM001 generates the 1925 error.

I believe, but would like a better understanding of the VFP database contention issue before I decide, that if I gave ITFCOM001 it's own separate views database that has the same views as the views database that is used by INMATETRUSTFUND, but it doesn't need all 1200 views and instead only needs the 100 or so views that ITFCOM001 uses, that this problem could simply go away meaning the VFP database contention issue would go away because the 2 apps would never use the same views database.

Again, I would really like to fully understand the exact nature of the "VFP database contention" issue before trying my possible solution of having separate views databases.

Thanks, John

Answer 1

I wanted to leave a comment, but I don't have enough reputation apparently... and I understand this post is 6 months old, but OP has been working on it for 10 years and if I can help, why not? :-)

Adding to the great comments from Stefan & Tamar, when I look at the error message and code I conclude that at the point of error, loCursor is an Object and is not null, but it doesn't have a member called Parameters. This suggests to me that This.FindCursor(lcCursor) or This.oCursor are either returning an unexpected object type or maybe an object that's not instantiated properly...?

As Stefan suggests, extra logging should help. I suggest adding a TRY..CATCH block to log the error, call stack,the value/type/class of loCursor and the value of tcParameter (which should allow you to identify if loCursor was returned by This.FindCursor(lcCursor) or by This.oCursor). My suspicion is that the root cause lies in This.FindCursor() or This.oCursor default values. I think you should be able to implement a TRY...CATCH without making changes to the global ON ERROR code and throw/raise the original error (after logging) up to ON ERROR - either within the CATCH or after the TRY CATCH block - to keep any code called in the ON ERROR hierarchy running as before.

If upon consideration, you think it's a valid situation for FindViewParameter() to get a value from This.FindCursor(lcCursor) or This.oCursor that isn't a valid cursor object then you can suppress these errors by replacing:

IF VARTYPE(loCursor) = T_OBJECT

with:

IF TYPE("loCursor.Parameters") = T_OBJECT

or Maybe even:

IF TYPE("loCursor.Parameters") # "U" .and. VARTYPE(loCursor.Parameters) = T_OBJECT 

(TYPE() won't throw an error if you pass it an invalid reference, but unhelpfully won't tell you if an object ref is null, so combine the two) This might be enough, but you might just end up with errors regarding .item() not being a member of loCursor.Parameters instead.

In that case you could just catch and suppress the errors with a TRY CATCH block. Based on the comments it looks like FindViewParameter() was amended to return null in some situations back in 1999... They must have considered this important if, in 1999, it took precedence over "millennium bug" preparations! Oops, my age is showing! :-)

Hope this helps. Good Luck!