Generating Sql script via Code Template

[gdodd12]

Hi. I am trying to generate a sql script off of new objects placed in the EA Diagram. I was thinking I could create a custom Code Template to do that. For example, someone would create a new class shape in a diagram, along with attributes and some links. They could then choose generate code and use my custom template to generate the sql that uses that class, attributes, and links. Is this possible? Is there documentation that lists all the key words to write the custom code templates?

[Uffe]

Hey there,


The best place is Help -> SDK for Enterprise Architect -> Code Template Framework in SDK.

What that might not tell you is that in order to create a new language, which you can do from the Code Template Editor, you need to define at least one datatype for the language. The dialog to do so pops up when you create the language, but it is not immediately obvious why.
Without at least one datatype, the language does not exist within EA and you cannot write templates for it.

I use EA for modelling and forward-engineering static configuration-type stuff, usually with its own proprietary syntax. I typically just create a dummy type.

... And I'm assuming you've exhausted the Data Modeling facilities of EA. Otherwise, check Help -> Data Modeling.


/Uffe

[gdodd12]

The SDK was pretty useless honestly, unless I was looking in the wrong area. I will take a look at the data modeling thing. I basically just want to create a script that can be run against an object in the EA Diagram, and produce a Sql script. That is why I need some reference of all the keywords that can be used in those code generation templates.

[«Midnight»]

Data modeling is a bit of an 'orphan' as far as the EA code generation framework is concerned.

Beyond the manual (i.e. through the UI) settings for DDL generation you cannot manipulate the process. Most settings - those on the DDL dialog - are stored in the Windows registry. A few product-specific settings (things like file store names) use tagged values. Documentation of these appears incomplete, and does not seem consistent.

EA's DDL generator is a black box; there is no equivalent of code generation templates, or any other method to intervene in how EA produces SQL. As of Last time I looked - at the end of EA 7.1; there is nothing in the release notes and new features list for EA 7.5 to indicate this has changed - any way to invoke this from automation.

If you want to change the 'main' DDL generation settings for EA you need to tweak the Windows registry via direct calls to Windows. You'll still have to open the EA UI and manually run the EA dialogs to generate the DDL. Note that changing the registry settings will affect all subsequent DDL generation for all models and projects, including those you've already completed. For example, if you change the default database (for SQL USE statements and such), and then go back and generate DDL from an old project, the resulting SQL files will have USE statements with the newly set database name.

[Yes, I've reported this to Sparx. No response.]

David

[gdodd12]

Interesting.

I really just was hoping to use the Code Generation templates to create something. If they can use them to build .cs files, I assume there is a way to build SQL scripts off of objects as well. I just don't know any of the syntax for that code generation language they are using, nor can I find it referenced anywhere. I just want my BA's to be able to build objects in the diagrams, than right-click and generate sql scripts with my custom code generation script.

[«Midnight»]

You might very well be able to build DDL by tweaking the code generation templates. Still, you'd have to do a lot of ground work.

One possible issue is that this would involve 'inventing' a 'new' programming language - at least as far as EA was concerned - so that you could recognize the various components and decide what code to emit. Some (but not all) features of code generation seem to drop off a bit once you leave the core languages recognized by EA.

You might be able to get something working via a transformation template. You would have to construct a set of strings - the actual DDL output - and capture them somehow in the target model. Then you'd need to somehow gather these up (in the correct order) and export them somehow. Nothing specific jumps out at me, but I have a feeling this might be doable. [It might be quite arcane though, but who cares as long as it works.] The nice thing about a transformation script is that it could be triggered through the API. As long as you have an automation client running, you could also handle extracting the DDL strings from the target model and exporting them.

Sorry I cannot suggest something more concrete.

[Eve]

Reference for the available keywords in the code templates is at http://www.sparxsystems.com/uml_tool_guide/sdk_for_enterprise_architect/fieldsubstitutionmacros.html

It would certainly be possible to do using the code templates.

[RoyC]

Uffe wrote:

"What that might not tell you is that in order to create a new language, which you can do from the Code Template Editor, you need to define at least one datatype for the language. The dialog to do so pops up when you create the language, but it is not immediately obvious why.
Without at least one datatype, the language does not exist within EA and you cannot write templates for it."

This information is in:

http://www.sparxsystems.com/uml_tool_guide/sdk_for_enterprise_architect/creatingtemplatesforcustom.html

[Carmenza]

Hi gdodd12:

Did you generate a custom SQL script using code template editor?
I did a bit of work to generate a list of objects that are new in a package using version and modified date attached to the tables but I get stack to link this file with the DDL code generator.

[«Midnight»]

Don't attempt to link it to DDL generation. Do it though the Code (i.e. program source code in your 'new' language) generation process, or via model transformation. Which way you do it depends on whether you build your scripts via the code generation or model transformation features of EA.