×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Converting Set Filters to Remote Views

Converting Set Filters to Remote Views

Converting Set Filters to Remote Views

(OP)
Hi,

Using remote views, what does the syntax look like to convert "set filters" to something more for remote views and how are they tied to the form's datasource?

This code is in a combobox where the user selects what is to be shown.

CODE -->

Case This.DisplayValue = 'Available'
	Set Filter To rv_list.registered <> 1
	Go Top

Case This.DisplayValue = 'MeterLocked'
	Set Filter To rv_list.meterlocked = 1
	Go Top

Case This.DisplayValue = 'Publicly List'
	Set Filter To rv_list.publicly_list = 1
	Go Top 

Thanks, Stanley

RE: Converting Set Filters to Remote Views

You need to parameterize the remote view. Try to forget about using SET FILTER - it is a notoriously flaky command.

Example of parameterized view:

CREATE SQL VIEW rv_Customers ;
REMOTE CONNECTION Northwind SHARE ;
AS SELECT * FROM Customers ;
WHERE customerid LIKE ?lcCustomerID

lcCustomerID is a memvar

RE: Converting Set Filters to Remote Views

Also, you should consider dumping remote views for a combination of pass-through and CursorAdapters. Both have major advantages over remote views. For example, say you want to automatically get the primary key on an insert (SQL @@IDENTITY is sooo retro... ):

CODE -->

lcCmdIns = "INSERT INTO lbdocument (ikey_folder, ckey_entity, ckey_iv, cfiletype, vheader) " + ;
                           "OUTPUT inserted.ikey_document AS ikey_document " + ;
                           "VALUES (?liKeyFolder, ?tcKeyEntity, ?lcKeyIV, ?lcFileType, ?lcHeader)"

IF SQLEXEC(liHandle, lcCmdIns, "cur_temp") <> 1
   SQLROLLBACK(liHandle)
   SQLDISCONNECT(liHandle)
   RETURN -1
ENDIF

liKeyDocument = cur_temp.ikey_document 

CursorAdapters are an extremely powerfull and flexible "hook fest". I don't understand why more people don't use them. There is a bit of a learning curve. Best way is to create a PRG template of a CursorAdapter class and then fill in the properties:

LOCAL p_folderkey, loCAD

p_folderkey = 1000000001
loCAD = CREATEOBJECT("cad_headeralbum")
loCAD.DataSource = SQLSTRINGCONNECT(loCAD.ConnectString)
loCAD.CursorFill(.T., .F.) && Note that the BeforeCursorFill event hook (see below) is called before CursorFill

CODE -->

DEFINE CLASS cad_headeralbum AS CursorAdapter

Alias                  = "cur_headeralbum"
AllowSimultaneousFetch = .T.
BatchUpdateCount       =  1
BufferModeOverride     =  5
CompareMemo            = .F.
ConflictCheckType      =  3
ConnectString          = "Driver={SQL Server Native Client 11.0};Server=(local)\LipBits;Database=LipBitsMusic;Trusted_Connection=Yes"
ConversionFunc         = ""
CursorSchema           = "ikey_album i, ikey_folder i, valbum c(100), vartist c(100), vgenre c(50), iyear i, lhasbeenflag l, balbumartlg m"
DataSourceType         = "ODBC"
FetchAsNeeded          = .F.
FetchMemo              = .T.
FetchSize              = -1
KeyFieldList           = ""
MapBinary              = .T.
MapVarchar             = .T.
MaxRecords             = -1
Name                   = "cad_headeralbum"
NoData                 = .T.
RefreshCmd             = ;
"SELECT lbmusic.ikey_album, lbmusic.ikey_folder, lbalbum.valbum, lbartist.vartist, lbgenre.vgenre, lbyear.iyear, lbalbum.lhasbeenflag, lbalbum.balbumartlg " + ;
  "FROM lbalbum " + ;
  "JOIN lbartist ON lbartist.ikey_artist = lbalbum.ikey_artist " + ;
  "JOIN lbgenre ON lbgenre.ikey_genre = lbalbum.ikey_genre " + ;
  "JOIN lbyear ON lbyear.ikey_year = lbalbum.ikey_year " + ;
  "JOIN lbmusic ON lbmusic.ikey_album = lbalbum.ikey_album " + ;
 "WHERE lbmusic.ikey_folder = ?p_folderkey " + ;
 "GROUP BY lbmusic.ikey_album, lbmusic.ikey_folder, lbalbum.valbum, lbartist.vartist, lbgenre.vgenre, lbyear.iyear, lbalbum.lhasbeenflag, lbalbum.balbumartlg"
SelectCmd              = ;
"SELECT lbmusic.ikey_album, lbmusic.ikey_folder, lbalbum.valbum, lbartist.vartist, lbgenre.vgenre, lbyear.iyear, lbalbum.lhasbeenflag, lbalbum.balbumartlg " + ;
  "FROM lbalbum " + ;
  "JOIN lbartist ON lbartist.ikey_artist = lbalbum.ikey_artist " + ;
  "JOIN lbgenre ON lbgenre.ikey_genre = lbalbum.ikey_genre " + ;
  "JOIN lbyear ON lbyear.ikey_year = lbalbum.ikey_year " + ;
  "JOIN lbmusic ON lbmusic.ikey_album = lbalbum.ikey_album " + ;
 "WHERE lbmusic.ikey_folder = ?p_folderkey " + ;
 "GROUP BY lbmusic.ikey_album, lbmusic.ikey_folder, lbalbum.valbum, lbartist.vartist, lbgenre.vgenre, lbyear.iyear, lbalbum.lhasbeenflag, lbalbum.balbumartlg"
