EARecordset is a Script Library I’ve created to let me more easily work with Recordsets (XML structures) returned by Sparx EA queries.
One of the key features of Sparx EA is the ability to directly query the repository using SQL to pull information for reports and to export.
Sparx EA’s API provides a three functions as part of the Repository Class for working with SQL.
- GetElementSet: returns a collection of Element Objects based on the the Element IDs returned by a provided SQL query.
- SQLQuery: returns an XML structure containing the results of the query. I’ll refer to this XML structure as a Recordset.
- Execute: allows you to insert and update records in the repository. Execute is an undocumented feature, and should only be used if you really understand how the Sparx EA repository works. Currently I limit using it to inserting and updating tagged values, and some of the fields of other objects. There are complexities in the Sparx EA data model that even now I’m still not sure I fully understand, so I use the the API to add all other objects. E.g. Elements.addNew()
Sparx EA’s template fragments also let you:
- Query the repository directly using an SQL Query
- Call a Script that returns a Recordset that the fragment treats as if it was returned from and SQL Query
- Call a Script that returns a string containing Rich Text Format (RTF) text.
My primary object provided by the library is and object that encapsulates a Recordset so that I can:
- Initialize with an SQL query string, and then treat the resulting encapsulated Recordset as a set of named columns and enumerated rows. E.g. getColumnValue(10,”Object_ID”) will return the value for the Object_ID field from the 11th row of the Recordset.
- Initialize without an SQL query string, and then procedural add rows and columns to the encapsulated Recordset. E.g. iRow = addRow(); setColumnValue(iRow,”Name”,”this is the new name, for a new row”)
- Use to update the encapsulated Recordset. E.g. setColumnValue(10,”Name”,”Updated Name”);
- Use to bulk add Tagged Values. I create a encapsulated Recordset using option 2, then create a row for each Tagged Value I want to add, set the EAID (of the Object the Tagged Value is for), Property Name, Property Value, and optionally the Property Notes. My library loops through the encapsulated Recordset, and if supported by the Repositories DB, the library creates a single Insert SQL for adding multiple tagged values (this uses the undocumented Repository.Execute function, and some SQL tricks)
- Create a Recordset from a CSV file
- Create a Recordset from an Excel file
- Write a Recordset to an Excel file (allows you to keep the formatting of you Excel file intact)
I’m in the process of tightening up the code for the library and finishing up a set of unit tests to make it easier to debug and maintain.