VB Scripts

Top  Previous  Next

If you select a Table and then click on the SQL tab in the Property Browser, you will see the corresponding CREATE TABLE statement.

SQL Property Page with the Run Script button selected

SQL Property Page with the Run Script button selected

   

SQL Property Page with the Edit Script selected

SQL Property Page with the Edit Script selected

ModelRight generates the CREATE TABLE statement, and any other DDL statement, by evaluating a VB Script  in the context of the given object (in this case the Table you selected).  This section describes how this is accomplished and the many ways you can customize it.

Any object that generates DDL for has a SQL Property Page.  If you select the object and then select the SQL Property Page (i.e. the SQL tab in the Property Browser) you will see the DDL that resulted from evaluating a VB Script.  If you then select the Edit Script button in the page's toolbar, you will see the VB script that was evaluated.

When ModelRight needs to generate DDL, it first needs to determine what VB Script to evaluate.  It attempts to find a VB Script by performing the following steps:

1.if the object being evaluated has a value for the script, use it.

2.if the Template that the object inherits from has a value for the script, then use that.

3.use the corresponding Script object in the Script Explorer.  When ModelRight starts up, it looks for VB Scripts that have been stored in files under the "Scripts" sub-directory (beneath the executable's directory).  If it finds any, then it stores this internally as the default script value.  This way all models will use the given script as the default.  If it doesn't find a script file, then ModelRight will load the script from an internal resource.

Hence, changing a VB Script  in different areas of the product will affect the DDL that different objects produce:

Individual Object - If you want to change the VB Script for just one object, then select the object, go to the SQL Property Page, click the Edit Script button in the page's toolbar, and make changes to the VB Script in the edit control provided.  This creates a local value for the given script property.  If you then decide that you want to use this VB Script for all objects (of the given type in the model), click the "Apply To All" button.  This will removed the local value and set it on the corresponding Script object in the Script Explorer.

Template Level - if you want the change the VB Script for all objects that inherit from a given Template, change the script in the SQL Property Page of the Template.

Script Objects - Select the Scripts tab at the bottom of the Model Explorer bar.  Navigate down the Database tree to the type of object and type of script that you want to change.  Script values entered here will be used as the default script for all objects of the given type.

File - If you enter a value for a Script object as described above, then you can right click on it in the Script Explorer to bring up a context menu.  Select "Save To Disk" to save the script to file, it will then be used as the default across models.

Script Explorer

The Script Explorer is located under the Scripts tab of the Model Explorer.  It contains the Script Categories and Script objects that are used for schema generation and other purposes.  Script objects under the database category  provide a place to view and change the model-wide default VB Script values.  If you modify the text of a Script object, it will effect the DDL generated for all objects of the given object type (like Table) for the given property (like Create Statement).  If you would like to apply that change to all models, bring up the context menu (by right clicking on the Script in the Script Explorer) and select Save To Disk.

GlobalNamespace Script

ModelRight appends the contents of the built-in Script named GlobalNamespace to any other script that it executes.   i.e. this is the place where you can add a scripting function that can be re-used by all of the other scripts.

Pre and Post Scripts

These scripts will be evaluated before and after the Create Statement.

Ad Hoc Scripts

While VB Scripts are used primarily for DDL generation, you can write scripts to do anything - produce reports, make batch modifications, perform custom validations, etc...

1.Bring up the the Script Explorer - select the Scripts tab at the bottom of the Model Explorer

2.Create a new Script object - right click on the User Defined Script Category and select New Script from the context menu

3.Enter a script.  Here's a sample script that prefixes all Table names with a "T_".   The Scripting API is integrated with the internal transaction management system, so after you run the script, make you can hit Undo (and then Redo, etc...).

Dim Framework

Set Framework = CreateObject("SCF.ScriptFramework")

Set Models = Framework.ActiveModels

Set Model = Models.Item(1)

Model.BeginTransaction("Batch Rename Tables")

Set Tables = Model.AsObject.Children("Table")

For Each Table In Tables

 Set PropValue = Framework.CreatePropertyValue("Table", "Name")

 PropValue.FromString("T_" + Table.Name)

 Table.SetProperty "Name", PropValue

Next

Model.EndTransaction