Point by point, but not in the original order as presented:
1. "The problem is that they have screens that pull back all the data from
certain key tables that are pretty large."
ALL the data? so the sql query code looks like:
select * from tablename.
Always bad form , imo/ime, in any client/server environment. Even if it's
a KEY table, it sounds like there needs to be some other normalization of
the data, or at least shift off to doing a parameterized query that
includes a 'where' clause, and the item in the where clause is a hit on a
column that is indexed.
2. "but they say when they had the FoxPro database, the app would simply
open a pipe to the data and they could bring back the first screen full of
data in a couple of seconds, and then Foxpor [sic]would leave the pipe
open so it could smootly scroll forward/backward throught the results
without having to issue another data request."
Yup - thats the beauty of the speed of data access with native vfp tables.
Doesn't exist in SQL SERVER, sorry.
3. "Therefore, the application is not built with any form of paging
capability"
Ah - but it could be! With the same remote views into sql server, you can
set up the connection properties from vfp to not pull everything, and
limit each pull to 100 to 200 records. Then, as the pseudo-cursor is
moved (ie- go bottom) , VFP will force the odbc connection to get to the
end. There is a time lag, of course, but NOT for the initial VFP
form/screen load.
4. "The proplem [sic] they've run into now that SQL Server is the
database is that in order to simulate the scrolling capability of the
FoxPro based tables, the middle layer has to pull all the data into a
cache area first."
OK - so now comes 'the rub' right ? This is where the design change comes
into play.
Going forward.
The set of tables that need to be 'replicated' - How often does this
information change ? How is each record uniquely identified? Have you done
any matching with primary ID's in the SQL table with primary ID's in the
VFP table?
IFIW - I'd take a 3 shot approach to your 'replication' process, could be
done in stored procedure, then migrated over to something else.
1. from sql server to VFP table via oledb
- get a sql cursor (call it diff_a) of the primary id's in the vfp table
2. in sql server
- do a diff on the diff_a cursor with the sql table you wish to push out,
getting only the primary id's, creating a new sql cursor (again storing
only the primary id's), call it diff_b
- create a sql cursor with everything in it that matches the primary id's
stored in diff_b
3. from sql server to vfp table via oledb
- simply append to the vfp table, the contents of the diff_b sql cursor.
This will negate the error you run across with the vfpoledb driver having
the parameterized problem.
Good Luck !
Mondo Regards [Bill]
--
William Sanders / efGroup {rmv the DOT BOB to reply}
VFP Webhosting? You BET! -> http://efgroup.net/vfpwebhosting
Failing dotNet Project? -> http://www.dotnetconversions.com
_______________________________________________
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
** All postings, unless explicitly stated otherwise, are the opinions of the
author, and do not constitute legal or medical advice. This statement is added
to the messages for those lawyers who are too stupid to see the obvious.
