Cursor help

Cursor help

• danielwetzler

  Thank you Cetin
  
  This was very helpfull. Thank you for walking me through the concept & code.
  
  
  Rob
  
  

• CET PRG455

  Hi Cetin
  
    Thanks for the info again!
  
    I checked and found that I have 38991 records in the listbox without an error so they must have increased that limitation.
  
    Can you post a code example of how to use a VFP table as a view And also how would I display this view on a form  
  
  Thanks again for helping a noobie.
  
   Rob
  

• Alwyn Rozario

  SqlPassThrough, View (local or remote) and CursorAdapter cursors can update their base tables. Other cursors have no relation with the tables they're populated from.
  
  You could create a view like this:
  
  create sql view v_client as ;
    select * from client ;
    where val(clnum) between  m.RangeLow and m.RangeHigh ;
    order by name
  
  It's created as part of your dbc (you need a database -dbc- for this). Then you use it like this:
  
  use myDatabase!v_client && It asks for low and high values
  
  or:
  m.RangeLow = 9001
  m.RangeHigh = 9049
  use myDatabase!v_client && It doesn't ask because parameter values are known
  
  
  Views are row buffered by default (and you can make them table buffered as you did in your code).
  
  However you could simply use Client table with buffering instead of creating a view. Assuming you have an index on val(clnum) with a tag name clnum:
  
  * this one misses order by name
  use client order tag clnum
  cursorsetprop('Buffering',5,'client')
  set key to range 9001,9049
  
  * If you also have a tag on name
  use client order tag ("name")
  cursorsetprop('Buffering',5,'client')
  set filter to between(val(clnum),9001,9049)
  
  
  

  
  

• Leo Pedersen

  I don't know one concrete proper way:) Anyone of them could be used as they're supported by VFP. Which ever you use, don't forget with Views or CursorAdapters you should be updating only a single base table. VFP supports otherwise but I don't suggest.
  
  I wouldn't directly bind a listbox to a table. Instead my preference is to set RowSourceType = 3, and RowSource to SQL of it. However if data is large as you say, it wouldn't help (I don't use listboxes with tables large, doesn't listindex cause problems I remember max as 32767).
  You can use a VFP table as a view (local or remote) or via SQLPassThrough. In other words to be a 'remote view' it doesn't really need to be remote. Creating a view you can specify parameters as you do for an SQL server view.
  
  Or my favorite: A grid is the best and better alternative to a listbox.
  One final note: Views are always buffered and rowbuffered by default. If you ever buffer base table too, do not forget to tableupdate() base when you tableupdate() view.
  
  

• HeatherS2006

  Rob,
  Check my first reply. There were a view creation code there. However you would use view designer to create the view. It adds a number of properties for you into the code. When using designer use 'filter' tab to define your parameters. ie: in code:
  
  open database testdata && First open the database that this view belongs
  create sql view myView as ;
   select * from orders where cust_id = m.cust_id
  
  In filter tab you'd add it as:
  cust_id = m.cust_id  
  
  When you create such a view in code (say from command window) you could use the view like this:
  
  use testdata!myView && DatabaseName!ViewName
  
  VFP would prompt you for the cust_id value. Enter an Id and browse you see only records from that customer. This is another legal view syntax:
  
  create sql view myView as ;
   select * from orders where cust_id = "Customer Id"
  
  The prompt changes to include that text. However it was just for sampling and we do not want such a thing:) We actually do not want to be prompted for the value (at times you might want it):
  
  *First close it if open
  use in 'myView'
  
  m.Cust_id = "BONAP"
  use testdata!myView
  browse && leave it open and go to command window
  
  This time it doesn't prompt and show the orders of 'BONAP'. Now you're done with 'BONAP' and want to see 'CACTU' (ie: moved pointer in parent customer table):
  
  m.cust_id = "CACTU" && or if customer is open m.cust_id = customer.cust_id
  ReQuery('myView') && Browse content changes
  
  
  You coudl have instead created the view like this:
  create sql view myView as ;
   select * from orders where cust_id = customer.cust_id
  
  Then all you need is the requery('myView') as pointer moves in customer.
  
  However it depends on customer alias. It should be in use, otherwise you'd get the prompt (unless you use NODATA clause of the view). If you're sure it'd be open all the time when this view is opened you might do but not suggested.
  
  OK now to form. As you see a view is just like a table for you. For it to be updatable it needs some properties like KeyFields, SendUpdates ... If you use designer it writes all of them for you and VFP handles the rest (well almost all of it:). There are more than one way (as always) to use it on a form. The easy one:
  
  * In this one we don't want to deal with navigation buttons
  * Will create 2 grids - for Customer and myView (better name it v_orders)
  -Create a new form
  -Open dataenvironment
  -Add customer table and your view to dataenvironment
  -In DE select your view and in properties sheet set NoDataOnLoad to .T.
  -Create a grid from customer table (click title and drag&drop on to form)
  -Do same for the view
  
  -Doubleclick customer grid (should be named grdCustomer automatically)
  -Select AfterRowColChange event and code:
  
  lparameters nColIndex
  m.cust_id = customer.cust_id
  requery('myView') && or Requery('v_orders') if you created it as v_orders
  thisform.grdMyView.refresh && refresh its grid - check actual name of grid
  
  *Note: AfterRowColChange fires when only column is changed too
  * and this code would fire more than needed. For the time being
  * we don't care
  -Run the form
  
  You'd see as you navigate in customer the view's grid is changing to show that customer's orders. Go and make some edits in orders' grid. Close the form.
  
  use orders
  browse
  
  Edits you did is there though you didn't call tableupdate(). Is views are not buffered you might ask. Views are always buffered but default buffering is RowBuffer which means it implicity tableupdate()s as row changes. If you don't change the row on that you edited then you wouldn't see that particular row  updated in base orders table.
  That's enough for now:) If you use them effectively views could be your best friend but it's easy to make them your enemies.
  
  Personal (read it as you don't need to stick and I don't want to go in details and/or debate:) suggestions:
  Whatever you do, do not let a view to update more than one of its base tables (VFP supports it but open to headaches). 
  Do not use an integer surrogate key (ie: autoinc or custom) as your key field. Use a GUID instead.
  

• maxnis

     Cetin you are the man! 
  
     My programmer was using the views to buffer the data. I am starting to wonder if he knows he could have just buffered the table as you have shown me. This seems like the way to go. 
  
     In your opinon what is the proper way we should be doing this buffering, Buffer the table, Cursor Adapter(Which I dont understand to well), or the View  
  
     One other question. We have a list control on a form with 6 fields from a database with 35,000 records. We use the table with an index and the list is bound to the table. This seems to be rather slow the first time we load the form and the list populates with the data (Takes about 7 seconds and we are on an very fast network). I see if we were working with an SQL server instead of a local database we could set it to only get records when they are needed (IE the user scrolls past the end of visable data or hits page down). Is there a way to do this with a local database IF not is this the way we should be doing this We are trying to mimick our old Clipper based apps screens.
  
  
   Thank you very much for all your help!
  
  Rob
  
  
  
  
• Cursor help