Chat now with support
Chat with Support

Toad Data Modeler 7.0 - User Guide

Introduction User Interface Models and Model Objects
Physical Data Model
Entity Relationship Diagram Objects Basic Database Design Advanced Database Design
Universal Data Model Logical Data Model Working with Model Objects
Features and Tools
Application Variables Export/Import DDL Script Generation Graphics Model Actions Print Create New Project Reports Reverse Engineering Scripting and Customization About Templates Tips and Tricks Toad for Oracle Integration Toad Intelligence Central (TIC) Integration Tools Version Control
Options and Configuration Databases
Amazon Redshift 1.0 IBM DB2 LUW 9.5 IBM DB2 LUW 9.7 IBM DB2 LUW 10.1 IBM DB2 LUW 10.5 IBM DB2 LUW 11.1 IBM DB2 z/OS 10 IBM DB2 z/OS 11 Greenplum 4.1 Greenplum 4.2 Ingres 9.3 Ingres 10.0 EDB Postgres Advanced Server 10 Microsoft Access 2007/2010 Microsoft Azure SQL Database V12 Microsoft SQL Server 2005 Microsoft SQL Server 2008 Microsoft SQL Server 2012 Microsoft SQL Server 2014 Microsoft SQL Server 2016 Microsoft SQL Server 2017 MySQL 5.0 MySQL 5.1 MySQL 5.5 MySQL 5.6 MySQL 5.7 MySQL 8.0 Oracle 10g Oracle 11g Release 1 Oracle 11g Release 2 Oracle 12c Release 1 Oracle 12c Release 2 Oracle 18c Specifics - Oracle 12c Release 2 PostgreSQL 9.0 PostgreSQL 9.1 PostgreSQL 9.2 PostgreSQL 9.3 PostgreSQL 9.4 PostgreSQL 9.5 PostgreSQL 10 SQLite 3.7 Sybase ASE 15.5 Sybase ASE 15.7 SAP ASE 16.0 Sybase IQ 15.2 Sybase SQL Anywhere 11 SAP SQL Anywhere 17 Teradata 13 Vertica Database 8.0
Copyright Legal Notices

Scripting in Script Editor

In Toad Data Modeler, you can create and edit scripts via Script Explorer and Package Explorer. For scripting purposes, Script Explorer should be used. (Script Explorer)

To create a new script

Select a script/folder in Script Explorer | right-click and select Create New Script.

To edit already existing script

Select a script/folder in Script Explorer | right-click and select Edit Source Code(in New Window).

In both cases Script Editor opens.

When you modify a script source code or write a new script code:

  • The particular script is locked and no other user can modify it.
  • The script is disabled in Script and Package Explorers.
  • Use Commit to save the changes and Rollback to cancel the changes in Script Editor. As soon as you press any of these buttons, the script becomes available for other users (will be unlocked automatically).

Note: After you click Commit, the changes you've made for the script will be saved within the TDM application, however they will not be saved in particular package on your disk (My Package). You can either save the package in Package Explorer, or close the application - the changes will be saved in appropriate package and on disk automatically.

Scripting Languages in Toad Data Modeler:

To make a petty script modification