SendUpdates            = .F.
Tables                 = ""
TimestampFieldList     = ""
UpdatableFieldList     = ""
UpdateNameList         = ""
UpdateType             =  1
UseCursorSchema        = .T.
UseMemoSize            =  255 
UseTransactions        = .F.
WhereType              =  3

PROCEDURE AfterCursorAttach
LPARAMETERS tcAlias AS String, tlResult AS Logical
ENDPROC

PROCEDURE AfterCursorClose
LPARAMETERS tcAlias AS String, tlResult AS Logical
ENDPROC

PROCEDURE AfterCursorFill
LPARAMETERS tlUseCursorSchema AS Logical, tlNoDataOnLoad AS Logical, tcSelectCmd AS String, tlResult AS Logical
ENDPROC

PROCEDURE AfterCursorUpdate
LPARAMETERS tiRows AS Integer, tlTableUpdateResult AS Logical, tuErrorArray AS Variant
ENDPROC

PROCEDURE AfterDelete
LPARAMETERS tcFldState AS String, tlForce AS Logical, tcDeleteCmd AS String, tlResult AS Logical
ENDPROC

PROCEDURE AfterInsert
LPARAMETERS tcFldState AS String, tlForce AS Logical, tcInsertCmd AS String, tlResult AS Logical
ENDPROC

PROCEDURE AfterUpdate
LPARAMETERS tcFldState AS String, tlForce AS Logical, tiUpdateType AS Integer, tcUpdateInsertCmd AS String, tcDeleteCmd AS String, tlResult AS Logical
ENDPROC

PROCEDURE BeforeCursorAttach
LPARAMETERS tcAlias AS String
ENDPROC

PROCEDURE BeforeCursorClose
LPARAMETERS tcAlias AS String
ENDPROC

PROCEDURE BeforeCursorFill
LPARAMETERS tlUseCursorSchema AS Logical, tlNoDataOnLoad AS Logical, tcSelectCmd AS String
tcSelectCmd = "SELECT lbmusic.ikey_album, lbmusic.ikey_folder, lbalbum.valbum, lbartist.vartist, lbgenre.vgenre, lbyear.iyear, lbalbum.lhasbeenflag, lbalbum.balbumartlg " + ;
                "FROM lbalbum " + ;
                "JOIN lbartist ON lbartist.ikey_artist = lbalbum.ikey_artist " + ;
                "JOIN lbgenre ON lbgenre.ikey_genre = lbalbum.ikey_genre " + ;
                "JOIN lbyear ON lbyear.ikey_year = lbalbum.ikey_year " + ;
                "JOIN lbmusic ON lbmusic.ikey_album = lbalbum.ikey_album " + ;
               "WHERE lbmusic.ikey_folder = ?p_folderkey " + ;
            "GROUP BY lbmusic.ikey_album, lbmusic.ikey_folder, lbalbum.valbum, lbartist.vartist, lbgenre.vgenre, lbyear.iyear, lbalbum.lhasbeenflag, lbalbum.balbumartlg"
ENDPROC

PROCEDURE BeforeCursorRefresh
LPARAMETERS tcSelectCmd AS String
ENDPROC

PROCEDURE BeforeCursorUpdate
LPARAMETERS tiRows AS Integer, tlForce AS Logical
ENDPROC

PROCEDURE BeforeDelete
LPARAMETERS tcFldState AS String, tlForce AS Logical, tcDeleteCmd AS String
ENDPROC

PROCEDURE BeforeInsert
LPARAMETERS tcFldState AS String, tlForce AS Logical, tcInsertCmd AS String
ENDPROC

PROCEDURE BeforeUpdate
LPARAMETERS tcFldState AS String, tlForce AS Logical, tiUpdateType AS Integer, tcUpdateInsertCmd AS String, tcDeleteCmd AS String
ENDPROC

PROCEDURE Destroy
ENDPROC

PROCEDURE Init
ENDPROC

PROCEDURE Error(tnError AS Integer, tcMethod AS String, tnLine AS Integer)
ENDPROC

PROCEDURE Release
   SQLDISCONNECT(This.DataSource)
   RELEASE This
ENDPROC

ENDDEFINE 

Here is the result of the album cursor:


RE: Converting Set Filters to Remote Views

(OP)
Hi Vernpace,

Looks good...

Quote (Vernpace)


I don't understand why more people don't use them.

Thats easy... Too many options that makes it too complex, at least for me, given all the nightmares I've had with remote views. I've had the best experience with SPT, but is extremely counterproductive as everything has to be built up, can't drag and drop for forms and grids, and more.

I do like your version into a prg and that looks similar to filling out a property sheet. I would like to implement them, but don't have weeks to learn and implement, before being pulled off to deal with another fire... repeat, so my time is too fragmented. I will be looking at it closer and will start my 4th go around/attempt with them. If you look at my previous posts here on Tek-Tips over the years, you will see my attempt to get them working.

And there is all this talk about mixing technologies (views, spt and ca) that complicates things.

