Automating ER/Studio from Delphi 2009 - Getting Started

Last month I've been in Toronto on Embarcadero technical kick-off session where software consultants, product managers, and all technical stuff were sharing knowledge about CodeGear and DatabaseGear products, technologies, plans, roadmaps and - most importantly - building team spirit of our new, bigger family of techies. Right after the kick-off I went for summer vacation to sunny Croatia and that is the reason for the delay in writing this post.

I would say that 95% of Delphi and C++Builder applications are using some kind of a database. While Delphi already provides everything you need to rapidly build database applications, it is not aimed at full featured cross-platform database modelling like ER/Studio. I've been using many of the 3rd party programs to build my databases, but I'm really impressed with the capabilities of ER/Studio.

Before joining Borland/CodeGear/Embarcadero I've spent 5 years writing Delphi 3-5 applications that were automating Excel for creating nicely formatted spreadsheets full of figures coming from a big data warehouse at the financial controlling department at one the biggest retail banks in Poland. Some of my colleagues were afraid that there would not be enough work left for them to do if you can have a Delphi applications that generates Excel files on a massive scale:-)

My first inspiration for writing automation code in Delphi was an excellent article "Delphi and Microsoft Office: Automating Excel and Word" by Charlie Calvert - currently working as C# Evangelist at Microsoft. Another great source of information at the time was Binh Ly's great "Techvanguards" site. Nowadays there is a very nice collection of links to Delphi Automation resources on the "OLE/COM/DCOM/Automation/ActiveX - Delphi knowledge base" site.

It will take some time until CodeGear and DatabaseGear products have deep integrations, but there are some things possible today. ER/Studio can be automated through OLE Automation and there are lots of existing VBA macros that make use of the rich object model of ER/Studio. Why not to go one step further and automate ER/Studio using Delphi?

In this post I'm going to present step-by-step instructions on creating a Delphi VCL Forms Win32 application that starts ER/Studio, displays its version number and closes it. Nothing fancy but just the starting point for more complicated scenarios. I will be using the current shipping version of ER/Studio 7.6.0 and the beta version of Delphi 2009 aka Tiburon.

Delphi provides the "Import Component" wizard available from the "Components" menu that can take a COM type library and generate VCL components that wrap functionality provided by classes defined in a type library. If you want to install these components into the "Tool Pallette" all you need is to create a "Package" project, add to it a Delphi unit generated by the wizard, and install the package into the IDE. In Delphi 2009 the "Import Component" wizard is even easier to use as described in the "Tiburon Sneak Peek: Import Component Wizard" blog post by Chris Bensen. You can start directly from the wizard and the package project will be generated for you automatically. There is also a very handy new addition of search functionality in the "Registered Type Libraries" tab, so you do not have to scroll through the list of all registered type libraries, which typically tends to be very long.

Importing ER/Studio type library into Delphi

First make sure that ER/Studio and RAD Studio are installed on your machine and then start "CodeGear RAD Studio" and select "Import Component" from the "Components" main menu. This will display the "Import Component" wizard.



On the first "Personality, Framework and Platform" tab select "VCL for Delphi Win32" and click the "Next >>" button. Note that if you have started RAD Studio using "Delphi 2009" shortcut this tab would not appear, because the choice of the IDE personality to be used has been already made.



On the next tab select "Import a Type Library" option and click "Next >>".



The next tab is titled "Registered Type Libraries". Here we can use the new Delphi 2009 search functionality and locate "ER/Studio Type Library", which can be found in the "C:\Program Files\Embarcadero\ERStudio7.6\ERSTUDIO.exe" file.



On the next tab we can specify the name of the Palette Page, where generated components should be installed. I have entered "ER Studio" here, but this could be anything. Also make sure to check the "Generate Component Wrappers" option and click "Next>>".



On the next tab we are going to select the new Delphi 2009 option "Install to New Package", which will save us a step of creating a package project.



On the next screen we need to specify the name and the location of the new package project and also an optional description of the project that will appear in the list of installed packages. I have chosen for "D2009ERS76" project name and location under Delphi 2009 demos, but again this could be anything.

This is the last screen of the wizard. All necessary information was provided, so when we click on the "Finish" button, the wizard will generate a new "Package" project and import the ER/Studio type library into the "ERStudio_tlb.pas" Delphi unit. Now it is a good time to save the project, using either "File \ Save" menu or just press "Ctrl+S" keyboard shortcut.



