locked
REFRESH() Function and RecordRefresh() Does Not Refresh Memo? RRS feed

  • Question

  • Hello,

    I'm using:

    VFP9 SP2 + hot fixes
    CursorAdapter retrieving SQL Server 2012 data via the SQL Server Native Client v11.0 ODBC driver

    I'm finding that when I use either REFRESH(1,0,Alias) or CursorAdapter.RecordRefresh(1), CURVAL(Field,Alias) will return the current field contents from SQL Server, as is documented, for all data types other than memo.

    For memo fields, CURVAL() returns an empty string "" after issuing a REFRESH().

    Can anyone confirm this?

    Thanks.


    Wednesday, April 23, 2014 6:58 PM

Answers

  • The BEGIN TRANSACTION is what was causing REFRESH()/CURVAL() to return "" for memo fields.

    This link exlains why I was using both a VFP transaction as well as a SQL transaction: http://msdn.microsoft.com/en-us/library/ms947432.aspx

    Since all of my data is now in SQL, I think I can remove the VFP transaction altogether, because while calling ROLLBACK does make REFRESH()/CURVAL() work for memo fields, it causes problem with other data types.

    If I do not wrap SQL TABLEUPDATE() in a VFP transaction, everything works as documented.

    Thanks for your help Olaf!

    Bill

    Monday, May 5, 2014 11:50 PM

