Hi Dimitry,
Yes, your code will be work with one exception - asynchronous execution (sqlexec return zero).
>But how would you suggest to do it in SQL Server?
1) You can use timeout error
- don't forget set QueryTimeout to value greather then zero.
- first vfp
nRetVal = SQLSETPROP( oApp.conn_handle, "transactions", 2)
IF nRetVal < 0
RETURN .F.
ENDIF
IF nRetVal > 0
nRetVal = SQLEXEC( oApp.conn_handle, "Update MyTable1 SET MyField1 = '123' WHERE MyField1 = 'ABC"" )
ENDIF
SUSP
IF nRetVal > 0
nRetVal = SQLEXEC( oApp.conn_handle, "Update MyTable2 SET MyField1 = '123' WHERE MyField1 = 'ABC"" )
ENDIF
IF nRetVal < 0
SQLROLLBACK(oApp.conn_handle)
ELSE
SQLCOMMIT(oApp.conn_handle)
ENDIF - second vfp
nRetVal = SQLSETPROP( oApp.conn_handle, "transactions", 2)
IF nRetVal < 0
RETURN .F.
ENDIF
IF nRetVal > 0
nRetVal = SQLEXEC( oApp.conn_handle, "Update MyTable1 SET MyField1 = '123' WHERE MyField1 = 'ABC"" )
ENDIF
SUSP
IF nRetVal > 0
nRetVal = SQLEXEC( oApp.conn_handle, "Update MyTable2 SET MyField1 = '123' WHERE MyField1 = 'ABC"" )
ENDIF
IF nRetVal < 0
SQLROLLBACK(oApp.conn_handle)
ELSE
SQLCOMMIT(oApp.conn_handle)
ENDIF 2) You can change field validation for field MyField1 or trigger for table
MartinaJ
>Hi,
>
>The following is just a draft of what I am planning to do when updating more than one table in SQL Server.
>Will this approach work?
>
>
>*-- oApp.conn_handle is the Connection Handle number obtained when application connects to the SQL Server.
>
>nRetVal = SQLSETPROP( oApp.conn_handle, "transactions", 2)
>
>IF nRetVal < 0
> RETURN .F.
>ENDIF
>
>IF nRetVal > 0
> nRetVal = SQLEXEC( oApp.conn_handle, "Update MyTable1 SET MyField1 = '123' WHERE MyField1 = 'ABC"" )
>ENDIF
>
>IF nRetVal > 0
> nRetVal = SQLEXEC( oApp.conn_handle, "Update MyTable2 SET MyField1 = '123' WHERE MyField1 = 'ABC"" )
>ENDIF
>
>IF nRetVal < 0
> SQLROLLBACK(oApp.conn_handle)
>ELSE
> SQLCOMMIT(oApp.conn_handle)
>ENDIF
>
>nRetVal = SQLSETPROP( oApp.conn_handle, "transactions", 1)
>
>
>
>Obviously I will add some error code to display if come across a problem. But my main question is whether setting SQLSETPROP "transaction" will work for the SQLEXEC() functions.
>TIA.
>
>UPDATE. My initial real coded test shows that the code above should work. The only thing I need is to figure how to make SQL Server fail to update one of the tables. So that I can see that Transaction really works. In VFP it would be easy; I would open a table EXCLUSIVE in another data session. But how would you suggest to do it in SQL Server?
