Insert multiple records with a single query (Oracle)

Warning: I typically only use the method described in this post for TaggedValues, almost every other “add” to the Sparx EA repository requires additions to multiple tables in the underlying database, unless you know what you’re doing I strongly suggest using the API to do additions and updates in your repository.

I often need to add extra information to elements in Sparx EA using tagged values. Currently at my work we’re using Oracle as the DB for our Sparx EA repository, and I found the API approach to adding tagged values to be fairly slow:

  1. Use the API to “get” the Element you want to add the tagged value to.
  2. Use Element.TaggedValues.addNew()
  3. Use Element.TaggedValues.Update();
  4. Use Element.TaggedValues.Refresh(); // required if you going to add more than one TaggedValue to the element.

So instead I wanted to use the undocumented Repository.Execute function to directly insert new TaggedValues in to the underlying database (see warning above).

There are multiple tables in the repository for TaggedValues, with a seperate table for:

  • Attributes: t_attributetag
  • Connectors: t_connectortag
  • Elements: t_objectproperties
  • Packages: t_objectproperties (each Package is “paired” with an entry in the Element table t_objects)
  • Operators: t_operationtag
Sparx EA tables for tagged values

To insert in to any of these tables, using Oracle, I needed to learn 3 things:

  1. How to generate a GUID: EA_GUID
  2. How to increment a counter: PropertyID
  3. How to insert multiple new rows in a single query statement

1.0 Generate a GUID

Each tagged value entry has to have a unique GUID assigned to it. The GUID is stored in the ea_guid field. You should be able to use a function provided by your Repositories DB to get a GUID, for Oracle the function is SYS_GUID().

INSERT INTO t_objectproperties 
(PropertyID, ObjectID, Property, Value, ea_guid)
VALUES
(PropertyID_seq.nextval, 2329, 'Tracking ID', 'bREQ-A00012', SYS_GUID())

If you Database doesn’t provide a function to generate GUIDs, you can generate a pseudo GUID using a script, and then include that GUID in you SQL statement.

// Example JScript / Javascript Code for Sparx EA
// Adds a TaggedValue to an Element with the Object_ID of 2329
function createGUID()
{
function private_p8(s)
{
var p = (Math.random().toString(16)+"000000000").substr(2,8);
return s ? "-" + p.substr(0,4) + "-" + p.substr(4,4) : p ;
}
return "{" + private_p8() + private_p8(true) + private_p8(true) + private_p8() + "}";
}
SQL = "INSERT INTO t_objectproperties ";
SQL += "(PropertyID, ObjectID, Property, Value, ea_guid) ";
SQL += " VALUES ";
SQL += " (PropertyID_seq.nextval, 2329, 'Tracking ID',";
SQL += " 'bREQ-A00012', '" + createGUID() + "')";
Repository.Execute(SQL);

2.0 Increment a counter index

The filed PropertyID is an auto-increment unique key, in most DBs, this would automatically be incremented when you insert a new record, but for Oracle you have to “tell it” to increment.

The syntax for this is a bit odd in oracle, there is a sequence object that can be referenced by a pseudocolumn, the name of the sequence pseudocolumn for the t_objectproperties table is PropertyID_seq:

INSERT INTO t_objectproperties 
(PropertyID, ObjectID, Property, Value, ea_guid)
VALUES
(PropertyID_seq.nextval, 2329, 'Tracking ID', 'bREQ-A00012', SYS_GUID())

3.0 Insert multiple entries with a single statement

One of the key reasons I started experimenting with this is I wanted to speed up the creation of tagged values that store tracking IDs (for traceability). Initially I used a loop to run Repository.Execute for each insert, which was faster than the API, but still not as fast as I had hoped.

When I did my initial research it seemed that I didn’t have a choice but to use the “looping” method, but recently found a way insert multiple rows and increment the sequence object in a single insert statement.

INSERT INTO t_objectproperties 
(PropertyID, ObjectID, Property, Value, ea_guid)
SELECT
PropertyID_seq.nextval, OBJECTID, Property, Value, ea_guid
FROM
(
SELECT 2329 AS OBJECTID, 'Tracking ID' AS Property,
'bREQ-A00012' AS Value, SYS_GUID() AS ea_guid
FROM DUAL
UNION ALL
SELECT 2330 AS OBJECTID, 'Tracking ID' AS Property,
'bREQ-A00013' AS Value, SYS_GUID() AS ea_guid
FROM DUAL
)

The key is to insert from a Select instead of directly providing the Values. Basically we’re tricking Oracle in to thinking it’s inserting based on the contents of an existing table.

The insertTaggedValues function in my EARecordset library takes care of all of this for me.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.