(It doesn't work for read-only scripts.)

  1. In Script Explorer, select a script that you want to modify.
  2. Right-click the script and select Edit Source Code.
  3. In the Script Editor, edit the source code directly.
  4. Press Commit to confirm changes, or Rollback to cancel the changes.
  5. Save the changes in appropriate package and on your disk - Click in Package Explorer.

To modify functions of system scripts

System scripts are read-only and it's not possible to edit their source code directly. (See disabled Commit and Rollback icons in Script Editor.)

If you want to edit any functions of a system script, you have to create a new script and edit appropriate functions in it. For detailed example, please see User Guide, "Customization - Sample" chapter.

To write a new script from scratch  

In Toad Data Modeler, you can create a new script or further extend functionality of already existing scripts - user and also system scripts.

  1. Open Script Explorer.
  2. Select a folder (category) where you want to add the new script.
  3. Right-click the selected folder and select Add New Script. -> A new item will display in the Script Explorer under the selected folder.
  4. Double-click the new script to open it in Script Editor.
  5. Write or insert the scripting code to the right window. Use Commit or Rollback for saving and canceling the changes. Remember to save the changes in the package too.

Internal Scripting

In Toad Data Modeler, the following scripts are supported:

  • JScript
  • Visual Basic
  • Internal Script

Use the internal script for writing more extensive texts where only few commands are contained.

The internal script is similar to markup languages such as XML, HTML etc.

Every sign that is not a text must be marked by this sign - "#".

Key Words

  • import  
  • require
  • if
  • else
  • endif
  • script - Script function is generated. It allows users to define script type (e.g. JScript or VBScript) where the content between script and endscript commands is written.
  • endscript
  • proc - Creates a function in JScript. It is possible to define parameters here.
  • endproc -
  • call - Calls a procedure (function). It is possible to define parameters here.
  • <% - Beginning of macro.
  • %> - End of macro.
  • Forall - Executes iteration over list.
  • @ - Shows that the following text is an expression.

"script" Command

Use this command to insert to the internal script another part of a scripting language.

#script language=”{Scripting language}”

{Code of Scripting Language}

#endscript

Example:

#script language="Jscript"

function something() {

  Log.Infomation(‘My Message’);

}

#endscript

"proc" and "endproc" Command

Use this command to create a procedure that is available in internal language.

#proc

  Name([parameters])

    Code of Procedure

#endproc

Example:

  #proc

    Greetings()

      Hello

      Buy

  #endproc

"Call" Command

Use this command to call procedures defined by command "proc".

Example:

#call Greetings()

"forall" Command

Use this command if you want to execute iteration over a list. The result of the iteration should be a text.

Command syntax is the following:

#forall LIST_NAME (PARAM1, PARAM2, PARAM3, PARAM4, PARAM5 );

Example:

  #forall Model.tables('Create Table'+IterateItem.Name+'(','',GenerateColumns(),',',')' );

"<%", "%>" Command

Angled brackets with percentage insert part of internal script where JScript is used (or another scripting language). The expression is evaluated immediately.

Example:

<% if (Model.Count>0) Log.Information(‘something’); %>

"@" Command

It is similar to "<% %>", however the difference is that after the @ sign, only one expression follows.

How to Create Custom Properties

To further extend the functionality of Toad Data Modeler, you can create your own custom properties for most objects. To create a new custom property:

  1. Right-click an object in Model Explorer and select Add Custom Property.

  2. New Custom Property dialog appears. Here you can configure your property. Once, you're done, restart Toad Data Modeler and your property will be created and accessible via scripting.

Option Description
Caption/Name Logical and physical name of the property. Name is used to refer to the property in scripting.
Level

Determines on which level the property should be created. For example, if you create a new custom property for an entity (in Oracle 10g model), you can choose from the following options:

  • PEREntityOR10 - The property can be used only in Entities in Oracle 10g models.
  • PEREntityOR - The property can be used only in Entities in any Oracle models.
  • PEREntity - The property can be used in all Entities.
Type

Sets the custom property data type. The data type should be chosen depending on what would you like to store in the property:

  • Widestring - Text strings in general, supports Unicode characters.
  • String - Text strings in general, does not support Unicode characters.
  • Integer - Positive/negative whole numbers.
  • Boolean - True/False values.
  • Real - Floating points numbers.
  • Dispatch - Any Toad Data Modeler object (Entities, Attributes...)
Readonly Flags the property as Read Only, meaning its value cannot be changed.
Script Implementation

This option allows you to customize the way your property will behave using scripting. You should check it in two cases:

  • Your custom property value will be set according to other property values - In this case, use the automatically generated Get method to get other properties values and set your custom property value according to them.
  • Other properties values will be changed depending on your custom property value - This can be done using the automatically generated Set method. From there, you can change the value of any other property.
Package Determines in which Package the custom property will be stored. Loading/Disallowing the selected Package will cause the property to be usable/unavailable.

Custom Property Example

Scenario

You would like to add an additional property to all entities which would indicate that modeling of a particular entity is not done yet and there are more things to be done before the entity is considered complete. The property could be named InProgress.

Solution:

Create a simple custom property and add a checkbox to Entity Properties linked to the property. Use the following steps as a guide:

  1. Right-click any entity in Model Explorer and select Add Custom Property.

  2. In the New Custom Property dialog, enter the following:
    Caption/Name - enter InProgress as Name and anything you want as Caption
    Level - we assume the property should be available in all entities in all models, select PEREntity
    Type - the boolean type is the most suitable for our type of property (true/false)
    Readonly - the property value should be editable, do not check this checkbox
    Script Implementation - the property value is not dependent on other property values and it doesn't change any other property value either, do not check this checkbox
    Package - feel free to choose any Package

  3. Confirm the changes, you will need to restart the application in order to access the newly created property.

  4. The custom property is now available for use. You can verify that by accessing it via scripting.

  5. Now it's time to add a checkbox to the Entity Properties form. Right-click anywhere in the form and select Customize Form.

    Note:If you have chosen Package other than My Package, you need to select Customize Form as option and choose the Package you have selected previously (step 2). This option is disabled by default. To enable it, go to Settings Menu | Options | Expert Mode and uncheck the Save the definitions the the 'My Package' option.


  6. The Entity Properties form is now editable and several other forms appear. The checkbox for our property should be placed somewhere where we can see it no matter what tab is currently selected. Let's place it next to the Generate option. First, click the bottom section of the form, where the option is placed along with several buttons.

    Note: If you accidentally close some of the customization forms, go to Settings Menu | Options | Expert Mode | Editable Forms and check the appropriate option to show them again.


  7. Now find the Component Palette, go to Data section and double-click DataCheckBox to add it to the form.


  8. Move the newly added checkbox next to the Generate option (you can use Alignment form). Make sure the checkbox is selected, then locate the Component Inspector.

  9. In Component Inspector, change the following properties:
    Caption - enter a descriptive caption, e.g. In Progress
    DataSource - choose Entity
    DataField - find your custom property in the list and select it (InProgress in our case)

  10. Close the editable Entity Properties via cross button in the upper-right corner. This will cause you to exit the editable mode of the form.
  11. Right-click anywhere in the form and select Save Form. The changed form definition will be saved to My Package.

  12. Your custom property is now available in all entities across all databases. The property and the modified form definition are stored in My Package (or in other package, depending on what you chose in step 2).

TIP: If you want to transfer your customizations to another installation of Toad Data Modeler, simply move the package file from the Packages folder to the same folder of the target installation (e.g. "C:\Program Files (x86)\Quest Software\Toad Data Modeler 5.5\Packages\User\My Package.txg")

Related Documents