CursorAdapter.CursorRefresh doesn't seem to work when starting a VFP transaction 1

[xinjie]

Hi,everyone

I am a newbie in terms of using CursorAdapter. My development environment is VFP9 SP2 7423 . I am having a very confusing problem.

I have a document entry UI with a parent-child table structure. The child table needs to display the record corresponding to the parent table when the parent table's record pointer moves.

In the BeforeCursorFill of the child table, I added the following code:

LPARAMETERS luseCursorSchema, lNoDataOnLoad, cSelectCmd
 
TEXT TO m.cSelectCmd ADDITIVE NOSHOW TEXTMERGE PreText 2
	Where ParentID = ?Parent.ID 
	Order by NO 
EndText

With this, I can get the results I want in browse mode.

The problem is in edit mode. When starting to enter a new record, I first execute the Begin Transaction command. After the records are added to the Parent table, I need to refresh the child to get an empty result set. Like this:

*!* Parent
Append Blank
Child.CursorRefresh

But my Child are not empty result sets, and it seems that CursorRefresh doesn't do anything. It still has the records that were there before the edit.

I tried to change cSelectCmd in (Child's) BefroeCursorRefresh , like this:

LPARAMETERS cSelectCmd

TEXT TO m.cSelectCmd NOSHOW TEXTMERGE PreText 1 + 2 + 4 + 8
    Select * From Child Where ParentID = ?Parent.ID Order by NO 
ENDTEXT

But the results were also disappointing and not what I was looking for.

I'm confused as to whether CursorRefresh in a VFP transaction is the result I'm seeing, whatever it is. Or is there something wrong with my approach that is causing the result to not be what I expect?

Any comments or suggestions are welcome!

Thanks in advance!

 

[Chris Miller]

It's unclear what you do? Where do you append blank, for example. If you described this in more detail, it would help a lot.

Also, CursorRefresh is a method, not an event, BeforeCursorRefresh is an event triggered by you calling CursorRefresh. The question then still is, what cursoradapter are you doing this in, you should have two, you only decribe the child table cursoradapter, but there also is a parent table cursor adapter, isn't there?

Your first approach would fail, as you'd additively add more and more where clauses. If you use the SelectCmd from your second approach, it's totally sufficient if that's a static selectCmd, no Before CursorRefresh needed. The parameterization with ?Parent.Id would be enough to get the child records of that parent id. Just notice, that this would a) need to see Parent.id while it refreshes the child cursoradapter cursor - I think that's given you're working in one datasession - and b) would only fetch child records of one parent record, as Parent.id is just the id of the current record, not all parent records.

The SelectCmd needed in a child cursoradapter to read all child records of all parent records in the alias of the parent cursoradapter would be

Select Child.* From ParentCursorAdapterAlias Inner Join Child Where Child.ParentID = ParentCursorAdapterAlias.ID

Which makes the child CA depend on the ParentCA having filled its cursor, of course.

Further rules apply: A CA CursorRefresh works the same way as a REFRESH("alias") in legacy VFP style: Only when there are no buffered changes, you can refersh a cursor. Otherwise you first need to empty the buffer by saving with TABLEUPDATE or cancelling with TABLEREVERT. By the way, the adapter nature of a cursoradapter comes into play as doing the REFRESH() will cause the CursorRefresh of the cursoradapter that is associted with the alias, the intention of the CA there is to allow a feasable integration into legacy code that does use REFRESH(). If you're not even familiar with REFRESH, better read the help topic, otherwise you'll also be surprised that only the current record is refreshed.

I also think it fails on the fact that it does not fetch new records, just refreshes already fetched records.

In the situation you are, with new parent records that have no child record, you would better use CursorFill, not CursroRefresh, there is nothing to refresh there, which you loaded before, because there is nothing yet at all, in the child table, isn't there?



Chriss

 

[xinjie]

Hi, Chriss

Thank you very much for your quick reply!

The lengthy narrative above is just a replication of the application scenarios I've encountered as best as I can. In fact, we can simply reproduce the problem(This just indicates the logic, the code itself may not be feasible to run):

Public a
m.a = 1

Local loCA As my
m.loCA = NewObject([myCA])
m.a = 2
m.loCA.CursorRefresh	&& Normal. No problems.

Begin Transaction 
	m.a = 3
	m.loCA.CursorRefresh	&& There doesn't seem to be any change in the cursor record, which is what I'm confused about
End Transaction

Define Class myCA as CursorAdapter
	SelectCmd = "Select * From my "
	
	Procedure BeforeCursorFill
            LPARAMETERS luseCursorSchema, lNoDataOnLoad, cSelectCmd
 
            TEXT TO m.cSelectCmd ADDITIVE NOSHOW TEXTMERGE PreText 2
		Where ID = ?a
            EndText
	Endproc
EndDefine

 

[Chris Miller]

Well, the base idea you have is that cursorrefresh reexecutes the selectcommand. It doesn't it refreshes already existing records based on their primary key, so changed parameters have no effect. It's only there to refresh already fetched records not redo the query.

CursorFill is for what you want to do.

Chriss

 

[Chris Miller]

There's also no need to add the WHERE part separately. WHERE ID = ?a will always use the current value of a at the time you do the CursorFill, it's not done when you set or add to SelectCmd.

That's generally true, not only for Cursoradapters. SQLEXEC(connhandle,"Select * From table WHERE ID = ?a") is not the same as SQLEXEC(connhandle,"Select * From table WHERE ID = "+ALLTRIM(STR(a))), so it's not setting SelectCmd to a specific ID and needs no refreshing every time before doing CursorFill.

The whole idea of a query with named parameters is that the query itself doesn't change, the only thing changing is the parameter value.

Chriss

 

[Chris Miller]

I just implemented an example CA on Northwind, and what you starred is actually wrong.

Whether I change parameter and do CursorFill or CursorRefresh, I get another record, when changing my parameter value.

Nevertheless what you do has a blatant structural error in that you add more and more WHERE clauses to the SelectCmd. That can't work, can it? Do you not have any error messages?



Chriss

 

[xinjie]

Hi, Chriss

Thank you very much for your insights.

As of now, CursorFill is the most efficient approach and is the most likely way to refresh data in a VFP transaction. I can solve the other problems caused by this relatively easily (typically, Child are represented on the UI, usually in a Grid, and CursorFill can cause some problems at the UI level as a result)

I've written my own not-so-perfect code to reset the Grid settings.

*!* 2022.06.13
*!* Modify the code appropriately to fit the Grid class in own development framework.

*!* 2019.12.18
*!* Refine the code so that the Grid automatically adjusts column widths

*!* 2016.12.22 bug fix
*!* If other controls are added to the Column, the resulting code will ignore the Visible property setting of the control, and direct reuse will cause the bound data source content not to be displayed.

*!* 2016.12.15 bug fix
*!* 1 Explicit operation completion
*!* 2 Insert the Refresh method instead of overriding it
*!* 3 Ignore read-only attributes
*!* 4 Note: Some properties are available at design time and read-only at runtime and cannot be ignored in code. Remove them yourself.。

Private All Like l*
Private i, j, k, n

Local loObject	As Object,		;
	  lcSet 	As Character,	;
	  lnNum		As Number,		;
	  laObject

Dimension laObject[1]

If Aselobj(laObject) = 0
	Messagebox([The object to be manipulated is not selected!], 16, _Screen.Caption)
	Return
Else
	m.loObject = laObject[1]
Endif

If m.loObject.BaseClass <> [Grid]
	Messagebox([This tool operates only on the Grid!], 0, _Screen.Caption)
	Return
Endif

Dimension laGrid[1]

m.lnNum = Amembers(laGrid, m.loObject, 3, [C])

TEXT to m.lcSet textmerge noshow pretext 1
    With This
    	*!* Code by tq_tool_AutoGridSet Ver:1.0.0.4
    
EndText

*!*	Get and combine setup codes
*!*	1 Grid
For m.i = 1 to m.lnNum
	If laGrid[m.i, 2] == [Property] and PemStatus(m.loObject, laGrid[m.i, 1], 1) = .F.
		TEXT to m.lcSet additive textmerge noshow pretext 1
        	.<<laGrid[m.i, 1]>>	=	<<Iif(Vartype(GetPem(m.loObject , laGrid[m.i, 1])) = [C], "["+ GetPem(m.loObject , laGrid[m.i, 1]) + "]", GetPem(m.loObject , laGrid[m.i, 1]))>>				&&	<<laGrid[m.i, 4]>>
        	
		EndText 
	EndIf 
EndFor 

*!*	2 Column
For m.i = 1 to m.loObject.ColumnCount
	Dimension laColumn[1]
	m.lnNum = AMembers(laColumn, m.loObject.Objects[m.i], 3, [C])

	For m.j = 1 to m.lnNum
		If laColumn[m.j, 2] == [Property] and PemStatus(m.loObject.Objects[m.i], laColumn[m.j, 1], 1) = .F.
			TEXT to m.lcSet additive textmerge noshow pretext 1
            	.Column<<Alltrim(Str(m.i))>>.<<laColumn[m.j, 1]>>	=	<<Iif(Vartype(GetPem(m.loObject.Objects[m.i] , laColumn[m.j, 1])) = [C], "[" + GetPem(m.loObject.Objects[m.i] , laColumn[m.j, 1]) + "]", GetPem(m.loObject.Objects[m.i] , laColumn[m.j, 1]))>>				&&	<<laColumn[m.j, 4]>>
        	
			EndText 
		EndIf  
	EndFor 

	*!*	3 Header and xxxxxxx
	For m.k = 1 to m.loObject.Objects[m.i].ControlCount
		Dimension laHeader[1]
		m.lnNum2 = AMembers(laHeader, m.loObject.Objects[m.i].Controls[m.k], 3, [C])	

		For m.n = 1 to m.lnNum2
			If laHeader[m.n, 2] == [Property] and PemStatus(m.loObject.Objects[m.i].Controls[m.k], laHeader[m.n, 1], 1) = .F.
				TEXT to m.lcSet additive textmerge noshow pretext 1
                   	.Column<<Alltrim(Str(m.i))>>.<<m.loObject.Objects[m.i].Controls[m.k].Name>>.<<laHeader[m.n, 1]>>	=	<<Iif(Vartype(GetPem(m.loObject.Objects[m.i].Controls[m.k] , laHeader[m.n, 1])) = [C], "[" + GetPem(m.loObject.Objects[m.i].Controls[m.k] , laHeader[m.n, 1]) + "]", GetPem(m.loObject.Objects[m.i].Controls[m.k] , laHeader[m.n, 1]))>>				&&	<<laHeader[m.n, 4]>>
                   	
				EndText 
			EndIf 
		EndFor 
		
		If m.loObject.Objects[m.i].Controls[m.k].Class # [Textbox] and m.loObject.Objects[m.i].Controls[m.k].Class # [Header]
			TEXT to m.lcSet additive textmerge noshow pretext 1
               	.Column<<Alltrim(Str(m.i))>>.<<m.loObject.Objects[m.i].Controls[m.k].Name>>.Visible	=	.T.
                   	
			EndText 
		EndIf 
	EndFor
EndFor 


TEXT to m.lcSet additive textmerge noshow pretext 1
    
    	.AutoFit()
    	
    	For each m.loColumn in .Columns
    		m.loColumn.Width = m.loColumn.Width + 10
    	EndFor 
    	
    	Try
    		.Mask()
    	Catch 
    	EndTry 
    EndWith 
EndText

m.lcSet = m.lcSet + Chr(13) + m.loObject.ReadMethod([Refresh])

_cliptext = m.lcSet

Local lcTemp as Character
m.lcTemp = GetEnv("TEMP") + [\] + Sys(2015) + [.prg]

Set Safety Off

StrToFile(m.lcSet, m.lcTemp)
AutoBeauty(m.lcTemp)
loObject.WriteMethod([Refresh], FileToStr(m.lcTemp), .T., 1)

Set Safety On 

MessageBox([The customized property settings for the selected Grid have all been extracted to the control's Refresh method!])

Procedure AutoBeauty
    Lparameters m.LcPRG

    *Automatically get VFP beautify options and beautify specified PRG files:
    Local LcResource As Character, ;
          LaBeautify[1]

    If Set([Resource]) == [ON]
        Select Data From Sys(2005) Where Upper(Id) == [BEAUTIFY] Into Array LaBeautify

    Else
        m.LcResource = Addbs(Getenv([APPDATA])) + [Microsoft\Visual FoxPro 9\FoxUser.dbf]
        If File(m.LcResource, 1)
            Select Data From (m.LcResource) Where Upper(Id) == [BEAUTIFY] Into Array LaBeautify
        Endif
    Endif

    If Select([FoxUser]) > 0
        Use In Foxuser
    Endif

    Set Procedure To (_Beautify) Additive

    Try    && BUG:Error if beautify has never been used, or foxuser.dbf has been reset/existing beautify defaults have been broken
        Strtofile(Filetostr(beautify(m.LcPRG, Right(m.LaBeautify[1], 36))), m.LcPRG)
    Catch 
        *!*	The beautify options are made up of 9 sets of ASC characters:
        *!*	ONE		chr(n) + chr(0) + chr(0) + chr(0) ,n Corresponds to the case setting of the variable, with the same value and display order.
        *!*	TWO		chr(n) + chr(0) + chr(0) + chr(0) ,n Corresponds to keyword case settings, with the same value and display sequence
        *!*	THREE	chr(n) + chr(0) + chr(0) + chr(0) ,n is the space value specified when TAB is set to space; n = system setting when TAB is used to set indentation (presumably you can specify this yourself, not tested)
        *!*	FOUR	chr(n) + chr(0) + chr(0) + chr(0) ,n Corresponds to the TAB setting, with the same value and display order.
        *!*	FIVE	chr(0) + chr(0) + chr(0) + chr(0)  
        *!*	SIX		chr(n) + chr(0) + chr(0) + chr(0) ,  Comments are not indented n=0 else n =1
        *!*	SEVEN	chr(n) + chr(0) + chr(0) + chr(0) ,  Line continuation is not indented n=0 else n =1
        *!*	EIGHT	chr(n) + chr(0) + chr(0) + chr(0) ,  proc is not indented n=0 else n =1
        *!*	NINE	chr(n) + chr(0) + chr(0) + chr(0) ,  case not indented n=0 else n =1
        m.LaBeautify[1] = chr(3) + chr(0) + chr(0) + chr(0) + ;    && mixed case
                          chr(3) + chr(0) + chr(0) + chr(0) + ;    && Keywords do not change
                          chr(4) + chr(0) + chr(0) + chr(0) + ;    && Four spaces
                          chr(2) + chr(0) + chr(0) + chr(0) + ;    && spaces
                          chr(0) + chr(0) + chr(0) + chr(0) + ;
                          chr(1) + chr(0) + chr(0) + chr(0) + ;    && comment indentation
                          chr(1) + chr(0) + chr(0) + chr(0) + ;    && Line continuation indented
                          chr(1) + chr(0) + chr(0) + chr(0) + ;    && proc indented
                          chr(1) + chr(0) + chr(0) + chr(0)        && case indented
        Strtofile(Filetostr(beautify(m.LcPRG, Right(m.LaBeautify[1], 36))), m.LcPRG)
    EndTry 
EndProc

The above code is simply a return of generous help. Maybe someone needs it.
Thanks again!

 

[xinjie]

Hi, Chriss

Whether I change parameter and do CursorFill or CursorRefresh, I get another record, when changing my parameter value.

This is exactly the result I need. And, I didn't get any errors.

 

[Chris Miller]

Well, one simple reason also is: That when you execute CA.CursorRefresh, that doesn't trigger the CA.BeforeCursorFill event, it triggers the CA.BeforeCursorRefresh event.

I mean, the names speak for themselves...

Chriss

 

[xinjie]

Hi, Chriss

Description from the help[URL unfurl="true"]https://www.vfphelp.com/help/_5wn12pnzp.htm[/url]):

Visual FoxPro stores the value changes, which are used by CursorFill. The CursorRefresh method uses the value of the SelectCmd parameter, not the SelectCmd property. This facilitates construction of custom queries "on the fly". For example, you can store a basic SELECT – SQL command in the SelectCmd property and add a WHERE clause to the SelectCmd parameter in this event without changing the underlying value of the property. The value of the SelectCmd parameter is then passed to the AfterCursorFill event.

 

[Chris Miller]

xinjie,

you posted a class that has code in BeforeCursorFill and expect it to run when you call CursorRefresh. The CursorRefereh method has no parameters. Also, you point to the help topic of BeforeCursorFill.
The events BeforeCursorFill and BeforeCursorRefresh get the current SelectCmd property value passed in, so if you program nothing in these events, both CursorRefresh and CursorFill execute the SelectCmd propery.

And you don't need any of the Before events to have a parameter in the query, just have it in the SelectCmd and change the parameter value before doing either CursorFill or CursorRefresh. There's no need for what you do in the BGefore events, if you just once and for all fils and refreshes have a static query with a parameter, of which you change the value.

You can make use of the Before... events, if you need changes in the query on the fly, that involve more than just a parameter value change, but you never have to program them.
Waht I said about CursorRefresh earlier was wrong, it can actually be used, but if you expect it to run with your where clause, then either put it in the SelectCmd from the beginning or program the BeforeCursorRefreh, not the BeforeCursorFill, you used the wrong Before event.

Besides, use the builder for a CA, when you are in the class designer, right click on the CA and pick the builder, you have a far easier construction of your CA class this way, especially specifying the updatable fields and the key field, and being "interviewed" in the tabs of the builder about theessntial properties of the CA.

Chriss

 

[xinjie]

Chriss,

Whether you define the SelectCMD property or add any code to BeforeCursorFill/BeforeCursorRefresh, it is normal just in the normal case.

But once CursorRefresh is executed in a VFP transaction, everything becomes unbelievable and the correct result is not obtained.

 

[Chris Miller]

I don't see what you're describing.

Can you add your CA class and data (reduced as necessary) in an attachment, so your situation could be reproduced?



Chriss

 

[xinjie]

Chriss,

This is the simplest example code.

Create Cursor my(ID N(1))
For m.i = 1 to 9
	Insert into my(ID) Values(m.i)
EndFor 
Go top

Public a, loCA
m.a = 1

m.loCA = NewObject([myCA])
m.loCA.CursorFill

m.a = 2
m.loCA.CursorRefresh	&& OK
? demo.ID
?? m.a

Begin Transaction 
	m.a = 3
	m.loCA.CursorRefresh
	? demo.ID			&& Error
	?? m.a
End Transaction 

m.loCA.CursorRefresh
? demo.ID				&& OK
?? m.a

Define Class myCA As CursorAdapter
	Alias				= [demo]
	BufferModeOverride	= 5 
	DataSourceType		= [NATIVE]
	SelectCMD			= [Select ID From my ]
	Tables				= [my]
	UpdatableFieldList	= [ID]
	UpdateNameList		= [ID my.ID]  
	
	Procedure BeforeCursorFill
		Lparameters luseCursorSchema, lNoDataOnLoad, cSelectCmd

		TEXT TO m.cSelectCmd ADDITIVE NOSHOW TEXTMERGE PreText 2
			Where ID = ?a
		ENDTEXT
	Endproc
Enddefine

 

[Chris Miller]

Thanks xinje,

now I can reproduce that unexpected behavior within transactions.

I tried a few things to exclude some irregularities:
1. Not using reserved names (ID -> nID)
2. Not using single letter variable names (even when using m. for clarity)
3. Making the query parameter a private variable
4. Creating a database + table, not a cursor as the source of data, also to enable a (true) primary key
5. Setting Refresh to always read from disk
6. Monitored fieldstates - nothing gets buffered, there also are no modificatons done to the demo cursor, so I removed that code.
7. Looked into some cursor properties

Private All
m.p1 = 1

Local Loca, lnI
Clear
Set Refresh To 0,-1

If Not File('refreshbugdemo.dbc')
   Create Database refreshbugdemo
   Create Table mytable (nID N(1), Primary Key nID Tag nID)
   For m.lnI = 1 To 9
      Insert Into mytable (nID) Values (m.lnI)
   Endfor
   Use
Endif
Use mytable In Select("mytable") Shared

m.loCA = Newobject([myCA])
m.loCA.CursorFill()
? 'cursorfill'
? m.p1, demo.nID, "- as expected, OK"
? "KeyFieldList:",CursorGetProp("KeyFieldList")
? "SQL:", CursorGetProp("SQL")
? "UpdatableFieldList:", CursorGetProp("UpdatableFieldList")
? "UpdateNameList:", CursorGetProp("UpdateNameList")
? "Refresh:", CursorGetProp("Refresh")
?
? 'cursorrefresh outside of transaction'
m.p1 = 2
m.loCA.CursorRefresh()
? m.p1, demo.nID, "- as expected, OK"
? "KeyFieldList:",CursorGetProp("KeyFieldList")
? "SQL:", CursorGetProp("SQL")
? "UpdatableFieldList:", CursorGetProp("UpdatableFieldList")
? "UpdateNameList:", CursorGetProp("UpdateNameList")
? "Refresh:", CursorGetProp("Refresh")

Begin Transaction
   ?
   ? 'cursorrefresh within transaction'
   m.p1 = 3
   m.loCA.CursorRefresh()
   ? m.p1, demo.nID, "- expected: 3 3, ERROR"
   ? "KeyFieldList:",CursorGetProp("KeyFieldList")
   ? "SQL:", CursorGetProp("SQL")
   ? "UpdatableFieldList:", CursorGetProp("UpdatableFieldList")
   ? "UpdateNameList:", CursorGetProp("UpdateNameList")
   ? "Refresh:", CursorGetProp("Refresh")
End Transaction
?
? 'cursorrefresh outside of transaction'
m.loCA.CursorRefresh()
? m.p1, demo.nID, "- as expected, OK"

Define Class myCA As CursorAdapter
   Alias		= [demo]
   BufferModeOverrnIDe	= 5
   DataSourceType	= [NATIVE]
   SelectCmd		= [Select nID From mytable Where nID = ?m.p1]
   Tables		= [mytable]
   KeyFieldList		= [nID]
   UpdatableFieldList	= [nID]
   UpdateNameList	= [nID mytable.nID]
Enddefine

The changed code is still having the issue of CursorRefresh within a transaction not refreshing the cursor, so none of my changes were fruitful.

You could still:
1. Set the KeyFieldList of your CA
2. Not use the BeforeCursorFill event, when there's no need for it - use a parameterized SelectCmd and you're done.

While my advice to use CursorFill is wrong as can be seen from using it outside of transactions, it does seem to be necessary to enforce the regeneration of the CA cursor "demo", which then takes into account the changed parameter value. It does cause grid reconstruction, so it means CursorFill closes the CA alias and recreates it.

I assume that's making the difference to CursorRefresh, which works like REQUERY() - not REFRESH() - and it's documented in CursorFill: "When Visual FoxPro calls CursorFill, it closes the currently attached cursor, if it exists, and the work area that contains the newly created cursor becomes the active work area."

But there's not much else in the help topics of CursorFill vs CursorRefreh that points out another difference that can explain the transaction issue. So my advice works, with the overhead of needing to recreate the grid. There's a possibility of using CursorDetach before CursorFill, but in the end also demo is recreated and the grid deconstructs.

I'm not yet 100% sure it's a bug and not by design due to some specific property of CA attached cursors, that differs from normal cursors, but it pretty much looks like a bug. There's nopthing preventing you to requery a cursor within a transaction. You nevertheless could also think about mitigating the problem by changing transaction strategy to buffering only, then only start a transaction close before saving by TABLEUPDATE.

I'll now experiment with a CA done with the builder and see if it works there, this may take a while.

Chriss

 

[Chris Miller]

One more observation: CursorGetProp("Refresh") returns -2.00 and I don't find that value explained. I don't think that's important for the issue, though.

Chriss

 

[Chris Miller]

I did get the same issue with a CA I have based on the CA builder of VFP:

**************************************************
*-- Class:        custca (c:\programming\tek-tips\projects\carefresh\cas.vcx)
*-- ParentClass:  cursoradapter
*-- BaseClass:    cursoradapter
*-- Time Stamp:   08/09/24 12:16:02 PM
*
DEFINE CLASS custca AS cursoradapter


	Tag = "C:\USERS\PUBLIC\VFP9\SAMPLES\NORTHWIND\NORTHWIND.DBC"
	Height = 22
	Width = 23
	SelectCmd = "select ADDRESS, CITY, COMPANYNAME, CONTACTNAME, CONTACTTITLE, COUNTRY, CUSTOMERID, FAX, PHONE, POSTALCODE, REGION from CUSTOMERS WHERE customerid=m.cid"
	CursorSchema = "ADDRESS C(60), CITY C(15), COMPANYNAME C(40), CONTACTNAME C(30), CONTACTTITLE C(30), COUNTRY C(15), CUSTOMERID C(5), FAX C(24), PHONE C(24), POSTALCODE C(10), REGION C(15)"
	Alias = "crscustomers"
	DataSourceType = "Native"
	Flags = 0
	SendUpdates = .T.
	WhereType = 1
	KeyFieldList = "CUSTOMERID"
	Tables = "CUSTOMERS"
	UpdatableFieldList = "ADDRESS, CITY, COMPANYNAME, CONTACTNAME, CONTACTTITLE, COUNTRY, FAX, PHONE, POSTALCODE, REGION"
	UpdateNameList = "ADDRESS CUSTOMERS.ADDRESS, CITY CUSTOMERS.CITY, COMPANYNAME CUSTOMERS.COMPANYNAME, CONTACTNAME CUSTOMERS.CONTACTNAME, CONTACTTITLE CUSTOMERS.CONTACTTITLE, COUNTRY CUSTOMERS.COUNTRY, CUSTOMERID CUSTOMERS.CUSTOMERID, FAX CUSTOMERS.FAX, PHONE CUSTOMERS.PHONE, POSTALCODE CUSTOMERS.POSTALCODE, REGION CUSTOMERS.REGION"
	UseCursorSchema = .T.
	Name = "custca"


	PROCEDURE AutoOpen
		*** Setup code: DO NOT REMOVE
		if not pemstatus(This, '__VFPSetup', 5)
			This.AddProperty('__VFPSetup', 1)
			This.Init()
		endif
		*** End of Setup code: DO NOT REMOVE
	ENDPROC


	PROCEDURE Init
		*** Setup code: DO NOT REMOVE
		local llReturn
		do case
			case not pemstatus(This, '__VFPSetup', 5)
				This.AddProperty('__VFPSetup', 0)
			case This.__VFPSetup = 1
				This.__VFPSetup = 2
			case This.__VFPSetup = 2
				This.__VFPSetup = 0
				return
		endcase
		set multilocks on
		llReturn = dodefault()
		*** End of Setup code: DO NOT REMOVE
		*** Select connection code: DO NOT REMOVE

		local lcDBC
		lcDBC = _samples+'\NORTHWIND\NORTHWIND.DBC'
		if dbused(lcDBC)
			set database to (lcDBC)
		else
			open database (lcDBC)
		endif
		*** End of Select connection code: DO NOT REMOVE

		*** Setup code: DO NOT REMOVE
		if This.__VFPSetup = 1
			This.__VFPSetup = 2
		endif
		return llReturn
		*** End of Setup code: DO NOT REMOVE
	ENDPROC


ENDDEFINE
*
*-- EndDefine: custca
**************************************************

This should work for anyone having northwind data in _samples, too. And reducing the transaction test to the minium, this shows the same behavior:

oCA = CreateObject("custca")
cid="ALFKI"
oCA.CursorFill
? cid, customerid
Begin Transaction
cid="ANTON"
oCA.CursorRefresh
? cid, customerid
End Transaction
oCA.CursorRefresh
? cid, customerid

I don't see what else to make more specific than what the CA builder generates, so if it also doesn't overcome the transaction issue. So the last hope I had that a more comprehensive CA would overcome the issue is gone now, too.

Seems you found a bug. I know transactions do limit some operations, for example you can't create a database within a transaction, but you shouldn't be limited to do basically a REQUERY of a cursor already existing, A transaction is not locking anything for yourself, it's only preventing secondary users access to data until it is finally committed by ending the transaction.

I would recommend thereofre to change transaction strategy overall. It's not recommended to have long running transactions, i.e. from APPEN BLANK/INSERT to finalization. Instead use buffering and only use transactions before TABLEUPDATE actually save changes, that separates other users from seeing the new data before its final state, too.




Chriss

 

[mjcmkrsr]

Hi Chriss,

CursorRefresh() is the bad boy and does not behave correctly (see code below)

CLEAR 
PRIVATE oCA, cID

cID = ""
oCA = .NULL.

oCA = CreateObject("custca")

cid="ALFKI"
oCA.CursorFill
? cid, customerid

Begin Transaction

cid="ANTON"

? "CursorRefresh" , oCA.CursorRefresh()
IF !(oCA.CursorRefresh())
	oCA.CursorFill
ENDIF

? cid, customerid

End Transaction

? "CursorRefresh" , oCA.CursorRefresh()
oCA.CursorRefresh
? cid, customerid 

CLOSE ALL
CLEAR ALL 
RETURN 

**************************************************
*-- Class:        custca (c:\programming\tek-tips\projects\carefresh\cas.vcx)
*-- ParentClass:  cursoradapter
*-- BaseClass:    cursoradapter
*-- Time Stamp:   08/09/24 12:16:02 PM
*
DEFINE CLASS custca AS cursoradapter


	Tag = "C:\USERS\PUBLIC\VFP9\SAMPLES\NORTHWIND\NORTHWIND.DBC"
	Height = 22
	Width = 23
	SelectCmd = "select ADDRESS, CITY, COMPANYNAME, CONTACTNAME, CONTACTTITLE, COUNTRY, CUSTOMERID ;
					from CUSTOMERS WHERE customerid = m.cid"
	CursorSchema = "ADDRESS C(60), CITY C(15), COMPANYNAME C(40), CONTACTNAME C(30), CONTACTTITLE C(30), COUNTRY C(15), CUSTOMERID C(5)"
	Alias = "crscustomers"
	DataSourceType = "Native"
	Flags = 0
	SendUpdates = .T.
	WhereType = 1
	KeyFieldList = "CUSTOMERID"
	Tables = "CUSTOMERS"
	UpdatableFieldList = "ADDRESS, CITY, COMPANYNAME, CONTACTNAME, CONTACTTITLE, COUNTRY, CUSTOMERID"
	UpdateNameList = "ADDRESS CUSTOMERS.ADDRESS, CITY CUSTOMERS.CITY, COMPANYNAME CUSTOMERS.COMPANYNAME, CONTACTNAME CUSTOMERS.CONTACTNAME, ;
					CONTACTTITLE CUSTOMERS.CONTACTTITLE, COUNTRY CUSTOMERS.COUNTRY, CUSTOMERID CUSTOMERS.CUSTOMERID"
	UseCursorSchema = .T.
	Name = "custca"


	PROCEDURE AutoOpen
		*** Setup code: DO NOT REMOVE
		if not pemstatus(This, '__VFPSetup', 5)
			This.AddProperty('__VFPSetup', 1)
			This.Init()
		endif
		*** End of Setup code: DO NOT REMOVE
	ENDPROC


	PROCEDURE Init
		*** Setup code: DO NOT REMOVE
		local llReturn
		do case
			case not pemstatus(This, '__VFPSetup', 5)
				This.AddProperty('__VFPSetup', 0)
			case This.__VFPSetup = 1
				This.__VFPSetup = 2
			case This.__VFPSetup = 2
				This.__VFPSetup = 0
				return
		endcase
		set multilocks on
		llReturn = dodefault()
		*** End of Setup code: DO NOT REMOVE
		*** Select connection code: DO NOT REMOVE

		local lcDBC
		lcDBC = _samples+'\NORTHWIND\NORTHWIND.DBC'
		if dbused(lcDBC)
			set database to (lcDBC)
		else
			open database (lcDBC)
		endif
		*** End of Select connection code: DO NOT REMOVE

		*** Setup code: DO NOT REMOVE
		if This.__VFPSetup = 1
			This.__VFPSetup = 2
		endif
		return llReturn
		*** End of Setup code: DO NOT REMOVE
	ENDPROC


ENDDEFINE
*
*-- EndDefine: custca
**************************************************

hth

MarK

 

[Chris Miller]

Thanks, Mark,

I didn't even look at the return value of CursorRefresh().

The help tells when CursorRefresh() returns .F. you can get error information from AERROR(), and when doing you get error 1593 "Command cannot be issued within a transaction"

Makes me wonder which command. If this is about the CursorRefresh method in general, shouldn't it have a special error number.

You get 0 returned from REQUERY("demo") and it's not documented that in this case AERROR tells anything more about why you get 0. But doing AERROR you get the same error. I checked whether this is just still the error from CursorRefresh by doing something else that errors. So REQUERY() also seems to be forbidden within transactions.

Ah, it's mentioned in the help of BEGIN TRANSACTION, the forbidden commands and functions include REQUERY(). It's even among the list of generally forbidden functions, not only when acting on the tables that participate in the transaction already by having a modification. The cursor is not modified yet.

Okay, for whatever reason that is, A CursorRefesh is basically a REQUERY and actually a REQUERY also triggers the BeforeCursorRefresh event by the adaptive nature of the CursorAdapter class.

That explains it.

Chriss

 

[xinjie]

Hi, Chriss

Thanks again!