Record creation is handled differently for OpenEdge databases and MS SQL Server data sources accessed through the DataServer. The difference occurs because ABL code run against an OpenEdge database follows different record-scoping rules than ABL code run against a MS SQL Server data source.
Records are scoped to the outermost block in which they are used, or the smallest enclosing block that encompasses all references to a record. In the absence of enclosing blocks, a record is scoped to the procedure block. Outside of record scope, a buffer associated with a record is cleared by ABL.
The OpenEdge database writes a record to the database after all its indexed columns are assigned. The MS SQL Server DataServer writes records or record changes out to the database at the end of record scope or at the end of a transaction boundary within that record scope. For this reason, the duration of record locks can vary between OpenEdge and a MS SQL Server data source since the record lock will be held from the time of the write until the end of a transaction.
While both OpenEdge and MS SQL Server data sources both commit changes at a transaction boundary, OpenEdge is capable of downgrading the lock on a record within scope after a commit boundary has been traversed. A MS SQL Server data source drops all record locks at the end of a transaction. See Transaction scoping and buffer management for more information.
The following ABL statements will force a physical database write to a MS SQL Server data source before the end of a transaction block or the end of record scope:
RELEASE
VALIDATE
RECID/ROWID
While these statements can change lock status and force a write, they have no affect on transaction scope. Locks will be held from the time of the write until the time of a commit or rollback. For the RELEASE statement, the record is released from its buffer but is inaccessible and still locked awaiting a transaction boundary. After a RELEASE, a record would need to be reread in order to refill buffer content. The OpenEdge database waits for all the key values to be assigned or for the end of record scope to write a record regardless of the outcome of a VALIDATE statement. The MS SQL Server DataServer writes a record at the time of the VALIDATE since a write is required in order to resolve the integrity of a record within the MS SQL Server data source. OpenEdge can assign a RECID or ROWID without writing a record. For a MS SQL Server data source, the generation of RECID and ROWID are contingent on a record write in order to produce its value.
The availability of database changes is dependent on when the record is written. The user writing the record can read back the in-memory copy of the record as soon as it has been written out to the database. Another user trying to access the same record may or may not see the changes written to the database, depending on the transaction isolation level of the foreign data source. While OpenEdge will show other users records modified but not yet committed, a MS SQL Server data source will not report on the state of record content until a transaction boundary has been traversed. Therefore, while OpenEdge might indicate that a record already exists or has certain content before another user has committed such changes, a MS SQL Server data source will report that the record is in use and block access until the transaction is complete. The code fragments in this section illustrate these differences.
Suppose that you have a table called customer with a field called custnum that is defined as an indexed field, and you write the following procedure:
DO TRANSACTION:
CREATE customer.
ASSIGN
customer.name = "SMITH"
customer.custnum = 10
customer.address = "1 Main St".
END.
When you run this procedure:
The OpenEdge database does not create the record at the CREATE statement. Instead, it writes it to the database at the end of the record scope or when the index information is supplied, whichever occurs first. In this example, the OpenEdge database writes the record after executing the statement custnum = 10.
The DataServer writes the record later, at the end of the record scope. In this example, it writes the record after executing the statement END.
The following procedure, which uses multiple buffers for the same record, illustrates the differences between the OpenEdge database and DataServer record creation:
DEFINE BUFFER xcust FOR customer.
CREATE customer.
customer.custnum = 111.
FIND xcust WHERE xcust.custnum EQ 111.
DISPLAY xcust.
In this procedure, the code creates a customer, sets custnum equal to 111, then finds and displays the customer record using custnum (the unique index). In this case:
The OpenEdge database displays the customer 111 record.
The DataServer fails to find customer 111 because it has not yet written the record for customer 111 to the data source.
To get a consistent response from the DataServer, use this procedure instead:
DEFINE BUFFER xcust FOR customer.
CREATE customer.
customer.custnum = 111.
VALIDATE customer. /* or RELEASE customer. */
FIND xcust WHERE xcust.custnum EQ 111.
DISPLAY xcust.
The VALIDATE or RELEASE statement causes the DataServer to write the customer 111 record to the database before the FIND statement occurs. Using the VALIDATE or RELEASE statements forces a write to the data source even if the transaction has not yet completed. This makes the record available in the local database cache and it will be found if the record must be re-read before the end of the transaction.
Note: If you set the default value when creating a record, you must change the value before you create another record with the default value if the field is part of a unique key. Otherwise, the second record will cause a duplicate key error.
Record updates are handled similarly to record creation. A record is updated in a MS SQL Server data source at the end of record scope or at the end of a transaction, whichever comes first. For example, when you run the following procedure, the newly updated record is not found:
FIND customer WHERE customer.custnum EQ 12.
DO TRANSACTION.
ASSIGN customer.address = "14 Oak Park".
FIND customer WHERE customer.address EQ "14 Oak Park".
END.
To send the record to the data source sooner, use the VALIDATE statement, as follows:
FIND customer WHERE customer.custnum EQ 12.
DO TRANSACTION:
ASSIGN customer.address = "14 Oak Park".
VALIDATE customer.
FIND customer WHERE customer.address EQ "14 Oak Park".
END.
For more information about record scoping and transaction behavior, see OpenEdge Getting Started: ABL Essentials.
RELEASE