Visual FoxPro OleDb Provider: select count(*) returns type System.Decimal

[acc]

int count;
using (OleDbCommand cmd = connection.CreateCommand()) {
cmd.CommandText = "SELECT COUNT(*) FROM MyTable";
//count = (int)cmd.ExecuteScalar(); // error (invalid cast)
count = (int)(decimal)cmd.ExecuteScalar(); // horror (but works)
}

using (OleDbCommand cmd = connection.CreateCommand()) {
cmd.CommandText = "SELECT COUNT(*) FROM MyTable";
object obj = cmd.ExecuteScalar();
Console.WriteLine("type: {0}", obj.GetType());
Console.WriteLine("value: '{0}'", obj.ToString());
}

Output:
type: System.Decimal
value: '9'

vfpoledb.dll version: 9.0.0.5815
Visual C# 2010 Express
Windows XP Pro SP3

System.Decimal? Why? Is this a bug?

[Arne Vajhøj]

Post by acc
int count;
using (OleDbCommand cmd = connection.CreateCommand()) {
  cmd.CommandText = "SELECT COUNT(*) FROM MyTable";
//count = (int)cmd.ExecuteScalar();    // error (invalid cast)
  count = (int)(decimal)cmd.ExecuteScalar(); // horror (but works)
}
using (OleDbCommand cmd = connection.CreateCommand()) {
  cmd.CommandText = "SELECT COUNT(*) FROM MyTable";
  object obj = cmd.ExecuteScalar();
  Console.WriteLine("type: {0}", obj.GetType());
  Console.WriteLine("value: '{0}'", obj.ToString());
}
type: System.Decimal
value: '9'
vfpoledb.dll version: 9.0.0.5815
Visual C# 2010 Express
Windows XP Pro SP3
System.Decimal? Why? Is this a bug?

I have seen it before.

Not with FoxPro, but with something else.

Sorry - I cannot remember what database it was. I am getting old. :-(

It is certainly surprising.

But I think it is legal.

The SQL standard (92) specify that:

<quote>
7) If COUNT is specified, then the data type of the result is exact
numeric with implementation-defined precision and scale of 0.
</quote>

That tells me that INTEGER is valid but also that NUMERIC(20,0) or
similar is valid.

I think most databases return INTEGER. And then the ADO.NET provider
maps that to int. Fine.

But if a database return NUMERIC(20,0) or similar then it seems
natural that the ADO.NET provider maps that to decimal.
Because decimal is the standard mapping for all NUMERIC and DECIMAL.

Arne