Toad World® Forums

Customize SQL script generation for SQL Server

Hi

We have some base level features for our application framework implemented on level datebase for each table (Views, Public Synonyms, Grants, Triggers). To generate the DDL scripts automaticly we customized TDM 3.6 in the follwing two parts:

  • customized package (property settings GUI based)

  • extension class „PERCodeGeneratorMS08“ in script „GenerationsMS08.txm“ at method “function CreateTable”

The second part doesn’t work with TDM 5.3 because the method is hidden now. My problem is not to find a documentation how to extend the system methodes with a customized package. I spend some time for analysis and need your support now.

I want to create a customized package to extend the „create table“ step the DDL script generation for SQL Server 2008/2012/2014 with my own code sequence (see PERCodeGeneratorMS08_add01_v2.txt).

For this I tried to create a package called „qmn-ch.ddl.sqlserver.2008.generation“ (see qmn-ch.ddl.sqlserver.2008.generation_work.txg). Which method is to derive and extend with my code sequence?

Thanks for your support!

Regards,
Chris

here comes the attachment;)
ddl_sqlserver_extend.zip (8.85 KB)

Hi,

thank you for your feedback and questions. Toad Data Modeler scripting was partially rewritten from JavaScript to Delphi. That's why the scripts are no longer available for direct customization. However, the software is still customizable. Download sample package that my friend Mario created for you from:

Please extract the archive and copy the TXG file to user packages. Path to user packages can be found in Settings:

custom-gen-0.png

Restart Toad Data Modeler and try to create a model for Microsoft SQL Server 2008. Edit any entity and see SQL Preview:

custom-gen-1.png

Of course, the script can be edited and modified:

custom-gen-2.png

I hope that helps.

BTW: The rewrite from JavaScript to Delphi resulted in much faster code generation and positively affected generation of alter scripts.

Regards,

Vaclav & Mario

Hi Vaclav & Mario,

you did done a great job for me, many thanks! I’ve customized my own script and I’ve generated my full DDL scripts package with no issues. It works fine and realy much faster!

One last question : if I convert the model to MS12 or MS14 database I’ll got an error. Are you able to develop a derived package GenerationsMS12_user and GenerationsMS14_user? In this case I feel free to convert to the higher database platform as soon as possible.

Thanks for your effort!

Regards,

Chris

Hi Chris,

you are welcome. We are glad the sample package helped you.

RE derived packages: Mario updated the sample, please download it from: http://www.toadworld.com/products/toad-data-modeler/m/toad-data-modeler-packages/1499.aspx
It should work fine with SQL Server 2008/2012/2014 models. There is still just one package in the zip file, but if you want separate packages, that can be done as well. Just let us know and Mario will prepare them for you.

RE model conversion: please send us more information about the error. It might not be caused by the package/scripting.

Thanks,

Vaclav & Mario

Hi Vaclav & Mario,

thanks for the update version. For a short test, I just converted my model to the database MS SQL Server 2012. It works fine. After the integration of my jscript sequence I got an error (see attachment). It seams I’ve to adapt my jscript;)

Thanks again for your great service!

Regerads,
Chris

Hi Vaclav & Mario,

I’m back again on your stage. I hope for a last time with a last question about my customized configuration. The counterpart of the package GenerationsMS08_user is my other package qmn-ch.ddl.sqlserver with the extensions for the EnityEdit and Generator dialog for properties settings.

It is required to extend the packages Microsoft Database SQL Server 2008/ Microsoft Database SQL Server 2012/ Microsoft Database SQL Server 2014 to get access from these database models to the included and customized classes PEREntityMS08 and PERCodeGeneratorMS08?

Thanks again for your support!

Regards,
Chris
qmn-ch.ddl.sqlserver.txg.zip (6.01 KB)

Hi Chris,

there are various ways how to achieve the desired result. In this scenario, we will keep what you have and just recommend small modification.

Please do the following:
Create class PEREntityMS12 in the package/metamodel where your PEREntityMS08 is defined. Then put into the PEREntityMS12 class what you have in class PEREntityMS08.

Short info/explanation:

  • Both MS08 and MS12 inherit from MS. (That’s why it is necessary to define both PEREntityMS08 and PEREntityMS12.)
  • But MS14 inherits from MS12. (And that’s why no additional class for MS14 is necessary).
    I hope it helps. Feel free to write us back in case you need our assistance.

Regards,

Vaclav & Mario

Hi Vaclav & Mario,

Thanks for the suggested approach. Over last weekend I don’t found my peace of mind and spent some time for solving my issue. I came to he same result as you suggested. What I did was to include the class PERCodeGeneratorMS12MS12 to the other package GenerationsMS08_user additional. Now everything works fine with database models for MS08/MS12/MS14!

The crux was to include the additional classes in the same packages. First I wanted to inherit the all the packages.

You did a great job, thanks!

Regards,
Chris

Hi Chris,

congratulations! You are the one who did a great job!

And I am really glad you managed to do the customizations.

Regards,

Vaclav