Hello,

I'm using LLBLGenPro designer v 4.2 Final August 2nd, 2015 Template group: Adapter Target Framework: LLBLGen Pro Runtime Framework Target Language: C# Target platform: .Net 4.6

The database is Oracle 11g. Using oracle instant client 11.1 and Oracle MS driver.

We upgraded the project from 2.5. to 2.6. and then to 4.2. We then generated the source code and all was well. Then we refreshed the catalog. After the refresh we got errors/warnings on some of our tables and a lot of our views. The problem is with our foreign keys.

For example we have a PAR_EMAIL_DENIED_REASON table with a field ID NUMBER(38 ). Also in the PAR_PARTNER table we have a FK EMAIL_DENIED_REASON NUMBER(38 ). Before the refresh (and in the old 2.5 project) both of the fields have their properties: Precision - 38 and Scale - 0. After the refresh the properties change to: Precision - 38 and Scale - 38.

Example message: Entity 'ParEmailDeniedReason' • Entity migrated to target 'DEV.PAR_EMAIL_DENIED_REASON'. • Type definition of the target 'DEV.PAR_EMAIL_DENIED_REASON.ID' of field 'Id' changed. • Type properties Max length, precision and scale of field 'Id' synced with the type properties of target 'DEV.PAR_EMAIL_DENIED_REASON.ID'.

If we leave the scale at 38 we get runtime errors. Why is the change in scale happening? Is there a way to prevent the scale change? Just to add that we didn't have this problem in the 2.5 version.

Thank you. Kind regards. Goran

first try to use the latest v4.2 build. Yours is quite old.

Updated the designer to November 29th, 2016. The problem remains, scale is set to 38 after refresh.

And to add a new problem we found, even if we manually set scale to 0 in designer and generate the source code, in code the scale remains at 38.

Ah I see you're using the MS Driver. The Microsoft Oracle ADO.NET provider (which is deprecated btw) converts all NUMBER(x, y) to decimals, which have no precision/scale in .NET at runtime. So we assume NUMBER(38, 38 ).

What runtime errors do you get, could you give an example? If you get cast errors when you read a property, please check with the C# debugger and a breakpoint on the 'get' part of the property what value is stored in the field.

