Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Michel Levy
  Where is Michel Levy?
 
 France
 Michel Levy
 To: Anders Altberg
  Where is Anders Altberg?
 Uppsala
 Sweden
 Anders Altberg
 Tags
Subject: RE: SQL Transactions
Thread ID: 365203 Message ID: 365229 # Views: 47 # Ratings: 0
Version: Visual FoxPro 9 SP2 Category: Databases, Tables and SQL Server
Date: Saturday, December 22, 2012 2:58:43 PM         
   


> > 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*
>
> If you take the course that Michel Levy posted you shouöd first check up the server DB documentation about which kinds of serverside views are updatable; not all of them are.
>
> -Anders

--
Bonjour Anders,

With TRIGGERS INSTEAD OF, all serverside views are "updatable" - not directly, but through the trigger.

Michel L

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