Is Transaction Processing Necessary When Using SQL Statements? 
Author Message
 Is Transaction Processing Necessary When Using SQL Statements?

Do I need to wrap my SQL Update & SQL Insert statements in BEGIN & END
Transactions since they automatically lock tables?  If I do, how do I check
for success?

TIA!
MD



Sun, 12 Jan 2003 03:00:00 GMT  
 Is Transaction Processing Necessary When Using SQL Statements?

Quote:
> Do I need to wrap my SQL Update & SQL Insert statements in BEGIN & END
> Transactions since they automatically lock tables?  If I do, how do I
check
> for success?

Use transactions only if you are updating multiple tables who's updates rely
on all being successfully completed. You can read more about this in the
chapter Chapter 17: Programming for Shared Access (though it's not nearly as
helpful as Booth and Sawyer's book Effective Techniques for Application
Development).


Sun, 12 Jan 2003 03:00:00 GMT  
 Is Transaction Processing Necessary When Using SQL Statements?
Thank you for your response.  I don't have access to Booth & Sawyer, though.
Is there a way to check for success with the SQL statements?

Thanks a zillion!
MD


Quote:


> > Do I need to wrap my SQL Update & SQL Insert statements in BEGIN & END
> > Transactions since they automatically lock tables?  If I do, how do I
> check
> > for success?

> Use transactions only if you are updating multiple tables who's updates
rely
> on all being successfully completed. You can read more about this in the
> chapter Chapter 17: Programming for Shared Access (though it's not nearly
as
> helpful as Booth and Sawyer's book Effective Techniques for Application
> Development).



Wed, 15 Jan 2003 03:00:00 GMT  
 Is Transaction Processing Necessary When Using SQL Statements?
Hi Marc

As long as you work with buffering you can check the RetVal of the
Tableupdate()
function. Update SQL delivers a _tally number of the updated records too.

HTH
Markus



Quote:
> Thank you for your response.  I don't have access to Booth & Sawyer,
though.
> Is there a way to check for success with the SQL statements?

> Thanks a zillion!
> MD



Wed, 15 Jan 2003 03:00:00 GMT  
 Is Transaction Processing Necessary When Using SQL Statements?

Quote:

> Thank you for your response.  I don't have access to Booth & Sawyer,

though.

Okay, but it's well worth the 44 bucks (Or whatever it is) if you can at all
afford it.

Quote:
> Is there a way to check for success with the SQL statements?

I'm not sure what you mean exactly? Do you mean executing them? I'll assume
you mean checking for success updating data.

I'll assume tables with buffering = optimistic, table

LOCAL llOK
llOK = TABLEUPDATE( 2, 0, 'myCursor' )
IF llOK
    = MESSAGEBOX( 'Table updated with buffered data')
ELSE
    = MESSAGEBOX( 'Table was NOT updated successfully')
ENDIF

With transactions (if you need them)

LOCAL llOK
BEGIN TRANSACTION
    llOK = TABLEUPDATE( 2, 0, 'myCursor1' )
    IF llOK
        llOK = TABLEUPDATE( 2, 0, 'myCursor2' )
        IF llOK
            END TRANSACTION
        ELSE
            = MESSAGEBOX( 'Unable to update myCursor2' )
        ENDIF
    ELSE
        = MESSAGEBOX( 'Unable to update myCursor1' )
    ENDIF
    IF .NOT. llOK
        ROLLBACK
        TABLEREVERT( .T., 'myCursor1' ) && TRANSACTION ROLLBACKs don't undo
the buffered data
        TABLEREVERT( .T., 'myCursor2' )
    ENDIF

Keep in mind that instead of just reverting the buffered data, you might
want to let the user know what went wrong so they can have a chance to
correct it.  Read about these commands, and post back if you have more
questions.



Wed, 15 Jan 2003 03:00:00 GMT  
 Is Transaction Processing Necessary When Using SQL Statements?
I'll definitely buy Booth & Sawyer.  Thanks for the advice.

I am aware of using the tableupdate function to commit changes, but I'm
using the plain old "update - SQL" command.  I know that it performs
automatic record locking, but I'm uncertain how to check to make certain
that it actually performed the table update successfully.  I was wondering
if there was flag or a variable that was set when successful.

One other user has advised me that update - SQL delivers a _tally number of
the updated records.  Is this the only way to check?

Thanks for all of the advice, everyone.  This is much better than the
alternative!

MD


Quote:


> > Thank you for your response.  I don't have access to Booth & Sawyer,
> though.

> Okay, but it's well worth the 44 bucks (Or whatever it is) if you can at
all
> afford it.

> > Is there a way to check for success with the SQL statements?

> I'm not sure what you mean exactly? Do you mean executing them? I'll
assume
> you mean checking for success updating data.

> I'll assume tables with buffering = optimistic, table

> LOCAL llOK
> llOK = TABLEUPDATE( 2, 0, 'myCursor' )
> IF llOK
>     = MESSAGEBOX( 'Table updated with buffered data')
> ELSE
>     = MESSAGEBOX( 'Table was NOT updated successfully')
> ENDIF

> With transactions (if you need them)

> LOCAL llOK
> BEGIN TRANSACTION
>     llOK = TABLEUPDATE( 2, 0, 'myCursor1' )
>     IF llOK
>         llOK = TABLEUPDATE( 2, 0, 'myCursor2' )
>         IF llOK
>             END TRANSACTION
>         ELSE
>             = MESSAGEBOX( 'Unable to update myCursor2' )
>         ENDIF
>     ELSE
>         = MESSAGEBOX( 'Unable to update myCursor1' )
>     ENDIF
>     IF .NOT. llOK
>         ROLLBACK
>         TABLEREVERT( .T., 'myCursor1' ) && TRANSACTION ROLLBACKs don't
undo
> the buffered data
>         TABLEREVERT( .T., 'myCursor2' )
>     ENDIF

> Keep in mind that instead of just reverting the buffered data, you might
> want to let the user know what went wrong so they can have a chance to
> correct it.  Read about these commands, and post back if you have more
> questions.



Fri, 17 Jan 2003 03:00:00 GMT  
 Is Transaction Processing Necessary When Using SQL Statements?

Quote:
> I am aware of using the tableupdate function to commit changes, but I'm
> using the plain old "update - SQL" command.

Ah. Sorry, I didn't grok that originally.


Fri, 17 Jan 2003 03:00:00 GMT  
 Is Transaction Processing Necessary When Using SQL Statements?
No problem.  I am MOST grateful for all the input I've received.  I'll try
to give it back when I can contribute something worthwhile.

MD


Quote:


> > I am aware of using the tableupdate function to commit changes, but I'm
> > using the plain old "update - SQL" command.

> Ah. Sorry, I didn't grok that originally.



Fri, 17 Jan 2003 03:00:00 GMT  
 
 [ 8 post ] 

 Relevant Pages 

1. Error using VFP7 transaction processing on Win2K File Server

2. using variables in SQL Statements

3. Need A Fast Response Using Sql Statement On Large Table

4. Problems with Memo data type transfers when using SQL pass-through statements

5. Using Count() in SQL statement

6. SQL Statement using same table twice w/ multiple left outer joins

7. ASP access .dbc using SQL statement ?

8. Using Sql statements and VFP tables

9. How to reference a temporary cursor created using an SQL statement

10. Transaction processing and free tables

11. TRANSACTION Processing

12. Help: Transaction Processing

 

 
Powered by phpBB® Forum Software