Chat now with support
Chat with Support

Toad Data Modeler 7.2 - 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 Microsoft SQL Server 2019 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 Oracle 19c PostgreSQL 9.0 PostgreSQL 9.1 PostgreSQL 9.2 PostgreSQL 9.3 PostgreSQL 9.4 PostgreSQL 9.5 PostgreSQL 10 PostgreSQL 11 PostgreSQL 12 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 Window

Scripting Window allows you to run simple scripts only. In Scripting Window, you cannot save scripts as they do not relate to any package.

To open Scripting Window

  1. Enable Expert mode: select Settings | Options | General | select the Expert Mode checkbox.
  2. Select Expert Mode | Scripting Window.

In the Application Window, see the new tab Scripting with the Scripting Window open.

Scripting Window toolbar

Option Description
Show Windows Automatically Displays a corresponding side tab. When you are writing a script, Code Explorer is displayed. When a script is being executed, Log is displayed.
Show Log Displays a log window that shows log messages and errors related to Scripting Window.
Show Code Explorer Displays a side tab that lists code segments.
Execute Script Executes a script in Scripting Window.
Stop Script Stops a running script.
Type

Switch between:

  • JScript
  • VBScript
  • Internal Script
Load Script from File Load a script from a file.
Save Script Saves a script.
Save Script as Saves a script under a new name.

To display the Available Objects and the Selected Objects windows in Scripting Window

Select View | Show Registered Objects.

Script Explorer

In Script Explorer, you can edit existing scripts, write your own scripts/macros and more.

To open Script Explorer

  1. Enable Expert mode: select Settings Menu | Options | General | check the Expert Mode checkbox.
  2. Click on the toolbar or select Expert Mode Menu | Customization | Script Explorer.

Script Explorer does not contain all data that you can find in Package Explorer, but only scripts stored in folders.

Via the Script Explorer, you can:

  • Make fine modifications in user scripts - see option Edit Source Code (in New Window)
  • View source code of read-only scripts (via the Edit Source Code option as well), and copy their parts to use them in new scripts
  • Extend functionality of existing scripts and modify them significantly - see option Add New Script
  • Delete user scripts

See Scripting in Script Editor for more information.

The Script Explorer Tree

From the combo-box at the top, you can select a script for:

  • All Models
  • Physical Model
  • Specific database
  • Metamodel
  • Logical Model

Example: See the screenshot above - Microsoft SQL Server 2012 has been selected. The scripts with visibility enabled in Microsoft SQL Server 2012 are displayed.

Options for Folders

Folders (Script Categories):

  • Constants - scripts with constants
  • CSAO Class Definitions - scripts with class definitions, mostly organized to folders with specific database names
  • Dictionaries - contains dictionary scripts
  • Form Events - contains event scripts
  • Reports - contains scripts for HTML and RTF report generation
  • Reverse engineering - contains scripts for creating a model via reverse engineering
  • Verification - contains scripts for verification

Right-click a folder to see the following options:

Option Description

Add New Script

Adds a new script under the selected folder.

Delete Folder

Deletes the selected folder, including its contents.

Folders which contain system scripts cannot be deleted - they are locked (see the padlock icon).

Add New Folder

Creates a new folder in selected folder.

Properties

Opens the Properties dialog of the selected folder  From here, you can also manage items of the folder - add, delete and edit them, provided the folder is not read-only.

General Tab

This tab contains some basic information on the folder.

Items Tab

All subfolders and scripts of a particular folder are listed on this tab. Here, you can also manage them - add, edit, delete. (Provided that they are not read-only.)

Options for Scripts

Right-click a script to see the following options:

Option Description
Create New Script Creates a new script under the selected script.

Delete Script

Deletes the selected script. If this script contains other script items, they will be deleted as well.

System scripts cannot be deleted. They are locked (see the padlock icon). It's not possible to delete other read-only scripts either.

Edit Source Code

Opens Script Editor in the Application Window.

