Cursor in Data Environment

[SitesMasstec]

Hello colleagues!

A have a Form which has in its Data Environment the table TEMPREC2.DBF (it is a temporary table with selected records from another table).

Is it possible to have a Cursor in the Data Environment of the Form, instead of that table?

Cursor has a random name, so I could not define it in the Data Environment of the Form. Is there another way to do it?

Thank you.

 

[costin77]

you can create and populate your cursor in a new method defined in your form.
this new method can be called from Form.init method

 

[Chriss Miller]

The data environment can contain:
1. Tables
2. Views
3. Cursoradapters

So the direct answer is no. You can create a view that queries the data, you can create a cursoradapter that queries the data, but I doubt you'd want that, as it has much overhead and not just the query. Instead, just write a query INTO CURSOR in code, like in the Load event of a form or in the BeforeOpenTables event of the data environment.

I would recommend the Load event of the form, as code in there is easier to discover, later, to see code of the Data Environment you always first need to open the Data Environment to get to its code.

 

[GTGeek88]

A view won't work?

 

[SitesMasstec]

Hello GTGeek!

I let the Data Environment empty in the form (no table, no cursor, no view), and I used this approach and the result was ok:

WITH thisform
    .txtRCODI.Value=YRCODI
    .txtRNOME.Value=YRNOME
    .txtRCLAS.Value=YRCLAS
    .txtRVALI.Value=YRVALI
    .txtRARMA.Value=YRARMA
ENDWITH

WITH thisform.Pageframe1.Page1
    .txtRcust.Value=YRCUST
    .txtRrend.Value=YRREND
    .txtRunid.Value=YRUNID
    .txtRpper.Value=YRPPER
    .txtRpesu.Value=YRPESU
  
*   etc etc
ENDWITH

FOR X=1 TO 20
    strX=STR(X,2)
    IF SUBSTR(strX,1,1)=" "
        strX="0"+SUBSTR(strX,2,1)
    ENDIF
       
    frase="thisform.Pageframe1.Page1.txtRor"+strX+".Value"
    &frase=YROR(X)

    frase="thisform.Pageframe1.Page1.txtRco"+strX+".Value"
    &frase=YRCO(X)

*   etc etc
NEXT X

 

[mjcmkrsr]

Hi GTG,

A view won't work?

They will ala they have been defined and parameterized - I have quite a lot of LOCAL VIEWS in my DEs. I guess, as Chrisss posted, that you can't CREATE them in the DE.


MarK

 

[Chriss Miller]

Should I point out a list of problems you can have with views (and all solve with experience) that will each pose a problem to SitessMasstec simply based on how I know his forum history, alone?

1. Views first need a DBC. Already have one? Fine. Better use a separate for views, because views lock a dbc briefly and theferefore can fail to be used.
2. Parameterized views take in values in variables that need to be defined when the form starts. Init runs after Load and after the dataenvironment runs, so the way in is private variable to define before starting the form.
3. If you modify a table a view query queries you can be confronted with ERROR 1542:
Base table fields have been changed and no longer match view fields. View field properties cannot be set.
4. There are 27 further erros about views, and some are not about sql views but offline views and viewe files you create with CREATE VIEW (not CREATE SQL VIEW), this overlap of meanings of the word doesn't help someone like SitesMasstec

I'm stopping here, though I could continue.

There's nothing that makes views unusable for developers that have a bit better understanding of many things, mainly SQL, but also many more aspects views have on top of the core SQL query defining them. But views and even less so cursoradapters are something I'd not recommend to SitesMasstec. Sure, there's an easy start, just CREATE SQL VIEW viewname AS query is just a tiny bit more complex than writing out query INTO CURSOR cursorname. So why hesitate? Well, you see there's a whole lot more to understand first, before you can master not just the simplle view creation and usage, but all problems you can be faced with following up.

I'd rather recommend skipping views and going for cursoradapter, which removes some of the problems, but my experience with people trying out to use cursoradapters is also not very great, I give up preaching them. Just go for a simple query INTO cursor in code.

I take back everything, when you say you already use views, SitesMasstec, but then I wonder why you didn't see you can use them in a data environment. The dialog to add to the DE is captioned "Add Table or View". I bet, though, I pointed out things about views you didn't knew could become a problem in the longer run.

 

[Chriss Miller]

I'll even give you some arguments against my argumentation:

1. There are thousands of error messages about anything and that's no reason to not use VFP
2. You always start with something sometimes. And lurking problems may not ever become real.
3. It would make sense to have an overview of all data used by a form in a data environment, including the data you get by queries into a cursor.

And here's what's I think a very valid and solid compromise about point 3: Just add in the tables you will need in the query into a cursor and you still have an overview of the data used by the form, I'd argue an even better overview as a cursorname (or indeed viewname) does not strictly reveal what tables it queries.
Just opening the DBFs for the form in the DE also makes use of the autoclosetables feature that may be one of the reasons to use DEs. I'd argue for private datasession forms for that matter, but so what. A query does even work when tables are not yet opened, but for sake of that argument of point 3 there's no need to add a cursor to the DE, just the underlying tables.

And then do the query you need on these tables into a cursor in the form code. Make it a new form method and you can repeatedly call it.

 

[mjcmkrsr]

Hi Chrisss,

Thanks for the insight.

ad 1) Of course. How would you create a LOCAL view outside a DBC? I must confess - I never did work with REMOTE or OFFLINE views

ad 2) Yes, but this variable can be fed from inside the form like I below

SELECT Houses.name, Houses.room, Students.pfa, Students.dentry,;
  Students.dexit, Students.rent,;
  Students.pfa+INT((4+Students.dexit-Students.dentry)/365*12*4)*Students.rent/4,;
  Students.dcaution, Students.cinstitution, Students.pkey, Students.nkey,;
  Students.fkey;
 FROM ;
     wunnraum!students ;
    INNER JOIN wunnraum!houses ;
   ON  Houses.pkey = Students.fkey;
 WHERE  Students.pkey = ( ?Thisform.cPKey );
 ORDER BY Students.dentry DESC

...

and from HG7

This one's really cool. When you use a parameterized view as the source for a control in a form (say, a grid or list), the parameter can be something like "ThisForm.SomeProperty." You can set up the view just as you want it on the form and not have to use a separate variable for the parameter. When you open the view outside the form, you get prompted for the parameter just as you do in any other case. This is the only place we know of in Visual FoxPro where you can use ThisForm notation in something other than method code or a property definition. Here's an example, letting you filter Employee on country

CREATE SQL VIEW EmpsByCountry AS SELECT First_Name,Last_Name FROM TasTrade!Employee WHERE Country = ?ThisForm.Country

ad 3) of course - you have to update the view accordingly

ad 4) As noted above I never did work with OFFLINE or REMOTE views

As to SitesMassTec - well I can't speak for him

MarK