Chat now with support
Chat with Support

Toad Data Modeler 6.2 - User Guide

Introduction User Interface Models and Model Objects
Physical Data Model
About Physical Data Modeling Benefits of Physical Data Model Entity Relationship Diagram Objects Basic Database Design Advanced Database Design
Universal Data Model Logical Data Model Working with Model Objects Model Properties Model Statistics
Features and Tools
Application Variables Export/Import DDL Script Generation Graphics Model Actions Print Projects Reports Reverse Engineering Scripting and Customization SQL Templates Tips and Tricks Toad for Oracle Integration Toad Intelligence Central (TIC) Integration Tools Version Control
Options and Configuration Databases
Supported Databases Connection Types by Database DB2 UDB v. 8 (LUW) DB2 v. 9 (LUW) DB2 v. 9.5 (LUW) DB2 v. 9.7 (LUW) DB2 v. 10.1 (LUW) DB2 v. 10.5 (LUW) DB2 z/OS v. 9 DB2 z/OS v.10 DB2 z/OS v. 11 Greenplum 4.1 Greenplum 4.2 Ingres 9.3 Ingres 10.0 Microsoft Access 2000/2002/2003 Microsoft Access 2007/2010 Microsoft Azure SQL Database Microsoft Azure SQL Database V12 Microsoft SQL Server 2000 Microsoft SQL Server 2005 Microsoft SQL Server 2008 Microsoft SQL Server 2012 Microsoft SQL Server 2014 Microsoft SQL Server 2016 MySQL 5.0 MySQL 5.1 MySQL 5.5 MySQL 5.6 MySQL 5.7 Oracle 9i Oracle 10g Oracle 11g Release 1 Oracle 11g Release 2 Oracle 12c Release 1 Oracle 12c Release 2 PostgreSQL 8.1 PostgreSQL 8.2 PostgreSQL 8.3 PostgreSQL 8.4 PostgreSQL 9.0 PostgreSQL 9.1 PostgreSQL 9.2 PostgreSQL 9.3 PostgreSQL 9.4 PostgreSQL 9.5 SQLite 3.7 Sybase ASE 12.5 Sybase ASE 15 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
Legal Information

Automation - Calling TDM From Other Applications

Features and Tools > Scripting and Customization > 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");
}

Command Line Parameters

Features and Tools > Scripting and Customization > Command Line Parameters

You can start Toad Data Modeler from command line and add additional parameters to make it perform various tasks on startup:

  • Open files
  • Create a new model
  • Script execution
  • Start logging
  • Import Toad for Oracle Diagram or Model
Open files

Definition:

Open-File -File:"Name=*Path to file*"[,Name=<string>]*

Example:

The following command opens two models:

TDM.exe Open-File -File:Name="C:\Models\Videorental.txp", Name="C:\Program Files (x86)\Quest Software \Toad Data Modeler - Beta\Samples\Employee.txl"

Create a new model

Definition:

New-Model -ModelType:"*model type*"[ -ModelName:"*model name*"]

Examples:

Creates a new logical model with default name:

TDM.exe New-Model -ModelType:"Logical Model"

Creates Oracle 10g model with the specified name:

TDM.exe New-Model -ModelType:"Oracle 10g" -ModelName:"My Physical Model"

Script execution

Executes script stored in the application. First parameter is ScriptName (name of the script), second is MethodName(name of the method in script) and the following parameters (Par1Name, Par2Name...) will be passed as parameters to the script method.

Definition:

Execute-Script -Parameter:ScriptName="*script name*",MethodName="*method name in script*"[,Par1Name=Par1Value, Par2Name=Par2Value...] -TDM [-Silent]

Examples:

TDM.exe Execute-Script -Parameter:ScriptName=MyScript,MethodName=MyMethod,Par1Name="ABC",Par2Name="DEF" -TDM -Silent

Switches:

-TDM: The executed script is stored in Toad Data Modeler.

-Silent: Script will be executed in Silent mode and you will be able to work with the application regardless the state of the executed script.

Start logging

Writes messages to log. May be useful for sending reports to TDM developers.

Definition:

Start-Log

Import Toad for Oracle ER Diagram or Toad for Oracle Project

TDM.exe Import-ToadForOracleERD -file:"NopathMovERD.erd"