This option is available for all scripts - user, system and read-only scripts. Note that there is a significant difference though:

  • User scripts that are not locked: You can edit the source code entirely.
  • System scripts and read-only scripts: You can only view the script and copy its parts. You cannot edit source code of such scripts directly.

Edit Source Code in New Window

Same as above, however the source code of the selected script will open in a new instance of Script Editor.

Note: You can open source code of the same script multiple times, for example when viewing different parts of a script in two windows.

If any of the Edit options is selected, the script will be locked automatically, and other modifications of this script won't be allowed. (In Script Explorer, such scripts are disabled.)

Properties

Opens the Script Properties dialog. Properties of system and read-only scripts cannot be edited.

Script Properties

General Tab Description

Name

Physical Script name

Caption

Logical Script name

Category

Selects the script category (folder) in which it can be found.

Script Type

Select Script Type:  JScript, VBScript and Internal script are available.

Package

Name of the package where the script is stored. User scripts are stored in "My Package" by default.

Script Folder

Name of the folder in Script Explorer in which the script was created. Cannot be changed.

Visibility Tab

On this tab, you can find information about the script visibility. It defines when the script is visible when you filter scripts in Script Explorer.

Let's say your script visibility is set to "Physical Model". When you filter scripts in Script Explorer by category, your script will be shown each time the filtered category falls under Physical Model. But if you filter script by the Logical Model category, your script won't be shown.

Others Tab

Contains mostly identifying info such as Author, Company, Version etc.

Notes Tab

A tab for writing notes about the script.

Script Editor

Script Editor is accessible from Script Explorer or Package Explorer.

To open Script Editor

  • Right-click a script and choose Edit Source Code or Edit Source Code in New Window.

or

  • Simply double click a script/macro in Script Explorer

When you open an instance of Script Editor, the Scripts tab is displayed at the top of the Application Window. When this tab is focused, a new tab row appears. All opened scripts are shown as tabs there.

Note: Read-only scripts (system scripts and locked scripts) can be viewed only. You can still copy their code and use it in your own scripts.

Left section of Script Editor

List of functions in currently opened script.

Save

Saves changes you made in the script. (Shortcut: CTRL+S)

Rollback

Discards all changes made since the last save.

Note: Double-click a function in the left section of Script Editor to move to its source code in the editor itself.

TIP:

  • You can insert bookmarks in your code:
  1. Press CTRL+SHIFT+number 0-9 to insert a bookmark on selected row.
  2. To move to a created bookmark, press CTRL + bookmark number.
  • You can also quickly search through all of existing scripts by going to Expert Mode Menu | Customization | Find in Scripts or pressing CTRL+ALT+F.

Script Editor Right-Click Options

These are standard functions that can be used while working with scripts in Script Editor.

Convert Internal Script

Option for internal scripts that allows you to see your internal script in JScript.

Convert Internal Script 2

Option for internal scripts that allows you to see your internal script in JScript. Moreover, via this option, you can see numbers of lines that you can map in case of errors in the script.

Note: If an error occurs and it is an error in script, the script will open in Script Editor and the particular problematic line in the script will be highlighted. (Expert mode must be enabled.)

Automation - Calling TDM From Other Applications

Toad Data Modeler can be called from other applications, for example by running JavaScript code. This section showcases several possible scripts that may be used to perform various Toad Data Modeler tasks.

Note: By default, scripts from outside Toad Data Modeler will use the latest installed version of the application. If you want to use some other version, you will need to locate its executable file and execute the following command in its folder:

TDM.exe /regserver

Available tasks

By calling Toad Data Modeler from other applications, you can perform the following tasks silently:

  • DDL script generation
  • Report and change report generation
  • Reverse engineering
  • Model conversion

DDL script generation - Generate.js

function Generate(Model, Output)
{
	var Generator = Model.DefaultCodeGenerator;
	Generator.GenerateToFile(Output);
}
//*****************************************************
var App = new ActiveXObject("TDM.App");

//Wait until all TDM packages are loaded
while(!App.Application.IsPackagesLoaded)
{
	WScript.Sleep(1000);
}

