Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Binod Binani
  Where is Binod Binani?
 Kolkata
 India
 Binod Binani
 To: Michel Levy
  Where is Michel Levy?
 
 France
 Michel Levy
 Tags
Subject: RE: SQL Transactions
Thread ID: 365203 Message ID: 365313 # Views: 37 # Ratings: 0
Version: Visual FoxPro 9 SP2 Category: Databases, Tables and SQL Server
Date: Monday, December 24, 2012 6:40:49 AM         
   


> > Dear Expert.
> >
> > Following is Dataupdate Routine for my part of a project.
> >
> >
** MyParentCRS --> Updatable CurSor For SQL Database
> > ** MyChildCRS  --> Updatable CurSor For SQL Database
> > 
> > ** Parent Table****
> > ** Update Existing Rows 
> > Update MyParentCRS set;
> >        MyParentCRS.MyField1 = TmpParent.MyField1 ,;
> >        MyParentCRS.MyField2 = TmpParent.MyField2;
> >  from  TmpParent ;
> > Where  MyParentCRS.MyPkey = TmpParent.MyPkey
> > ** Adding Fresh Rows
> > Insert into  MyParentCRS (MyField1, MyField2) ;
> >       SELECT MyField1, MyField2 FROM TmpParent ;
> > where Not Exist (Select * from MyParentCRS where MyParentCRS.MyPkey = TmpParent.MyPkey)
> > 
> > **** Child Table ***
> > ** Update Existing Rows 
> > Update MyChildCRS set;
> >        MyChildCRS.MyField1 = TmpChild.MyField1 ,;
> >        MyChildCRS.MyField2 = TmpChild.MyField2;
> >  from  TmpChild ;
> > Where  MyChildCRS.MyPkey = TmpChild.MyPkey
> > ** Adding Fresh Rows
> > Insert into  MyChildCRS (MyField1, MyField2) ;
> >       SELECT MyField1, MyField2 FROM TmpChild ;
> > where Not Exist (Select * from MyChildCRS where MyChildCRS.MyPkey = TmpChild.MyPkey)
> > 
> > ** Finally Updating Datas to SQL Server
> > 
> > IF SQLnotConnected()
> >    DO Something
> >    RETURN .f.
> > ENDIF
> > LOCAL llUpdated
> > 
> > BEGIN TRANSACTION 
> > DO case
> > CASE NOT TABLEUPDATE(1, .f., 'MyParentCRS')
> >      AERROR(SqlErr)
> >      MESSAGEBOX(Sqlerr(1,2),16,'SQL Err')
> >      RollBack
> > CASE NOT TABLEUPDATE(1, .f., 'MyChildCRS')
> >      AERROR(SqlErr)
> >      MESSAGEBOX(Sqlerr(1,2),16,'SQL Err')
> >      RollBack
> > OTHERWISE
> >      MESSAGEBOX('Data Updated to Server Properly! ')
> >      m.llUpdated = .t.
> >      END TRANSACTION 
> > ENDCASE
> > 
> > IF NOT llupdated
> >   ** Since Database is not updated properly
> >   Messagebox("Unable to Updated Records...Reverting.....")
> >   =Tablerevert(.T., 'MyParentCRS')
> >   =Tablerevert(.T., 'MyChildCRS')
> >   Return .F.
> > Else
> > 	Return .T.
> > Endif
> >           

> >
> >
> > it is working fine when there is no error in any one of the tables. But when
> >
> > updates fails I had seen that there is either records in Parent Table whose child
> > table have no records or vise-versa.
> >
> > How to use 'Transactions' function in SQL Server.
> >
> > it wud be great help if i found some sample.
> >
> > Please.
> >
> >
> >
> >
> >
> >
> > *Exchange of $1 Create only $1, But Exchage of one Ideas Makes different two Ideas*
>
> --
> Hi Binod,
>
> you are reaching the limits of the updatable remote data cursors, and you'll need soon to investigate the Sql Pass Through.
> But for now, there's still a solution ;-)
>
> The transactions mode on server side are defined by VFP with the SQLSETPROP function with the parameter 'Transactions'. Default value is 1 (automatically handled), but you may set it to 2 (manually handled). In the manual way, you send a SQLCOMMIT() or a SQLROLLBACK() as needed by your code.
>
> Another way is to send explicitely to the SQL Server the commands for INSERT and UPDATE. You send it in a SQLEXEC. You may send as many commands as you want in a single SQLEXEC, each command separated by a semi-colon (;). Simply put a 'BEGIN TRANSACTION' as the first command. Don't forget to COMMIT or ROLLBACK in the same SQLEXEC. The local TABLEUPDATE or TABLEREVERT will depend on the result of the SQLEXEC.
>
> A third way (the best IMHO) should be to write some views on the server itself, and to build VFP remote vies on these SQL views. The views on the SQL Server will hold some TRIGGERs INSTEAD OF, which receive the commands send by the client (VFP or .net or whatever you develop in), the code you write for the triggers do the job in the transactions you define. All is done on server side, it is more reliable and faster.
>
> Michel L

Thanx Sir Michel Levy.

Very nice to have ur expert idea.

It wud be great help for a sample on ur Third way.
How to handle remote SQLviews in server.

any web link might helpfull.

Please



*Exchange of $1 Create only $1, But Exchage of one Ideas Makes different two Ideas*

ENTIRE THREAD

SQL Transactions Posted by Binod Binani @ 12/22/2012 6:04:21 AM
RE: SQL Transactions Posted by David Mustakim @ 12/22/2012 7:24:31 AM
RE: SQL Transactions Posted by Michel Levy @ 12/22/2012 1:09:08 PM
RE: SQL Transactions Posted by Binod Binani @ 12/24/2012 6:40:49 AM
RE: SQL Transactions Posted by Michel Levy @ 12/24/2012 9:53:06 AM
RE: SQL Transactions Posted by Anders Altberg @ 12/22/2012 1:52:03 PM
RE: SQL Transactions Posted by Michel Levy @ 12/22/2012 2:58:43 PM
RE: SQL Transactions Posted by Anders Altberg @ 12/22/2012 7:07:49 PM
RE: SQL Transactions Posted by Anil Sharma @ 12/24/2012 8:47:47 AM