UDF - MSSQL.au3

TheLuBu · March 31, 2011

Hi there,

i recently created a new MSSQL.au3, because the only one i found contained only 3 functions.

Maybe you can use it.

If you need more functions or if you receive an error,

contact me and i will try to add or update the UDF

; #CURRENT# =====================================================================================================================
;~ _MSSQL_Con
;~ _MSSQL_Query
;~ _MSSQL_End
;~ _MSSQL_CreateTable
;~ _MSSQL_CreateColumn
;~ _MSSQL_AddRecord
;~ _MSSQL_GetRecord
;~ _MSSQL_TableExist
;~ _MSSQL_ColumnExist
;~ _MSSQL_ListAllColumns
;~ _MSSQL_ListAllTables
;~ _MSSQL_GetColumninfo
;~ _MSSQL_UpdateRecord
;~ _MSSQL_DeleteRecord
;~ _MSSQL_DropColumn
;~ _MSSQL_DropTable
; ===============================================================================================================================


;===============================================================================
;
; Function Name....:    _MSSQL_CreateTable
; Description......:    Creates a New Table
; Syntax...........:    _MSSQL_CreateTable($oConnectionObj, $sTable, $identity, $Columnname)
; Parameter(s).....:    $oConnectionObj = Object, returned by _MSSQL_Con
;                       $sTable = Tablename to create
;                       $identity = [optional] Should there be a Primarykey (Standard = TRUE)
;                       $Columnname = [optional] If no Primarykey is created, name of the first Column
; Return Value(s)..:    Success - 1
;                       Failure - 0, sets @error
;                       |1 - $oConnectionObj is not an object
;                       |2 - $sTable already exists, @extended
;.........@extended:        |1 - $oConnectionObj is not an object
;                           |2 - $sTable already exists
;                           |3 - $aResult is not an array ( not happened yet, but maybe possible)
;                           |4 - Query Error, Query saved to @extended (Check permissions to sys.tables)
; Author(s)........:    TheLuBu <LuBu@veytal.com>
;
;===============================================================================

Func _MSSQL_CreateTable($oConnectionObj, $sTable, $identity = True, $Columnname = "ID")
    Local $sPrimeKey = "ID", $str, $tableexist
    If IsObj($oConnectionObj) And Not @error Then
        $tableexist = _MSSQL_TableExist($oConnectionObj, $sTable)
        If $tableexist = 1 Then Return SetError(2, @error, 0)
        If $identity = True Then
            $str = "CREATE TABLE " & $sTable & " (" & $sPrimeKey & " int IDENTITY (1,1) PRIMARY KEY) ;"
        Else
            $str = "CREATE TABLE " & $sTable & " (" & $Columnname & ");"
        EndIf
        $oConnectionObj.execute($str)
        Return 1
    ElseIf @error Then
        Return SetError(1, 0, 0)
    EndIf
EndFunc   ;==>_MSSQL_CreateTable

;===============================================================================
;
; Function Name....:    _MSSQL_CreateColumn
; Description......:    Create one or more new Columns
; Syntax...........:    _MSSQL_CreateColumn($oConnectionObj, $sTable, $sColumn, $Null = "NULL", $sDataType = "VARCHAR(45)")
; Parameter(s).....:    $oConnectionObj = Object, returned by _MSSQL_Con
;                       $sTable = Tablename
;                       $sColumn = Columnname and Type
;                       $Null = [optional] Allow Zero (Standard = True)
;                       $sDataType = [optional] If $sColumn is an 1-D Array, sets the Columtype for all Columns (Standard = "VARCHAR(45)")
; Requirement......:    If $sColumn is an array, it has to be indexed 1,
;                           - If $sColumn is an 2-D Array, the Columnname has to be in $avArray[$i][0], the datatype has to be in $avArray[0][$i]
;                       If $sColumn is a String ist, it has to be formated like this:
;                           - "Name1 varchar(50),Name2 varchar(50),Name3 varchar(50),Name4  varchar(50),NameN Datatype"
; Return Value(s)..:    Success - 1
;                       Failure - 0, sets @error
;                       |1 - $oConnectionObj is not an object
;                       |2 - $sColumn has more than 2 dimensions
;                       |3 - A Columnname from $sColumn already exists in Database
;                           - The Columnname is saved in @extended
;                       |4 - A Columnname from $sColumn occurs more than 1 time in the $sColumn
;                           - The Columnname is saved in @extended
; Author(s)........:    TheLuBu <LuBu@veytal.com>
;
;===============================================================================
Func _MSSQL_CreateColumn($oConnectionObj, $sTable, $sColumn, $Null = True, $sDataType = "VARCHAR(45)")
    Local $str, $Result, $Columnsplit, $Columnsplit2
    If IsObj($oConnectionObj) And Not @error Then
        If IsArray($sColumn) Then
            If UBound($sColumn, 2) = 2 Then
                $str = "ALTER TABLE " & $sTable & " ADD "
                If $Null = True Then
                    For $i = 1 To UBound($sColumn) - 1
                        If StringInStr($str, $sColumn[$i][0]) <> 0 Then Return SetError(4, $sColumn[$i][0], 0)
                        $str &= "" & $sColumn[$i][0] & " " & $sColumn[$i][1] & " NULL,"
                    Next
                Else
                    For $i = 1 To UBound($sColumn) - 1
                        If StringInStr($str, $sColumn[$i][0]) <> 0 Then Return SetError(4, $sColumn[$i][0], 0)
                        $str &= "" & $sColumn[$i][0] & " " & $sColumn[$i][1] & ","
                    Next
                EndIf
                For $i = 1 To UBound($sColumn) - 1
                    $Result = _MSSQL_ColumnExist($oConnectionObj, $sTable, $sColumn[$i][0])
                    If $Result = 1 Then Return SetError(3, $sColumn[$i][0], 0)
                Next

            ElseIf UBound($sColumn, 2) = 0 And @error = 2 Then
                $str = "ALTER TABLE " & $sTable & " ADD "
                If $Null = "Null" Then
                    For $i = 1 To UBound($sColumn) - 1
                        If StringInStr($str, $sColumn[$i]) <> 0 Then Return SetError(4, $sColumn[$i], 0)
                        $str &= "" & $sColumn[$i] & " " & $sDataType & " NULL,"
                    Next
                Else
                    For $i = 1 To UBound($sColumn) - 1
                        If StringInStr($str, $sColumn[$i]) <> 0 Then Return SetError(4, $sColumn[$i], 0)
                        $str &= "" & $sColumn[$i] & " " & $sDataType & ","
                    Next
                EndIf
                For $i = 1 To UBound($sColumn) - 1
                    $Result = _MSSQL_ColumnExist($oConnectionObj, $sTable, $sColumn[$i])
                    If $Result = 1 Then Return SetError(3, $sColumn[$i], 0)
                Next
            Else
                Return SetError(2, 0, 0)
            EndIf
            $str = StringTrimRight($str, 1) & ";"
            $oConnectionObj.execute($str)
        Else
            $str = "ALTER TABLE " & $sTable & " ADD " & $sColumn & ";"
            $Columnsplit = StringSplit($sColumn, ",")
            For $i = 1 To $Columnsplit[0]
                $Columnsplit2 = StringSplit($Columnsplit[$i], " ")
                $Result = _MSSQL_ColumnExist($oConnectionObj, $sTable, $Columnsplit2[1])
                If $Result = 1 Then Return SetError(3, $Columnsplit2[1], 0)
            Next
            $oConnectionObj.execute($str)
            Return 1
        EndIf
    Else
        Return SetError(1, 0, 0)
    EndIf
