SELECT Selects Old Data From Somewhere

[Steve Meyerson]

I try to work with cursors as much as possible.

The AllClients cursor (downloaded from MySql) contains the main data (about 28,000 records, 60 fields)

The user starts with the Clients cursor created with

SELECT * FROM AllClients INTO CURSOR Clients READWRITE

When the user changes a record in Clients, that same change is made (by the program) in the AllClients cursor.

If the user then selects a criteria, a new Clients cursor is created. Example:

SELECT * FROM AllClients INTO CURSOR Clients WHERE State='NC'

The problem is the new Clients cursor contains the old data (before the edit).

To fix this I had to copy the downloaded AllClients cursor to a dbf instead of leaving it as a cursor.

I tried the FLUSH command (didn't work). I believe it might have something to do with buffering (CURSORSETPROP?), but I don't know what parameter(s) to use.

Is there any way I could keep AllClients a cursor instead of a dbf?

Thanks for any ideas.

Steve

 

[Steve Meyerson]

learn how to make a remote view or cursoradapter updatable to only need TABLEUPDATE() for saving all buffered changes

Thanks, Chris. Is there a good example somewhere? (that's how I usually learn

I often use a macro in the SQLPREPARE() statement to substitute a value or clause. I wonder if that's any "safer" than inserting a value or variable name (or the question mark before the variable name)?

Steve

 

[Chris Miller]

SQLPREPARE, no matter if you use MySQLs or VFPs, should not need any substitution, or you prepare multiple statements that way. You ideally prepare one statement with parameters, you get back a handle from VFP and reusing that only transfer a set of values via variables (or expressions like DATE()).

So when you change your query before doing SQLPREPARE and then only execute it once, you could also simply use SQLEXEC() directly, you don't use the benefit of the same statement executed multiple times with varying parameter values.

Chriss

 

[Chris Miller]

Regarding Remote View:

Open or create a DBC, then type in CREATE CONNECTION and follow the connection designer. I recommend using a connection string like you perhaps already use it in SQLSTRINGCONNECT(), just to not depend on a DSN entry. Distributing the DBC then also distributes the connection used by the remote views.

Then create a remote view by entering CREATE SQL VIEW.

You can use these commands with their extended syntax and options to do everything in code, but these shorthand commands will pop up the "wizards" of VFP, just like CREATE TABLE pops up the table designer.

With Cursoradapters you first create a class based on cursoradapter. Then in the class designer you only have a cursoradapter icon. Right-click and choose to use the Builder, and you get guided through similar forms like for the remote view.

Chriss

 

[Steve Meyerson]

Without a DBC, I call THISFORM.DbConnect() (below) to open (and reopen) the connection. The property values here are assigned in Init(). I use SQLDISCONNECT(0) to disconnect immediately after use (I've had problems leaving the connection open for any length of time).

* DbConnect
WITH THISFORM
 .Fh = SQLSTRINGCONNECT(;
   + 'Driver='    + .SqlDriver   ;
   + ';Server='   + .SqlServer   ;
   + ';Database=' + .SqlDbPfx + ALLTRIM(.SqlDatabase) ;
   + ';UID='      + .SqlUserPfx + ALLTRIM(.SqlUser) ;
   + ';Password=' + .SqlPassword,.T.)
 RETURN .Fh
ENDWITH

Everything's in my exe, except I think I would also have to set up ODBC for new users (unless I could eliminate that somehow).

As I said before, I have much studying to do and appreciate your help!

Steve

 

[Chris Miller]

You need a DBC for Remote views, but not for cursoradapters. If you wnt to test that route, this DBC only needs to be local, can even be embedded in your EXE, I think, it will only have view definitions and the connection object in itelf, no data DBFs. It's just the way remote views work, they are stored in the DBC/DCX/DCT files themselves.

Establishing a connection is integrated in the view and cursoradapter definitions anyway, so you'll need to change your code a bit. In a cursoradapter, see the builder dialog, it asks for a full connection string, not the single options, those are too individually different. You may reuse your code to just store the full connection string to, say, a _SCREEN property and then set this in the builder, it will just make testing the connection harder.

ODBC: You obviously need the ODBC driver, but you don't need a DSN for Remote Views nor for Cursoradapters. It's an option but instead, you can also set up a connection string, like you already do.

Tip: Make use of OOP - your base class cursoradapter could just establish a connection and have a query like SELECT version() or SELECT Now() to get the server time, you can also use the builder dialog to set this up. Then create further cursoradapters from that base cursoradapter and you can concentrate on the actual Table, SQL, etc.

Chriss