Hi Todd ---
Change the "Transactions" property of the connection to 2 (manual). The default is 1 (auto). Use DBSETPROP() for that. Then, use SQLCOMMIT() and SQLROLLBACK() as the commands to commit/rollback the transaction.
>
>I am updating a remote data source via remote views. I have a data base of updates that I want to apply to the remote data base.
>
>I create the remote data base and its views like this:
>
>create database tovistar
>create connection tovistar datasource &msource userid "dba" password "sql"
>nHandle = sqlconnect('tovistar')
>sqlsetprop(nHandle, "Asynchronous", .f.)
>x=SQLTABLES(nHandle, 'table')
>zztotal_tables = reccount()
>zztables_complete = 0
>GO TOP
>SCAN
> zztables_complete = zztables_complete + 1
> mytherm.shape1.setsize(zztables_complete/zztotal_tables)
> MTABLE = ALLTRIM(TABLE_NAME)
> MDATATABLE = 'tovistar!' + MTABLE
> create sql view &mtable remote connection tovistar as select * from &mtable
> SELECT 2
> USE (MDATATABLE)
> sqlsetprop(nHandle, 'transactions', 2)
> sqlsetprop(nHandle, 'PacketSize', 40960)
> DBSetProp(mtable, 'View', 'SendUpdates', .t.)
> FOR MFIELDNUM = 1 TO FCOUNT()
> MTABLEFIELD = MTABLE + '.' + FIELD(MFIELDNUM)
> DBSetProp(MTABLEFIELD, 'Field', 'Format', 'T')
> DBSetProp(MTABLEFIELD, 'Field', 'Updatable', .T.)
> IF DBGETPROP(MTABLEFIELD, 'FIELD', 'DATATYPE') = 'C' THEN
> DBSETPROP(MTABLEFIELD, 'FIELD', 'DATATYPE', 'M')
> ENDIF
> IF DBGETPROP(MTABLEFIELD, 'FIELD', 'DATATYPE') = 'D' THEN
> DBSETPROP(MTABLEFIELD, 'FIELD', 'DATATYPE', 'T')
> ENDIF
> next
> select 1
>endscan
>
>
>I then have a bunch of code to figure how to apply the updates, either insert (via INSERT INTO FROM MEMVAR), delete(DELETE FROM TABLE WHERE lcond) or update (GATHER MEMVAR MEMO). That code is all wrapped in a Begin/End transaction statement. Problem is that the transactions get commited even if the end transaction never fires. (I have a rollback in my on error procedure, but it doesn't seem to do anything.)
>
>Can you all debug the above code? Maybe give me some transaction processing notes? I've read the relevant sections out of the developer's guide. Maybe there is someone I could call that would lead me thru this? I don't even mind paying, if its a reasonable hourly rate.
>
>Thanks!!!
------------------------------------------------
John Koziol, ex-MVP, ex-MS, ex-FoxTeam. Just call me "X"
"When the going gets weird, the weird turn pro" - Hunter Thompson (Gonzo) RIP 2/19/05