OP Start transaction

version 1.5

OP Start transaction (connectionID) Longint

Parameter	Type		Description
connectionID	Longint		Connection ID with target server
Function result	Longint		Error code result for the function

OP Start transaction starts a transaction for the current connection. All changes to the database will be stored temporarily until the transaction is accepted (validated, commited) or cancelled (rolled back).

For each open connection, you can start only one transaction. You cannot nest transactions. If you start a transaction inside another transaction, 4D Server ignores the second transaction.

If OP Start transaction executes successfully, it returns 0. Otherwise, this function returns one of the following errors:

Error Code	Description
10128	The 4D Open for 4th Dimension package has not been initialized.
10136	The connection does not exist.
10154	This command cannot be executed right now.

In this example we are going to add a new invoice. We will also update the gross sales field of the customers table. We will use a transaction to guarantee that either both operations are carried-out or none.

   C_LONGINT ($BindInvID;$BindInvID;$ErrCode)

   C_LONGINT ($transErr)

   C_DATE (vInvDate)
   C_REAL (vInvTotal;vCustSales)
   C_STRING (30;vCustID;vInvoiceID)

   C_LONGINT (vTableInvoices;vTableCustomers;vRecords;vIsLocked;$unused1)
   C_LONGINT (vFieldAmount;vFieldInvCustID;vFieldInvDate;vFieldInvID;$unused2)
   C_LONGINT (vFieldCustID;vFieldCustName;vFieldSales)

   ` Get [Invoices] tableID and [Invoices]Invoice ID fieldID   
   $errCode:=OP Get one field number (vConnectID;"[Invoices]Invoice ID";vTableInvoices;vFieldInvID)
   ` Get [Invoices]Customer ID fieldID   
   $errCode:=OP Get one field number (vConnectID;"[Invoices]Customer ID";$unused1;vFieldInvCustID)
   ` Get [Invoices]Invoice date fieldID   
   $errCode:=OP Get one field number (vConnectID;"[Invoices]Invoice date";$unused1;vFieldInvDate)
   ` Get [Invoices]Invoice total fieldID   
   $errCode:=OP Get one field number (vConnectID;"[Invoices]Invoice total";$unused1;vFieldAmount)

   ` Get [Customers] tableID and [Customers]Customer ID fieldID   
   $errCode:=OP Get one field number (vConnectID;"[Invoices]Customer ID";vTableCustomers;vFieldCustID)
   ` Get [Customers]gross sales fieldID
   $errCode:=OP Get one field number (vConnectID;"[Customers]Gross sales";$unused1;vFieldSales)
   ` Get [Customers]Company fieldID
   $errCode:=OP Get one field number (vConnectID;"[Customers]Company";$unused1;vFieldCustName)

      `…Connect to the server

   ` Create the bind list to create a new invoice
   $ErrCode := OP Create bind ($BindInvID)
   $ErrCode := OP Define bind by numbers ($BindInvID;vTableInvoices;vFieldInvID;0;0;"vInvoiceID")
   $ErrCode := OP Define bind by numbers ($BindInvID;vTableInvoices;vFieldInvCustID;0;0;"vCustID")
   $ErrCode := OP Define bind by numbers ($BindInvID;vTableInvoices;vFieldInvDate;0;0;"vInvDate")
   $ErrCode := OP Define bind by numbers ($BindInvID;vTableInvoices;vFieldAmount;0;0;"vInvTotal")

   ` Create the bind list to load and modify a customer record
   $errCode := OP Create bind ($BindCustID)
   $ErrCode := OP Define bind by numbers ($BindCustID;vTableCustomers;vFieldSales;0;0;"vCustSales")
   $ErrCode := OP Define bind by numbers ($BindCustID;vTableCustomers;vFieldCustID;0;0;"vCustID")
   
   ` Enclose all coming REMOTE database operations in a transaction
   $transErr := OP Start transaction (vConnectID)

   ` Make the customer record, the current record
   vValue:= "ACME Inc."
   $errCode := OP Single query (vConnectID;vTableCustomers;vFieldCustName;"=";->vValue;vRecords)

   ` Let's assume the record exist
   ` Set the table to Read/write in order to lock the record
   $errCode := OP Set access mode (vConnectID;vTableCustomers;1)

   ` Load the record
   Repeat
      vIsLocked := 0
     ` The $BindCustID bind list OP Load record where to put the record data when it loads it.
      $errCode := OP Load record (vConnectID;$BindCustID;vTableCustomers;vIsLocked)
   Until (vIsLocked =0) `assuming it does not remain locked for too long

   ` Create/collect/generate invoice data
   vInvoiceID := "0102030405"
   vInvDate := Current date
   vInvTotal := 100
   ` vCustID has already been set by loading the customers record, thanks to the $BindCustID bind list

   ` Send the new invoice to the server
   $transErr := $transErr + OP New record (vConnectID;$BindInvID)

   ` Let's update the customer record
   vCustSales := vCustSales + vInvTotal
   $transErr := $transErr +  OP Update record (vConnectID;$BindCustID)

   If ($transErr = 0) ` if all went well
      ` Save changes to both tables
      $errCode := OP Validate transaction (vConnectID)
   Else
      ` Let's cancel all operations since Start Transaction (New record, Update record)
      $errCode := OP Cancel transaction (vConnectID)
   End if

   $ErrCode:=OP Delete bind ($BindInvID)
   $ErrCode:=OP Delete bind ($BindCustID)

OP Cancel transaction, OP Validate transaction, START TRANSACTION.