id	summary	reporter	owner	description	type	status	milestone	component	version	severity	resolution	keywords	cc
36	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	"	Bug	new		AutoIt	3.2.10.0