Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Bernard Bout
  Where is Bernard Bout?
 Brisbane
 Australia
 Bernard Bout
 To: Ken Murphy
  Where is Ken Murphy?
 Springhill
 Canada
 Ken Murphy
 Tags
Subject: RE: VFP 9 and SQL Server 2000
Thread ID: 112331 Message ID: 112675 # Views: 1 # Ratings: 0
Version: Visual FoxPro 9 Category: Databases, Tables and SQL Server
Date: Friday, November 10, 2006 10:11:40 PM         
   


> > > > Thanks Sir,
> > > > for your cooprate. I am success to get data through CA but how can I use Buffering,Tableupdate(),Tableuprevert() and refresh data please help me
> > > >
> > > > thanks
> > > >
> > > >
> > > > > > Hello Experts,
> > > > > > I never use SQL Server with VFP before. Please help me how i connect to the data of SQL Server. And also something about cursoradaptor
> > > > > >
> > > > > > Thank in Advance

> > > > >
> > > > > Asif,
> > > > >
> > > > > When connecting to a SQL backend, you have three basic flavours: SPT, Remote Views and CA's
> > > > >
> > > > > SPT - SQL PassThrough
> > > > > You begin by creating a connection to the SQL database using SQLCONNECT() or SQLSTRINGCONNECT(). Once the connection has been created, you pass SQL command strings to the server using SQLEXEC(). The first thing you need is a connection string (you can use a named connection but this is not recommended.) Go to www.ConnectionStrings.Com to find the connection string you need. The SQLSTRINGCONNECT() returns an integer handle that you can then use in your SQLEXEC() commands. You pass the SELECT, UPDATE and INSERT INTO commands to the server using the following format:
SQLEXEC(MyConnectionHandle,[SELECT ... FROM ... WHERE ... ORDER BY ...],[csrResultCursor])
In this case the result of the SELECT command (fill in your own particulars) will be sent to a local cursor called csrResultCursor.
> > > > >
> > > > > I recommend that you look at the help file on all commands beginning with SQL.
> > > > >
> > > > > Remote Views
> > > > > A remote view also uses a connection that can be generated using SQLCONNECT() or SQLSTRINGCONNECT(). When you create a view in the view designer, go to the VFP menu Query->View SQL. You will see that the view is simply a SELECT command followed by a series of commands to set cursor properties.
> > > > >
> > > > > CA'S
> > > > > A CA is simply a class based version of a view. It again uses a connection created with SQLCONNECT() or SQLSTRINGCONNECT() and it uses a SELECT command.
> > > > >
> > > > > My recommendation would be to play with all three options. To begin with, start with Bernard's very good series of articles on "A Basic CA Tutorial" found in the artlicles page of this site. I am just beginning an exploration of the CA and found Bernard's articles quite usefull. Next, I recommend that you go to Andy's website and look at his article on using SPT in "Class Based Data Management" here: www.tightlineComputers.Com What I normally use is based largely on that article.
> > > > >
> > > > > Hope this helps.
> > > > >
> > > > > Ken
> > > > > You shall know the truth - and the truth shall set you free. (John 8:33)

> > > >
> > > >
> > > >
> > > > i accept all the condition of forum

> > >
> > > Asif,
> > >
> > > See my reply to your other post.
> > >
> > > I typically just put a "save" button on the form, and in the .Click() call a custom form method .SaveData(). In the .SaveData method, I simply call a series of TABLEUPDATES() (one for each table or CA cursor) and if they all succeed, issue an END TRANSACTION. If any of the TABLEUPDATEs fails I tell the user and issue a ROLLBACK.
> > >
> > > Ken
> > > You shall know the truth - and the truth shall set you free. (John 8:33)

> >
> >
> > Asif and Ken
> >
> > When using CA's I use the following code segment if ever I need to update > 1 table in SQLServer.
> >
> >
> > * first for VFP - start the transaction
> > BEGIN TRANSACTION
> > 
> > * now SQLServer - start the transaction
> > SQLSETPROP(oConn,"Transactions",2)
> > 
> > IF NOT ThisForm.SaveData()
> > 	* roll back here
> > 	* first SQLServer
> > 	SQLROLLBACK(oConnection)
> > 	* Next VFP local cursors
> > 	ROLLBACK
> > ELSE
> > 	* for SQLServer
> > 	IF SQLCOMMIT(oConn) < 0
> > 		* store and log the error
> > 		AERROR(aer)
> > 		ThisForm.logsqlerror()
> > 		MESSAGEBOX(aer[2])
> > 		* rollback SQLServer
> > 		SQLROLLBACK(oConn)
> > 		* rollback local vfp cursors
> > 		ROLLBACK
> > 	ELSE
> > 		* This is for VFP local cursors
> > 		END TRANSACTION 
> > 		Messagebox("Data has been saved. .",64,"Data Saved",0)
> > 		lSave = .T.
> > 	ENDIF
> > ENDIF
> > * set transactions off in SQLServer
> > SQLSETPROP(oConn,"Transactions",1)