EndFunc   ;==>_MSSQL_CreateColumn
;===============================================================================
;
; Function Name....:    _MSSQL_AddRecord
; Description......:    Creates a new Row in the database
; Syntax...........:    _MSSQL_AddRecord($oConnectionObj, $sTable, $Values, $UNIQUE, $condition)
; Parameter(s).....:    $oConnectionObj = Object, returned by _MSSQL_Con
;                       $sTable = Tablename
;                       $Values = Values to be inserted into the table
;                       $UNIQUE = [optional] Set to True to avoid double data
;                       $condition = [optional] condition, how to determine double data
; Requirement......:    You need to add a Value for each Column in the Table
;                       If $Values is an Array , it has to be indexed 1,
;                       If $Values is a String , it has to be formated like this:
;                       - 'Value1', 'Value2', 'Value3', 'Value4', 'Value5', 'Value n'
; Return Value(s)..:    Success - 1
;                       Failure - 0, sets @error
;                       |1 - $oConnectionObj is not an object
;                       |2 - $condition was not set
;                       |3 - Only returned if $UNIQUE = True
;                               - All Values already in Database
; Author(s)........:    TheLuBu <LuBu@veytal.com>
;
;===============================================================================
Func _MSSQL_AddRecord($oConnectionObj, $sTable, $Values, $UNIQUE = False, $condition = "")
    Local $str, $check
    If IsObj($oConnectionObj) And Not @error Then
        If IsArray($Values) Then
            If UBound($Values, 2) = 0 Then
                $str = "INSERT INTO " & $sTable & " VALUES('"
                For $grades = 1 To UBound($Values) - 1
                    If $UNIQUE = False Then
                        $str &= $Values[$grades] & "', '"
                    Else
                        If $condition = "" Then Return SetError(2, 0, 0)
                        $check = _MSSQL_GetRecord($oConnectionObj, $sTable, "*", $condition)
                        If @error = 4 Then
                            $str &= $Values[$grades] & "', '"
                        EndIf
                    EndIf
                Next
                $str = StringTrimRight($str, 3) & ");"
                ConsoleWrite('@@ Debug(' & @ScriptLineNumber & ') : $str = ' & $str & @crlf & '>Error code: ' & @error & @crlf) ;### Debug Console
                If StringRight($str, 7) = "VALUE);" Then Return SetError(3, 0, 0)
                $oConnectionObj.execute($str)
                Return 1
            Else
                For $rows = 1 To UBound($Values) - 1
                    $str = "INSERT INTO " & $sTable & " VALUES('"
                    For $grades = 1 To UBound($Values, 2) - 1
                        If $UNIQUE = False Then
                            $str &= $Values[$rows][$grades] & "', '"
                        Else
                            If $condition = "" Then Return SetError(2, 0, 0)
                            $check = _MSSQL_GetRecord($oConnectionObj, $sTable, "*", $condition)
                            If @error = 4 Then
                                $str &= $Values[$rows][$grades] & "', '"
                            EndIf
                        EndIf
                    Next
                    $str = StringTrimRight($str, 3) & ");"
                    ConsoleWrite('@@ Debug(' & @ScriptLineNumber & ') : $str = ' & $str & @crlf & '>Error code: ' & @error & @crlf) ;### Debug Console
                    If StringRight($str, 7) = "VALUE);" Then Return SetError(3, 0, 0)
                    $oConnectionObj.execute($str)
                Next
                Return 1
            EndIf
        Else
            If $UNIQUE = False Then
                $str = "INSERT INTO " & $sTable & " VALUES(" & $Values & ");"
            Else
                If $condition = "" Then Return SetError(2, 0, 0)
                $check = _MSSQL_GetRecord($oConnectionObj, $sTable, "*", $condition)
                If @error = 4 Then
                    $str = "INSERT INTO " & $sTable & " VALUES(" & $Values & ");"
                Else
                    Return SetError(3, 0, 0)
                EndIf
            EndIf
            $oConnectionObj.execute($str)
            Return 1
        EndIf
    Else
        Return SetError(1, 0, 0)
    EndIf
EndFunc   ;==>_MSSQL_AddRecord
;===============================================================================
;
; Function Name....:    _MSSQL_GetRecord
; Description......:    Get one or more Values from Database
; Syntax...........:    _MSSQL_GetRecord($oConnectionObj, $sTable, $Columns = "*", $condition = "", $order = "")
; Parameter(s).....:    $oConnectionObj = Object, returned by _MSSQL_Con
;                       $sTable = Tablename
;                       $sColumn = [optional] Name of one or more Columns in the Table [ Standard = "*" (all Columns)]
;                       $condition = [optional] A WHERE Case, to look for special Values [Standard = "" (all Values)]
;                                       - The datafields in the WHERE Case have to be like this:  'VALUE'
;                       $order = [optional] ORDER BY Case, to sort the returned values [Standard = "" (no order)]
; Requirement......:    If $sColumn is an Array it has to be indexed 1,
;                           - $sColumn contains the Columns to read from
; Return Value(s)..:    Success - Returns an array
;                                   - If you searched in only one column, $aResult is a 1-D Array, where $aResult[0] is the number of found values
;                                   - If you searched in more then one column, $aResult is multidimensional, where $aResult[0][n] is the number of found values
;                       Failure - 0, sets @error
;                       |1 - $oConnectionObj is not an object
;                       |2 - $aResult is not an array ( not happened yet, but msaybe possible)
;                       |3 - $sColumn is not an 1-D array
;                       |4 - Query Error, Query saved to @extended
; Author(s)........:    TheLuBu <LuBu@veytal.com>
;
;===============================================================================
Func _MSSQL_GetRecord($oConnectionObj, $sTable, $Columns = "*", $condition = "", $order = "")
    Local $str, $quer, $aResult, $iColumns, $iRows
    If IsObj($oConnectionObj) And Not @error Then
        If IsArray($Columns) Then
            If UBound($Columns, 2) - 1 <> 1 Then Return SetError(3, 0, 0)
            $str = "SELECT '"
            For $i = 1 To UBound($Columns) - 1
                $str &= $Columns[$i] & "','"
            Next
            If $order = "" Then
                $str = StringTrimRight($str, 2) & "FROM " & $sTable & " " & $condition & ";"
            Else
                $str = StringTrimRight($str, 2) & "FROM " & $sTable & " " & $condition & " ORDER BY " & $order & " ;"
            EndIf
            $quer = $oConnectionObj.execute($str)
            With $quer
                If Not .EOF Then
                    $aResult = .GetRows()
                    If IsArray($aResult) And UBound($aResult, 2) > 1 Then
                        $iColumns = UBound($aResult, 2)
                        $iRows = UBound($aResult)
                        ReDim $aResult[$iRows + 1][$iColumns]
                        For $x = $iRows To 1 Step -1
                            For $y = 0 To $iColumns - 1
                                $aResult[$x][$y] = $aResult[$x - 1][$y]
                            Next
                        Next
                        For $i = 0 To $iColumns - 1
                            $aResult[0][$i] = .Fields($i).Name
                        Next
                    ElseIf IsArray($aResult) And UBound($aResult, 2) = 1 Then
                        $iRows = UBound($aResult)
                        Local $bResult[$iRows + 1]
                        For $x = $iRows To 1 Step -1
                            $bResult[$x] = $aResult[$x - 1][0]
                        Next
                        $bResult[0] = $iRows
                        Return $bResult
                    Else
                        Return SetError(2, 0, 0)
                    EndIf
                Else
                    Return SetError(4, $str, 0)
                EndIf
            EndWith
            Return $aResult
        Else
            If $order = "" Then
                If $Columns = "*" Then
                    $quer = $oConnectionObj.execute("SELECT " & $Columns & " FROM " & $sTable & " " & $condition & ";")
                Else
                    $quer = $oConnectionObj.execute("SELECT '" & $Columns & "' FROM " & $sTable & " " & $condition & ";")
                EndIf
            Else
                If $Columns = "*" Then
                    $quer = $oConnectionObj.execute("SELECT " & $Columns & " FROM " & $sTable & " " & $condition & " ORDER BY " & $order & " ;")
                Else
                    $quer = $oConnectionObj.execute("SELECT '" & $Columns & "' FROM " & $sTable & " " & $condition & " ORDER BY " & $order & " ;")
                EndIf
            EndIf
            If Not $quer.EOF Then
                $aResult = $quer.GetRows()
                If IsArray($aResult) And UBound($aResult, 2) > 1 Then
                    $iColumns = UBound($aResult, 2)
                    $iRows = UBound($aResult)
                    ReDim $aResult[$iRows + 1][$iColumns]
                    For $x = $iRows To 1 Step -1
                        For $y = 0 To $iColumns - 1
                            $aResult[$x][$y] = $aResult[$x - 1][$y]
                        Next
                    Next
                    For $i = 0 To $iColumns - 1
                        $aResult[0][$i] = $quer.Fields($i).Name
                    Next
                ElseIf IsArray($aResult) And UBound($aResult, 2) = 1 Then
                    $iRows = UBound($aResult)
                    Local $bResult[$iRows + 1]
                    For $x = $iRows To 1 Step -1
                        $bResult[$x] = $aResult[$x - 1][0]
                    Next
                    $bResult[0] = $iRows
                    Return $bResult
                Else
                    Return SetError(2, 0, 0)
                EndIf
            Else
                Return SetError(4, $str, 0)
            EndIf
            Return $aResult
        EndIf
    EndIf
    Return SetError(1, 0, 0)
