Reverse Engineering Custom DataTypes -> GUID() in SQL Server to PostgreSQL

[Former Member]

Hi,

I am reverse engineering SQL Server Database to a customized PostGreSQL DB.

In SQL Server we have CLR based DataTypes like GUID().

I want to map GUID() from SQL to a Varchar(max) in above customized PostGreSQL,

Any Ideas?

Regards

Shiv

[GeorgeMcGeachie · Accepted solution]

Hello Shiv

If a DBMS doesn't include the data type you need, or a conversion doesn't work the way you want it to, you need to edit the DBMS definition via the Database menu. Preferably work on a copy of the definition (see c:\program files\Sybase\PowerDesigner 16\Resource Files\DBMS).

Data type conversions are in the Script area of the DBMS definition - search for the help topic "Script/Data Type Category (DBMS)" for more information.

[Former Member · Accepted solution]

Hi George,

I created a new ".xdb" files from "c:\program files\Sybase\PowerDesigner 16\Resource Files\DBMS" for my new Customized PostGreSQL Database.

You are right about how to customize Data Types, I already have couple of them.

What is tricky about SQL Server Guid() Is, it is a CLR DataType. It doesn't have any Internal Power Designer Types.

What I am interested in knowing is what Data Type I need to add in "Script/Data Type" in order to reverse engineer SQL Server CLR Data Types in Power Designer.

I have read about Abstract Types and I tried them and not getting it right.

If you have any Ideas how to fix this do let me know.

Regards

Shiv

[Former Member · Accepted solution]

Hello,

you have to differenciate between Reverse Eng and Creation

First you reverse engineer from a script where the GUID is, into the PDM, there you should get an "internal" type CLS in the model.

this should be in mssql20???.xdb, if not you can add it(what i did in this example):

then you create the model by changing the type to Postgres, where the internal type CLS should be translated to varchar(max)

and for that you need to add:

HTH

dj

Message was edited by: dirk jaeckel

[Former Member · Accepted solution]

Dirk,

Thank you for the tip, it works for GUID().

I got one more quick question.

In MSSQL, I got a "customdatatype" like "Areaname", how does it map to power designer Internal Data Type?

Any Ideas.

Regards

Shiv

[Former Member · Accepted solution]

I can only guess, you should look in the script / db what sort of primitive type it is and the map

in the xdb ( first picture), you can do so for every userdatatype, but i dont know if there is a better practise for Usertypes?

Physical Model	Internal
Areaname	varchar(128)   <<----FOR EXAMPLE

[Former Member · Accepted solution]

Sure. Thanks Dirk, your comments are really helpful.

[Former Member · Accepted solution]

if you do a lot with rerverse engineering of mssql then see my other content about fixing bugs of the mssql2008.xdb:

http://scn.sap.com/community/sybase-powerdesigner/blog/2013/11/28/series-mssql-server-reverse-engine...

Message was edited by: dirk jaeckel

[Former Member · Accepted solution]

Dirk,

Any idea how to reverse engineer hierarchy-id in SQL Server to new DB?

Regards

Shiv

[Former Member · Accepted solution]

Please open a new Question so people can find this topic by search and explain a little more. I dont have a MSQL installation around and dont know this out of my head.

regards

dj