Compound SQL Statements

The Rhapsody Database Configuration Editor supports having multiple SQL commands within a single 'statement'. This allows closely related commands to be kept together, and is particularly intended for using foreign keys, and automatically generated identities and sequences. For example, in SQL Server, you may perform an insert such as:

INSERT INTO NextOfKin (PatientID, FamilyName, GivenName, Sex, Relationship, PhoneNumber)
   VALUES (
      @PID.PatientID.IDNumber,
      @NKL.NextOfKinName[0].FamilyName.Surname,
      @NKL.NextOfKinName[0].GivenName,
      @NKL.Sex,@NKL.ContactRole.Identifier,
      @NKL.PhoneNUmber[0].PhoneNumber.PhoneNumber,
)

Assuming that the NextOfKin table had an identity column as the primary key, the newly allocated identifier could then be retrieved using the following SQL statement, and then used in subsequent statements:

SELECT @@IDENTITY As NextOfKinID 

Previously these closely coupled statements would have to be defined seperately, and then manually kept together. The Rhapsody Database Configuration Editor allows compound statements to be defined for any SQL statement (but not stored procedure calls).

Multiple statements are separated using a semi-colon (semi-colons defined within string literals or comments are ignored). The compound statement for the example above is shown below:

INSERT INTO NextOfKin (PatientID, FamilyName, GivenName, Sex, Relationship, PhoneNumber)
   VALUES (
      @PID.PatientID.IDNumber,
      @NKL.NextOfKinName[0].FamilyName.Surname,
      @NKL.NextOfKinName[0].GivenName,
      @NKL.Sex,@NKL.ContactRole.Identifier,
      @NKL.PhoneNUmber[0].PhoneNumber.PhoneNumber,
);
SELECT @@IDENTITY AS NextOfKinID

The example above first performs the SQL insert into the NextOfKin table. The first column in that table is defined as a SQL Server identity column, and so its value is automatically generated by the database. The subsequent line then retrieves the most recently allocated identifier within the current transaction and returns it as NextOfKinId. This 'column' can then be used in Result Substitution, or used by subsequent queries.