All replies

  • I have to prepare several things to confirm. For a similar reason I rather use SQL Server ODBC driver instead of Native Client. Varchar(MAX) fields return as C(0) fields in SPT SqlExec with other drivers.

    It may be a bug of native client, you may have a chance, if setting cursoradapter.fetchmemo = .t.

    I guess the fetchmemo setting is making use of some odbc feature not supported by Native Client, at least the problem of memo fields fetched wrong is nothing new and can be overcome using other drivers, so that rather is a ODCB driver issue than a VFP issue.

    Bye, Olaf.


    Olaf Doschke - TMN Systemberatung GmbH

    http://www.tmn-systemberatung.de

    Wednesday, April 23, 2014 7:45 PM
  • Yes, I understand what you're saying about the VARCHAR(MAX) memo issue (which is resolved with explicit data types in the CursorSchema property), and I have had various other data type issues with other versions/drivers, so I have to stick w/ the SQL Server Native Client for ODBC.

    Other than the REFRESH() issue, my cursoradapter is functioning properly (query/insert/update/delete), so I was hoping to confirm before I go to writing custom code to check for memo field contents outside of what REFRESH() does for me.

    And, yes, my FetchMemo = .T.

    Thanks.


    Wednesday, April 23, 2014 8:11 PM
  • OK, now I setup a sql2012 test database, test table, the ODBC Native Client 11 driver and a cursoradapter, and that works, both REQUERY(ca.alias) and ca.RecordRefresh refresh the memo.

    That is, I change data in SQL Server Management Studio in the table and refresh the CA cursor.

    DEFINE CLASS caTest AS cursoradapter
    	Tag = "Driver={SQL Server Native Client 11.0};Server=(local);Database=...;Trusted_Connection=yes;"
    	Height = 22
    	Width = 23
    	SelectCmd = "Select id, mText from tabMemo"
    	CursorSchema = "ID I, MTEXT M"
    	Alias = "curTest"
    	DataSourceType = "ODBC"
    	Flags = 0
    	WhereType = 1
    	KeyFieldList = "ID"
    	Tables = "tabMemo"
    	UpdatableFieldList = "MTEXT"
    	UpdateNameList = "ID tabMemo.id, MTEXT tabMemo.mText"
    	UseCursorSchema = .T.
    	FetchMemoCmdList = ""
    ENDDEFINE

    These are the settings, on top of that Init and AutoOpen code also is generated by the CA Builder, but that's just standard code, eg using the Connectionstring show here as tag property value in the init to set the DataSource.

    As you see I didn't even set the FetchMemoCmdList nor programmed the DelayedMemoFetch method.

    Bye, Olaf.

    Edit: The table schema is id int identity and mText Varchar(MAX).
    Edit2: Refresh() works, too. Are you not seeing updated form controls? They may need an additional control.refresh() or thisform.refresh() before showing the refreshed cursor memo field.


    Olaf Doschke - TMN Systemberatung GmbH

    http://www.tmn-systemberatung.de

    Friday, April 25, 2014 9:16 AM
  • Hello Olaf!

    Thank you for your time and effort on this. I can also confirm the same. I  mocked up a simple test with a VFP baseclass and found both the REFRESH() function and RecordRefresh() method work as advertised.

    In my application, which we are currently re-designing to use SQL data rather than VFP data, they behave as I originally posted. So now I just need to track down what's getting in the way.

    Even more interesting, I'm seeing different results in my app depending on when the function/method is called.

    My original post was describing the behavior after an update conflict error is returned by SQL, but if I call the function/method without first having an update conflict, both CURVAL() and OLDVAL() return what is currently in SQL.

    I'll post my findings when I get it worked out.

    Bill

    Friday, April 25, 2014 3:59 PM
  • OK, after an update conflict I guess I know the reason for VFP refusing to refresh is the simple case of not allowing requeries until the buffer is clean. You have to use setfldstate then and set the field to unchanged instead of refreshing it with the new current value. It doesn't matter, if the memo get's the curval, what matters is the field state. After resolving the update conflict by flagging the memo as unchanged, the update will work.

    You can also force the new (and conflicting) value, when using the force parameter of the TABLEUPDATE function.

    And after that you can refresh the record with the changes by the other user(s).

    Bye, Olaf.


    Olaf Doschke - TMN Systemberatung GmbH

    http://www.tmn-systemberatung.de

    Friday, April 25, 2014 5:52 PM
  • I've been able to narrow down the cause, at least in my app.

    When I issue:

    BEGIN TRANSACTION
    SQLSetProp(DataSrc,"TRANSACTIONS",DB_TRANSMANUAL)

    immediately before a:

    TABLEUPDATE(1,.T,.F)

    And TABLEUPDATE() fails because of an update conflict, REFRESH() causes CURVAL() to return "" for memo fields instead of the current field value in SQL.

    Olaf, would you be able to confirm on your side?

    Our application framework uses CURVAL() to compare what changes were made by other users so we can inform the user dealing with an update conflict what the values are that were "saved" before they did.

    Thanks.

    Bill


    Monday, May 5, 2014 10:24 PM
  • The BEGIN TRANSACTION is what was causing REFRESH()/CURVAL() to return "" for memo fields.

    This link exlains why I was using both a VFP transaction as well as a SQL transaction: http://msdn.microsoft.com/en-us/library/ms947432.aspx

    Since all of my data is now in SQL, I think I can remove the VFP transaction altogether, because while calling ROLLBACK does make REFRESH()/CURVAL() work for memo fields, it causes problem with other data types.

    If I do not wrap SQL TABLEUPDATE() in a VFP transaction, everything works as documented.

    Thanks for your help Olaf!

    Bill

    Monday, May 5, 2014 11:50 PM
  • Actually that FoxTalk article does explicitly warn, a VFP transaction wrapping update of a remote view (or updatable SPT cursor) will only handle VFP side changes, eg ROLLBACK reverts the cursor, but not the updated remote tables.

    But that doesn't explain why CURVAL() would return empty memo from the SQL Server.

    Bye, Olaf.

    Edit: This all may be related to the isolation level of SQL Server transactions.

    You might have another client blocking even read access to tables within the other clients transactions. Check out how the isolation level is set on the SQL Server in question. As you want to use CURVAL to tell the client what another user has changed you might want READ UNCOMMITED, to even see changes of a concurrent, yet not commited transaction. You may also want READ COMMITED, to only see changes another client has made AND commited already, but you see concurrent transactions are then having the risc neither client sees the conflict before committing.

    There's more to it than VFP transactions do. Also note, you should only start a transaction right before saving changes. Otherwise concurrent transactions become the usual case and cause more trouble than they prevent - especially deadlocks.

    An empty curval may be returned in situations where SQL Server has already made uncommited changes and is in READ COMMITED mode. You don't get a dirty read, but you also don't get the snapshot value that existed at the begin of your transaction.


    Olaf Doschke - TMN Systemberatung GmbH

    http://www.tmn-systemberatung.de


    Tuesday, May 6, 2014 5:59 AM