Hello,

In a project to be ported from BL Gen 2.6 to LBL Gen 5.9, fields are added to entities in several places.

For example, the following function is used to transfer fields found in an MS SQL model to an Oracle model.

private EntityFieldDefinition AddFieldToEntity(EntityDefinition oracleEntity, EntityFieldDefinition sqlserverFieldDef)
{
  IEntityFieldMapTargetElement oraField = null;

  if (oracleEntity.TargetType == EntityMapTargetElementType.Table)
  {
    DBTableField oraTableField = new DBTableField();
    oraTableField.ParentTable = (IDBTable)oracleEntity.Target;
    oraTableField.FieldName = sqlserverFieldDef.MappedField.FieldName;
    oraTableField.OrdinalPosition = sqlserverFieldDef.MappedFieldOrdinalPosition;
    oraTableField.IsComputed = sqlserverFieldDef.MappedFieldIsComputed;
    oraTableField.TypeDefinition.DBTypeAsNETType = sqlserverFieldDef.DotNetType;
    oraTableField.TypeDefinition.DBType = OracleDotNetTypeMapper.GetMatchingOracleType(oraTableField.TypeDefinition.DBTypeAsNETType);
    oraTableField.TypeDefinition.DBTypeAsString = OracleDotNetTypeMapper.GetTypeAsString(oraTableField.TypeDefinition.DBType);
    oraTableField.TypeDefinition.RequiresInsertValue = false;
    oraField = oraTableField;
  }
  else
  {
    DBViewField oraViewField = new DBViewField();
    oraViewField.ParentView = (IDBView)oracleEntity.Target;
    oraViewField.FieldName = sqlserverFieldDef.MappedField.FieldName.ToUpperInvariant();
    oraViewField.OrdinalPosition = sqlserverFieldDef.MappedFieldOrdinalPosition;
    oraViewField.IsComputed = sqlserverFieldDef.MappedFieldIsComputed;
    oraViewField.TypeDefinition.DBTypeAsNETType = sqlserverFieldDef.DotNetType;
    oraViewField.TypeDefinition.DBType = OracleDotNetTypeMapper.GetMatchingOracleType(oraViewField.TypeDefinition.DBTypeAsNETType);
    oraViewField.TypeDefinition.DBTypeAsString = OracleDotNetTypeMapper.GetTypeAsString(oraViewField.TypeDefinition.DBType);
    oraViewField.TypeDefinition.RequiresInsertValue = false;
    oraField = oraViewField;
  }

  ProjectProperties properties = oracleEntity.Container.ContainingProject.Properties;
  EntityFieldDefinition result = oracleEntity.MapField(oraField, properties);
  return result;
}

I am now looking for an approach to enable something similar as universal as possible for the new LLBL Gen version. The difficulty here seems to lie in providing the right data type. The old version solves this by using the represent C# types.

I'm also not sure if the DBTableField and DBViewField part is necessary anymore.

My idea is to clone the Ms SQL Field and change the problematic values afterwards. Is this approach ralistic? And can I create the FieldType using the C# type?

var returnField = sqlserverFieldDef.Clone(oracleEntity, sqlserverFieldDef.Name);
returnField.FieldType = new ???;

regards, relevart

Are you trying to dynamically add non-existing entityFields (Fields that were not defined at design time) to a pre-existing entity?

What you're trying to do is built-in: Reverse engineer from sql server and then use model first syncing to the oracle model, the designer will create the fields for you in the oracle meta-data and you can export a DDL SQL script to update oracle from there.

See: https://www.llblgen.com/Documentation/5.9/Designer/Syncingrelationalmodeldata.htm Set the sync sources (see the docs linked above for what this is) for the sql server meta-data to 'database' and for oracle to 'mixed' if you have elements coming from the db like stored procs or 'model' if it only contains tables.

Then sync the project so it pulls the data from sqlserver (enable the sync tasks for sqlserver only), this will create the new fields in the entities. They don't have mappings yet for oracle as the fields don't exist in the table meta-data in the oracle part of the project. Then sync the project again and enable the sync task for oracle (the model first one), this will create the fields in the tables so the entity fields have the proper mappings.

This of course assumes you have a project with 1 entity model and 2 database metadata containers in the catalog explorer: 1 for sql server and 1 for oracle.

Hello Walaa,

Walaa wrote:

Are you trying to dynamically add un existing entityFields (Fields that were not defined at design time) to a pre-existing entity?

yes that is what I'm tying.

For the bigger Scope: I have to update an old LLBL Gen 2.6 Project wich was used by my empoyer for years.

Our Programms support MS SQL and Oracle Databases, but the development is almost done on MS SQL Server. So once the following automatic LLBL Gen process was estabished:

• Use "CliSyncWithDatabase.exe" to Update the MS SQL Model.
• Run several Plugins to Fix some things at the MS Sql Model and fix differences with the Oracle Model.
• Use "CliGenerator.exe" to create the model.

The code Part in my question is called if the Plugin finds a field a the MS Sql Model that is missed at the Oracle model. So it trys to add a copy of the MS SQL Field to the coresponding Oracle Table.

Hello Otis,

Otis wrote:

What you're trying to do is built-in: Reverse engineer from sql server and then use model first syncing to the oracle model, the designer will create the fields for you in the oracle meta-data and you can export a DDL SQL script to update oracle from there.

that soungs prommessing. So I will read the articles. But I have still one questin. The hole existing process starts automaticly. At the articels I saw screenshots of the LLBL Gen Gui. So is it possible to trigger the processes from the commandline or from a Plugin sourcecode?

relevart wrote:

Hello Otis,

Otis wrote:

What you're trying to do is built-in: Reverse engineer from sql server and then use model first syncing to the oracle model, the designer will create the fields for you in the oracle meta-data and you can export a DDL SQL script to update oracle from there.

that soungs prommessing. So I will read the articles. But I have still one questin. The hole existing process starts automaticly. At the articels I saw screenshots of the LLBL Gen Gui. So is it possible to trigger the processes from the commandline or from a Plugin sourcecode?

You can start it with 1 click of a button in the Sync tab. Database first syncing on the command line is done with the cli refresher tool but model first isn't possible on the command line nor plugin.

You likely also want to look into type conversions and type converters for the Oracle part. This way the designer can make proper decisions which field type to create for a .net type.

To test how this works, you can create a new project on your sql server database, reverse engineer it to an entity model and then add a new relational model data container in the catalog or project explorer for Oracle ODP.NET. You then sync the project and execute the model first task (it produces these tasks automatically for you in the Sync tab) and it will create new tables for all the entities you have in the Oracle relational model data container.