So how does one wireup a simple form using your prg based class? And how is the next and previous navigation skipping work? What goes in the next button? I have all of Doug's auticals and have attempted to use them in the past. Where would start?

Thanks,
Stanley

RE: Converting Set Filters to Remote Views

(OP)
Hi Vernpace,

CODE -->

CREATE SQL VIEW rv_Customers ;
REMOTE CONNECTION Northwind SHARE ;
AS SELECT * FROM Customers ;
WHERE customerid LIKE ?lcCustomerID

lcCustomerID is a memvar 

Where does this all go, and how are nav buttons wired? In a button, form's init or load

If the where criteria changes, do you scrap the whole view and build a new one and somehow make all the controlsources lineup.

Thanks,
Stanley

RE: Converting Set Filters to Remote Views

Quote (Stanlyn)

Where does this all go, and how are nav buttons wired?

The view definition goes before anything else, once, it's a view design step and the result is having a view with parameters.

To use the view it's parameters must be given, so a view like that can only go into a forms DE if you set it to not query anything and wait for that until the form establishes the view parameters. That is possible, as you can set a DE object to nodataonload=.T. (notice that property is fixed for tables, this only is available for views).

And then you will not know parameters before a) they are passed in to the form init or b) a user interactively enters or picks them.

In the example of a view that has a customerid as parameter, well, the user either picks the customer in a form running previous to this one and that call this form and passes in a customerid, so the init can then set lcCustomerID and the requery() the view. Or the user picks the customer in the form itself, but then the view will only be available to use when that's picked.

There are several situations where you could know a parameter in advance, choose to always use the maximum ID, or whatever, but I don't understand how you think the CREATE SQL VIEW has to go anywhere in the form. You of course define the view outside of the form, a view is a static part of the database, what has to change is only the parameters.

All that said, I don't even know if view parameters would help you with the tableupdate problem at all. They are a solution to not needing individually built queries. No more, no less. And, well, they unbind you from using SET FILTER. That's a performance improvement, and surely it is a necessary improvement if instead you'd always need to query all data and then SET FILTER to it. It's just saving a waste of bandwidth usage for data you filter out after you already queried it.

Think about SET FILTER, it works fine with the USE of a table, as you don't load data when you USE a table, you only load data when a grid or browse fills and that only loads as much as it needs to fill in the visible area. And then you use the filter while querying. But a view doesn't work like that. When you query a view you query all of its data, no matter if a browse or grid only needs 5 rows of it. So you load a lot of data you then actually don't want. That's the reason to parameterize views. And it's not a suggestion you could do, it's a really strong reason to let things work at all. Otherwise this will only be fine as long as your amount of data is low.

Chriss

RE: Converting Set Filters to Remote Views

SET FILTER is not flaky if you know how to use it. However, views are a bad design. If it is in the shared DBC, every user the uses that view gets a little hit. They can add up until you have several users waiting on the view.

The cursor adapter is very powerful, but vern has made it simpler. Needing the grid to populate the controlsources is a crutch. It's not that hard to do it yourself.

USE does cache data when the table opens. Depending on SET DELETED it also moves to the first undeleted record.

Use TEXT...ENDTEXT to format your queries. Then you can test them more easily.

TEXT TO tcSelectCmd TEXTMERGE NOSHOW
SELECT lbmusic.ikey_album, lbmusic.ikey_folder, lbalbum.valbum, lbartist.vartist, lbgenre.vgenre, lbyear.iyear, lbalbum.lhasbeenflag, lbalbum.balbumartlg ;
FROM lbalbum ;
JOIN lbartist ON lbartist.ikey_artist = lbalbum.ikey_artist ;
JOIN lbgenre ON lbgenre.ikey_genre = lbalbum.ikey_genre ;
JOIN lbyear ON lbyear.ikey_year = lbalbum.ikey_year ;
JOIN lbmusic ON lbmusic.ikey_album = lbalbum.ikey_album ;
WHERE lbmusic.ikey_folder = ?p_folderkey ;
GROUP BY lbmusic.ikey_album, lbmusic.ikey_folder, lbalbum.valbum, lbartist.vartist, lbgenre.vgenre, lbyear.iyear, lbalbum.lhasbeenflag, lbalbum.balbumartlg
ENDTEXT

If you're sending this query to FoxPro you'll likely want to leave semicolons as I show above. If you're sending it to SQL Server you would not need the semicolons.

We have a little utility to get rid of carriage returns, line feeds, tabs etc, which are not needed by Fox or SQL. The utility is used like this (after the above):

tcSelectCmd=x7RemoveCRLFTAB(m.tcSelectCmd,.T.)

If you're sending the query to Fox then you'll want to drop semicolons as well, so you'd do this instead:

tcSelectCmd=x7RemoveCRLFTab(m.tcSelectCmd,.T.,';')

Here's the code:

CODE -->