Initially you will see a number of errors in the "Errors" section of the "Structure" view related to some comments in the generated unit not starting from "//". These errors are easy to fix. Just select these lines of code within generated comment blocks that do not start from "//" and use "Ctrl+/" shortcut to automatically comment them out. The fastest way to identify offending lines is through trying to compile the project. To compile the project you can either select "Project \ Compile" from menu or right click on the project node in the "Project Manager" and select "Compile" from the context menu or just do "Ctrl+F9". After commenting out all offending lines you should see the "Done: Compiled" message.



There is one more not obvious thing to do before we would be able to install the package into the IDE.

It is not allowed to have more then one component of the same name in the "Tool Palette". Delphi comes with the preinstalled "TShape" component and one of the generated component classes also happened to be named "TShape". In order to be able to install our ER/Studio package into the "Tool Palette" we need to rename the generated "TShape" component name to something else, for example "TShapeERS". This is where Delphi "Rename" refactoring comes handy.

First let's locate the "TShape = class(TOleServer)" declaration inside the generated unit. For this just select "Ctrl+F" to display "Find Text" dialog and enter "TShape = class" into the text to find edit and click OK. Position cursor inside the "TShape" identifier, right click and select "Refactoring \ Rename" option from the context menu to display the "Rename" refactoring dialog (or just select "Shift+Ctrl+E" shortcut).



Type in "TShapeERS" in the "New name" editbox and click OK. This will display "Refactoring" tab with the list of all places that would be affected by the refactoring. Just click on the Rubic cube icon or press "Ctrl+R" to perform the rename refactoring and change all occurences of "TShape" to "TShapeERS".



Now we are ready to install the components into the "Tool Palette". Save the project for example by "Ctrl+S" and then right click on the project name ("D2009ERS78.bpl") inside the Project Manager window and select "Install" from the context menu. This will compile the project and display the information message with the confirmation that our package has been installed and with the names of all components that have been registered.



Very impressive number of components, I must say:-) If I count them correctly there are 208 new components installed. It looks like lots of things to automate;-)

At this point we are done with importing ER/Studio type library into the IDE and we can now start using generated components in Delphi VCL Forms applications. Select "File \ Close All" to close the package project.

Simple Delphi ER/Studio Automation

Let's try to automate ER/Studio from Delphi application. Select "File \ New \ VCL Forms Application - Delphi" to create an empty application. Select "File \ Save All" and give the application and its main form any name that makes sense to you. I've chosen for "ERSVersionInfo" for the project and left the default "Unit1" for the the main form.

If you expand the new "ER Studio" tab on the "Tool Palette" you should see all the new components ready to be dropped on the form, but which one to start from?



In the ER/Studio installation directory there is the "ERStudio Automation Interface Reference" (AIRef.chm) help file that contains all the necessary information to get us started. The ER/Studio can be automated by external application or from inside using macros. We are going to use the first scenario and automate ER/Studio externally using our Delphi VCL Forms application. According to the reference external automation programs should start from an instance of an "Application" object.



We start from dropping the "TApplication" component on the form. Just start typing "Application" in the top of the Tool Palette to find the "TApplication" component and double-click it to add it to the form. To keep things simple we're just going to display the version number of the currently installed version of ER/Studio.

All wrapper components generated by the "Import Component" derive from the "TOleServer" class and inherit from it some published properties that show up in the Object Inspector at the design time. After dropping the "TApplication" component on the form I have changed "AutoConnect" and "AutoQuit" from "false" to "true", so the ER/Studio starts and stops automatically when our Delphi application starts and stops.



I've also dropped a button component on the form to display ER/Studio version number. It is only one line of code that needs to be written in the button's "OnClick" event.



And now we are done! Just click on the green arrow icon to run the application (or just press "F9") and we should see a little form with just one button. At the moment the application starts, the ER/Studio starts automatically as well. If you press the "Show Version" button you should see the following message:



When you close the Delphi application, the ER/Studio will close as well.

Summary

Delphi is the best tool for building native Windows applications. The support for COM Automation was introduced many years ago in Delphi 3 and it is still being improved, also in the new Delphi 2009. ER/Studio exposes very rich object model that can be accessed by external applications to automate its functionality. In this article I have demonstrated importing ER/Studio type library into Delphi and created a very simple application that is just the starting point for more useful automation scenarios.

The Delphi package project with ER/Studio VCL component wrappers ready to be installed into Delphi is available for download Code Central.

Go Delphi! Go ER/Studio! Go Embarcadero!