EndFunc   ;==>_MSSQL_GetRecord
;===============================================================================
;
; Function Name....:    _MSSQL_TableExist
; Description......:    Checks, if a Table Exists
; Syntax...........:    _MSSQL_TableExist($oConnectionObj, $sTable)
; Parameter(s).....:    $oConnectionObj = Object, returned by _MSSQL_Con
;                       $sTable = Tablename
; Return Value(s)..:    Success - 1
;                       Failure - 0, sets @error
;                       |1 - $oConnectionObj is not an object
;                       |2 - Table not found
;                       |3 - Query Error, Query saved to @extended (Check Permissions on sys.tables)
;                           - also Returned when Table does not exist
; Author(s)........:    TheLuBu <LuBu@veytal.com>
;
;===============================================================================

Func _MSSQL_TableExist($oConnectionObj, $sTable)
    Local $quer
    If IsObj($oConnectionObj) And Not @error Then
        $quer = $oConnectionObj.execute("SELECT TABLE_SCHEMA FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '" & $sTable & "';")
        If Not $quer.EOF Then
            Return 1
        Else
            Return SetError(3, "SELECT TABLE_SCHEMA FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '" & $sTable & "';", 0)
        EndIf
        Return SetError(2, 0, 0)
    Else
        Return SetError(1, 0, 0)
    EndIf
EndFunc   ;==>_MSSQL_TableExist
;===============================================================================
;
; Function Name....:    _MSSQL_ColumnExist
; Description......:    Checks if a Column exists
; Syntax...........:    _MSSQL_ColumnExist($oConnectionObj, $sTable, $sColumn)
; Parameter(s).....:    $oConnectionObj = Object, returned by _MSSQL_Con
;                       $sTable = Tablename
;                       $sColumn = Columnname
; Return Value(s)..:    Success - 1
;                       Failure - 0, sets @error
;                       |1 - $oConnectionObj is not an object
;                       |2 - Column not found
;                       |3 - Table not found
;                       |3 - Query Error, Query saved to @extended (Check Permissions on sys.columns)
;                           - also Returned when Column does not exist
; Author(s)........:    TheLuBu <LuBu@veytal.com>
;
;===============================================================================
Func _MSSQL_ColumnExist($oConnectionObj, $sTable, $sColumn)
    Local $quer
    If IsObj($oConnectionObj) And Not @error Then
        If not _MSSQL_TableExist($oConnectionObj, $sTable) Then Return SetError(3,0,0)
        $quer = $oConnectionObj.execute("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = '" & $sColumn & "' AND TABLE_NAME = '" & $sTable & "';")
        If Not $quer.EOF Then
            Return 1
        Else
            Return SetError(4, "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = '" & $sColumn & "' AND TABLE_NAME = '" & $sTable & "';", 0)
        EndIf
        Return SetError(2, 0, 0)
    Else
        Return SetError(1, 0, 0)
    EndIf
EndFunc   ;==>_MSSQL_ColumnExist
;===============================================================================
;
; Function Name....:    _MSSQL_Con
; Description......:    Connect to a Database
; Syntax...........:    _MSSQL_Con($scIP, $scUser, $scPass, $scDB)
; Parameter(s).....:    $scIP = IP adress
;                       $scUser = User
;                       $scPass = Pass
;                       $scDB = Database
; Return Value(s)..:    Success - Returns the Database-"handle"
;
;===============================================================================
Func _MSSQL_Con($scIP, $scUser, $scPass, $scDB)
    Local $sqlCon
    $sqlCon = ObjCreate("ADODB.Connection")
    $sqlCon.Open("Provider=SQLOLEDB; Data Source=" & $scIP & "; User ID=" & $scUser & "; Password=" & $scPass & "; database=" & $scDB & ";")
    Return $sqlCon
EndFunc   ;==>_MSSQL_Con
;===============================================================================
;
; Function Name....:    _MSSQL_Query
; Description......:    Send a Query to the Database
; Syntax...........:    _MSSQL_Query($iSQLCon, $iQuery)
; Parameter(s).....:    $iSQLCon = $oConnectionObj = Object, returned by _MSSQL_Con
;                       $iQuery = MSSQL Query
; Return Value(s)..:    Success - Returns the Response from the server
;
;===============================================================================
Func _MSSQL_Query($iSQLCon, $iQuery)
    If IsObj($iSQLCon) Then
        Return $iSQLCon.execute($iQuery)
    EndIf
EndFunc   ;==>_MSSQL_Query
;===============================================================================
;
; Function Name....:    _MSSQL_End
; Description......:    Close SQL Session
; Syntax...........:    _MSSQL_End($sqlCon)
; Parameter(s).....:    $sqlCon = $oConnectionObj = Object, returned by _MSSQL_Con
; Return Value(s)..:    -
;
;===============================================================================
Func _MSSQL_End($sqlCon)
    If IsObj($sqlCon) Then
        $sqlCon.close
    EndIf
EndFunc   ;==>_MSSQL_End
;===============================================================================
;
; Function Name....:    _MSSQL_ListAllColumns
; Description......:    List all Columns from an existing Table
; Syntax...........:    _MSSQL_ListAllColumns($oConnectionObj, $sTable)
; Parameter(s).....:    $oConnectionObj = Object, returned by _MSSQL_Con
;                       $sTable = Tablename
; Return Value(s)..:    Success - Returns an Array
;                       - $aResult[0] returns the number of Columns
;                       Failure - 0, sets @error
;                       |1 - $oConnectionObj is not an object
;                       |2 - $aResult is not an array ( not happened yet, but maybe possible)
;                       |3 - $sTable does not exist
;                       |4 - Query Error, Query saved to @extended
; Author(s)........:    TheLuBu <LuBu@veytal.com>
;
;===============================================================================
Func _MSSQL_ListAllColumns($oConnectionObj, $sTable)
    Local $quer, $aResult, $iRows
    If IsObj($oConnectionObj) And Not @error Then
        If not _MSSQL_TableExist($oConnectionObj, $sTable) Then Return SetError(3,0,0)
        $quer = $oConnectionObj.execute("SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '" & $sTable & "';")
        If Not $quer.EOF Then
            $aResult = $quer.GetRows()
            If IsArray($aResult) And UBound($aResult, 2) = 1 Then
                $iRows = UBound($aResult)
                Local $bResult[$iRows + 1]
                For $x = $iRows To 1 Step -1
                    $bResult[$x] = $aResult[$x - 1][0]
                Next
                $bResult[0] = $iRows
                Return $bResult
            Else
                Return SetError(2, 0, 0)
            EndIf
        Else
            Return SetError(4, "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '" & $sTable & "';", 0)
        EndIf
    Else
        Return SetError(1, 0, 0)
    EndIf
EndFunc   ;==>_MSSQL_ListAllColumns
;===============================================================================
;
; Function Name....:    _MSSQL_ListAllTables
; Description......:    List all tables from the database
; Syntax...........:    _MSSQL_ListAllTables($oConnectionObj)
; Parameter(s).....:    $oConnectionObj = Object, returned by _MSSQL_Con
; Return Value(s)..:    Success - Returns an 2-D Array
;                                   - $Return[0][0]  = number of tables
;                                   - $Return[$i][n] = Tablename
;                                   - $Return[$i][n] = Table Type
;                       Failure - 0, sets @error
;                       |1 - $oConnectionObj is not an object
;                       |2 - $aResult is not an array ( not happened yet, but maybe possible)
;                       |3 - No permissions to INFORMATION_SCHEMA.TABLES
;                           - also returned, if no Tables exist at all
; Author(s)........:    TheLuBu <LuBu@veytal.com>
;
;===============================================================================
Func _MSSQL_ListAllTables($oConnectionObj)
    Local $quer, $aResult, $iColumns, $iRows
    If IsObj($oConnectionObj) And Not @error Then
        $quer = $oConnectionObj.execute("SELECT TABLE_NAME, TABLE_TYPE FROM INFORMATION_SCHEMA.TABLES;")
        If Not $quer.EOF Then
            $aResult = $quer.GetRows()
            If IsArray($aResult) And UBound($aResult, 2) = 2 Then
                $iColumns = UBound($aResult, 2)
                $iRows = UBound($aResult)
                ReDim $aResult[$iRows + 1][$iColumns]
                For $x = $iRows To 1 Step -1
                    For $y = 0 To $iColumns - 1
                        $aResult[$x][$y] = $aResult[$x - 1][$y]
                    Next
                Next
                $aResult[0][0] = $iRows
                $aResult[0][1] = ""
                Return $aResult
            Else
                Return SetError(2, 0, 0)
            EndIf
        Else
            Return SetError(3, 0, 0)
        EndIf
    Else
        Return SetError(1, 0, 0)
    EndIf
