Hi,

I'm using SelfServicing, LLBLGen Pro 4.2. I'm trying to convert some output of a webservice in the database. One of the fields is a decimal field with two decimals. I get an error storing 0.75 in this field. The following comes from a log:

2017-03-30 21:04:13,775 [1] INFO HITc.Utilities.Service - Quality 4.50 0.55 2017-03-30 21:04:13,791 [1] INFO HITc.Utilities.Service - Quality 0 0 2017-03-30 21:04:13,791 [1] INFO HITc.Utilities.Service - Quality 0 0 2017-03-30 21:04:13,822 [1] INFO HITc.Utilities.Service - Quality 7.30 0.75 2017-03-30 21:04:13,822 [1] ERROR HITc.Utilities.Service - Probleem bij verwerken artikel 10013953 System.ArgumentOutOfRangeException: The precision of value '0.75' is larger than the precision of the field: '2' and will cause an overflow in the database. Parameter name: GrossDensity at SD.LLBLGen.Pro.ORMSupportClasses.EntityCore1.ValidateValue(IFieldInfo fieldToValidate, Object& value, Int32 fieldIndex) at SD.LLBLGen.Pro.ORMSupportClasses.EntityCore1.SetValue(Int32 fieldIndex, Object value, Boolean performDesyncForFKFields, Boolean checkForRefetch) at HITc.SAP.EntityClasses.QualityClassEntity.set_GrossDensity(Decimal value) at HITc.Utilities.Service.ProcesQualityClass(Int32 itemNO, QualityClassArrayItem qualityClass) at HITc.Utilities.Service.ProcessCAMItem(ItemData1ArrayItem article) at HITc.Utilities.Service.GetCAMItems()

Value 0.55 goes fine, 0.75 gives a problem. These are string values that I have converted using the Convert.ToDecimal, decimal.Parse, Math.Round(System.Convert.ToDecimal(field), 2), .....

How to solve this?

Best regards,

Jan

Precision is the total digits of the number. So, 0.75 has a precision of 3.

However Scale is the total number of digits to the right of a decimal point.

You can let the Built-in Validation take care of the scale errors for you, which it does by default (Truncates) Please check Scale overflow correction action

Why does 0.55 not result in an error?

The creation script for the table in SQL Server 2005 is:

CREATE TABLE [dbo].[SAP_QualityClass]( [ItemNO] [int] NOT NULL, [CountryDel] nvarchar NOT NULL, [StrengthClass] [decimal](3, 2) NOT NULL, [GrossDensity] [decimal](2, 2) NOT NULL,

It is able to store 0.55. I can manually change the data in 0.75.

Defining it as decimal(2,2) means you can store all data with 2 decimals between 0 and 1: 0.01 to 0.99

0.55 as well as 0.75, both won't work. Also 1.5 will result in an exception from the database, for decimal (2,2).

However I believe there is a bug in the built-In Validation, as both can be saved in the database (if manually inserted).

We will revert back.

LLBLGen did insert 0.55 in the database. I did not do that manually.

Decimal(M,N) means M digits, N precision. Decimal(2,2) allows values from -0.99 to +0.99. When M=N there is just no integer part.

What's the buildnr of the v4.2 runtime, you're using the latest build?

4.2 Final from May 4th, 2016

Could you please download the latest version and try again? It might very well be it won't fix it, but we have to be sure.

Upgrading did not fix the problem,

Today I deployed the software with the new ddl. All inserts are now failing.

This software runs at 3 servers. Only one of them is running fine. That has file version 4.1.13.1213

Maybe the failure in the implementation was introduced in 4.2.

I have no idea why 0.55 succeeds and 0.75 fails, we have to look into that in the debugger which will be tomorrow. It can't be a rounding issue as that should be the same for both. For the field the precision is set to 2 and the scale is set to 2 as well?

the precision has to be at least 3 for x.yz to fit, but we have to check why v4.1 did work and v4.2 fails (and v5.x too).

The EntityCore.ScaleOverflowCorrectionActionToUse setting might be influential here but again, we have to check whether that's indeed the case. In your situation, EntityCore.ScaleOverflowCorrectionActionToUse is set to the default value (truncate) ?

Probably 0.55 was inserted using the older version of the software. I looked at the code and noticed that in the older version this is a string value. I converted the data to decimal(2,2) later in the project. SQL Server did not complain.

The precision has to be 3 only if x is not equal to 0. When the definition of the column in the database is decimal(2,2), the values range from -0.99 to +0.99.

I have not changed the default settings for truncating. I only save if something is changed, and it gives the error on the save. In this case 0.75 was new and had to be inserted.

I can change the field definition in the database to decimal(3,2) to solve my problem. Since SQL supports decimal(2,2) for a value range as mentioned above, this might be considered to be a bug in LLBLGen.

decimal(x, x) indeed means you can only store values between (-1.0, 1.0) excluded and with a scale of x. All values outside that range will cause an overflow. Hmmm I thought it was overflowing always. Looking into it, only the PostgreSQL docs give a hint about why this is: trailing zeros and leading zeros aren't stored physically. This means that a number of 0.55, has just 2 digits (as the position of the decimal point is stored separately in a numeric/decimal in most databases, except mysql, however that still accepts 0.55 as decimal(2, 2)) and thus is accepted. It's a bit of a hidden feature as no database documentation explicitly explains this exception that this is allowed, only that scale has to be equal or smaller than precision. But what 'equal' means isn't exactly clear.

Indeed an unneeded restriction, not necessarily a bug IMHO. We'll loosen the validation for this in 5.2

OK. I've altered the definition in the database to decimal(3,2).