> >
> >
> > and we are done here.

>
> Thanks Bernard,
>
> This is essentially what I do but I use a DO CASE structure. IF/ELSE/ENDIF works great when you have 2 tables, but when you have 3+ tables to update, you can end up with a lot of nested IFs. A CASE structure just seems like neater code to me.
>
> BEGIN TRANSACTION
> SQLSETPROP(oConn,"Transactions",2)
> DO CASE
>    CASE NOT SaveTable1()
>       ROLLBACK
>       SQLROLLBACK(oConn)
>    CASE NOT SaveTable2()
>       ROLLBACK
>       SQLROLLBACK(oConn)
>    CASE NOT SaveTable3()
>       ROLLBACK
>       SQLROLLBACK(oConn)
>    ...
>    OTHERWISE
>       END TRANSACTION
>       SQLSETPROP(oConn,"Transactions",1)
> ENDCASE
> 

> Ken
> You shall know the truth - and the truth shall set you free. (John 8:33)


HI Ken

My construct will work for any number of tables without any more nested IF's. I just call the SAVEDATA() method and that contains the code for updating all the cursors involved in the transaction. In the SAVEDATA if any cursor update returns an error then I return an .F. to the call to SAVEDATA() and rollback. I dont see why I would need more IF's in my above code.

I use the above to update 2 to n tables without adding a single extra IF.

Unfortunately your DO Case code will not work, for the simple reason that a DO case will only execute ONE case statement. In the sample you provided, if SaveTable1() runs without an error then none of the other statements will run! SaveTable2() will not run!

Your code in fact would be how my SAVEDATA() method would look like EXCEPT I'd use IF...ENDIF since DO Case would not work. PLus I dont need to repeat the ROLLBACK code.


IF NOT ThisForm.SaveData()
* roll back here
* rest of code shown above...

**METHOD SAVEDATA**
IF NOT TABLEUPDATE(..Table1..)
RETURN .F.
ENDIF

IF NOT TABLEUPDATE(..Table2..)
RETURN .F.
ENDIF

IF NOT TABLEUPDATE(..Table2..)
RETURN .F.
ENDIF
RETURN .T.

What do you think Ken? Please have a look at the IF ELSE ENDIF again and you will see that it does not need to be expanded for more tables.

ENTIRE THREAD

VFP 9 and SQL Server 2000 Posted by Asif Hussain @ 11/7/2006 12:41:55 PM
RE: VFP 9 and SQL Server 2000 Posted by Ken Murphy @ 11/7/2006 3:05:53 PM
RE: VFP 9 and SQL Server 2000 Posted by Asif Hussain @ 11/8/2006 7:43:20 AM
RE: VFP 9 and SQL Server 2000 Posted by Ken Murphy @ 11/8/2006 1:03:55 PM
RE: VFP 9 and SQL Server 2000 Posted by Bernard Bout @ 11/9/2006 7:03:53 AM
RE: VFP 9 and SQL Server 2000 Posted by Asif Hussain @ 11/10/2006 1:36:57 PM
RE: VFP 9 and SQL Server 2000 Posted by Bernard Bout @ 11/10/2006 10:18:39 PM
RE: VFP 9 and SQL Server 2000 Posted by Asif Hussain @ 11/11/2006 5:22:32 AM
RE: VFP 9 and SQL Server 2000 Posted by Ken Murphy @ 11/11/2006 3:55:42 PM
RE: VFP 9 and SQL Server 2000 Posted by Ken Murphy @ 11/10/2006 8:05:26 PM
RE: VFP 9 and SQL Server 2000 Posted by Bernard Bout @ 11/10/2006 10:11:40 PM
RE: VFP 9 and SQL Server 2000 Posted by Ken Murphy @ 11/11/2006 1:50:48 AM
RE: VFP 9 and SQL Server 2000 Posted by Bernard Bout @ 11/12/2006 3:25:06 AM
RE: VFP 9 and SQL Server 2000 Posted by Mike S @ 11/14/2006 12:04:52 PM
RE: VFP 9 and SQL Server 2000 Posted by Ken Murphy @ 11/14/2006 4:14:54 PM
RE: VFP 9 and SQL Server 2000 Posted by Bernard Bout @ 11/15/2006 4:31:51 AM
RE: VFP 9 and SQL Server 2000 Posted by Mike S @ 11/15/2006 9:33:27 AM
RE: VFP 9 and SQL Server 2000 Posted by Ken Murphy @ 11/15/2006 1:16:07 PM
RE: VFP 9 and SQL Server 2000 Posted by David Jo @ 11/11/2006 4:54:56 PM