EndFunc   ;==>_MSSQL_ListAllTables
;===============================================================================
;
; Function Name....:    _MSSQL_GetColumninfo
; Description......:    Get Information about a Column
; Syntax...........:    _MSSQL_GetColumninfo($oConnectionObj, $sTable, $sColumn = "Allcolumns")
; Parameter(s).....:    $oConnectionObj = Object, returned by _MSSQL_Con
;                       $sTable = Tablename
;                       $sColumn = [optional] Column to get info about (Standard = AllColumns)
; Return Value(s)..:    Success - Returns an multidimensional Array
;                       - $Return[$i][0] = Name of the Column
;                       - $Return[$i][1] = Allow Zero
;                       - In $Return[$i][2] = Datatype
;                       - In $Return[$i][3] = Max Character Lenght
;                       - In $Return[$i][4] = COLLATION NAME (i.E. latin1)
;                       Failure - 0, sets @error
;                       |1 - $oConnectionObj is not an object
;                       |2 - $aResult is not an array ( not happened yet, but maybe possible)
;                       |3 - $sTable or $sColumn does not exist
; Author(s)........:    TheLuBu <LuBu@veytal.com>
;
;===============================================================================
Func _MSSQL_GetColumninfo($oConnectionObj, $sTable, $sColumn = "Allcolumns")
    Local $quer, $aResult, $iColumns, $iRows
    If IsObj($oConnectionObj) And Not @error Then
        If $sColumn = "Allcolumns" Then
            $quer = $oConnectionObj.execute("SELECT COLUMN_NAME, IS_NULLABLE, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, COLLATION_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '" & $sTable & "';")
        Else
            $quer = $oConnectionObj.execute("SELECT COLUMN_NAME, IS_NULLABLE, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, COLLATION_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '" & $sTable & "' AND COLUMN_NAME = '" & $sColumn & "';")
        EndIf
        If Not $quer.EOF Then
            $aResult = $quer.GetRows()
            If IsArray($aResult) And UBound($aResult, 2) > 1 Then
                $iColumns = UBound($aResult, 2)
                $iRows = UBound($aResult)
                ReDim $aResult[$iRows + 1][$iColumns]
                For $x = $iRows To 1 Step -1
                    For $y = 0 To $iColumns - 1
                        $aResult[$x][$y] = $aResult[$x - 1][$y]
                    Next
                Next
                For $i = 0 To $iColumns - 1
                    $aResult[0][$i] = $quer.Fields($i).Name
                Next
                Return $aResult
            Else
                Return SetError(2, 0, 0)
            EndIf
        Else
            Return SetError(3, 0, 0)
        EndIf
    Else
        Return SetError(1, 0, 0)
    EndIf
EndFunc   ;==>_MSSQL_GetColumninfo
;===============================================================================
;
; Function Name....:    _MSSQL_UpdateRecord
; Description......:    Update one or more Values
; Syntax...........:    _MSSQL_UpdateRecord($oConnectionObj, $sTable, $sColumn , $sValue, $condition = "")
; Parameter(s).....:    $oConnectionObj = Object, returned by _MSSQL_Con
;                       $sTable = Tablename
;                       $sColumn = Columnname to update Value in
;                       $sValue = Value to change into
;                       $condition = [optional] A WHERE Case, to limit the found Values [Standard = "" (all Values in a Column)]
; Return Value(s)..:    Success - 1
;                       Failure - 0, sets @error
;                       |1 - $oConnectionObj is not an object
;                       |2 - $sTable does not exist
;                       |3 - $sColumn does not exist
; Author(s)........:    TheLuBu <LuBu@veytal.com>
;
;===============================================================================
Func _MSSQL_UpdateRecord($oConnectionObj, $sTable, $sColumn, $sValue, $condition = "")
    Local $tableexist, $columnexist, $str, $quer
    If IsObj($oConnectionObj) And Not @error Then
        $tableexist = _MSSQL_TableExist($oConnectionObj, $sTable)
        If $tableexist = 0 Then Return SetError(2, 0, 0)
        $columnexist = _MSSQL_ColumnExist($oConnectionObj, $sTable, $sColumn)
        If $columnexist = 0 Then Return SetError(3, 0, 0)
        If $condition = "" Then
            $str = "UPDATE " & $sTable & " SET " & $sColumn & " = " & $sValue & ";"
            $quer = $oConnectionObj.execute($str)
        Else
            $str = "UPDATE " & $sTable & " SET " & $sColumn & " = " & $sValue & " " & $condition & ";"
            $quer = $oConnectionObj.execute($str)
        EndIf
        Return 1
    Else
        Return SetError(1, 0, 0)
    EndIf
EndFunc   ;==>_MSSQL_UpdateRecord
;===============================================================================
;
; Function Name....:    _MSSQL_DeleteRecord
; Description......:    Delete one or more Rows from a Table
; Syntax...........:    _MSSQL_DeleteRecord($oConnectionObj, $sTable, $condition = "")
; Parameter(s).....:    $oConnectionObj = Object, returned by _MSSQL_Con
;                       $sTable = Tablename
;                       $condition = [optional] A WHERE Case, to limit deleted rows [Standard = "" (all rows in table)]
; Return Value(s)..:    Success - 1
;                       Failure - 0, sets @error
;                       |1 - $oConnectionObj is not an object
;                       |2 - $sTable does not exist
; Author(s)........:    TheLuBu <LuBu@veytal.com>
;
;===============================================================================
Func _MSSQL_DeleteRecord($oConnectionObj, $sTable, $condition = "")
    Local $tableexist, $columnexist, $str, $quer
    If IsObj($oConnectionObj) And Not @error Then
        $tableexist = _MSSQL_TableExist($oConnectionObj, $sTable)
        If $tableexist = 0 Then Return SetError(2, 0, 0)
        If $condition = "" Then
            $str = "DELETE FROM " & $sTable & ";"
            $quer = $oConnectionObj.execute($str)
        Else
            $str = "DELETE FROM " & $sTable & " " & $condition & ";"
            $quer = $oConnectionObj.execute($str)
        EndIf
        Return 1
    Else
        Return SetError(1, 0, 0)
    EndIf
EndFunc   ;==>_MSSQL_DeleteRecord

;===============================================================================
;
; Function Name....:    _MSSQL_DropTable
; Description......:    Delete a Table from Database
; Syntax...........:    _MSSQL_DropTable($oConnectionObj, $sTable)
; Parameter(s).....:    $oConnectionObj = Object, returned by _MSSQL_Con
;                       $sTable = Tablename
; Return Value(s)..:    Success - 1
;                       Failure - 0, sets @error
;                       |1 - $oConnectionObj is not an object
;                       |2 - $sTable does not exist
; Author(s)........:    TheLuBu <LuBu@veytal.com>
;
;===============================================================================
Func _MSSQL_DropTable($oConnectionObj, $sTable)
    If IsObj($oConnectionObj) And Not @error Then
        If Not _MSSQL_TableExist($oConnectionObj, $sTable) Then Return SetError(2, 0, 0)
        $str = "DROP TABLE " & $sTable & ";"
        $quer = $oConnectionObj.execute($str)
        Return 1
    Else
        Return SetError(1, 0, 0)
    EndIf
EndFunc   ;==>_MSSQL_DropTable
;===============================================================================
;
; Function Name....:    _MSSQL_DropColumn
; Description......:    Delete a Column from a Table
; Syntax...........:    _MSSQL_DropColumn($oConnectionObj, $sTable)
; Parameter(s).....:    $oConnectionObj = Object, returned by _MSSQL_Con
;                       $sTable = Tablename
; Return Value(s)..:    Success - 1
;                       Failure - 0, sets @error
;                       |1 - $oConnectionObj is not an object
;                       |2 - $sTable does not exist
;                       |3 - $sColumn does not exist
; Author(s)........:    TheLuBu <LuBu@veytal.com>
;
;===============================================================================
Func _MSSQL_DropColumn($oConnectionObj, $sTable, $sColumn)
    If IsObj($oConnectionObj) And Not @error Then
        If Not _MSSQL_TableExist($oConnectionObj, $sTable) Then Return SetError(2, 0, 0)
        If Not _MSSQL_ColumnExist($oConnectionObj, $sTable, $sColumn) Then Return SetError(3, 0, 0)
        $str = "ALTER TABLE " & $sTable & " DROP COLUMN " & $sColumn & ";"
        $quer = $oConnectionObj.execute($str)
        Return 1
    Else
        Return SetError(1, 0, 0)
    EndIf
