Generating PostgreSQL DDL by script throws an internal error

Toad Data Modeler
1

When trying to generate PostgreSQL 9.5 DDL and save it to file by using JScript throws an internal error. Generating and saving to file by a menu in Toad (Model -> Generate DDL Script -> Run) is working fine.

Steps to reproduce:
1.) Open Toad Data Modeler, from menu bar, go to Expert Mode -> Scripting Window
2.) Paste sample automation script as provided from Toad Data Modeler Help Topic (Content tab)

How to Call Toad Data Modeler from Other Applications

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();

3.) Change file source
4.) Run script.

Error as below:
Toad Data Modeler - Internal error Script: PERCodeGeneratorPG92 Description: Access violation at address 0000000003848BBE in module 'TDM.exe'. Read of address 0000000000000000 Row: 420 Column: 5 Callstack: "PERCodeGenerator" from package "Generations for PER Model" "PERCodeGeneratorPG92" from package "Generations for PostgreSQL 9.2"

2

Hello David,
this sample is determined for calling out of TDM. If you want to call similar script from "Scripting Window" in TDM, then you will need to do some changes.
I tried it on TDM version 7.0 and it is works. See my script

//*****************************************************
var App = System.GetInterface('Application');


//Model for which DDL script will be generated
var Model = App.OpenModelFromFile("C:\\Users\\pdaricek\\Documents\\Toad Data Modeler\\Standard Installation 7.0 - 64\\Models\\VideorentalPG.txp");
if(Model==null) Log.Information('Model is not opened');
//DDL script destination folder and name
Generate(Model, 'C:\\Scripts\\VideorentalGeneratedScript.sql');
// Model is deleted only from memory 
Model.Delete(); 
}

function Generate(Model, Output)
{
    var Generator = Model.DefaultCodeGenerator;
    Generator.GenerateToFile(Output);
}

Be aware that you have not opened already model from script.

In TDM you can run script from "Scripting Window", Macro or during from Window.
Maybe if you let me know your workflow I can help you more exactly.

Regard
Daril

3

Hi Daril,

Thanks for your reply. I am using version 6.5. Similar scripts i used to generate DDL without Data model conversion was running from Scripting Windows well without errors.
Two questions:
1.) Why my original script works in Oracle DDL generation but not in Postgresql?
2.) The original purpose was to convert Oracle Data Model to Postgresql from a list of Oracle file paths, then generate the Postgre DDL.

e.g.

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 10'); 
               //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(l_Model, l_Output){

var v_Generator = l_Model.DefaultCodeGenerator;
v_Generator.CreateComments = false;
v_Generator.GenerateToFile(l_Output);

}

function main(){
var v_App; //application object
var v_Fso;
var v_Model; //data model object
var v_ModelTxtFileName;
var v_ModelTxtFile;
var v_TxtFileLine;
var v_TxtFileLineSubstr1;
var v_TxtFileLineSubstr2;
var v_Delimiter;
var v_ModelTxtFilePath = "C:\test";
var v_ModelSQLFilePath = v_ModelTxtFilePath+"\PostgreSQL";
//v_ModelTxtFilePath = v_ModelTxtFilePath.replace(/\/g, "\\");
v_ModelTxtFileName = v_ModelTxtFilePath+"\model_postgresql_ddl_list.txt";

 v_App = new ActiveXObject("TDM.App");      
 v_Fso = new ActiveXObject("Scripting.FileSystemObject");  

 //Wait until all TDM packages are loaded
 while(!v_App.Application.IsPackagesLoaded)
 {
       WScript.Sleep(1000);
 }
 
 v_ModelTxtFile = v_Fso.OpenTextFile(v_ModelTxtFileName,1,false);
 
 while(!v_ModelTxtFile.AtEndOfStream){
    v_TxtFileLine = v_ModelTxtFile.ReadLine();
    //Log.Information("Line "+v_TxtFileLine);
    v_Delimiter = v_TxtFileLine.indexOf(";"); 
    //Log.Information("Index of Delimiter "+v_Delimiter);
    if (v_Delimiter > 0) {
       v_TxtFileLineSubstr1 = v_TxtFileLine.substring(0,v_Delimiter);
       v_TxtFileLineSubstr2 = v_TxtFileLine.substring(v_Delimiter+1,v_TxtFileLine.Length);
       //Log.Information(v_TxtFileLineSubstr1 + " " + v_TxtFileLineSubstr2);
       v_TxtFileLineTwoSlash = v_TxtFileLineSubstr1.replace(/\\/g, "\\\\");
       //Log.Information(v_TxtFileLineTwoSlash);
       v_Model = v_App.OpenModelFromFile(v_TxtFileLineTwoSlash);  
       //Log.Information(v_ModelSQLFilePath); 
       //v_ModelSQLFilePath = v_ModelSQLFilePath.replace(/\\/g, "\\\\");
       if(!v_Fso.FolderExists(v_ModelSQLFilePath)){
         v_Fso.CreateFolder (v_ModelSQLFilePath); 
       }
       
       //Convert Model
       var v_ModelPG = Convert(v_Model, v_App);
       // Save model
       v_App.SaveModelToFile(v_ModelPG, 'C:\\test\\Auth_postgresql.txp');
       // Generate SQL
       Generate(v_ModelPG, v_ModelSQLFilePath+"\\"+v_TxtFileLineSubstr2);
       // Delete only from memory.
       v_ModelPG.Delete();
       v_Model.Delete();

    }
 }
 
 v_ModelTxtFile.Close();

}

