Topic
3 replies Latest Post - ‏2008-11-17T10:49:20Z by SystemAdmin
SystemAdmin
SystemAdmin
228 Posts
ACCEPTED ANSWER

Pinned topic incorrect money values when using oledb

‏2007-04-23T10:41:08Z |
MONEY values don't respect decimals. If one queries for a value like 10.23 it returns 1023!

SETNET32 data is:
CLIENT_LOCALE=pt_PT.CP1252
DATABASE_LOCALE=en_US.819
in DBMONEY var I've tried to use ",." as well as blanks and the result is the same.
I've already tried to put blanks in the locales and the result is the same.

my regional options (control panel windows xp sp2) is:
Portuguese
Number: 123,456,789.00
Currency: 123,456,789.00

I've done a simple program using VB6 that shows this.

Create a project with one form with 2 command buttons and 2 textboxes. Make a reference to Microsoft ActiveX Data Objects 2.8 Library (ADO 2.8)

vb6 code:

Private Const OLEDB_ConnectionString As String = "Provider=Ifxoledbc.2;Data Source=mydb@myserver;" & _
"Extended Properties=""" & "DBPROP_INIT_PROVIDERSTRING=r8;" & _
"REPORTSTRINGASWSTRING=TRUE;CLIENT_LOCALE=pt_PT.CP1252;DATABASE_LOCALE=en_US.819; """

Private Const ODBC_ConnectionString As String = "Provider=MSDASQL.1;Extended Properties=""" & _
"DRIVER={IBM INFORMIX ODBC DRIVER};DATABASE=mydatabase;HOST=myhost;" & _
"SRVR=myserver;SERV=1610;PRO=onsoctcp;CLOC=pt_PT.CP1252;DLOC=en_US.819;" & _
""""

Private Sub createtable()

Dim myConnection As ADODB.Connection
Dim sql As String

Set myConnection = New ADODB.Connection

myConnection.Open ODBC_ConnectionString

sql = "DROP TABLE testtable;"
On Error Resume Next
myConnection.Execute sql
On Error GoTo 0

sql = "CREATE TABLE testtable (moneyvalue money(14,2), decimalvalue decimal(14,2))"
myConnection.Execute sql

sql = "INSERT INTO testtable values (10.23, 10.23)"
myConnection.Execute sql

myConnection.Close

Set myConnection = Nothing

End Sub

Private Sub Command1_Click()

ShowValues OLEDB_ConnectionString

End Sub

Private Sub Command2_Click()

ShowValues ODBC_ConnectionString

End Sub

Private Sub Form_Load()
Command1.Caption = "OLEDB"
Command2.Caption = "ODBC"
createtable
End Sub

Private Sub ShowValues(ByVal myConnectionString As String)

Dim myConnection As ADODB.Connection
Dim adr As ADODB.Recordset
Dim sql As String

Set myConnection = New ADODB.Connection

myConnection.Open myConnectionString

sql = "SELECT moneyvalue, decimalvalue FROM testtable"
Set adr = New ADODB.Recordset
adr.Open sql, myConnection, adOpenForwardOnly
Text1.Text = adr!moneyvalue
Text2.Text = adr!decimalvalue
adr.Close
Set adr = Nothing

myConnection.Close

Set myConnection = Nothing

End Sub
Updated on 2008-11-17T10:49:20Z at 2008-11-17T10:49:20Z by SystemAdmin
  • SystemAdmin
    SystemAdmin
    228 Posts
    ACCEPTED ANSWER

    Re: incorrect money values when using oledb - C#

    ‏2007-04-23T12:49:15Z  in response to SystemAdmin
    The same results are shown when using c# code with ADODB reference:

    using System;
    using ADODB;

    namespace ConsoleApplication
    {
    class Program
    {
    static void Main(string[] args)
    {
    Connection ifxCon = new Connection();
    string sql = @"select moneyvalue, decimalvalue from testtable";

    string connectionString = "Provider=Ifxoledbc.2;Data Source=mydb@myserver;" + @"" +
    "Extended Properties=Client Locale=pt_PT.CP1252;Database=sofinloc;Database Locale=en_US.819;" +
    "";
    ifxCon.Open(connectionString, "", "", 0);

    Recordset rs = new Recordset();
    rs.Open(sql, ifxCon, CursorTypeEnum.adOpenForwardOnly, LockTypeEnum.adLockReadOnly, 0);

    Console.WriteLine(rs.Fields[0].Value.ToString());
    Console.WriteLine(rs.Fields[1].Value.ToString());

    rs.Close();
    ifxCon.Close();

    Console.ReadKey();

    }
    }
    }
  • SystemAdmin
    SystemAdmin
    228 Posts
    ACCEPTED ANSWER

    Re: incorrect money values when using JDBC

    ‏2007-04-25T17:17:30Z  in response to SystemAdmin
    I have discovered yesterday a bug with JDBC and money values: Null values are not treated correctly which using bulk inserts (IFX_USEPUT=1). Sample code to show the problem is in the attached file. (Discovered on Informix 10, JDBC3.0 and a PMR has been filed).
    Updated on 2007-04-25T17:17:30Z at 2007-04-25T17:17:30Z by SystemAdmin
    • SystemAdmin
      SystemAdmin
      228 Posts
      ACCEPTED ANSWER

      Re: incorrect money values when using JDBC

      ‏2008-11-17T10:49:20Z  in response to SystemAdmin
      That is a great help to every one, thasnk for sharing that to us...
      [url=http://www.allinthemoney.com]Options Trading[/url]