*----------------------------------------------------------------
* Program: X7RemoveCRLFTAB.PRG
*
* Removes Carriage Returns/CHR(13), Line Feeds/CHR(10), 
* and TABs/CHR(9) from the passed string, and RETURNs 
* the resulting string.
*
* Also removes all leading spaces from the passed string
*
* Author:  Drew Speedie  
*
* USAGE
* ==================================================
* lcSomeString = X7RemoveCRLFTAB(m.lcSomeString)
*
* LOCAL lcSQL
* TEXT TO lcSQL NOSHOW
* SELECT Inv_PK, Inv_Number, Inv_CusFK, Cus_Name, 
*        Inv_ShipDate, Inv_Total, Inv_PO 
*   FROM Invoices 
*   INNER JOIN Customers ON Inv_CusFK = Cus_PK 
* ENDTEXT
* * Remove CRs, LFs, and TABs, and ensure that there
* * is a SPACE(1) between each existing line in the
* * resulting single continuous string; note that, 
* * while SQL Server can handle CRs and LFs embedded
* * in the string, VFP does not, and generates an 
* * error
* lcSQL = X7RemoveCRLFTAB(m.lcSQL, .T.)
*
* Parameters:
*             tcString (R) String whose CRs, LFs, and TABs
*                          are to be removed, along with
*                          any leading spaces
*
* tlSpaceAfterEachLine (O) If tcString consists of more than
*                          one line ending in a CRLF (as in 
*                          the above USAGE example) and this 
*                          parameter is passed as .T., the 
*                          CRLF is removed from the end of 
*                          each line as it is added to the
*                          single continuous string, with a
*                          SPACE(1) inserted between each
*                          line; BUT NO SPACE(1) IS ADDED TO
*                          THE VERY END OF THE RETURNED STRING
*
* tcOtherCharsToRemove (O) String of additional characters
*                          that are removed
*
*                          NOTE that any spaces included in this
*                          this string are IGNORED; spaces are
*                          NOT removed from the passed string
*
* tlReplaceCRLFTabWithSpace (O) If set to .T., replaces CR, LF
*                               and <Tab> with SPACE(1) in lieu
*                               SPACE(0), which removes them.
*----------------------------------------------------------------

#IF VERSION(5)>600
LPARAMETERS tcString                  AS String	,	;
            tlSpaceAfterEachLine      AS Boolean,	;
            tcOtherCharsToRemove      AS String	,	;
            tlReplaceCRLFTabWithSpace AS Boolean
#ELSE
LPARAMETERS tcString,tlSpaceAfterEachLine,tcOtherCharsToRemove,tlReplaceCRLFTabWithSpace
#ENDIF

IF NOT VARTYPE(m.tcString) = 'C' 
  ASSERT .F. message PROGRAM() + ;
       ' has not been passed the required tcString parameter as a character string'
ENDIF

LOCAL llReplaceCRLFTabWithSpace
IF      (PCOUNT() = 4	                         )	;
    AND (VARTYPE(tlReplaceCRLFTabWithSpace) = 'L')
  llReplaceCRLFTabWithSpace = tlReplaceCRLFTabWithSpace
ELSE
  llReplaceCRLFTabWithSpace = .F.
ENDIF

LOCAL lcString
IF (VARTYPE(m.tlSpaceAfterEachLine) = 'L') AND m.tlSpaceAfterEachLine
  lcString = SPACE(0)
  LOCAL laLines[1], lcLine
  ALINES(laLines, m.tcString, .T.)
  IF ALEN(laLines, 1) = 1
    * tcString doesn't consist of multiple CRLF-
    * separated lines; don't add an additional SPACE(1)
    lcString = m.laLines[1]
  ELSE
    FOR EACH lcLine IN m.laLines
      lcString = m.lcString + m.lcLine + SPACE(1)
    ENDFOR  

*!*	    * Remove the final SPACE(1)
*!*	    lcString = ALLTRIM(m.lcString)
  ENDIF
ELSE
  lcString = m.tcString
ENDIF

* Additional ALLTRIM() here to remove any leading spaces [as well as the final SPACE(1)]
lcString = ALLTRIM(m.lcString)

* AB - 8/7/2006 - Start
*!*	* Remove CRs, LFs, and TABs
*!*	lcString = CHRTRANC(m.lcString, CHR(13) + CHR(10) + CHR(9), SPACE(0))

IF llReplaceCRLFTabWithSpace
  * Replace CRs, LFs and TABs with one blank space
  lcString = CHRTRANC(m.lcString, CHR(13) + CHR(10) + CHR(9), REPLICATE(SPACE(1), 3))
ELSE
  * Remove CRs, LFs, and TABs
  lcString = CHRTRANC(m.lcString, CHR(13) + CHR(10) + CHR(9), SPACE(0))
ENDIF
* AB - 8/7/2006 - End

* Remove any other characters you have specified in the tcOtherCharsToRemove parameter
IF (VARTYPE(m.tcOtherCharsToRemove) = 'C') AND (NOT EMPTY(m.tcOtherCharsToRemove))
  LOCAL lcChars
  * First, remove any embedded spaces
  lcChars = CHRTRANC(ALLTRIM(m.tcOtherCharsToRemove), SPACE(1), SPACE(0))

  * Now remove all the specified characters
  lcString = CHRTRANC(m.lcString, m.lcChars, SPACE(0))
ENDIF

RETURN m.lcString 


Your next/prev buttons should just SKIP in the cursor created "cur_headeralbum"

IF NOT EOF("cur_headeralbum")
skip
ENDIF

Mike Yearwood - Former Microsoft Visual FoxPro MVP award winner. TWICE

RE: Converting Set Filters to Remote Views

Stanlyn,

