Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Double oddity

Discussion in 'Information Technology' started by Jamie Collins, Jul 28, 2006.

Tags:
  1. Jamie Collins

    Jamie Collins
    Expand Collapse
    Guest

    Can anyone explain why I get different (wrong) results each time I run
    the Take2 code (using ADO 2.8)?

    Sub Take1()
    ' Create test schema and data
    Kill "C:\DropMe.mdb"
    Dim cat
    Set cat = CreateObject("ADOX.Catalog")
    With cat
    .Create _
    "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=C:\DropMe.mdb"

    With .ActiveConnection
    .Properties("Jet OLEDB:Transaction Commit Mode") = 1
    .Properties("Jet OLEDB:Lock Delay").Value = 1
    .Execute _
    "CREATE TABLE DropMe (" & _
    " anything INTEGER);"
    .Execute _
    "INSERT INTO DropMe VALUES (1);"

    End With
    Set .ActiveConnection = Nothing
    End With
    End Sub

    Sub Take2()
    Dim con
    Set con = CreateObject("ADODB.Connection")
    With con
    .ConnectionString = _
    "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=C:\DropMe.mdb"
    .CursorLocation = 3
    .Open

    Dim rs
    Dim counter As Long
    For counter = 1 To 2
    Set rs = .Execute( _
    "SELECT 2147483647 + 1 FROM DropMe")
    Debug.Print CStr(rs(0))
    rs.Close
    Next
    .Close
    End With
    End Sub

    Jamie.

    --
     
  2. Loading...

    Similar Threads Forum Date
    Double Standards As Per Gubani | Bhai Sarbjit Singh Dhunda Gurmat Vichaar May 23, 2016
    India Corruption rate in India is double of global average Breaking News Jul 9, 2013
    Kirpan - Single edged or Double edged? Questions and Answers Jun 23, 2013
    India Double Standard? You decide! Breaking News Apr 22, 2013
    India Does Punjab have double standards on terrorism? Breaking News Apr 20, 2013

  3. '69 Camaro

    '69 Camaro
    Expand Collapse
    Guest

    Hi, Jamie.

    > Can anyone explain why I get different (wrong) results each time I run
    > the Take2 code (using ADO 2.8)?


    Of course. You're exceeding the number range of a Long data type, the
    default data type that will be used with 2147483647, which causes an
    overflow error when one is added to it. The values in the registers are
    incorrect when an overflow error occurs, but for some reason the overflow
    register isn't being checked when this operation is performed, so no error
    message is displayed, but the incorrect values are returned to the calling
    program.

    You can avoid this silent overflow error by using a number that will
    automatically use a data type that will accommodate the range you need. For
    example, the following equivalent equation will prevent a Long data type
    from being used, but will still give the correct answer:

    Set rs = .Execute("SELECT 2147483647.1 - 0.1 + 1 FROM DropMe")

    HTH.
    Gunny

    See http://www.QBuilt.com for all your database needs.
    See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
    http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
    info.


    "Jamie Collins" <jamiecollins@xsmail.com> wrote in message
    news:1152543625.903968.114150@b28g2000cwb.googlegroups.com...
    > Can anyone explain why I get different (wrong) results each time I run
    > the Take2 code (using ADO 2.8)?
    >
    > Sub Take1()
    > ' Create test schema and data
    > Kill "C:\DropMe.mdb"
    > Dim cat
    > Set cat = CreateObject("ADOX.Catalog")
    > With cat
    > .Create _
    > "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    > "Data Source=C:\DropMe.mdb"
    >
    > With .ActiveConnection
    > .Properties("Jet OLEDB:Transaction Commit Mode") = 1
    > .Properties("Jet OLEDB:Lock Delay").Value = 1
    > .Execute _
    > "CREATE TABLE DropMe (" & _
    > " anything INTEGER);"
    > .Execute _
    > "INSERT INTO DropMe VALUES (1);"
    >
    > End With
    > Set .ActiveConnection = Nothing
    > End With
    > End Sub
    >
    > Sub Take2()
    > Dim con
    > Set con = CreateObject("ADODB.Connection")
    > With con
    > .ConnectionString = _
    > "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    > "Data Source=C:\DropMe.mdb"
    > .CursorLocation = 3
    > .Open
    >
    > Dim rs
    > Dim counter As Long
    > For counter = 1 To 2
    > Set rs = .Execute( _
    > "SELECT 2147483647 + 1 FROM DropMe")
    > Debug.Print CStr(rs(0))
    > rs.Close
    > Next
    > .Close
    > End With
    > End Sub
    >
    > Jamie.
    >
    > --
    >
     
  4. Jamie Collins

    Jamie Collins
    Expand Collapse
    Guest

    '69 Camaro wrote:
    > > Can anyone explain why I get different (wrong) results each time I run
    > > the Take2 code (using ADO 2.8)?

    >
    > Of course. You're exceeding the number range of a Long data type, the
    > default data type that will be used with 2147483647, which causes an
    > overflow error when one is added to it. The values in the registers are
    > incorrect when an overflow error occurs, but for some reason the overflow
    > register isn't being checked when this operation is performed, so no error
    > message is displayed, but the incorrect values are returned to the calling
    > program.


    Hi Gunny. Interesting. Which registers are these?

    Note that when I use a server side cursor (whatever that means for Jet
    <g>) the result is 'Empty'.

    > You can avoid this silent overflow error by using a number that will
    > automatically use a data type that will accommodate the range you need. For
    > example, the following equivalent equation will prevent a Long data type
    > from being used, but will still give the correct answer:
    >
    > Set rs = .Execute("SELECT 2147483647.1 - 0.1 + 1 FROM DropMe")


    Ah yes, the trick of operating on a numeric using a native DECIMAL type
    to coerce the result to a DECIMAL (when will the Access team get around
    to fixing the broken CDEC() casting function in their private version
    of Jet, I wonder?)

    As you probably guessed, I was testing to which type an INTEGER gets
    promoted; I wasn't too surprised that the result was a FLOAT
    (Double)...until I looked more closely at the result!

    There's an even easier way to get the result as a DECIMAL <g>:

    SELECT 2147483648 AS data_value,
    TYPENAME(2147483648) AS data_type
    FROM DropMe;

    Cheers,
    Jamie.

    --
     
  5. '69 Camaro

    '69 Camaro
    Expand Collapse
    Guest

    Hi, Jamie.

    > Which registers are these?


    The 32-bit registers in your computer's CPU. That is unless you're not
    using a Pentium or equivalent processor. 64-bit registers and 16-bit
    registers are out there, too, but I wouldn't expect you be playing on those.

    HTH.
    Gunny

    See http://www.QBuilt.com for all your database needs.
    See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
    http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
    info.


    "Jamie Collins" <jamiecollins@xsmail.com> wrote in message
    news:1152561938.186487.225730@35g2000cwc.googlegroups.com...
    >
    > '69 Camaro wrote:
    >> > Can anyone explain why I get different (wrong) results each time I run
    >> > the Take2 code (using ADO 2.8)?

    >>
    >> Of course. You're exceeding the number range of a Long data type, the
    >> default data type that will be used with 2147483647, which causes an
    >> overflow error when one is added to it. The values in the registers are
    >> incorrect when an overflow error occurs, but for some reason the overflow
    >> register isn't being checked when this operation is performed, so no
    >> error
    >> message is displayed, but the incorrect values are returned to the
    >> calling
    >> program.

    >
    > Hi Gunny. Interesting. Which registers are these?
    >
    > Note that when I use a server side cursor (whatever that means for Jet
    > <g>) the result is 'Empty'.
    >
    >> You can avoid this silent overflow error by using a number that will
    >> automatically use a data type that will accommodate the range you need.
    >> For
    >> example, the following equivalent equation will prevent a Long data type
    >> from being used, but will still give the correct answer:
    >>
    >> Set rs = .Execute("SELECT 2147483647.1 - 0.1 + 1 FROM DropMe")

    >
    > Ah yes, the trick of operating on a numeric using a native DECIMAL type
    > to coerce the result to a DECIMAL (when will the Access team get around
    > to fixing the broken CDEC() casting function in their private version
    > of Jet, I wonder?)
    >
    > As you probably guessed, I was testing to which type an INTEGER gets
    > promoted; I wasn't too surprised that the result was a FLOAT
    > (Double)...until I looked more closely at the result!
    >
    > There's an even easier way to get the result as a DECIMAL <g>:
    >
    > SELECT 2147483648 AS data_value,
    > TYPENAME(2147483648) AS data_type
    > FROM DropMe;
    >
    > Cheers,
    > Jamie.
    >
    > --
    >
     
  6. Jamie Collins

    Jamie Collins
    Expand Collapse
    Guest

    '69 Camaro wrote:
    > > Which registers are these?

    >
    > The 32-bit registers in your computer's CPU.


    I getcha. Thanks.

    Jamie.

    --
     
  7. '69 Camaro

    '69 Camaro
    Expand Collapse
    Guest

    You're welcome.

    Gunny

    See http://www.QBuilt.com for all your database needs.
    See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
    http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
    info.


    "Jamie Collins" <jamiecollins@xsmail.com> wrote in message
    news:1152601640.178318.198850@m73g2000cwd.googlegroups.com...
    >
    > '69 Camaro wrote:
    >> > Which registers are these?

    >>
    >> The 32-bit registers in your computer's CPU.

    >
    > I getcha. Thanks.
    >
    > Jamie.
    >
    > --
    >
     

Share This Page