Hello,

I'm testing LLBLGen Pro version 2.6 Final, but I have a problem with the Numeric datatype.

I have a Firebird 2.0 database with a **Con ** Table and a **Saldo **field type Number(18,4). This field is mapped by LLBLGen as Decimal (lenght 8, precision 8, scale 4).

So when I try to set values to this field that have more than 8 digits lenght I get an overflow error

Example:

**EntityClasses.ConEntity con = new EntityClasses.ConEntity(123); ** con.Saldo = 1234567.89; //try this: con.Saldo -= 1; //or this, when the original value was 1234568.89

and you get the next error:

**"The precision of value '1234567.8900' is larger than the precision of the field: '8' and will cause an overflow in the database. Nombre del parámetro: Saldo" **

but if I use the next SQL statement directly to db, I dont get errors. **Update Con Set Saldo = 1234567.89 Where ID = 123; **

I guess the problem is the mapping "lenght 8", but LLBLGen put that value, so how can I change it? I already try with Decimal(18,4) or Numeric(16,4) and I get the same error.

My database have a lot of fields of Numeric datatype, so its impossible to change all fields to another type.

thanks, David Sanginés

Hola David.

Please take a look at Bypassing build-in validation logic manual section.

thanks by the reference, but... I still think the bug is there.

if I use the next code it works ok, but the Entity lost the feature of validation, then I have to write it by hand

EntityClasses.ConEntity.BuildInValidationBypassMode = BuildInValidationBypass.AlwaysBypass;

Build-in field validation logic ... for string based fields and byte-array based fields, the length of the value is checked compared to the length set for the field. For numeric fields the precision is checked and for fields with a fraction like Decimal, Single/float and Double based fields the scale is also checked. ...

I guess that in your validation code, you also check the lenght of numeric fields as strings and it's a bug because the max value of a number with precision 8 is ((256^8 )/2)-1, and its length is 19. So you shouldnt check the length, or you should make the mapping of the numeric(16,4) as Decimal, precision 8, length 17.

Do you agree, or... ?

Precision 8 means the total # of digits is 8. 1234567.89 has 9 digits, so it's an overflow.

The precision / scale is read from the database. So if the firebird database eats a number which clearly violates the precision value it reports to us, I can't help you unfortunately.

The precision and scale checking is done with # of digits, not with the length. The length is in bytes and not used in this case as it's a numeric value and numeric values are expressed in precision and if applicable: scale.

Thanks by the answer, now I found the problem.

Precision 8 means the total # of digits is 8.

thats for SqlServer, not for Firebird! (what about the others DB's?) In Firebird Precision means the number of bytes, not the number of digits.

For SQL Server **Int have Precision 10 ** (ugly, because int != text, int = 4 bytes) **Numeric(a,b) have Precision a Scale b **(i dont know who SQL store the Numeric type)

For Firebird **Int have Precision 4 ** (it means 4 bytes or 256^4) **Numeric(a,b) have Precision 2, 4 or 8 (see the next table) Scale b ** because Firebird store the Numeric datatype as smallint (2 bytes), int (4 bytes) or bigInt (8 bytes), and only put the point in the place of scale (really simple )

• Numeric(1,b) have Precision 2 Scale b
• Numeric(2,b) have Precision 2 Scale b
• Numeric(3,b) have Precision 2 Scale b
• Numeric(4,b) have Precision 2 Scale b
• Numeric(5,b) have Precision 4 Scale b
• Numeric(9,b) have Precision 4 Scale b
• Numeric(10,b) have Precision 8 Scale b
• Numeric(18,b) have Precision 8 Scale b

¿So, do you will implement this diference or I will have to redefine all my numeric(18,4) as bigInt, or maybe with the source code I could make a Firebird version of LLBLGen?

No kidding! . Ok, that makes sense I guess.

So indeed, this has to change in the driver. The driver sourcecode is in the SDK. I'll look into this tomorrow (friday, it's already 8PM here) to see if I can change the driver sourcecode to meet this request.

What has to be done is basicly to convert the firebird precision number to a precision number for digits. (Oracle has something similar for FLOAT, a precision number for that type has to be multiplied by 0.3038 or thereabout).

(with a fixed driver, refreshing the catalog will automatically then fix the project and re-generating the code will make your application work)

Indeed, numeric(2,2) accepts 327.67 but refuses 327.68

Amazing. Never knew this. But then again, there's hardly proper docs for these kind of details for firebird. Nevertheless, I'll correct the precision in the driver. It's still not very solid though as the above test shows: you still can get an overflow due to this.

PLease use the attached driver to refresh your project and to correct precision values on all numeric (int/numeric/decimal etc.) typed fields.

Fantastic!

Your support is really good, I'm really surprise by your inmediatly solution. Only 1 day, now it works fine!

But then again, there's hardly proper docs for these kind of details for firebird. Nevertheless, I'll correct the precision in the driver. It's still not very solid though as the above test shows: you still can get an overflow due to this.

One tip: the proper docs of firebird is in "The Firebird Book: A Reference for Database Developers" of Helen Borrie.

You are right. Numeric and Decimal datatypes are bad implemented by Firebird, actually, Decimal is worst! in a Decimal (4,1) you can store a number like 214,748,364.7 without an overflow! (Firebird Book, page 134).

Again, Thank you very much! David Sanginés

Hi Frans,

The issue with Firebird returning a byte count rather than a digit count is not a feature of Firebird, but a bug in the Firebird .net data provider. This bug was fixed in version 2.1, so your latest fix will probably break with recent versions.

See http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=11530 for an earlier discussion on this.

mckeogh wrote:

Hi Frans,

The issue with Firebird returning a byte count rather than a digit count is not a feature of Firebird, but a bug in the Firebird .net data provider. This bug was fixed in version 2.1, so your latest fix will probably break with recent versions.

See http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=11530 for an earlier discussion on this.

The driver doesn't work with v2.1 of the firebird.net provider as the maintainer is too stubborn to include a policy file in the installer.

In v3 we're using the db provider factories and this won't happen, but indeed, we then need to anticipate on this (i.o.w: people have to use 2.1 to get the proper precisions etc. ).

Btw, I'm a bit puzzled that it's really a bug of the .net provider, because when I use ib phoenix on a fb 2.0 db, I also see byte counts as precision, instead of digit count... (ibphoenix isn't a .net program).

Anyway, thanks for the heads up! . So in short: v2.x drivers/dqe's: the fix is needed, as the driver/dqe's don't work with the newest firebird.net provider. In v3, we're assuming v2.1 of the provider. (We'll add a note to v3's docs about this.)

For people who use manual assembly redirects... I think it's unfortunate... perhaps I should add a check for the assembly version number...

I've added a check for the provider version. See attached dll. All v2.1 and higher providers aren't doing the conversion, the rest does.