Using Generated Unique Primary Keys for Inserts

Many database products provide a mechanism for generating unique primary keys.

For each database concept that requires a primary key value to be provided in this way, you configure the mechanism provided by your database and provide it to TIBCO BusinessEvents in a stored procedure that you reference in an XML file. You can also reference an Oracle sequence in the same way. When a record is inserted into the database, the unique value generated for the specified primary key property is used. The database concept also uses the generated primary key value.

Procedure

  1. Create an XML file with the extension .sequences.xml .

    Configure the XML file is as follows: 

    <?xml version="1.0" encoding="UTF-8"?>
<unique_identifiers>
   <unique_identifier entity="ConceptURI"
                    property="PropertyName"
           unique_identifier="SequenceName"
                 stored_proc="StoredProcStatement"/>
</unique_identifiers>

    Where, add a unique_identifier element for each database concept that will acquire a primary key value using this mechanism. Following are the parameters that you can define:

    Parameter Description
    entity The URI of the concept whose specified property will use the sequence or stored procedure.
    property Name of the property that holds the primary key.
    unique_identifier Name of the sequence (if you are using a sequence). Used for Oracle DBMS only.

    If both unique_identifier and stored_proc are present, stored_proc is used.

    stored_proc Name of the stored procedure (if you are using a stored procedure). The value must be a callable JDBC statement. The called stored procedure must take only one OUT type parameter.
    Note
     The syntax of the value for this attribute depends on the JDBC driver you are using for the database concept.

    For Oracle thin Driver use this syntax: 

    stored_proc="CALL YourStoredProc (?)"

    For SQL Server use this syntax: 

    stored_proc="EXEC YourStoredProc ?"
  2. Add the XML file to the project as follows:
    1. Select File > New.
    2. Select Other > XML > XML.
    3. Click Next, type a name for the file, and click Finish.
  3. You can also import an existing XML file. To do that, perform the following:
    1. Select File > Import > File System.
    2. Specify the file you want to import, and click Finish.