Hi there,

We are using ES 2007.0.913 with VB.NET 2008 on a SQL2005 database with quite a few money columns in it.

We noticed that for all money columns, the ES generated metadata class has the following properties:
NumericPrecision = 19
NumericScale = 0

The scale is incorrect. A query in syscolumns in SQL Server tells that the Numeric Scale should be 4.

We have worked around this by modifying the EntitySpaces_VB_Metadata template, but this is probably just a temporary hack at this stage.

Could you please advise. Has this issue been addressed in the new 2008 version?

 Thank you,

Sorin

I do not remember any changes regarding SQL Server money data types. We have a few of them in test databases. Here are the relevant sections from 2007.1.1210.0. Notice there is no Scale specified at all, but this works fine for loading and saving.

c = New esColumnMetadata(CustomerMetadata.ColumnNames.CreditLimit, 10, GetType(System.Decimal), esSystemType.Decimal)	
c.PropertyName = CustomerMetadata.PropertyNames.CreditLimit
c.NumericPrecision = 19
c.IsNullable = true
_columns.Add(c) 

...

meta.AddTypeMap("CreditLimit", new esTypeMap("money", "System.Decimal"))

I also checked the latest ES2008 release, and those sections are virtually identical, except for a few extra line breaks. Again, loading and saving for both C# and VB works correctly, with 4 decimal places being saved.

1    // The only hack so far in any of the templates and it will be removed
2    switch(MyMeta.DriverString)
3    {
4    	case "ORACLE":
5    					
6    		prop = props["CONCURR:" + col.Name];
7    		if(prop != null)
8    		{
9    			output.autoTabLn("c.IsConcurrency = true");
10   		}
11   		break;							
12   					
13   	case "SQL":
14   					
15   		if(col.DataTypeName == "timestamp")
16   		{
17   			output.autoTabLn("c.IsConcurrency = true");
18   		}
19   
20   		// 13-Aug-2008 - Fix NumericScale issue for money field on SQL
21   		if(col.DataTypeName == "money")
22   		{
23   			output.autoTabLn("c.NumericScale = 4");
24   		}
25   							
26   		break;
27   							
28   	case "VISTADB3X":
29   					
30   		if(col.DataTypeName == "Timestamp")
31   		{
32   			output.autoTabLn("c.IsConcurrency = true");
33   		}
34   		break;							
35   						
36   	default:
37   		break;
38   }

Thank you,

Sorin

Sorry, in my last post I said mentioned precision. We need the SCALE for all data types. Unfortunately, it turns out that the template code uses IColumn.NumericScale for a data type that is assumed to be decimal with no scale... Hence the scale returns 0 which is incorrect for the money data type.

The problem si not saving the data, it is in fact the maximum value the field can take.

Technically a Decimal(19,0) (as ES assumes Money is) would be able to receive the following value 9999999999999999999 (ie, 19 of 9). This however fails if saved to SQL.

However, the Decimal (19,4) (as the correct Money type is), can only take up to 999999999999999 (ie. 15 digits max because the rest are decimals... Or 14 in fact I think...) Anyway, less than 19. This is enough to break our validation code that relies on precision and scale to limit the entry in various screen fields.

So the idea is that we kinf od need the scale. The hack fixed it but it is not the best. Ideally IColumn.NumericScale should return the correct value...

Thank you,

Sorin

Gotcha. Our tests are for valid money ranges, and not for 19 digits to the left of the decimal. For ES2007, there is little we can do. MyMeta in both MyGeneration 1.2 and 1.3 report (19, 0) for the SQL Server money data type. Your template hack is the best approach.

A similar template change would also be necessary in the ES 2008.0.0811.0 release, as it too reports (19, 0). But, since we are using our own EntitySpaces.MetadataEngine, we can take a look and see if it can be fixed at that level for a future maintenance release.

Thank you, David.

We'll keep using the hack then.

It shouldn't be too difficult to fix though, as syscolumns in SQL returns the right scale and precision. This is not a priority fix however, as not too many people use NumericScale. Otherwise this issue would have been spotted before.

It would be good to setup some test cases with max and min values for all data types to see how they propagate through the layers and whether they save correctly to the database...

Thank you again.

Sorin