EndFunc   ;==>_MSSQL_DropColumn

(Sorry for the poor comments, i´m from germany, so my english is not so good )

TheLuBu

Edited March 31, 2011 by TheLuBu

TheLuBu · March 31, 2011

Example Script

#include <MSSQL.au3>
#include <Array.au3>
#Region Arrays and Values
Dim $TestArray1 [10]
    $TestArray1[1] = "VALUE1"
    $TestArray1[2] = "Value2"
    $TestArray1[3] = "Value3"
    $TestArray1[4] = "Value4"
    $TestArray1[5] = "Value5"
    $TestArray1[6] = "Value6"
    $TestArray1[7] = "Value7"
    $TestArray1[8] = "Value8"
    $TestArray1[9] = "123456789"
    $TestValue = "Value15"
    $UNIQUEValue = "Value15"
Dim $Testarray1D [10]
    $Testarray1D [1] = "Column1"
    $Testarray1D [2] = "Column2"
    $Testarray1D [3] = "Column3"
    $Testarray1D [4] = "Column4"
    $Testarray1D [5] = "Column5"
    $Testarray1D [6] = "Column6"
    $Testarray1D [7] = "Column7"
    $Testarray1D [8] = "Column8"
    $Testarray1D [9] = "Column9"
Dim $Testarray2D [10][2]
    $Testarray2D [1][0] = "Column1"
    $Testarray2D [2][0] = "Column2"
    $Testarray2D [3][0] = "Column3"
    $Testarray2D [4][0] = "Column4"
    $Testarray2D [5][0] = "Column5"
    $Testarray2D [6][0] = "Column6"
    $Testarray2D [7][0] = "Column7"
    $Testarray2D [8][0] = "Column8"
    $Testarray2D [9][0] = "INT_Column"
    $Testarray2D [1][1] = "VARCHAR(45)"
    $Testarray2D [2][1] = "VARCHAR(60)"
    $Testarray2D [3][1] = "VARCHAR(75)"
    $Testarray2D [4][1] = "VARCHAR(40)"
    $Testarray2D [5][1] = "VARCHAR(20)"
    $Testarray2D [6][1] = "VARCHAR(17)"
    $Testarray2D [7][1] = "VARCHAR(52)"
    $Testarray2D [8][1] = "VARCHAR(100)"
    $Testarray2D [9][1] = "INT"
Dim $AddArray2D [3][10]
    $AddArray2D [1][1] = "Row 1 Column 1"
    $AddArray2D [1][2] = "Row 1 Column 2"
    $AddArray2D [1][3] = "Row 1 Column 3"
    $AddArray2D [1][4] = "Row 1 Column 4"
    $AddArray2D [1][5] = "Row 1 Column 5"
    $AddArray2D [1][6] = "Row 1 Column 6"
    $AddArray2D [1][7] = "Row 1 Column 7"
    $AddArray2D [1][8] = "Row 1 Column 8"
    $AddArray2D [1][9] = "111111111"
    $AddArray2D [2][1] = "Row 2 Column 1"
    $AddArray2D [2][2] = "Row 2 Column 2"
    $AddArray2D [2][3] = "Row 2 Column 3"
    $AddArray2D [2][4] = "Row 2 Column 4"
    $AddArray2D [2][5] = "Row 2 Column 5"
    $AddArray2D [2][6] = "Row 2 Column 6"
    $AddArray2D [2][7] = "Row 2 Column 7"
    $AddArray2D [2][8] = "Row 2 Column 8"
    $AddArray2D [2][9] = "222222222"
#EndRegion


$oMyError = ObjEvent("AutoIt.Error", "MyErrFunc")
$sqlCon = _MSSQL_Con("IP", "USER", "PASS", "DATABASE")

_MSSQL_CreateTable($sqlCon, "TestTable")
_MSSQL_CreateColumn($sqlCon, "TestTable", "TestColumn VARCHAR(150)")
For $i = 1 To UBound($TestArray1) - 1
_MSSQL_AddRecord($sqlCon, "TestTable", "'"&$TestArray1[$i]&"'")
Next
$UNIQUESHOW = _MSSQL_AddRecord($sqlCon, "TestTable", "'Value8'", TRUE, "WHERE TestColumn = 'Value8'")
If @error = 3 Then MsgBox(16, "Error", "Value8 already exist in Table TestTable, Column TestColum")
$Table = _MSSQL_CreateTable($sqlCon, "TestTable")
If @error = 2 Then MsgBox(16, "Error", "Table Already exists")

MSgbox(0, "Lets have a look", "Take a look at your database before you click OK")

$DeleteTable = _MSSQL_DropTable($sqlCon, "TestTable")
If $DeleteTable = 1 Then MsgBox(48, "Success", "TestTable was dropped")

_MSSQL_CreateTable($sqlCon, "TestTable1DArray")
$CreateColumn1D = _MSSQL_CreateColumn($sqlCon, "TestTable1DArray", $Testarray1D)
_MSSQL_AddRecord($sqlCon, "TestTable1DArray", $TestArray1)

_MSSQL_CreateTable($sqlCon, "TestTable2DArray")
$CreateColumn1D = _MSSQL_CreateColumn($sqlCon, "TestTable2DArray", $Testarray2D)
_MSSQL_AddRecord($sqlCon, "TestTable2DArray", $TestArray1)

MSgbox(0, "Lets have a look", "Take a look at your database before you click OK")
$DeleteTable = _MSSQL_DropTable($sqlCon, "TestTable1DArray")
If $DeleteTable = 1 Then MsgBox(48, "Success", "TestTable was dropped")
_MSSQL_AddRecord($sqlCon, "TestTable2DArray", $AddArray2D)
$getrecord = _MSSQL_GetRecord($sqlCon, "TestTable2DArray")
_arrayDisplay ($getrecord)

$DeleteTable = _MSSQL_DropTable($sqlCon, "TestTable2DArray")
If $DeleteTable = 1 Then MsgBox(48, "Success", "TestTable was dropped")

_MSSQL_End($sqlCon)

Func MyErrFunc()
    Local $HexNumber
    $HexNumber = Hex($oMyError.number, 8)
    MsgBox(0, "COM Error Test", "We intercepted a COM Error !" & @CRLF & @CRLF & _
            "err.description is: " & @TAB & $oMyError.description & @CRLF & _
            "err.windescription:" & @TAB & $oMyError.windescription & @CRLF & _
            "err.number is: " & @TAB & $HexNumber & @CRLF & _
            "err.lastdllerror is: " & @TAB & $oMyError.lastdllerror & @CRLF & _
            "err.scriptline is: " & @TAB & $oMyError.scriptline & @CRLF & _
            "err.source is: " & @TAB & $oMyError.source & @CRLF & _
            "err.helpfile is: " & @TAB & $oMyError.helpfile & @CRLF & _
            "err.helpcontext is: " & @TAB & $oMyError.helpcontext _
            )
    SetError(1); to check for after this function returns
EndFunc   ;==>MyErrFunc

hannes08 · March 31, 2011

Hi TheLuBu,

did you take a look at the _SQL.UDF by ChrisL?

I've used it in some scripts and its a great UDF to use with both MSDE and MSSQL Server.

TheLuBu · April 1, 2011

I´ve seen the UDF from ChrisL but i missed some Functions like Create a Table or Column without using the complete SQL Query.

For sure, it is great to use with MSDE, but i needed a UDF for MSSQL only, so i created it myself

TheLuBu

blitzkrg · May 16, 2012

@TheLuBu in case you stop by back these forums,

I just wanted to say thanks for putting together this UDF, it's been very helpful in a project I'm working on.

samuraj154 · November 26, 2012

Hi there,

i have problem with funcion _MSSQL_GetRecord. If i try chose column like this:

$getrecord = _MSSQL_GetRecord($sqlCon,"Kontrahent kon join Sklep s on (s.SklepId = kon.KontrId)","kon.KontrId,kon.Nazwa,kon.Skrot","where 1 = 1","kon.kontrid desc")
_arrayDisplay ($getrecord)

