Hi Martina,
First, thank you for your help. But could you please explain what do you mean by "asynchronous execution"? Do I understand that SQLEXE will return 0 even if there is a problem SQL Server has with updating a table?
>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?
"The creative process is nothing but a series of crises." Isaac Bashevis Singer
"My experience is that as soon as people are old enough to know better, they don't know anything at all." Oscar Wilde
"If a nation values anything more than freedom, it will lose its freedom; and the irony of it is that if it is comfort or money that it values more, it will lose that too." W.Somerset Maugham