TDM.exe Import-ToadForOracleERD -file:NopatMovERD.erd -Connection:"SERVER=OstDbServer:1521/ORCL, USER=movies,PROTOCOL=TNS,CONNECTAS=NORMAL,SAVEPASSWORD=1,ORACLEHOME=c:\oracle\product\10.2.0\db_1,HOST=OstDbServer,SErVICENAME=ORCL, PORT=1521,LDAP=,METHOD=1"

Expert Mode

Features and Tools > Scripting and Customization > Expert Mode

Before you start customizing the application, you need to enable the Expert Mode option in Toad Data Modeler.

Once you enable Expert Mode, certain functions and options will become available.

  1. Go to Settings Menu| Options | General and check Expert Mode.

  2. The section Expert Mode appears in Options.


  3. Expert Mode Menu as well as Test/Repair Model become available.


Package Explorer

Features and Tools > Scripting and Customization > Package Explorer

Package Explorer displays package structure in Toad Data Modeler. Package Explorer allows you to:

  • Manage objects saved in the packages (rename, move, copy objects, open Object Properties dialog etc.)
  • Access all package Metamodels

To open Package Explorer

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

Toad Data Modeler contains packages for:

  • Specific supported databases (e.g. Oracle, Microsoft SQL Server 2005, MySQL 5.0 etc.)
  • Physical ER models
  • Logical models
  • HTML and RTF reports
  • and other functions, such as SQL/DDL generation, reverse engineering etc.

The Package Explorer Tree

Via the box at the top, you can filter the list of packages.

You can edit only the packages/scripts that are not read-only.

Root folders contain packages that have various lists of objects, e.g.:

  • Forms Definitions - contain partial definitions with a list of changed visual components of forms modified by user  
  • External Class Definitions- contain classes and their members designed by user (see the "Metamodel" topic)
  • Package Dependencies - show dependencies between the selected package and other packages

and others.

Right-click Package options

Option Description

Load Package

Loads the package from disk to Toad Data Modeler. For faster work with packages and for faster loading of application, some information is loaded only on demand.

Delete Package

Deletes the selected Package and all items it contains.

Disallow Package

Selected package won't be loaded during next Toad Data Modeler launch.

Export Package

Exports the selected Package in .tbg format to the chosen folder

Open Metamodel

Opens the selected Package Metamodel. See Metamodels for more information.

Extend with New Package

Creates a new package that will extend the currently selected package.

Create Dependent Package

Creates a new package that will be dependent on the currently selected package (will inherit from it).

Properties

Contains information about the selected package and its items (General information, Visibility, Script folders, Scripts, Description).  From here, you can also manage the scripts in the package - add, delete or edit them. (See the following example.)

Package File box in the Package Properties dialog - Path to the location where the package is stored. Click the button on the right to open the location in file explorer.

Save BIN The selected XML package (*.txg) will be saved in binary format (*.tbg).
Save All as BIN All XML packages (*.txg) will be saved in binary format (*.tbg).

Package Explorer Toolbar

Icon Command

Save Actual - saves changes to actually active Package

Save All - saves all changes made in all modified Packages

Creates a new user Package.
Adds an existing Package to the Package Explorer.
Hides all system Packages, leaving only the user and add-on ones visible.

Icons of Packages in Package Explorer

Package Unlocked Locked

Type of Package/State

Loaded

Loaded-Modified

Unloaded

Disallowed

Loaded

Unloaded

Disallowed

Error during Loading

System

Add-On

My Package

-

-

State Description

Locked

A package is locked when:

a) it is marked as read-only on the disk

b) it is a system package and user does not have Expert Mode enabled

Note: All system packages are read-only by default. However, in Expert mode it is possible to change the lock/unlock property of the package (right-click the package in Package Explorer | Properties.) Generally, in Expert mode it is possible to lock/unlock system packages and add-on packages.

Loaded

Complete package has been loaded to memory.

Loaded - Modified

Package has been modified by user.

Unloaded

Package has not been loaded to memory.

Disallowed

Package has been disallowed by user (right-click the package in Package Explorer | Disallow Package).

Error during Loading

Loading of the package failed. It is an error state. This situation can happen e.g. when a dependent package of this package is missing (for example it was not selected during installation of the application).

Note: It is not possible to edit a script when package is locked. Unlock a package to edit its scripts.

Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating