I am using Toad Data Modeler trial version. How can I handle unicode in data modeler?
My SQL 2008 database table has nVarchar columns.
When I create a logical data model from existing SQL database, nVarchar translates into Varchar. So now when I create physical model from the logical model, I only get Varchar and loose the nVarchar type.
Model -> Data Types doesn’t let me add a new data type.
How can I create a data type for nVarchar in Logical model so that I can map the new data type to nVarchar in Data Type Conversion Settings?
what is the reason why you use logical model when you need to preserve specifics of your target physical database? You can work with logical information in physical model in TDM and if you don’t need to work with inheritance (which will be destroyed during logical-physical conversion and will never be created via physical-logical conversion) then it might be better to continue with physical model.
BTW: it is possible to setup rules for datatypes conversion.
Enable Expert mode - click Settings | Options and in section General check the Expert mode checkbox.
Click Settings | Data type conversion settings. New dialog opens. Select logical model from Source combo box and MS SQL 2008 from Destination combo box and click the Load icon (curved arrow similar to refresh).
Click data type name in Destination column and press F2. Choose new data type and click Apply. Then close the dialog by clicking OK.
The reason why I need a logical model is that we want our application to support both Oracle and SQL server.
So I want to create a logical model from our existing SQL database which can then be converted to SQL and Oracle physical models. I want to be able to make changes to LER which can then be applied to SQL & Oracle PERs.
That’s why I want to retain the nVarChar in the logical model. It would be great if you could add a nVarChar datatype to Logical model so then I would be able to set up a rule for SQL - Logical conversion.
I also hava another question. I have a Logical model and converted it to a Physical model and changed a column’s datatype from varchar to nvarchar in PER.
Now I add a new column to LER. Can I only add this new column to PER without overwriting the entire table? All I need is to retain the nVarchar change I made to PER.
Please let me know if you can add a unicode datatype to Logical model.
Thanks for the help.
you can create model for Oracle as a primary source and convert the Oracle model to MSSQL.
Advantages:
Less conversions. You will not have to convert model to logical model at all. You will have only your Oracle model (which can be synchronized with physically existing database much more comfortably then logical model), you will also not need conversion from logical model to MSSQL because such conversion can be done directly from Oracle model.
Better conversion results. NVarchar data types conversion will be converted properly, moreover, if you define sequences/identities then such parts of your model will be converted properly too. With logical modeling you will certainly lose such settings.
Direct migration of KF attributes to child entities, easier management of FK attributes…
Disadvantages:
?
To your question: No, you cannot add unicode datatype to logical model, but you can define rules how to convert any datatype to unicode datatype in physical model.
Universal model only contains data types that are not specific to any database platform, that is why NVarcharis is not available.
As a workaround, you can configure TDM to convert Varchar data type to NVarchar when converting Universal model to Physical model. With Expert Mode enabled (Settings Menu | Options | General), go to Expert Mode Menu | Expert Mode Settings | Data Type Conversion Settings. Now select Universal as the Source and your desired database platform as Destination, find Varchar(x) in the Source column and change it to NVarChar(x) in the Destination column.
This will cause all of your Varchar attributes to be converted to NVarchar. If you want to keep some of your Varchar attributes in the converted model, you can assign them a Domain with Varchar data type and then change them to Varchar data type after the conversion (the Varchar data type of Domain will be also converted to NVarchar).
Hello Lukas, Thanks for your answer, My problem is that i can not sacrifice varchar field, because not all my fields are varchar. Currently we have controlled the work only with a central structure of universal model without making any changes to the physical model. We are greatly complicates. I think everything should enable nvarchar field in the universal because if the coversion ago to varchar2 in Oracle and maintains SQL varchar type;I do not see why it could not run nvarchar = nvarchar2 (in oracle) and nvarchar (SQL). By the time my departure was temporary sacrifice VarcharGraphic field and place that when converted to physical, becomes nvarchar. Any idea my friend lukas.??
The suggested workaround will allow you to have both Varchar and NVarchar fields in your final Physical Model. Let me illustrate it a little better:
An entity has two attributes. We want Attribute1 to be NVarchar in the Physical model and we also want Attribute2 to stay as Varchar after the conversion.
Let's assign the Attribute2 a Domain. The Domain will be of Varchar type.
No problem, just simply change it back to Varchar. Now all of your attributes that have Domain assigned will be Varchar again.
And you're done. The Domain only serves as a helpful tool to change data type or parameters of multiple attributes at once (you can read more about Domains here). When you try to generate SQL code, this is the result:
Another way is to create a Physical model for SQL Server and then convert it to an Oracle model (or the other way around). See the Data Type Conversion Settings for Microsoft SQL Server -> Oracle to see if this works for you.
I'm afraid we won't be adding any more data types to the Universal Model, since it would go against its original purpose.
I'm not sure if I understand your problem with VarcharGraphic correctly, but if it converts to NVarchar, check the Data Type Conversion Settings and possibly change the destination data type to something else.