I get this:

Posted Image

IF i tray do this with array:

dim $tab[3]=["kon.KontrId","kon.Nazwa","kon.Skrot"]
$sColumn = $tab


$getrecord = _MSSQL_GetRecord($sqlCon,"Kontrahent kon join Sklep s on (s.SklepId = kon.KontrId)",$sColumn,"where 1 = 1","kon.kontrid desc")
_arrayDisplay ($getrecord)

Then i get nothing, no data and no error.

When i select all columns :

$getrecord = _MSSQL_GetRecord($sqlCon,"Kontrahent kon join Sklep s on (s.SklepId = kon.KontrId)","*","where 1 = 1","kon.kontrid desc")
_arrayDisplay ($getrecord)

That its ok, but i want to select columns. Sorry for my english.

mintea · July 21, 2014

I got an error when running script after upgrade to 3.3.12.0

MSSQL.au3" (324) : ==> Variable must be of type "Object".:

If Not $quer.EOF Then

If Not $quer^ ERROR

jazzyjeff · September 9, 2014

Hey mintea,

Did you get this figured out, because I am also seeing this.

Thanks

mintea · September 10, 2014

Hey mintea,

Did you get this figured out, because I am also seeing this.

Thanks

Nope, I swap to _SQL.au3 currently but need to make code change for existing script.

jazzyjeff · September 10, 2014

Thanks.

I ended up using this array sort UDF from Melba23.

'?do=embed' frameborder='0' data-embedContent>>

It works well for me as I was trying to use the ORDER BY statement, but was getting issues with it. So sorting the data with this UDF worked for me (I needed multiple columns).

Edited September 10, 2014 by jazzyjeff

jchd · September 10, 2014

It would be way faster to use SQL power to correctly filter and order data for you rather than adding slow script code for doing that.

jazzyjeff · September 11, 2014

SQL is definitely the preference, but working is more important than speed. The Udf is actually pretty quick. I tried others and they were slow. When work slows down I'll spend more time trying to address the SQL Udf

uncommon · October 23, 2014

Hey there,

I noticed this SQL UDF works pretty well, but I have a problem with large records. (100k in some tables) Sometimes I will get a error saying "Exception occurred., Query timeout expired" or "Not enough storage is avaible to complete this operation".

I have done some google seaches on this and tryed a few things I thought might fix this such as adding

$oSQL.ConnectionTimeout = 0
$oSQL.CursorLocation = 3;adUseClient

Then I figured maybe I can just ask for smaller rows and concate the arrays all into one but I had trouble creating the correct syntax to get the total record count. Anyway After spining my wheels for a while I figured I should just ask you clever people.

Here is a sample of my code.

$oSQL = _MSSQL_Con(@ComputerName, "Sa", "password", "test")
    $oSQL.ConnectionTimeout = 0
    $oSQL.CursorLocation = 3;adUseClient
    Local $aTableData[1][2]
    $aTableData[0][0] = "Table Name"
    $aTableData[0][1] = "Table Array"
    $aTableList = _MSSQL_ListAllTables($oSQL);import
    For $x = 1 To UBound($aTableList) - 1
        $aTable = _MSSQL_GetRecord($oSQL, $aTableList[$x][0])
        If IsArray($aTable) = 0 Then ContinueLoop
        ReDim $aTableData[UBound($aTableData) + 1][2]
        $aTableData[UBound($aTableData) - 1][0] = $aTableList[$x][0]
        $aTableData[UBound($aTableData) - 1][1] = $aTable
        _ArrayDisplay($aTableData)
    Next

Edited October 23, 2014 by uncommon

Fredricz · November 10, 2014

Hi there,

i have problem with funcion _MSSQL_GetRecord. If i try chose column like this:

$getrecord = _MSSQL_GetRecord($sqlCon,"Kontrahent kon join Sklep s on (s.SklepId = kon.KontrId)","kon.KontrId,kon.Nazwa,kon.Skrot","where 1 = 1","kon.kontrid desc")
_arrayDisplay ($getrecord)

I get this:

IF i tray do this with array:

dim $tab[3]=["kon.KontrId","kon.Nazwa","kon.Skrot"]
$sColumn = $tab


$getrecord = _MSSQL_GetRecord($sqlCon,"Kontrahent kon join Sklep s on (s.SklepId = kon.KontrId)",$sColumn,"where 1 = 1","kon.kontrid desc")
_arrayDisplay ($getrecord)

Then i get nothing, no data and no error.

When i select all columns :

$getrecord = _MSSQL_GetRecord($sqlCon,"Kontrahent kon join Sklep s on (s.SklepId = kon.KontrId)","*","where 1 = 1","kon.kontrid desc")
_arrayDisplay ($getrecord)

That its ok, but i want to select columns. Sorry for my english.

I'm getting the same here... GetRecord doesnt seems to work for me. Does it work for anyone?

redrider81 · September 27, 2015

Has anyone tested this with Microsoft Azure SQL?

cdebel2005 · January 11, 2016

For the sake of other users, i've got the same error with _MSSQL_GetRecord.

I don't know how the poster tested is stuff, but if you want to select some columns of your table like this:

dim $tab[3]=["kon.KontrId","kon.Nazwa","kon.Skrot"]

Then this line is wrong in _MSSQL_GetRecord.

If UBound($Columns, 2) - 1 <> 1 Then Return SetError(3, 0, 0)
It should be If UBound($Columns, 0) <> 1 Then Return SetError(3, 0, 0) because his comment on Error 3 mean that $Columns is not a 1D array.

Hi there,

i have problem with funcion _MSSQL_GetRecord. If i try chose column like this:

$getrecord = _MSSQL_GetRecord($sqlCon,"Kontrahent kon join Sklep s on (s.SklepId = kon.KontrId)","kon.KontrId,kon.Nazwa,kon.Skrot","where 1 = 1","kon.kontrid desc")
_arrayDisplay ($getrecord)

I get this:

Posted Image

IF i tray do this with array:

dim $tab[3]=["kon.KontrId","kon.Nazwa","kon.Skrot"]
$sColumn = $tab


$getrecord = _MSSQL_GetRecord($sqlCon,"Kontrahent kon join Sklep s on (s.SklepId = kon.KontrId)",$sColumn,"where 1 = 1","kon.kontrid desc")
_arrayDisplay ($getrecord)

Then i get nothing, no data and no error.

When i select all columns :

$getrecord = _MSSQL_GetRecord($sqlCon,"Kontrahent kon join Sklep s on (s.SklepId = kon.KontrId)","*","where 1 = 1","kon.kontrid desc")
_arrayDisplay ($getrecord)

That its ok, but i want to select columns. Sorry for my english.

cdebel2005 · January 11, 2016

In fact, to use _MSSQL_GetRecord with specified columns, it should be like this (remove the ConsoleWrite, it was there for debugging purpose)

;===============================================================================
;
; Function Name....: _MSSQL_GetRecord
; Description......: Get one or more Values from Database
; Syntax...........: _MSSQL_GetRecord($oConnectionObj, $sTable, $Columns = "*", $condition = "", $order = "")
; Parameter(s).....: $oConnectionObj = Object, returned by _MSSQL_Con
; $sTable = Tablename
; $sColumn = [optional] Name of one or more Columns in the Table [ Standard = "*" (all Columns)]
; $condition = [optional] A WHERE Case, to look for special Values [Standard = "" (all Values)]
; - The datafields in the WHERE Case have to be like this: 'VALUE'
; $order = [optional] ORDER BY Case, to sort the returned values [Standard = "" (no order)]
; Requirement......: If $sColumn is an Array it has to be indexed 1,
; - $sColumn contains the Columns to read from
; Return Value(s)..: Success - Returns an array
; - If you searched in only one column, $aResult is a 1-D Array, where $aResult[0] is the number of found values
; - If you searched in more then one column, $aResult is multidimensional, where $aResult[0][n] is the number of found values
; Failure - 0, sets @error
; |1 - $oConnectionObj is not an object
; |2 - $aResult is not an array ( not happened yet, but msaybe possible)
; |3 - $sColumn is not an 1-D array
; |4 - Query Error, Query saved to @extended
; Author(s)........: TheLuBu <LuBu@veytal.com>
;
;===============================================================================
Func _MSSQL_GetRecord($oConnectionObj, $sTable, $Columns = "*", $condition = "", $order = "")
Local $str, $quer, $aResult, $iColumns, $iRows
If IsObj($oConnectionObj) And Not @error Then
If IsArray($Columns) Then
           ConsoleWrite("Is an array" & @LF)
           ConsoleWrite(UBound($Columns, 0) & @LF)