As Mike indicated, the cursor cur_headeralbum is created from the cad_headeralbum CursorAdapter object. And yes, TEXT...ENDTEXT is the better way to go :>. Once you have the cursor, you can bind it anyway you want.

Using CADs can force a rethink about how to construct data environments. I agree with Mike that DBCs are problematic. In the old days, they were cool for visual representation... but not practical IMHO. With CADs, you have an opportunity and the flexibility to construct your own data environments. You might ask how? I'll leave it to your imagination. The most fun and challenging thing about software developement is design. It will keep you up at night, but it's very rewarding.

RE: Converting Set Filters to Remote Views

Quote (myearwood)

every user the uses that view gets a little hit. They can add up until you have several users waiting on the view.
The common solution to that is having a local view DBC, so nobody has to wait for others to get t a view (see threads on ERROR 1709, also see https://jeffpar.github.io/kbarchive/kb/170/Q170917...)

But don't worry, we're on the same page that CAs are the better option. You can do everything views can do and more.

Chriss

RE: Converting Set Filters to Remote Views

I cannot imagine two users attempting to execute a query should require a lock on a dbc record. Yes the local dbc gets around that. Drew Speedie built a set of middle-tier objects so I never got into the CAs.

Mike Yearwood - Former Microsoft Visual FoxPro MVP award winner. TWICE

RE: Converting Set Filters to Remote Views

Quote (myearwood)

I cannot imagine two users attempting to execute a query should require a lock on a dbc record.

But that's how they designed it. It's the access to a database object, the record of the view in the DBC, that can cause the lock contention on the DBC. Even though there's nothing to be locked when reading a query to execute it. AS the KB article says it's just a matter of a RETRY to get to the view, but if that's a local DBC there only is one user and so locks work right away.

One way I used to deploy view DBCs is embedding them in the EXE and let them be copied out at start.

I think the DBC object lock is not only limited to views, it happens at any use of a dbc object. I also don't get why they designed it that way, as the main access is read only, there's no need for a lock and the lock contention that could occur. Another reason, I guess, why people also still prefer free DBFs.

It's a sidetrack anyway. Stanlyn's problem is not accessing/opening/using the view but view design with parameterization.

Chriss

RE: Converting Set Filters to Remote Views

Ah yes, middle-tier objects - otherwise known as business objects: COM+ SOAP webservice dlls running on MS Server 2000. Actually, some of these were designed like CAs with before and after hooks. Too bad MS cut VFP from that technology...

RE: Converting Set Filters to Remote Views

The retry could also end up with a series of people lined up waiting to run the query. I bet it was to prevent one person changing the view while another wanted to run it.

I use something like the

CursorSchema = "ikey_album i, ikey_folder i, valbum c(100), vartist c(100), vgenre c(50), iyear i, lhasbeenflag l, balbumartlg m"

to create a local cursor instead of hitting the backend for it when the form starts.

Mike Yearwood - Former Microsoft Visual FoxPro MVP award winner. TWICE

RE: Converting Set Filters to Remote Views

(OP)
Hi,

Quote (Chriss)


I don't understand how you think the CREATE SQL VIEW has to go anywhere in the form.
Because the data has to be bound to the form's controls.

Quote (Chriss)


they unbind you from using SET FILTER.

So, instead the code

CODE -->

Case This.DisplayValue = 'Available'
  Set Filter To rv_list.registered <> 1
  Go Top

* would become 

Case This.DisplayValue = 'Available'
  select * from rv_list where registered <> 1
  =requery('rn_list')
  Go Top 

Quote (Chriss)


When you query a view you query all of its data
Please a yes, no, or other answer, then elaborate if needed.

Does this mean the data in the remote view, or the underlying SQL data that makes up the view, as they can be different, as another user could have made and saved their changes to SQL and your view may not be reflecting them? So, when you query the view, does the query only look at the data in the view and not the sql table?

Any I understanding this correctly, yet?

Quote (Mike)


SET FILTER is not flaky
I use it very extensively and never had an issue with it, and its fast enough for most things. On large data sets, I will use an index, if speed becomes an issue.

Stanley


RE: Converting Set Filters to Remote Views

(OP)
Hi,

CODE -->

CREATE SQL VIEW rv_Customers ;
REMOTE CONNECTION Northwind SHARE ;
AS SELECT * FROM Customers ;
WHERE customerid LIKE ?lcCustomerID 

Is this correct...

1. from outside a form and within the command window or view designer I run the code shown code to create a view named 'rv_Customers'

2. create a form and add the view into the form's DE

3. add controls to the form from the view as the proper binding occurs

When running the form

4. in the form's load or init, set lcCustomerID = '' which should load nothing

5. once the form loads the user will make a selection on what fields they wish to search. Then using the combobox's selected displayvalue we can popup an input box to take user input to create the query.

Combobox lostfocus code...

CODE -->

Case This.DisplayValue = 'Customer ID'
  lcCustomerID = upper(allt(inputbox('Enter CompanyID', 'Select by Company ID')))
    *old set filter way * Set Filter To upper(allt(rv_list.company_id)) = lcCustomerID
  
  select * from rv_Customers WHERE customerid LIKE ?lcCustomerID
  =requery('rv_Customers')
  Go Top

Case This.DisplayValue = 'Customer Name'
  lcCustomerName = upper(allt(inputbox('Enter Company Name', 'Select by Company Name')))
    *old set filter way * Set Filter To upper(allt(rv_list.company_name)) = lcCustomerName
  
  select * from rv_Customers WHERE customer_name LIKE ?lcCustomerName
  =requery('rv_Customers')
  Go Top

Case This.DisplayValue = 'Full Text'
  lcFullText = upper(allt(inputbox('Enter Text', 'Full Text Serach')))
    *old set filter way * Set Filter To lcFullText $ rv_list.company_name ;
                           .or. lcFullText $ rv_list.comments ;
                           .or. lcFullText $ rv_list.address
   
  select * from rv_Customers WHERE contains(company_name, ?lcFullText)
    .or. contains(comments, ?lcFullText)
    .or. contains(address, ?lcFullText)
  =requery('rv_Customers')
  Go Top

....... 

I know I'm missing something as the created view doesn't know anything about the other parameters, (lcFullText and lcCompanyName) I used above.

Now for the nav button code, will it be as simple as skip, skip -1, go top, and go bottom followed by a requery?

Note that many times a user doesn't know exactly how its in the system, so most times the user will need to see rows before an exact match and rows after the match, then land them on their match. They can go prev and see prior records. I also factor in the sort order as selected from a similar combobox.

Thanks,
Stanley

RE: Converting Set Filters to Remote Views

So I quess that parameterized remote views are out of the question for you. To bad - they have been used by VFP devs for years. Instead of going around in circles with this, why don't you put your head down, practice a little trial and error until you get it. I have found that to be the best way to learn. You previously implied that you have no time to learn new things. How unfortunate - what a pity.

RE: Converting Set Filters to Remote Views

(OP)
Nike,

Quote (Mike)


Use TEXT...ENDTEXT
Been using this for years with SPT and was thinking the PreText option does most of the things that your utility does, except your utility extends it greatly.

Quote (Vernpace)

cur_headeralbum = cursor
cad_headeralbum = cursor adapter ?? (the D part)

Stanely





RE: Converting Set Filters to Remote Views

(OP)
Hi,

More info has surfaced related to the cmdApply.click event and could be related.

Start VFP, load single view form and navigate to a record via skip
Change a single string/char value and click cmdApply

In the code below...
The tableupdate() succeeds with .T.
The requery() fails, see image below.
The underlying SQL table WAS updated correctly.

CODE -->

With Thisform As Form
	Select 'rv_Domain'

	lnKey = rv_Domain.pk
	=INKEY(.2)
	
	IF Tableupdate(0, .F., 'rv_Domain') = .F.		&&, laError)
		Messagebox('TableUpdate Error')
	ENDIF

	Requery('rv_Domain')
	Locate For rv_Domain.pk = lnKey
	.Refresh
