PRB: ADO Does Not Support Decimal Numbers with Precision That Is Greater Than 28 (327557)

SYMPTOMS

When ActiveX Data Objects (ADO) queries a table that contains a numeric or decimal value with a precision that is greater than 28, you receive the following error message:

Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.

CAUSE

ADO returns all values in VARIANTs. The decimal value of a VARIANT is documented to hold a decimal value with a maximum precision of 28:

Decimal variables are stored as 96-bit (12-byte) unsigned integers scaled by a variable power of 10. VT_DECIMAL uses the entire 16 bytes of the Variant.

Data types such as Oracle NUMBER can have a precision of up to 38. However, ADO cannot support these large values because of the restriction in a VARIANT.

RESOLUTION

An application can still work with these large numbers through OLE DB. By writing an OLE DB DLL that Microsoft Visual Basic (or any other client application) can call, an application can fetch these large numbers and can store the results in a double. For additional information about how to retrieve such large numbers and how to store large numbers in a double, click the article number below to view the article in the Microsoft Knowledge Base:

229884 HOWTO: Use OLE DB DBTYPE_VARNUMERIC

MORE INFORMATION

Use the following Visual Basic code to reproduce the behavior. Make sure that you modify the connection parameters as necessary for your environment before you run the code.

Sub Main()
   On Error GoTo AdoError
   Dim Con As New ADODB.Connection
   Dim Cmd As ADODB.Command
   Dim rs As ADODB.Recordset
   Dim er As ADODB.Error

   Con = CreateObject("ADODB.Connection")

   Con.ConnectionString = "Provider=MSDAORA;User ID=myID;password=myPwd;Data Source=myoratns;Persist Security Info=True"
   Con.Open

   On Error Resume Next
   Con.Execute ("drop table y")

   On Error GoTo AdoError
   Con.Execute ("Create table y (x number(38, 4))")

   Con.Execute ("Insert Into y(x) Values(0.1234e33)")

   Set Cmd = CreateObject("ADODB.Command")
   Cmd.ActiveConnection = Con
   Cmd.CommandText = "select x from y"
   Set rs = Cmd.Execute
   MsgBox (rs.Fields(0).Value)
   Exit Sub

AdoError:
   For Each er In Con.Errors
       MsgBox (Err.Description)
   Next