Insert VFP cursor data into a SQL table 2

[robsuttonjr]

I want to know what the best way is to insert data from VFP tables/cursors into SQL server tables. I am converting a program that was in VB/ADO to VFP. I am actually rewriting from the ground up. Mostly I am looking for others advice on whether to use SQLEXEC or ADO or ? in VFP. What is the best methodology to take full advantage of SQL server in VFP. For example of my concern if I use SQLEXEC to insert into a SQL table with an identity field, how do I get the identity record I just inserted without using a separate query.

 

[rgbean]

Actually, I just attended a VFP SQL Server training seminar last weekend, and that speaker's advice is use a mixed bag. Jim Duffy, of TakeNote Technologies in NC, has been using SQL Server / MSDE exclusively for his VFP apps data store for more than 5 years. His basic recommendations are to use SPT (SQL passthrough) for queries (results into VFP cursor) and ADO calls to SQL Server Stored Procedures for all updates. This makes coding (at least logical) n-tier application much easier.

While, I'm just beginning in this area, his arguments for this approach seem solid, AND real-world experience for his many clients proves that it works well.

Rick

 

[agoeddeke]

robsutonjr,

I don't have much experience in this area and I imagine you will get different replies based on everyone's taste. I know that Jim Duffy (MS MVP) suggests using a combination of BOTH.

He uses SQLEXEC for all fetches and uses ADO for all inserts/updates/deletes. Not only will ADO return the @@IDENTITY as a command parameter, but ADO also respects varchar types and does not automatically pad character fields with spaces on the back end.

He also uses Stored Procedures for ALL SQL Server interaction because of the fact that these are precompiled on the server and should increase performance.

We also have the CursorAdapter class on the horizon in VFP 8.0 so it will be interesting to see how this little puppy changes anyone's thinking.

Thanks for starting this thread... hopefully others will chime in with their thoughts.

Andy

 

[Mike Lewis]

Hi Rob,

Personally, I feel most comfortable writing plain old INSERT statements and sending them to the server via SLQEXEC().

Remote views are OK up to a point, but you don't have a huge amount of control over them. One issue that I found particularly irritating was that, when you insert a record via a remote view, the fields are always padded with spaces, even if they VarChars (that's probably a way round that, but it's not obvious to me).

You mentioned ADO. Personally, I think ADO is great in VB and ASP, but unnecessarily complicated in VFP. After all, VFP already has everything that ADO can give, and it is much simpler in VFP.

I take your point about identity fields. It took me a long time to figure that one out. The answer is that you use the @@INDENTITY variable, which returns the key of the record you just inserted.

I do encourage you to peservere with this. VFP and SQL Server are both great tools, and they work well together.
Mike Lewis
Edinburgh, Scotland

http://www.ml-consult.demon.co.uk

 

[rgbean]

Mike,
You can give up with the padding and VarChars in ODBC and remote views - I beat that dead horse until I got someone from MS to acknowledge you simply can't do it! In fact that's one reason ADO IS a better (although more complicated) way to go, it's much more flexible. Also, using SQL SPs you end up having even fewer restrictions, they can do "anything", because you're just passing the the data along to them!

Rick

 

[robsuttonjr]

When inserting data into a table I want to be be able to get the identity number returned for a purchase order header number. So far ADO is the answer via stored procedure. Also when updating 1-100 records and what if the update is interupted? Poweroutage? I want to develop a fool proof data update/insert/delete procedure. For inserts/updates/deletes I need to do this:
Insert/update/delete 1-1000 records
at the end of process verify and rollback if necessary.
I should be able to have the database verify incoming data and have a trailer record which signals end of transactions. Then complete or rollback current transactions. From this I can only see an active connection to the database as my answer. Sqlexec is not as robust as ADO from what I have seen. Remote views are worthless to me especially if you have to wait for 1,000,000 records to load. Anway, thanks for all of the posts and I appreciate any other insight given. I would not survive as a programmer without help from Tek-Tips forum members!

 

[Mike Lewis]

Rick,

<< You can give up with the padding and VarChars in ODBC and remote views >>

That's encouraging. I thought I might be missing something obvious.

Actually, I did find a solution, but it was a pretty clumsy one. I wrote a stored procedure on the server to scan the table and trim all the VarChars. It worked ok, but only because it was a one-off operation that had to be performed after a data conversion run. Wouldn't be much good in day-to-day use.

The padding issue doesn't come up with SQLEXEC(), which I tend to use now for most of my SQL Server work.
Mike Lewis
Edinburgh, Scotland

http://www.ml-consult.demon.co.uk

 

[robsuttonjr]

with sqlexec can I get the @@identity returned? Can I do stored procedures and have sql pass back the @@identity in the same sqlexec statement. I would like to see some examples of how others use sqlexec to take advantage of stored procedures if possible. I just want to make sure I don't miss something with both methods. (ado vs. sqlexec)

 

[Mike Lewis]

Rob,

<< Can I do stored procedures and have sql pass back the @@identity in the same sqlexec statement. >>

You sure can.

This is off the top of my head, and would need testing, but something along these lines might work.

This is is a stored procedure:

CREATE PROC Get_Ident
@My_Ident int OUTPUT
AS
SET @My_Ident = @@IDENTITY

This is how you would call it in VFP

lnIdent = 0 && must always initialise this sort of param
SQLEXEC(lnConn,&quot;EXEC Get_Ident,?@lnIdent)

lnIdent should now contain the contents of @@IDENTITY. Of course, you'll need to tie it in with the code which inserts the new record, but this should give you a start.

Mike Lewis
Edinburgh, Scotland

http://www.ml-consult.demon.co.uk