Mediator driver does not limit itself to providing compatibility with DBFNTX and DBFCDX drivers. One huge advantage of this solution is providing programmers with SQL mechanisms offered by database on xBase language level.
SQL Transactions
One of the best features of SQL servers are transactions which ensure logical integrity of modified data. By default, Mediator driver works in auto-commit mode, so all the introduced changes are automatically confirmed on the record level. It ensures 100% compatibility with standard RDD drivers.
Introducing small changes in an application, you can fully use the advantages of a transactional system. There are following commands available in mediator.ch file for this purpose:
BEGIN TRANSACTION - it begins the transaction
COMMIT TRANSACTION - it ends the transaction and confirms all the changes
ROLLBACK TRANSACTION - it ends the transaction but the changes made are withdrawn
Executing SQL commands
Mediator offers a couple of functions which enables sending SQL commands, prepared by a programmer, to SQL server. SQL commands returning many records should be executed using USE qry AS 'SELECT ...' command. The command sends a given SQL command to the database, and it renders its result accessible as an open working area which includes the result records.
Example:
USE zap1 AS 'select name, surname, sum( salary ) as sw from salary group by name,surname'
While .not. eof()
? NAME, SURNAME, SW
SKIP
enddo
In a given example the result is returned as a set of READ-ONLY records, which can be looked through forward only. Using SCROLLABLE or PERMANENT option, you can place the result in a table, which gives a full ability to navigate and to keep the result records for later processing.
In a case when we are sure that SQL query will return only one value, it is better to use MedSelVAL() function. In this way, a forced creation of a new working area will be avoided.
Example:
sum_salary = MedSelVal('select sum(salary) from salary')
SQL command which does not return a result should be executed by using MedExecSQL() function.
Example:
Wyn = MedExecSQL('create table test ( f1 char(10), f2 number(10,2) )')
