Bob, you work at the wrong place.
So let's buy VFP DE department and join. Or maybe I go too far now.

Since I had the idea anyhow, in the next few months I will do the following :

Re-generate the app where all native DB-commands will be functions in a procedure

Gives an idea on how much slower all will be because of this only.

Allows for the most flexible hook-ins of whatever appear needed lateron.

Waits for all successive changes into common denominator SQL-commands.

Change the native DB-commands in the functions to SQL-commands

Will give the idea on what are the most 1 to 1 possibilities.

Once done, gives the idea what the performance-penalty will be.

Indicates the technical implications for de SQL DBMS (possible additional indexes etc.).

Generate a MySQL DB out of the native tables and indexes existing

Will be performed by a generator.

Will include the additional technical facilities (additional indexes etc.).

Gives the idea on the transparency which can be reached (should be 100 %).

Will be inidicative for a possible needed kind of DBC and

When needed, will be generated at the same time (never the VFP-DBC which is too small-sized).

Change the USE of tables into the open of Remote MySQL tables and run the app

Will give the most poor performance possible, and therefore is indicative for what can be reached after optimizing (amongst which SQL-Server etc).

Will probably show that performance is gained on not having to USE tables evermore (being there all over the place right now and being the most slow there is within VFP).

Will show that all about Locking and Transactions won't work (consistently) anymore.

Deal with the Locking and Transactions

May show that not all can be done like I want it e.g. may change user (!) logic.

Generate a SQL-Server DB and now have THAT connected.

May indicative for the incorporation of a custom Transaction log, and

when so, use our existing Transaction log to arrange transactions (rollback etc.).

If all is right, we now have a system running with SQL-Server as DBMS, and not with my cursor-idea incorporated. So, the re-generation of the app in fact came to a 1 to 1 native-SQL translation, retaining the program-logic 100 %. This is the worst-case scenario, but it shows what the performance is in this poor situation.
I take the change that this just will be workable;

I expect the overhead to be in the SQL DB-server (the machine) on forming the SQL-commands;
Our app is fully scalable down to the table-level, and when performance is too poor we have a second server and see what that brings (note that 2 servers may bring a factor 100 improvement).

This is where I'll stop, but this is where I just may be allowed to stop. If that is right we all can do it ourselves with the next restrictions :

I'll be possible using our Transaction mechanism you won't have;

A (one) general routine for Using tables will be necessary and which we just have; when you have not you might be in trouble because program-logic may have to change.

You'll be needing a very decent error-handler containing all there is to contain, and which I will be using.

However :

When this is all working like I expect (again, being the most poor situation), then this is what MS can do the same, but bringing improvements on :

Interpreting performance (no custom function-calls);

Implying the Locking and Transaction schemes I use (being 100 % normal VFP but now in remote SQL-environment);

Apply the necessary stuff to the VFP error-handler;

Allow for USE REMOTE or whatever is needed to a. connect to remote tables and b. have the necessary separation between remote and local tables (normal USE as ever). Or the other way around : normal USE preceeded by SET ENGINE and USE LOCAL for the native stuff.

In between I can work on another thing :

Run VFP on a West-Wind server (or whatever), connect to the remote native tabled DB overthere, and let's see whether we can get that one running as well.
And you see (Jim), it is just this which I (or you) cannot oversee, because just this one this the strange one. However, since the web-app just works by this principle (and for sure not necessarily with an SQL DBMS), it SHOULD get to run. It 'll need some more creativity though, but who's afraid of that.
By the time maybe I sneaked some COMs ADOs or SOAPes into it, if I ever get to understand that well enough. So, the oldfashioned approach for starters, for us all to improve.

When ready ? next spring or so (a one man in between the lines job). <s>

See you around; have some work to do ...

Peter




>>Peter and Jim,
>As you know, I am a fan, of the basic concept here, but it appears that that we are actually running into either a stumbling block or a brilliant idea.
>
>Something was said about transparent, and the ability for the old style programs to use the functionality of SQL server.
>
>I think this goes back to what I either thinking, or writing on some time ago.
>
>Something like a few new commands... (lots of work by MS) but for us. just a few new ones.
>
>Set session to remote myremoteserver && which is defined in the dsn
>or
>set session to local && to cancel it.
>
>if you issue a create table with session in the remote state, it creates the table on the sql server, modi table does the same thing.
>Everthing as much as possible works with the same VFP commands.
>While in the remote session mode, you could say.
>create table local d:\mydatafile\mydata.dbf if you so wanted. but the session is scoped for remote tables.
>
>Once table are created, then using tables (backend and transparent to the VFP Coder ) would be all the approiate sql statments.... Translated.
>
>now here is a thought. Some of us more advanced user of client server applications would love to hack into those translated commands sometimes, and tweek them even more. So I would hope that there would be a behind the sceens session long of every sql stament made to the server. which could be opened up and cut and pasted into code if and or when necessary.
>
>Management of tables (remote tables) would use a similar if not the same syntax as vfp's syntax, index on tag...But actually it would send the sql - converted statment to the attached server.
>
>From working with Sql Like I said earlier, there would be somethings which don't translate exactally, or would have to added to sql tables to get them to work the same..(deleted records )
>
>But this is do able. and Worthwhile.
>
>Keeping the best of VFP powerful cursror based language, and adding a subset of transparent sql conversation translators, to handle the bolting to process.
>
>
>Even tho I have already done much of this work (for my own apps) can you imaging, issusing a commad like
>dele next 20 and having VFP translate that to whatever it needs to, to send the sql stament to the server. ? Its very possible.
>
>Now the bennifit of doing this, for the developer is tremendous.
>
>
>Bob Lee

---

Heart-Profit