//Model for which DDL script will be generated
var Model = App.OpenModelFromFile("C:\\My\ Models\\Videorental.txp");
//DDL script destination folder and name
Generate(Model, 'C:\\Scripts\\VideorentalGeneratedScript.sql');
// Model is deleted only from memory 
Model.Delete(); 

HTML report generation - HtmlReport.js

function HTMLReport(Model, System, OutputPath)
{
	var ReportRegistrar = System.CreateObject('ReportRegistrar');
	ReportRegistrar.DataSource = Model;
	var Report = ReportRegistrar.CreateReport('BasicHTMLPERReport'+Model.ModelDef.Abbrev, 1 , Model); //1 - HTML report
	ReportRegistrar.RegisterLayoutClasses(1);
	Report.Path = OutputPath;
	Report.FileName = 'Report';	//Name of the HTML report file
	Report.Language = 'ENU'; //Abbreviation of language of dictionary used to translate terms in report (default is english - ENU)
	Report.Kind = 'HTML';
	Report.Layout = ReportRegistrar.GetLayoutClass(0); //Report Layout (0 - Frameless, 1 - Top Menu, 2 - Left Menu)
	Report.CSS = Report.Layout.CSSList.GetObject(0); //CSS style (Frameless 0-10, Top Menu 0-2, Left Menu 0-1)
	Report.GenerateInfo = false; //If true, adds information about model to the report
	Report.Generate();
}
//*****************************************************
var App = new ActiveXObject("TDM.App");

//Wait until all TDM packages are loaded
while(!App.Application.IsPackagesLoaded)
{
	WScript.Sleep(1000);
}

//Model for which the report will be generated
var Model = App.OpenModelFromFile("C:\\Models\\Videorental.txp");
//Report destination folder
HTMLReport(Model, App.System, 'C:\\Reports\\' );

Change script generation - GenerateChangeScript.js

function ChangeFiles(App, FileName1, FileName2, Output)
{
	var Model1 = App.OpenModelFromFile(FileName1);
	var Model2 = App.OpenModelFromFile(FileName2);
	var Convertor = Model1.CreateNewObjectInternal(25000);

	Convertor.Model2ToAlter = true;
	Convertor.Converting = true; // Do not modify
	Convertor.Altering = false; // Do not modify
	Convertor.Model1Model = Model1;
	Convertor.Model2Model = Model2;
	Convertor.CreateDefaultAlterScriptSetting();
	Convertor.InitSelectedOTPs();
	Convertor.InitAvailableOTPs();
	Convertor.SelectPhysicalPropertiesOnlyInSelectedOTPs();
	Convertor.AlterFileName = Output;
	var Stream = App.System.CreateObject('TextStream');
	Convertor.SynchronizeModels();
	Convertor.LoadAllDifferences();
	Convertor.SelectAllModel1ToModel2(true, false);
	Convertor.RunAlter();
	Stream.Text = Convertor.AlterScript;
	Stream.FileName = Convertor.AlterFileName;
	Stream.SaveToFile();
	
	Convertor.Delete();
	Model1.Delete();
	Model2.Delete();
}
//*****************************************************
var App = new ActiveXObject("TDM.App");

//Wait until all TDM packages are loaded
while(!App.Application.IsPackagesLoaded)
{
	WScript.Sleep(1000);
}
//First and second model to be compared, change script destination folder and name
ChangeFiles(App, "C:\\Models\\Videorental.txp", "C:\\Models\\Videorental2.txp", "C:\\Scripts\\VideorentalChangeScript.sql" );

Change report generation - GenerateChangeReport.js