If UBound($Columns, 0) <> 1 Then Return SetError(3, 0, 0)
$str = "SELECT "
For $i = 0 To UBound($Columns) - 1
$str &= $Columns[$i] & ","
Next
If $order = "" Then
$str = StringTrimRight($str, 1) & " FROM " & $sTable & " " & $condition & ";"
Else
$str = StringTrimRight($str, 1) & " FROM " & $sTable & " " & $condition & " ORDER BY " & $order & ";"
           EndIf
           ConsoleWrite($str & @LF)

$quer = $oConnectionObj.execute($str)
With $quer
If Not .EOF Then
$aResult = .GetRows()
If IsArray($aResult) And UBound($aResult, 2) > 1 Then
$iColumns = UBound($aResult, 2)
$iRows = UBound($aResult)
ReDim $aResult[$iRows + 1][$iColumns]
For $x = $iRows To 1 Step -1
For $y = 0 To $iColumns - 1
$aResult[$x][$y] = $aResult[$x - 1][$y]
Next
Next
For $i = 0 To $iColumns - 1
$aResult[0][$i] = .Fields($i).Name
Next
ElseIf IsArray($aResult) And UBound($aResult, 2) = 1 Then
$iRows = UBound($aResult)
Local $bResult[$iRows + 1]
For $x = $iRows To 1 Step -1
$bResult[$x] = $aResult[$x - 1][0]
Next
$bResult[0] = $iRows
Return $bResult
Else
Return SetError(2, 0, 0)
EndIf
Else
Return SetError(4, $str, 0)
EndIf
EndWith
Return $aResult
Else
If $order = "" Then
If $Columns = "*" Then
               ConsoleWrite("SELECT " & $Columns & " FROM " & $sTable & " " & $condition & ";" & @LF)
$quer = $oConnectionObj.execute("SELECT " & $Columns & " FROM " & $sTable & " " & $condition & ";")
Else
               ConsoleWrite("SELECT '" & $Columns & "' FROM " & $sTable & " " & $condition & ";" & @LF)
$quer = $oConnectionObj.execute("SELECT '" & $Columns & "' FROM " & $sTable & " " & $condition & ";")
EndIf
Else
If $Columns = "*" Then
               ConsoleWrite("SELECT " & $Columns & " FROM " & $sTable & " " & $condition & " ORDER BY " & $order & " ;" & @LF)
$quer = $oConnectionObj.execute("SELECT " & $Columns & " FROM " & $sTable & " " & $condition & " ORDER BY " & $order & " ;")
               Else
               ConsoleWrite("SELECT '" & $Columns & "' FROM " & $sTable & " " & $condition & " ORDER BY " & $order & " ;" & @LF)

$quer = $oConnectionObj.execute("SELECT '" & $Columns & "' FROM " & $sTable & " " & $condition & " ORDER BY " & $order & " ;")
EndIf
EndIf
If Not $quer.EOF Then
$aResult = $quer.GetRows()
If IsArray($aResult) And UBound($aResult, 2) > 1 Then
$iColumns = UBound($aResult, 2)
$iRows = UBound($aResult)
ReDim $aResult[$iRows + 1][$iColumns]
For $x = $iRows To 1 Step -1
For $y = 0 To $iColumns - 1
$aResult[$x][$y] = $aResult[$x - 1][$y]
Next
Next
For $i = 0 To $iColumns - 1
$aResult[0][$i] = $quer.Fields($i).Name
Next
ElseIf IsArray($aResult) And UBound($aResult, 2) = 1 Then
$iRows = UBound($aResult)
Local $bResult[$iRows + 1]
For $x = $iRows To 1 Step -1
$bResult[$x] = $aResult[$x - 1][0]
Next
$bResult[0] = $iRows
Return $bResult
Else
Return SetError(2, 0, 0)
EndIf
Else
Return SetError(4, $str, 0)
EndIf
Return $aResult
EndIf
EndIf
Return SetError(1, 0, 0)
EndFunc ;==>_MSSQL_GetRecord

dj__ml · March 30, 2017

;===============================================================================
;
; Function Name....:     _MSSQL_GetRecord
; Description......:     Get one or more Values from Database
; Syntax...........:    _MSSQL_GetRecord($oConnectionObj, $sTable, $Columns = "*", $condition = "", $order = "")
; Parameter(s).....:      $oConnectionObj = Object, returned by _MSSQL_Con
;                         $sTable = Tablename
;                        $sColumn = [optional] Name of one or more Columns in the Table [ Standard = "*" (all Columns)]
;                        $condition = [optional] A WHERE Case, to look for special Values [Standard = "" (all Values)]
;                                        - The datafields in the WHERE Case have to be like this:  'VALUE'
;                        $order = [optional] ORDER BY Case, to sort the returned values [Standard = "" (no order)]
; Requirement......:    If $sColumn is an Array it has to be indexed 1,
;                            - $sColumn contains the Columns to read from
; Return Value(s)..:     Success - Returns an array
;                                    - If you searched in only one column, $aResult is a 1-D Array, where $aResult[0] is the number of found values
;                                    - If you searched in more then one column, $aResult is multidimensional, where $aResult[0][n] is the number of found values
;                         Failure - 0, sets @error
;                         |1 - $oConnectionObj is not an object
;                        |2 - $aResult is not an array ( not happened yet, but msaybe possible)
;                        |3 - $sColumn is not an 1-D array
;                        |4 - Query Error, Query saved to @extended
; Author(s)........:     TheLuBu <LuBu@veytal.com>
;
;===============================================================================
Func _MSSQL_GetRecord($oConnectionObj, $sTable, $Columns = "*", $condition = "", $order = "")

    Local $str, $quer, $aResult, $iColumns, $iRows
    If IsObj($oConnectionObj) And Not @error Then
        If IsArray($Columns) Then
            If UBound($Columns, 0) <> 1 Then Return SetError(3, 0, 0)
            $str = "SELECT "
            For $i = 0 To UBound($Columns) - 1
                $str &= $Columns[$i] & ", "
            Next
            If $order = "" Then
                $str = StringTrimRight($str, 2) & " FROM " & $sTable & " " & $condition & ";"
            Else
                $str = StringTrimRight($str, 2) & " FROM " & $sTable & " " & $condition & " ORDER BY " & $order & " ;"
            EndIf

            $quer = $oConnectionObj.execute($str)
            With $quer
                If Not .EOF Then
                    $aResult = .GetRows()
                    If IsArray($aResult) And UBound($aResult, 2) > 1 Then
                        $iColumns = UBound($aResult, 2)
                        $iRows = UBound($aResult)
                        ReDim $aResult[$iRows + 1][$iColumns]
                        For $x = $iRows To 1 Step -1
                            For $y = 0 To $iColumns - 1
                                $aResult[$x][$y] = $aResult[$x - 1][$y]
                            Next
                        Next
                        For $i = 0 To $iColumns - 1
                            $aResult[0][$i] = .Fields($i).Name
                        Next
                    ElseIf IsArray($aResult) And UBound($aResult, 2) = 1 Then
                        $iRows = UBound($aResult)
                        Local $bResult[$iRows + 1]
                        For $x = $iRows To 1 Step -1
                            $bResult[$x] = $aResult[$x - 1][0]
                        Next
                        $bResult[0] = $iRows
                        Return $bResult
                    Else
                        Return SetError(2, 0, 0)
                    EndIf
                Else
                    Return SetError(4, $str, 0)
                EndIf
            EndWith
            Return $aResult
        Else
            If $order = "" Then
                If $Columns = "*" Then
                    $quer = $oConnectionObj.execute("SELECT " & $Columns & " FROM " & $sTable & " " & $condition & ";")
                Else
                    $quer = $oConnectionObj.execute("SELECT " & $Columns & " FROM " & $sTable & " " & $condition & ";")
                EndIf
            Else
                If $Columns = "*" Then
                    $quer = $oConnectionObj.execute("SELECT " & $Columns & " FROM " & $sTable & " " & $condition & " ORDER BY " & $order & " ;")
                Else
                    $quer = $oConnectionObj.execute("SELECT " & $Columns & " FROM " & $sTable & " " & $condition & " ORDER BY " & $order & " ;")
                EndIf
            EndIf
            If Not $quer.EOF Then
                $aResult = $quer.GetRows()
                If IsArray($aResult) And UBound($aResult, 2) > 1 Then
                    $iColumns = UBound($aResult, 2)
                    $iRows = UBound($aResult)
                    ReDim $aResult[$iRows + 1][$iColumns]
                    For $x = $iRows To 1 Step -1
                        For $y = 0 To $iColumns - 1
                            $aResult[$x][$y] = $aResult[$x - 1][$y]
                        Next
                    Next
                    For $i = 0 To $iColumns - 1
                        $aResult[0][$i] = $quer.Fields($i).Name
                    Next
                ElseIf IsArray($aResult) And UBound($aResult, 2) = 1 Then
                    $iRows = UBound($aResult)
                    Local $bResult[$iRows + 1]
                    For $x = $iRows To 1 Step -1
                        $bResult[$x] = $aResult[$x - 1][0]
                    Next
                    $bResult[0] = $iRows
                    Return $bResult
                Else
                    Return SetError(2, 0, 0)
                EndIf
            Else
                Return SetError(4, $str, 0)
            EndIf
            Return $aResult
        EndIf
    EndIf
    Return SetError(1, 0, 0)
