Dave Crozier wrote: > A general question regarding how those of you out there using SPT and Auto > update cursors onto a Client Server back ends deal with record inserts into > a table. > > As some of you may well know, I'm in the process of writing a set of data > access classes for VFP to Client server back ends. OK, you may say, it has > been done before but I wanted a specific architecture for a project I'm > involved in so thought it would be a good idea to develop a set of classes > from the floor up. >
Have you seen the "framework" I developed for my pubic n-tier project? It's what I use now for the past 2-3 years and it's great for my needs. I've got generic method called "AddRecord" "UpdateRecord" and "DeleteRecord." The trick is to use SPT but take full advantage of meta-data to define all of the cursors/structures for you. Works great for me. It's freely available on Ed's downloads page. > My question is, when you use SPT to generate a VFP cursor which you then > make updatable, how do you handle the insertion of records? I know how I do > it and have always done it, but that is not the point and I simply wanted to > get some feedback from others. > > Specifically: > 1 Do you allow free access to the VFP cursor and native VFP commands like > "append blank" and "insert into..." as well as the Auto insert when using > grids to display the attached cursor which in effect controls the data from > the front end GUI down and auto updates the back end? > OR > 2. Handle all the table insertions via an SPT "Insert into..." and then > requery the back end from VFP to fill the GUI controls with the newly added > blank record data. > #2 unless it's a performance problem, then I use #1 and only do #2 at time of official saving. > Why, you may ask do I need this information? Well I just came across a > VFP/SPT situation where using (1) above results in a type of deadly embrace > or impasse situation when using Identity fields (the equivalent of VFP > Autoinc fields) on the server for Primary Keys. > I use the auto-increment keys (integer) in MySQL5. It's a simple command to get the latest key generated too. > When adding a record into the client/server table the SQL Server > automatically generates the identity (Primary Key) at the back end and then > you use (in M$SQL anyway) the "select @@Identity" command to retrieve back > the new Server PK into VFP if you require it for further processing, the PK > in the VFP cursor will be 0 however and this is the problem. > > You can't write the Server PK back into the VFP cursor to make the client > and server mirror each other, as you get a table conflict error. This is > caused because the PK in the VFP table (which VFP sees as 0) is now > different to the correct PK on the server and apart from anything else you > cannot modify PK's from the front end anyway as the server will throw an > error!! > > However, we presumably now want to populate this newly inserted record with > data in VFP so the only way to do this is to requery the whole dataset of > records in the dataset from the server which gives you the newly inserted > record which you can navigate to, update with record the new data fields > from VFP and allow auto update to write back the changes to the back end. > > I can currently see no other way of doing this without the requery unless > anyone has any other ideas. Note that for existing records this is no > problem as the PK is never "out of sync" between VFP and the server. > That's why I always use some sort of filtering for my dataset so that I'm requerying a very small amount instead of a "SELECT * FROM MyTable" scenario. Or, you could make a parameterized query/cursor where you use the PK, and thus your requery would just be for the new record you've created. Depends I guess if you're needing 1 record or many. > I guess we are really spoilt when it comes to using Grids with VFP in that > we can scroll up/down, insert and delete records on the fly without > bothering with the underlying data changing because we always see live data > in the grid, not a background dataset copy. > I got out of inputting directly into grids in the late 1990s after advice from other respected developers (Bill Anderson is one that comes to mind, iirc) about issues when inputting directly into grids. And I got away from direct table access back at the same time too due to flakey performance of VFP5 over the Novell network. Once I changed to local views, all those flakey problems went away---on the same Novell network. Besides, I like the idea of datasets of small records that you want to work/deal with, as opposed to dipping right into the ocean of data directly on the server. Disconnected data is a better way to go, imo. I know that's a tough sell for some legacy Fox guys. > Obviously, the best thing is simply to avoid using live data grids and go > back to the record by record approach from days gone by where you can only > edit the record that is being worked on in a Form which always refreshes the > data from the server before going into edit mode - the equivalent of the > 'requery' stage above. > > As I say, I'm interested in finding out how others out there do it. > > A bit of a diatribe, I know but explaining it is not as easy as it seems. > > Comments welcome. > > I'd be happy to answer any questions about my framework. If I heard Kevin Cully right at last year's conference, he had used some of my code in a project of his and I think it turned out well. ?? --Michael _______________________________________________ Post Messages to: [email protected] Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/[EMAIL PROTECTED] ** 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.