Endwith 




Quote (Vernpace)


So I quess that parameterized remote views are out of the question for you.
What makes you say that? Is it the requirements? Remote Views not the right tech? I'm in left field? I've been giving customers what they want using old VFP ways (dbf and dbc) and this new tech will need a lot of ?? to achieve the same? Seriously, I need to know. I've told you a little about what our customers expect, so given that, how should I proceed?

Quote (Vernpace)


head down, practice a little trial and error until you get it,
Thats exactly how I've learned what I've learned so far. FoxTalk, Hentzenwerke's fox books, Doug Hennig's stuff, Tek-Tips, Foxite, and many others and old stuff from the Compuserve forums. No formal training, other than SouthwestFox and Will Hentzenwerke's Great Lakes conferences.

Also, I ask questions here only after much googling, searching and tons of trial and error. When all this fails, I ask here...

Quote (Vernpace)


You previously implied that you have no time to learn new things. How unfortunate - what a pity.
Please don't pity me, as I'm pursuing new business opportunities that replaces the excellent income we have enjoyed over the past 33 years from vertical VFP applications I wrote and still support.

I'm using these in-house sql apps as a learning tool. Many have said I should forget about VFP all-together going forward and may very well be true for remote access apps and get going with the c# and web stack.

For the short term, I'd be happy to build desktop apps connecting to sql. I have built a few actually using spt and vfp. It just takes too long and too much code, even for simple stuff. I was hoping for something closer to native vfp and that is where remote views came in. Then, what should be simple issues is taking days to fix and looks like I'm going to live with the inkey(.2) fix for now.

I have some time, however I do not work as a full time programmer xx hours a week like many others here that relies on a paycheck from a boss do. Between this stuff, the new business opportunities, and servicing our vendors and customers, I'm spending on average 16 hours a day, 6 days a week and sometimes 7 days a week. But, still, don't pity me as I know what will come from these efforts. I'm sure there are some here with similar roles that I have. Currently, I am near the end of what I can give this project for now. I do have it in a working state and have progressed with a lot of new features added and successful tested these past 3 weeks.

I would be interested in discussing hiring someone to help with building the new needed tooling. Drop me a message if interested.

I have an Exchange Server 2016 mail server down that I have to get fixed this weekend and I do not enjoy the powershell management required to fix an expired certificate that was removed using the IIS applet, that Exchange needs. And a bear to fix...

Also, anyone have any experience building a tool for adding 10000+ catalog items with images into a WordPress WooCommerce Marketplace store database? I have most of the heavy lifting done (such as image acquisition and exporting the file for Woo import) using native VFP. If so, we need to talk...

