1 or -1
MartinaJ
>Thank you very much! Do I understand that if I don't use the Asynchronous then the result is 0 or -1, correct? (sorry for being a pest but I want to be sure I understand this).
>
>>>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?
>>>
>>
>>No,
>>1 - all is OK
>>0 - command still executing
>>-1 - any error
>>
>>
>>nRetVal = SQLSETPROP( oApp.conn_handle1, "transactions", 2)
>>nRetVal = SQLSETPROP( oApp.conn_handle1, "Asynchronous", .T.)
>>
>>nRetVal = SQLSETPROP( oApp.conn_handle2, "transactions", 2)
>>nRetVal = SQLSETPROP( oApp.conn_handle2, "Asynchronous", .T.)
>>
>>IF nRetVal > 0
>> nRetVal1=SQLEXEC( oApp.conn_handle1, "Update MyTable1 SET MyField1 = '123' WHERE MyField1 = 'ABC"" )
>> nRetVal2=SQLEXEC( oApp.conn_handle2, "Update MyTable2 SET MyField1 = '123' WHERE MyField1 = 'ABC"" )
>>
>> DO WHILE nRetVal1=0 OR nRetVal2=0
>> IF nRetVal1=0
>> nRetVal1=SQLEXEC( oApp.conn_handle1, "Update MyTable1 SET MyField1 = '123' WHERE MyField1 = 'ABC"" )
>> ENDIF
>> IF nRetVal1=0
>> nRetVal2=SQLEXEC( oApp.conn_handle2, "Update MyTable2 SET MyField1 = '123' WHERE MyField1 = 'ABC"" )
>> ENDIF
>> ENDDO
>>
>> IF nRetVal1 < 0
>> SQLROLLBACK(oApp.conn_handle1)
>> ELSE
>> SQLCOMMIT(oApp.conn_handle1)
>> ENDIF
>>
>> IF nRetVal2 < 0
>> SQLROLLBACK(oApp.conn_handle2)
>> ELSE
>> SQLCOMMIT(oApp.conn_handle2)
>> ENDIF
>>
>>ENDIF
>>
>>
>>MartinaJ
>>
>>>>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?