Do i need to split into two scripts if i need to convert and generate DDL?
Thanks.
David

4

By the way, i tried your script and it has the same error. Thanks.

5

Hi David,
I'm able to reproduce your error, but only in version 6.5. Could you try upgrade to version 7.0?

I tried this script for Scripting Window, if you want call it outside TDM let me know. I can modify it.

  var App = System.GetInterface('Application');
  
  var PGModel = Convert(Model, App);

  Generate(PGModel, 'C:\\Scripts\\VideorentalGeneratedScript.sql');
  PGModel.Delete();
}

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 = System.GetInterface('PackageManager');
  var DestinationMDef = PM.ModelDefs.GetObjectByName('PostgreSQL 10'); 
               //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;
    Generator.GenerateToFile(Output);
}

Answer on your question(why it is works in Oracle and not in PG) will be that in version 6.5 is probably error in generator of PostgreSQL model.

Regard
Daril

6

Hi Daril,

It looks working. May i know what is the pros and cons calling outside TDM (any example how to call outside TDM) and similar script from "Scripting Window"?
My understanding is we can only call one single TDM version outside but we can choose the version as long as it is in Scripting Window.

My application is mostly calling JScript from the scripting window after opening Toad Data Modeler.

Thanks.
David

7

Hi David,
when you want run some planned job form windows you probably want to use script outside TDM, because you can run TDM, run some job and after finish close TDM. All can be done without human.
For run script from scripting window you need to running TDM already. Main purpose of scripting window is for debugging your script and try to do some part of scripts, but of course it can be used for more.

Example of Script Outside TDM

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:\\Models\\Videorental.txp");
//DDL script destination folder and name
Generate(Model, 'C:\\Scripts\\VideorentalGeneratedScript.sql');
// Model is deleted only from memory
Model.Delete();

Same script in Scripting Window

function main(){
  var App = System.GetInterface('Application');
  var Model = App.OpenModelFromFile("C:\\Models\\Videorental.txp");   
  //DDL script destination folder and name
  Generate(Model, 'C:\\Scripts\\VideorentalGeneratedScript2.sql');    
  // Model is deleted only from memory
  Model.Delete();
}
 
 
function Generate(Model, Output)
{
    var Generator = Model.DefaultCodeGenerator;
    Generator.GenerateToFile(Output);
}

Daril

8

Good Daril, thanks a lot.

9

Hi Daril,

May i know how to generate HTML report if it is calling from Scripting window instead of script outside TDM?

Thanks & Regards,
David

10

Hi David,
It is very similar as outside TDM, you need only do small modification to set variables Model and System.
Set Model by Scripting Window UI and instead App.System use directly System.

    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();
    }

    function main()
    {
      //Report destination folder
      HTMLReport(Model, System, 'C:\\Reports\\' );
    }

image
image.png1186×529 33.9 KB

Regard
Daril