And please, lets continue the conversation...

Thanks, Stanley

RE: Converting Set Filters to Remote Views

That's a strange case you have there. If a successful tableupdate (returning .T.) means still buffered changes, then the logic would say, since your tableupdate() only updates 1 row, buffered changes are in other rows. But then your inkey() kicks in and the buffer will be emptied? I would perhaps get it as something needing time to spread when you'd need to wait after the tableupdate, but before?

Hm.

How is the view defined? I don't need to see the details, but is it a view on a view? That might cause complications with buffering. Clearly just a straw of having a complicated situation. I don't think it is.

So more straight forward: How is the view buffered? It surely is set to something, otherwise no tableupdate.
If you're so sure you only ever need single row updates, then it should be row buffering. And if its row buffering, you could also use the full tableupdate, it'll know the row, you don't need to tell it to only update the current row. In short: I never use single row tableupdates.

Quote (stanlyn)

Change a single string/char value and click cmdApply
What's in the control of the field you are changing in its valid and lostfocus events? That's the only code directly running before cmdApply.when and .gotfcus and .click. Notice the event chain of things potentially having code in it before the click code runs. There are a lot of things happening in the current control and then next control, if you change focus. Always, not just in corner cases. You'll usually think correct in only caring for the click code, as you don't have anything in all these events. But it would be quite likely you have something in valid, even if it returns .t. and isn't stopping the cmdApply button to get focus.

Chriss

RE: Converting Set Filters to Remote Views

Another thought is, the backend is SQL Server, running local, was it? The connection to it can be asynchronous. But I don't see how a tableupdate() can return a preliminary .T., it only returns .T. after the buffered changes are successfully saved and the buffere is cleared. In a single row update for that row only and not necessarily the whole buffer.

From that perspective of "no matter what happens before" the only solution is there are other row changes not yet saved. Which requires table buffering and only row update. But then that can't be mended with an inkey() waiting time. The buffers are VFP internal and don't depend on SQL Server reactions, it's VFP which needs to know the changes are saved and then it purges the buffer, not SQL Server. The buffers are under full VFP control.

Chriss

RE: Converting Set Filters to Remote Views

Well, and a straight forward thing to do when you get error 1545 about uncommitted buffered changes: Look what is in the buffer:

CODE

Select rv_Domain
nnModifiedRecno=0
Do While .T.
   nModifiedRecno = GetNextModified(nModifiedRecno)
   If nModifiedRecno<>0
      Go nModifiedRecno
      ? nModifiedRecno, GetFldState(-1)
   Else 
      Exit
   Endif
EndDo 

Maybe you'll detect where you err in thinking nothing else but the one value you actively changed is buffered.

Chriss

RE: Converting Set Filters to Remote Views

(OP)

Quote (Chriss)


but is it a view on a view?

I don't know what that is of how it would be constructed.

Quote (Chriss)


How is the view buffered?
Currently it is 5, optimistic table and only resides in the form's load method.

Quote (Chriss)