EndFunc   ;==>_MSSQL_GetRecord

I have solved the problema with array results values is the column name.

bye

jakub969 · February 11

hello, i'm trying to add some data to database, but i'm getting error

#include <MSSQL.au3>
#include <Array.au3>
#Region Arrays and Values
Dim $TestArray1 [10]
    $TestArray1[1] = "VALUE1"
    $TestArray1[2] = "Value2"
    $TestArray1[3] = "Value3"
    $TestArray1[4] = "Value4"
    $TestArray1[5] = "Value5"
    $TestArray1[6] = "Value6"
    $TestArray1[7] = "Value7"
    $TestArray1[8] = "Value8"
    $TestArray1[9] = "123456789"
    $TestValue = "Value15"
    $UNIQUEValue = "Value15"
Dim $Testarray1D [10]
    $Testarray1D [1] = "Column1"
    $Testarray1D [2] = "Column2"
    $Testarray1D [3] = "Column3"
    $Testarray1D [4] = "Column4"
    $Testarray1D [5] = "Column5"
    $Testarray1D [6] = "Column6"
    $Testarray1D [7] = "Column7"
    $Testarray1D [8] = "Column8"
    $Testarray1D [9] = "Column9"
Dim $Testarray2D [10][2]
    $Testarray2D [1][0] = "Column1"
    $Testarray2D [2][0] = "Column2"
    $Testarray2D [3][0] = "Column3"
    $Testarray2D [4][0] = "Column4"
    $Testarray2D [5][0] = "Column5"
    $Testarray2D [6][0] = "Column6"
    $Testarray2D [7][0] = "Column7"
    $Testarray2D [8][0] = "Column8"
    $Testarray2D [9][0] = "INT_Column"
    $Testarray2D [1][1] = "VARCHAR(45)"
    $Testarray2D [2][1] = "VARCHAR(60)"
    $Testarray2D [3][1] = "VARCHAR(75)"
    $Testarray2D [4][1] = "VARCHAR(40)"
    $Testarray2D [5][1] = "VARCHAR(20)"
    $Testarray2D [6][1] = "VARCHAR(17)"
    $Testarray2D [7][1] = "VARCHAR(52)"
    $Testarray2D [8][1] = "VARCHAR(100)"
    $Testarray2D [9][1] = "INT"
Dim $AddArray2D [3][10]
    $AddArray2D [1][1] = "Row 1 Column 1"
    $AddArray2D [1][2] = "Row 1 Column 2"
    $AddArray2D [1][3] = "Row 1 Column 3"
    $AddArray2D [1][4] = "Row 1 Column 4"
    $AddArray2D [1][5] = "Row 1 Column 5"
    $AddArray2D [1][6] = "Row 1 Column 6"
    $AddArray2D [1][7] = "Row 1 Column 7"
    $AddArray2D [1][8] = "Row 1 Column 8"
    $AddArray2D [1][9] = "111111111"
    $AddArray2D [2][1] = "Row 2 Column 1"
    $AddArray2D [2][2] = "Row 2 Column 2"
    $AddArray2D [2][3] = "Row 2 Column 3"
    $AddArray2D [2][4] = "Row 2 Column 4"
    $AddArray2D [2][5] = "Row 2 Column 5"
    $AddArray2D [2][6] = "Row 2 Column 6"
    $AddArray2D [2][7] = "Row 2 Column 7"
    $AddArray2D [2][8] = "Row 2 Column 8"
    $AddArray2D [2][9] = "222222222"
#EndRegion


$oMyError = ObjEvent("AutoIt.Error", "MyErrFunc")
$sqlCon = _MSSQL_Con("ASUS-170\SQLEXPRESS", "Asus", "PWD", "DB_TEST")

_MSSQL_CreateTable($sqlCon, "TestTable")
_MSSQL_CreateColumn($sqlCon, "TestTable", "TestColumn VARCHAR(150)")
For $i = 1 To UBound($TestArray1) - 1
_MSSQL_AddRecord($sqlCon, "TestTable", "'"&$TestArray1[$i]&"'")
Next
$UNIQUESHOW = _MSSQL_AddRecord($sqlCon, "TestTable", "'Value8'", TRUE, "WHERE TestColumn = 'Value8'")
If @error = 3 Then MsgBox(16, "Error", "Value8 already exist in Table TestTable, Column TestColum")
$Table = _MSSQL_CreateTable($sqlCon, "TestTable")
If @error = 2 Then MsgBox(16, "Error", "Table Already exists")

MSgbox(0, "Lets have a look", "Take a look at your database before you click OK")

$DeleteTable = _MSSQL_DropTable($sqlCon, "TestTable")
If $DeleteTable = 1 Then MsgBox(48, "Success", "TestTable was dropped")

_MSSQL_CreateTable($sqlCon, "TestTable1DArray")
$CreateColumn1D = _MSSQL_CreateColumn($sqlCon, "TestTable1DArray", $Testarray1D)
_MSSQL_AddRecord($sqlCon, "TestTable1DArray", $TestArray1)

_MSSQL_CreateTable($sqlCon, "TestTable2DArray")
$CreateColumn1D = _MSSQL_CreateColumn($sqlCon, "TestTable2DArray", $Testarray2D)
_MSSQL_AddRecord($sqlCon, "TestTable2DArray", $TestArray1)

MSgbox(0, "Lets have a look", "Take a look at your database before you click OK")
$DeleteTable = _MSSQL_DropTable($sqlCon, "TestTable1DArray")
If $DeleteTable = 1 Then MsgBox(48, "Success", "TestTable was dropped")
_MSSQL_AddRecord($sqlCon, "TestTable2DArray", $AddArray2D)
$getrecord = _MSSQL_GetRecord($sqlCon, "TestTable2DArray")
_arrayDisplay ($getrecord)

$DeleteTable = _MSSQL_DropTable($sqlCon, "TestTable2DArray")
If $DeleteTable = 1 Then MsgBox(48, "Success", "TestTable was dropped")

_MSSQL_End($sqlCon)

Func MyErrFunc()
    Local $HexNumber
    $HexNumber = Hex($oMyError.number, 8)
    MsgBox(0, "COM Error Test", "We intercepted a COM Error !" & @CRLF & @CRLF & _
            "err.description is: " & @TAB & $oMyError.description & @CRLF & _
            "err.windescription:" & @TAB & $oMyError.windescription & @CRLF & _
            "err.number is: " & @TAB & $HexNumber & @CRLF & _
            "err.lastdllerror is: " & @TAB & $oMyError.lastdllerror & @CRLF & _
            "err.scriptline is: " & @TAB & $oMyError.scriptline & @CRLF & _
            "err.source is: " & @TAB & $oMyError.source & @CRLF & _
            "err.helpfile is: " & @TAB & $oMyError.helpfile & @CRLF & _
            "err.helpcontext is: " & @TAB & $oMyError.helpcontext _
            )
    SetError(1); to check for after this function returns
EndFunc   ;==>MyErrFunc

any ideas how to fix ?

Jos · February 11

1 hour ago, jakub969 said:

any ideas how to fix ?

Use the proper userid and password?

UDF - MSSQL.au3

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Create an account or sign in to comment

Create an account

Sign in

Recently Browsing 0 members