ModelRight

<% currentPage="resources" %>

<% currentItem="scripting" %>

Sample ModelRight COM Automation

<< Sample ModelRight Scripts

ModelRight Object Model Reference >>

COM Automation Introduction
Preparation to use ModelRight automation
Automation using Excel as the Automation client
Navigating and "dumping" a Database Model using COM Automation
- Preparation
- Sample Code
- Notes

COM Automation Introduction

ModelRight supplies an apparatus whereby you can create code in your own development environment and use it to manipulate ModelRight model files. This is the ModelRight DLL SCF.dll ("Script Framework"), which is installed as part of ModelRight, and registers a COM Automation interface that your code can call.

Preparation to use ModelRight automation

Currently (versions XXX), due to idiosyncrasies in the way Windows finds automation components, some of ModelRight's automation capability does not get loaded when called from external applications. So to use ModelRight automation currently requires a workaround. This entails copying all of ModelRight's DLLs from the ModelRight installation directory to the directory of the application which is to use ModelRight's automation.

In a future version we expect to revise ModelRight's DLLs so that this step will not be necessary. In the meantime, this capability is sufficiently useful that it's important to provide instructions on this functionality to allow getting started.

Recommendation: To copy the DLLs to your application directory, use a batch file, so that you will have a record of which DLLs belong to ModelRight, and can later remove these DLLs if you no longer need them.

Automation using Excel as the Automation client

The sample code here is a VBA routine stored in an Excel spreadsheet VBA project. This makes a good demo automation environment for several reasons:

Almost all users will have ready access to Excel, and be able to try this code.
It's directly representative of how to use SCF from any of the Microsoft Office applications, including Word, Excel, Access, Visio, Project.
It's representative of how to use SCF from Visual Basic 6.
It's only a small stretch to adapt this code for use with .NET languages (VB.NET, C#), Delphi, and many other languages.
Although handling COM Automation from C++ is more laborious, this example at least shows the steps that SCF requires you to perform.

Navigating and "dumping" a Database Model using COM Automation

This is essentially a revision of the navigate-and-dump sample previously presented as a VB script.

Preparation

Copy ModelRight DLLs: As noted above under "Preparation to use ModelRight automation", copy all the DLLs from the ModelRight install directory to the Excel install directory (the directory where Excel.exe is installed).
Create a new spreadsheet in Excel and save it with some memorable name
Open the VBA environment (Alt-F11), and create a new module (see Project Explorer, right-click on Microsoft Excel Objects within the current document node, and select Insert Module). A fresh module editing window should open.
Establish a reference to the SCF Automation library. This will make SCF's objects and properties known to Excel (for this project). To do this: Tools > References... then browse down the list to "SCF 1.0 Type Library" and click the checkbox to select it. Click OK. (Note: if you have more than one version of ModelRight installed, do not select the SCF library as just described. Instead, use the Browse button, and select the SCF.dll that you copied in step 1, in the Excel directory. This will ensure that the correct library is called by Excel.)
You can now view the SCF library's features using View > Object Browser, and select SCFLib in the combobox that lists available libraries. A list of available classes appears, and clicking those will show their methods and properties.
Create sample procedure: In the module editing window paste the code below.
Be sure to revise the FilePath string so that it points to a .wer file on your machine!
To run:
-- Open the debug output window: View > Immediate Window.
-- In the module editor, place the cursor somewhere in the Dump_sakilla procedure
-- Press the "Run sub" button on the toolbar (or Run > Run on the menu).
You should see a list of Tables and Columns print to the Immediate window.

Sample Code

Option Explicit
'-------------------------------------------------------
' Sample code showing how to access a ModelRight file
' Note: Use Tools > References to set a reference to "SCF 1.0 Type Library"
'-------------------------------------------------------
Const FilePath = "C:\Program Files\ModelRight\ModelRight3MySQL\Samples\sakilla51.wer"
'----------------------------- Sub Dump_sakilla()
'-----------------------------
Dim Framework As SCFLib.ScriptFramework
Dim Model As SCFLib.MRModel
Dim ModelMRObject As SCFLib.MRObject
Dim Tables As SCFLib.MRObjectCollection
Dim Table As SCFLib.MRObject
Dim Columns As SCFLib.MRObjectCollection
Dim Column As SCFLib.MRObject
Dim TableNameProp As SCFLib.MRPropertyValue
Dim TableName As String
Debug.Print "------- Start --------"
Set Framework = CreateObject("SCF.ScriptFramework")
Framework.Initialize
Set Model = Framework.LoadModel(FilePath)
Debug.Print "Model: " & Model.Name & " Model ID: " & Model.ID
Set ModelMRObject = Model.AsObject
Debug.Print "ModelMRObject: " & ModelMRObject.Name
Set Tables = ModelMRObject.Children("Table")
Debug.Print "------ Tables and columns -------"
For Each Table In Tables
Set TableNameProp = Table.Property("Name")
TableName = TableNameProp.AsString
Debug.Print Table.TypeName & ": " & Table.Name
Set Columns = Table.Children("Column")
For Each Column In Columns Debug.Print " " &
TableName _ & "." & Column.Property("Name").AsString _ & " : " &
Column.Property("Datatype").AsString Next
Next Framework.CloseModel Model Debug.Print "
------- Done --------" End Sub

Notes

Dims and typed variables: Though not strictly necessary, this sample declares all variables (as required by "Option Explicit"), and gives them a type (uses the "As" clause). Once an object variable has a type, when you later type in the code the editor provides code-completion suggestions, which is highly convenient and reduces errors.
Initialize: When calling SCF from an automation client, it's necessary that the first operation with the new Framework object be Framework.Initialize.
Load Model and CloseModel: To obtain a model to work on, it must be loaded from a file, using LoadModel. If you change the model (use Transactions!) you can call SaveModel. When done, Framework needs to be tidied up with a call to CloseModel.
Output via Debug.Print: Whereas the VBScript sample sent output to the Document window, this sample sends it to the Immediate window using Debug.Print. It could of course send output to the spreadsheet, or use Forms and provide a user interface, and so on.