function ChangeReport(App, FileName1, FileName2, OutputPath)
{
	var System = App.System;
	var Model1 = App.OpenModelFromFile(FileName1);
	var Model2 = App.OpenModelFromFile(FileName2);
	var Convertor = Model1.CreateNewObjectInternal(25000);

	Convertor.Model2ToAlter = false;
	Convertor.Converting = true; //Do not modify
	Convertor.Altering = false;	//Do not modify
	Convertor.Model1Model = Model1;
	Convertor.Model2Model = Model2;
	Convertor.CreateDefaultAlterScriptSetting();
	Convertor.InitSelectedOTPs();
	Convertor.InitAvailableOTPs();

	Convertor.SynchronizeModels();
	Convertor.LoadAllDifferences();
	Convertor.SelectAllModel1ToModel2(true, false);

	var ReportRegistrar = System.CreateObject('ReportRegistrar');
	ReportRegistrar.DataSource = Convertor;
	var Report = ReportRegistrar.CreateReport('UniversalHTMLAlterReport', 6, Convertor); // 6 - diff HTML
	ReportRegistrar.RegisterLayoutClasses(6);
	Report.Path = OutputPath;
	Report.FileName = 'Report'; //Name of the HTML report file
	Report.Language = 'ENU'; //Abbreviation of language of dictionary used to translate terms in report (default is english - ENU)
	Report.Kind = 'HTML'; 
	Report.Layout = ReportRegistrar.GetLayoutClass(0); //Report Layout (0 - Frameless, 1 - Top Menu, 2 - Left Menu)
	Report.CSS = Report.Layout.CSSList.GetObject(0); //CSS style (Frameless 0-10, Top Menu 0-2, Left Menu 0-1)
	Report.Generate();

	Convertor.Delete();
	Model1.Delete();
	Model2.Delete();
}
//*****************************************************
var App = new ActiveXObject("TDM.App");

//Wait until all TDM packages are loaded
while(!App.Application.IsPackagesLoaded)
{
	WScript.Sleep(1000);
}

ChangeReport(App,"C:\\Models\\Videorental.txp","C:\\Models\\Videorental2.txp","C:\\Reports\\")

Converting model to PostgreSQL - ConvertToPg.js

function Convert(Model, App)
{
	var Convertor = Model.CreateNewObjectInternal(25000);
	Convertor.Model2ToAlter = false;
	Convertor.Model2ToConvert = true;
	Convertor.Model2ModelTemp = true;

	Convertor.Converting = true; //Do not modify
	Convertor.Altering = false; //Do not modify
	Convertor.Model1Model = Model;

	var PM = App.System.GetInterface('PackageManager');
	var DestinationMDef = PM.ModelDefs.GetObjectByName('PostgreSQL 9.0'); //Target database platform and version
	DestinationMDef.LoadPackages();
 
	Convertor.Model2ModelDef = DestinationMDef;
	Convertor.Model2Model = App.NewModel(2001, DestinationMDef, true, true); //2001 = Physical ER Model (PERModel)
 
	Convertor.CreateDefaultAlterScriptSetting();
	Convertor.InitSelectedOTPs();
	Convertor.InitAvailableOTPs();

	Convertor.SynchronizeModels();
	Convertor.LoadAllDifferences();
	Convertor.SelectAllModel1ToModel2(true, false);
	Convertor.RunConvert();

	var Result = Convertor.Model2Model;
	Convertor.Delete();
	return Result;
}

function Generate(Model, Output)
{
	var Generator = Model.DefaultCodeGenerator;
	// Sample generator settings changes. For more available settigs, see class PERCodeGenerator in Reference Guide
	Generator.UseQuotations = false;
	Generator.GenerateToFile(Output);
}
//*****************************************************
var App = new ActiveXObject("TDM.App");

//Wait until all TDM packages are loaded
while(!App.Application.IsPackagesLoaded)
{
	WScript.Sleep(1000);
}

//Source Model Path
var Model = App.OpenModelFromFile("C:\\Models\\Videorental.txp");
var ModelPG = Convert(Model, App);
// Save model
App.SaveModelToFile(Model, 'C:\\Models\\VideorentalPG.txp');
// Generate SQL
Generate(ModelPG, 'C:\\Models\\script.sql');
// Delete only from memory.
ModelPG.Delete();
Model.Delete();

Reverse engineering using existing Alias - REExistingAlias.js