need single row updates, then it should be row buffering.
I need both. So, is it legal to change cursorsetprop('buffering', values to whatever for a one-up situation then return it back to what was set in the form' load event?

Follow this and is accurate?

CODE -->

select 'view'
cursorsetprop('buffering', 5, 'view')
go 50
replace status with 'Taken' next 10

cursorsetprop('buffering', 3, 'view')
go 100
replace status with 'Hold'
tableupdate(0, 'view')
* at this point, only row 100's changes are saved to the backend?

cursorsetprop('buffering', 5, 'view')
tabelrevert(.T., 'view')
* at this point, all other previous changes prior to row 100 are NOT changed and the buffer is cleared. 
Just making sure I'm understanding this correctly.

Quote (Chriss)


What's in the control of the field you are changing in its valid and lostfocus events? That's the only code directly running before cmdApply.when and .gotfcus and .click. Notice the event chain of things potentially having code in it before the click code runs.
I got excited to see that you have solved this, but commenting the lines out did not help... What I found was the first control with tab index = 1 which is the control the form stops at, once loaded. There is a 'lostfocus' on it as I was changing some values in the control that is bound to the view, and thought this was surely the issue, so I commented them out, saved, ran and issue still persists. Also, no valids anywhere. There are two other controls with lostfocus events, but none are in the open and save flow as they never receive focus.



Quote (Chriss)


Another thought is, the backend is SQL Server, running local, was it
MSSQL on same network, different machine. What do you consider local? same machine or network? I ask because this has been asked before and I need to be on same page.

Quote (Chriss)


From that perspective of "no matter what happens before" the only solution is there are other row changes not yet saved.
You raise some questions here where I may have some confusion...
Q1. If you close VFP, is there any way that "uncommitted changes" could presist into the next vfp session? I would think that all buffers would be flushed and a new session would be free from previous session's buffering statuses. Actually my experience is vfp won't close if there are uncommitted changes. I have to revert or update. This question is about where and what sessions can have unsaved changes that affects my session, and what I need to know to consider my session as clean.

Q2. Does tableupdate buffereing from a different user/session have any bearing on my session?

Q3. Chriss, you have kinda of indicated that tabe buffering can have two concurrent statuses, one that a tab;eupdate(0, will deal with and another tableupdate(1, for all unsaved records. I have never heard of that and need clarification. Whereas in an environment where cursorsetprop('buffering' is 5 (optimistic table buffering), I could change values in many view rows, then goto a specific record and change another value there and issue a tableupdate(0, and only that specific record would be updated. This would still leave all the other changed records unsaved waiting for a tableupdate or revert. Is this correct?

Q4. To my knowledge, tableupdate only works on the buffered copy of the view. Or does it only work on the view where the changed data is in the view?

Q5. Is there anything to quoting the view as is select statements? Like Select rv_Domain
vs Select 'rv_Domain' I read somewhere it should not be quoted. I do generally quote them.

Thanks, Stanley


RE: Converting Set Filters to Remote Views

Q1: No, there's no session persistence. At quit connection properties can also determine whether changes are reverted or committed.

Q2: No, the buffer is related to a workarea, not to a dbf or to a remote backend. So it's always local

Q3: single row update only cares for single row, yes. A view requery requires no buffered changes. None at all. If you're only interested in saving the one row, then tablerevert() all other changes, then you can also requery.

Q4: There is no such thing as a buffered copy of the view. The view itself is just it's query, the result of using a view is the workarea the results from doing that query, and the buffer is on that workarea. It starts empty, no matter if you pick row or table buffering. Buffered is what is changed or added, or deleted. Changing between row and table bufferig is no good idea, I think. What happens to uncommitted changes of a table buffer when you switch to row buffer is, I assume they are automaatically reverted or committed. I don't know, or an error is thirwn quite like that of view requery when there is something buffered.

There is no need to use anything else but tablebuffering, as you can pick to only update single rows in that mode, too. The benefit of switching doesn't occur to me.

Q5: VFP doesn't care, it understands SELECT 'rv_domain' just like SELECT rv_domain. There's really no point in quoting the name, as a workarea name isn't allowed to have spaces.

But the most important point would be what do you see in the buffer after your tableupdate()?

Is it perhaps really just a single misconception that requery of a view only refreshes he current row? You can only requery a view when the buffer of the workrea it uses is completely emptied, either by commit or revert. There's the whole point of why you don't have a view on the whole table and then set filter afterwards. You requery the view, the whole view. Nothing of it is buffered to begin with, but all of it is loaded, I think you're misinterpreting the meaning of the buffer.

Chriss

RE: Converting Set Filters to Remote Views

(OP)

Quote (Chriss)


Q3: single row update only cares for single row, yes. A view requery requires no buffered changes. None at all. If you're only interested in saving the one row, then tablerevert() all other changes, then you can also requery.
Chriss, don't try to see the end result as my example was to verify if the logic was correct (doable), regardless of the final result. Your original post suggests that we can have two buffering statuses concurrently, one for row and one for table, and maybe one for each of the 5 types, so long as we issue the cursorsetprop() before using them?

You quickly saw the end result was only one record should be saved, therefore reverting all others. What I was asking for is whether the progression of the code was legal, as in making save/revert decisions on the fly while traversing a code block without considering the current state of a buffer.

I'll play with this in a test as its hard to explain to you, yet easy for me to know what the question is...

Quote (Chriss)


hanging between row and table buffering is no good idea, I think. What happens to uncommitted changes of a table buffer when you switch to row buffer is, I assume they are automatically reverted or committed. I don't know, or an error is thrown quite like that of view requery when there is something buffered.
I will test a lot of this as understanding this will go a long way working with views.

Quote (Chriss)


then the logic would say, since your tableupdate() only updates 1 row, buffered changes are in other rows.
Chriss, we are discussing this because of this suggestion, which raised these related questions...

Quote (Chriss)


Is it perhaps really just a single misconception that requery of a view only refreshes he current row?
I understand that a requery() does a re-read of all the underlying data matching the select or parameters, not just a single current row. Is this correct?

Thanks, Stanley

RE: Converting Set Filters to Remote Views

Quote (stanlyn)

Your original post suggests that we can have two buffering statuses concurrently
No, what did I say that suggests that? A workarea can only have one buffer mode You switch modes, and you usually only use tablebuffering. The other mode differences are about locking. And some changes are not possible, some modes are also not available for views. Take a look into the help chapter for the details.

Regarding buffering in itself and isolated for itself there are only two modes. The other modes are just mixing in the possibilities about locks. Pessimistic and optimistic. That has nothing to do with buffers.

Chriss

RE: Converting Set Filters to Remote Views

The whole locking part of this is useless for a remote backend. An RLOCK or FLOCK is for a DBF file not for anything else.

PS: I'm sure someone will disagree, I'll stick to this, as thinking about locking with database backends other than VFP is best handled in the respective native manner. Usually the topic is more a topic of transactions and isolation level anyway.

Chriss

RE: Converting Set Filters to Remote Views

Stanley, you ONLY need optimistic table buffering. The view extracts data from SQL server, but data modeling should mean 2 users do not attempt to update 1 row simultaneously. That way you can be optimistic their changes can be saved. No locking is required. You trap for a conflict and warn the conflicted user to start over from the latest change or make some kind of utility to merge their changes into the recently changed row.

When the view extracts data, you have a local cursor that can indeed become different from the actual table, Fox or SQL.

If you bind controls to the cursor, you must not update those values or you will dirty the buffer.

Mike Yearwood - Former Microsoft Visual FoxPro MVP award winner. TWICE

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close