MS SQL BigInt masked with 0xFFFFFF00

[jpoulsen2]

When storing an integet value as a bigint, decimal or numeric in MS SQL db using ADODB, the value is stored correctly in the db (verified with DatAdmin 0.8.8). But when the value is returned it seems to be masked with 0xFFFFFF00 resulting in the last byte always being 0. This problem does not occur with the types float and int.

Sample code:

Global Const $SQLSRV = "TSTSQL01"
Global Const $DATABASE = "test"
Global Const $DB_UID = "user01"
Global Const $DB_PW = "user01"
Global Const $Table = "int_test"

$pSQL = _SQLConnect($SQLSRV, $DATABASE, 1, $DB_UID, $DB_PW, "{SQL Server}")
If NOT IsObj($pSQL) Then

MsgBox(16, "DB Error", "Unable to connect to database: " & $DATABASE & " " & _

"at SQL Server: " & $SQLSRV)

Exit(-1)

EndIf

If _TblExists($pSQL, $Table) Then

$Drop = "DROP TABLE " & $Table
$pDrop = _SQLQuery($pSQL, $Drop)

EndIf

$Create = "CREATE TABLE " & $Table & _

"(ip_addr1 float, " & _

"ip_addr2 int, " & _

"ip_addr3 bigint, " & _
"ip_addr4 decimal(12), " & _
"ip_addr5 numeric(12))"

$pCreate = _SQLQuery($pSQL, $Create)

$v1 = Dec("AC1C0934") ; 172.28.9.52
$Insert = "INSERT INTO " & $Table & " VALUES(" & $v1 & "," & $v1 & "," & $v1 & "," & $v1 & "," & $v1 & ")"
$pInsert = _SQLQuery($pSQL, $Insert)

$v2 = Dec("41D30DD7") ; 65.211.13.215
$Insert = "INSERT INTO " & $Table & " VALUES(" & $v2 & "," & $v2 & "," & $v2 & "," & $v2 & "," & $v2 & ")"
$pInsert = _SQLQuery($pSQL, $Insert)

$v3 = Dec("7F000002") ; 127.0.0.2
$Insert = "INSERT INTO " & $Table & " VALUES(" & $v3 & "," & $v3 & "," & $v3 & "," & $v3 & "," & $v3 & ")"
$pInsert = _SQLQuery($pSQL, $Insert)

$Query = "SELECT * FROM " & $Table
$pQuery = _SQLQuery($pSQL, $Query)

While NOT $pQuery.EOF

For $i = 0 to 4

WTO($i & ": " & $pQuery($i).Value & " - " & Hex($pQuery($i).Value))

Next
$pQuery.MoveNext

WEnd

_SQLDisconnect($pSQL)
Exit(0)

Func _SQLConnect($sServer, $sDatabase, $fAuthMode = 0, $sUsername = "", $sPassword = "", $sDriver = "{SQL Server}")

Local $sTemp = StringMid($sDriver, 2, StringLen($sDriver) - 2)
Local $sKey = "HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\ODBC Drivers", $sVal = RegRead($sKey, $sTemp)
If @error or $sVal = "" Then Return SetError(2, 0, 0)
$oConn = ObjCreate("ADODB.Connection")
If NOT IsObj($oConn) Then Return SetError(3, 0, 0)
If $fAuthMode Then $oConn.Open ("DRIVER=" & $sDriver & ";SERVER=" & $sServer & ";DATABASE=" & $sDatabase & ";UID=" & $sUsername & ";PWD=" & $sPassword & ";")
If NOT $fAuthMode Then $oConn.Open("DRIVER=" & $sDriver & ";SERVER=" & $sServer & ";DATABASE=" & $sDatabase)
If @error Then Return SetError(1, 0, 0)
Return $oConn

EndFunc

Func _SQLQuery($o_ADOcn, $s_Query, $cmd_TimeOut=900)

Local $o_ADOCmd, $o_ADOrs

ConsoleWrite($s_Query & @CRLF)

$o_ADOCmd = ObjCreate("ADODB.Command")
$o_ADOCmd.ActiveConnection = $o_ADOcn
$o_ADOCmd.CommandText = $s_Query
$o_ADOCmd.CommandTimeOut = $cmd_TimeOut
$o_ADOrs = $o_ADOCmd.Execute

if @error Then return 0
return $o_ADOrs

EndFunc

Func _TblExists($oConn, $TblName)

$Query = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME = '" & $TblName & "'"
$pQuery = _SQLQuery($oConn, $Query)
If $pQuery.EOF Then

Return False

Else

Return True

EndIf

EndFunc

Func _SQLDisconnect($oConn)

If NOT IsObj($oConn) Then Return SetError(1, 0, 0)
$oConn.Close
Return 1

EndFunc

Func WTO($String)

ConsoleWrite($String & @CRLF)

EndFunc