We recommed (if it's possible for you) to convert to ODP.NET btw, as the microsoft oracle ADO.NET provider (System.Data.Oracle, not our driver) hasn't been updated for years (and likely won't work with future CLIs, as it's marked as deprecated). The Oracle CLI comes with ODP.NET and with the managed provider, so it's not something extra you need to install.

Unfortunately converting to ODP.NET is not possible for us at this time.

We have found out that the problem is on tables where the number field is set only as NUMBER and not as NUMBER(38 ), we will fix that in the db. Also the problem is with our views, since the ms driver doesn't get the precision/scale of views.

We will try to set the "Lenght precision scale follow dblength precision scale" to "false" as that seems to prevent the changing of the scale after refresh and then generate the source code. Ofc then we will have to set the precision and scale manually for every new number field.

As for the runtime errors, we had some binding issues and we will check that further. But if we can generate the code wo changing the scale, that won't be a problem anymore.

Just to ask, is there a way to "force" the designer not to up the scale on number field in views wo changing the settings mentioned to "false"?

I can't reproduce it. In v4.2 I retrieved the meta data of a table with a NUMBER(38,0) field. The entity field was of type 'Decimal' with precision 38, scale 0. I changed the precision to 18, refreshed, and the precision stayed 18 and the scale stayed 0. The setting was set to true.

The setting only takes effect if the metadata of the table field changes. So if you change the precision/scale in the entity, to a value that fits in the table field's type (so scale of entity field < scale of table field), the setting isn't taking effect: the table field's type/precision/scale stays the same after a refresh (38,0), so the entity field is left alone.

So you can leave the setting to true, change the types in the tables to NUMBER(38,0) instead of just 'NUMBER' (which will result to the default NUMBER(38, 38 )), refresh. this will make the table fields be of different types (NUMBER(38,0) instead of the original NUMBER), and the designer will then sync the precision/scale to (38, 0) in the entity field due to the setting being true. Which is what you're after

Ok so we tried a few things and the problems are:

1. We cannot change the field to number(38,0) because those fields are PK and FK and not empty. And the same problem is with our views (there are around 300 of those). So changing the field definitions and view definitions are out of the question.

We could manually change the scale to 0 but after generating the code the scale in our code is still scale 38, for some reason the designer does not see the change to 0. Any idea why is that?

1. All of our views now have wrong isoptional flag values, some are all off, some are all on, and some have mixed on and off. In 2.5 we manually set the isnullable flag for view fields which translated to isoptional on convert fine, but after refresh, the flag is reset to wrong values. This happens only after updating the designer to the latest 4.2 build, when we had the August 2nd, 2015 build, the isoptional flag was fine after refresh.

We are starting to consider to revert the project back to llblgen 2.5 since everything was fine with that build.

Only thing I see we can do to not revert is to go through all the views and some tables (again, over 300) and set the isoptional flag and scale manually but only if we can fix the problem with manual scale change not registering in our code.

gfrankol wrote:

Ok so we tried a few things and the problems are:

1. We cannot change the field to number(38,0) because those fields are PK and FK and not empty. And the same problem is with our views (there are around 300 of those). So changing the field definitions and view definitions are out of the question.

We could manually change the scale to 0 but after generating the code the scale in our code is still scale 38, for some reason the designer does not see the change to 0. Any idea why is that?

Because the table field in the relational model data is NUMBER(38, 3 , and before code generation it syncs with the relational model data. In v5 we overhauled this system so sync is only done when you want to, and also in the direction you specify (so model first projects don't do a relational model data -> entity model sync).

But why not leave the scale at 38? It's ignored anyway by the generated code as the types are System.Decimal, a type which doesn't have precision/scale set.

1. All of our views now have wrong isoptional flag values, some are all off, some are all on, and some have mixed on and off. In 2.5 we manually set the isnullable flag for view fields which translated to isoptional on convert fine, but after refresh, the flag is reset to wrong values. This happens only after updating the designer to the latest 4.2 build, when we had the August 2nd, 2015 build, the isoptional flag was fine after refresh.

This has been changed in December 2015: See http://www.llblgen.com/Pages/ChangelogBrowser.aspx (select llblgen pro v4.2 and designer 4.2)

Catalog Refresher: If an element was mapped onto a view, IsOptional values for fields of the element weren't synced with the mapped target view's fields's Nullable values. This was by design. This has been changed to sync IsOptional with Nullable during a refresh of the catalog metadata if the element field's IsOptional value was equal to the target field's Nullable value. Previously this sync was only performed for tables, not views. As before for tables, if the element's field's IsOptional value differs from the old target field's Nullable value (so the user has changed the value of IsOptional at some point) the new view's field's Nullable value is ignored, to prevent overwriting a manual change.

This change was necessary as it made using views for entities not usable otherwise. In your case, the IsOptional flag in the entity mapped on the view is equal to the old view's IsNullable value. This means it will be synced (as it hasn't been manually changed). Then you refresh and the IsNullable flag for the field in the view is changed and as it is synced, the entity field is updated.

The 'IsNullable' flag for the view field has changed otherwise the entity field wouldn't be changed. Is this the case?

We are starting to consider to revert the project back to llblgen 2.5 since everything was fine with that build.

Only thing I see we can do to not revert is to go through all the views and some tables (again, over 300) and set the isoptional flag and scale manually but only if we can fix the problem with manual scale change not registering in our code.

I think you can safely ignore the scale issue, and generate code with precision/scale 38/38. The isoptional flags are coming from the DB meta data.

The issue with these are however that databases don't always update the metadata for a view if the underlying tables change, only when e.g. the view is dropped/recreated. this could lead to situations where a view which simply does Select * from Table, would show e.g. all fields are not nullable, however when the nullability for some fields in Table is changed, the view would still show 'all fields not nullable'.

Ok so we downgraded the 4.2 designer to Final August 2nd, 2015 so we don't have the is optional problem with views, the flags are the same as on 2.5 and that works for us. We will manually set the flags on every view entity add and update.

And the scale issue, we left it at 38 and changed our code to ignore the scale of 38 and put it to 0. A lil bit hacky but we will never have the "real" 38 scale.

So it seems that everything is fine and we will keep this approach for now. In the future we will try to make the switch to ODP.NET and the latest llblgen designer.

Thank You.