function LoadPackagesByModelDefName(AName, System)
{
	var PM = System.GetInterface('PackageManager');
	var MDef = PM.ModelDefs.GetObjectByName(AName);
	MDef.LoadPackages();
}
//*****************************************************
function REByAlias(Alias)
{
	var REDataMigrator = Alias.REDataMigrator;
	REDataMigrator.InitiateREDataMigrator();
	REDataMigrator.InitObjects();
	var List = Alias.REStruct.Tables;
	var i;
	
	for (i=0;i<List.Count;i++)
	{
		List.GetObject(I).Selected = true;
	}

	Alias.REDataMigrator.REDataProvider.OpenConnection();//NEW
	Alias.REDataMigrator.LoadObjects();
	Alias.REDataMigrator.REDataProvider.CloseConnection();//NEW
	if (Alias.REStruct.Model!=null)
	{
		return Alias.REStruct.Model;
	}
	else
	{
		return null;
	}
}
//*****************************************************
function RE(AliasName, App, AModelDefName)
{
	var REManager = App.REManager;
	//Load Packages
	LoadPackagesByModelDefName(AModelDefName, App.System);
	var Alias;
	Alias = REManager.REAliases.GetObjectByName(AliasName);
	if (Alias==null)
	{
		var AliasPath = App.ApplicationConfig.AliasesPath;
		Alias = REManager.LoadREAliasFromFile(AliasPath+AliasName+'.txa');
	}
	return REByAlias(Alias);
}
//*********************************************************************
var App = new ActiveXObject("TDM.App");
 
//Wait until the application loads all packages
while(!App.Application.IsPackagesLoaded)
{
	WScript.Sleep(1000);
}
 
var Model = RE('PG9 unidac', App, 'PostgreSQL 9.0');
if (App.Application.SaveModelToFile(Model, 'C:\\test.txp'))
{
	WScript.Echo("OK");
}
else
{
	WScript.Echo("Error");
}

Reverse engineering and new alias - RENewAlias.js

function REByAlias(Alias)
{
	var REDataMigrator = Alias.REDataMigrator;
	REDataMigrator.InitiateREDataMigrator();
	REDataMigrator.InitObjects();

	var List = Alias.REStruct.Tables;
	var i;
	//Select some objects to RE, only example
	for (i=0;i<List.Count;i++)
	{
		List.GetObject(i).Selected = true;
	}

	Alias.REDataMigrator.LoadObjects();
	if (Alias.REStruct.Model!=null)
	{
		return Alias.REStruct.Model;
	}
}
//*********************************************************************
function LoadPackagesByModelDefName(AName, System)
{
	var PM = System.GetInterface('PackageManager');
	var MDef = PM.ModelDefs.GetObjectByName(AName);
	MDef.LoadPackages();
}
//*********************************************************************
function SetAliasParameters(REAlias)
{
	var REDataProvider = REAlias.REDataProvider;
	REDataProvider.SetConnectionParam('HostName', 'Localhost');
	REDataProvider.SetConnectionParam('DatabaseName', 'Videorental');
	REDataProvider.SetConnectionParamAsInt('Port', 0);
	REDataProvider.SetConnectionParam('UserName', 'Scott');
	REDataProvider.Password = 'Lion';
	return REAlias;
}
//*******************************************************************************************
var App = new ActiveXObject("TDM.App");
 
//Wait until the application loads all packages
while(!App.Application.IsPackagesLoaded)
{
	WScript.Sleep(1000);
}
 
LoadPackagesByModelDefName('PostgreSQL 9.0', App.System);
var REManager = App.System.GetInterface('REManager');
var REAlias = REManager.CreateAlias('REDataSourceDBPG90', 'REDataProviderUniDACClientPG');
REAlias = SetAliasParameters(REAlias);
 
var Model = REByAlias(REAlias);
REAlias.Delete();
 
if (App.SaveModelToFile(Model, 'C:\\Models\\Videorental.txp'))
{
	WScript.Echo("OK");
}
else
{
	WScript.Echo("Error");
}
Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating