• Hallo ihr lieben,

    Ich habe mich mal drangesetzt und eine neue MSSQL.au3 geschrieben, da mir die 3 funktionen aus der alten UDF nicht gefallen haben ;)
    Bei mir laufen sie alle, falls ihr Fehler findet, meldet euch bitte, ich versuche sie zu lösen.
    Solltet ihr weitere Funktionen benötigen, meldet euch auch, ich versuche sie einzubauen.

    Die UDF wird noch weiter aktualisert, sobald ich weitere Funktionen habe

    Spoiler anzeigen
    [autoit]

    #include-once
    #include <Date.au3>
    #include <File.au3>

    [/autoit] [autoit][/autoit] [autoit]

    Opt("MustDeclareVars", 1)
    ; ##############################################################################
    ; Logging
    ; ##############################################################################
    ;
    Global Enum $LOG_ALL, $LOG_DEBUG, $LOG_WARN, $LOG_ERROR, $LOG_FIX
    Global Enum $LOG_TARGET_CONSOLE, $LOG_TARGET_TRAYTIP, $LOG_TARGET_MSGBOX, $LOG_TARGET_FILE
    Global $o_MyMSSQLError
    Global $o_MyMSSQLErrorShow
    Global $o_MyMSSQLErrorPATH

    [/autoit] [autoit][/autoit] [autoit]

    ; Default
    Global $log_level = $LOG_FIX
    Global $log_target = $LOG_TARGET_CONSOLE
    Global $log_filename = @ScriptDir & "\" & @ScriptName & ".log"
    Global $log_enable_timing = True

    [/autoit] [autoit][/autoit] [autoit]

    ; #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_DropTable
    ;~ _MSSQL_DropColumn
    ; ===============================================================================================================================

    [/autoit] [autoit][/autoit] [autoit][/autoit] [autoit]

    ;===============================================================================
    ;
    ; 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 <[email='LuBu@veytal.com'][/email]>
    ;
    ;===============================================================================

    [/autoit] [autoit][/autoit] [autoit]

    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
    __MSSQLLog("Tabelle existiert bereits", "Es ist bereits eine Tabelle mit dem Namen " & $sTable & " vorhanden", $LOG_WARN)
    Return SetError(2, @error, 0)
    EndIf
    If $identity = True Then
    $str = "CREATE TABLE " & $sTable & " (" & $sPrimeKey & " int IDENTITY (1,1) PRIMARY KEY) ;"
    Else
    $str = "CREATE TABLE " & $sTable & " (" & $Columnname & ");"
    EndIf
    __MSSQLLog("Query Debug", $str, $LOG_DEBUG)
    $oConnectionObj.execute($str)
    Return 1
    ElseIf @error Then
    __MSSQLLog("Keine Verbindung zur Datenbank", "Es besteht keine Verbindung zur Datenbank!", $LOG_ERROR)
    Return SetError(1, 0, 0)
    EndIf
    EndFunc ;==>_MSSQL_CreateTable

    [/autoit] [autoit][/autoit] [autoit]

    ;===============================================================================
    ;
    ; 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 <[email='LuBu@veytal.com'][/email]>
    ;
    ;===============================================================================
    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) & ";"
    __MSSQLLog("Query Debug", $str, $LOG_DEBUG)
    $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
    __MSSQLLog("Query Debug", $str, $LOG_DEBUG)
    $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 <[email='LuBu@veytal.com'][/email]>
    ;
    ;===============================================================================
    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) & ");"
    If StringRight($str, 7) = "VALUE);" Then Return SetError(3, 0, 0)
    __MSSQLLog("Query Debug", $str, $LOG_DEBUG)
    $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) & ");"
    If StringRight($str, 7) = "VALUE);" Then Return SetError(3, 0, 0)
    __MSSQLLog("Query Debug", $str, $LOG_DEBUG)
    $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
    __MSSQLLog("Query Debug", $str, $LOG_DEBUG)
    $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 <[email='LuBu@veytal.com'][/email]>
    ;
    ;===============================================================================
    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
    __MSSQLLog("Query Debug", $str, $LOG_DEBUG)
    $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
    $str = "SELECT " & $Columns & " FROM " & $sTable & " " & $condition & ";"
    __MSSQLLog("Query Debug", $str, $LOG_DEBUG)
    $quer = $oConnectionObj.execute($str)
    Else
    $str = "SELECT " & $Columns & " FROM " & $sTable & " " & $condition & " ORDER BY " & $order & " ;"
    __MSSQLLog("Query Debug", $str, $LOG_DEBUG)
    $quer = $oConnectionObj.execute($str)
    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 <[email='LuBu@veytal.com'][/email]>
    ;
    ;===============================================================================

    [/autoit] [autoit][/autoit] [autoit]

    Func _MSSQL_TableExist($oConnectionObj, $sTable, $Use = 1)
    Local $quer, $str
    If IsObj($oConnectionObj) And Not @error Then
    If $Use = 1 Then
    $str = "SELECT TABLE_SCHEMA FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '" & $sTable & "';"
    __MSSQLLog("Query Debug", $str, $LOG_DEBUG)
    $quer = $oConnectionObj.execute($str)
    If Not $quer.EOF Then
    Return 1
    Else
    Return SetError(3, $str, 0)
    EndIf
    Else
    $str = "SELECT TABLE_SCHEMA FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = '" & $sTable & "';"
    __MSSQLLog("Query Debug", $str, $LOG_DEBUG)
    $quer = $oConnectionObj.execute($str)
    If Not $quer.EOF Then
    Return 1
    Else
    Return SetError(3, $str, 0)
    EndIf
    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 <[email='LuBu@veytal.com'][/email]>
    ;
    ;===============================================================================
    Func _MSSQL_ColumnExist($oConnectionObj, $sTable, $sColumn)
    Local $quer, $str
    If IsObj($oConnectionObj) And Not @error Then
    If Not _MSSQL_TableExist($oConnectionObj, $sTable) Then Return SetError(3, 0, 0)
    $str = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = '" & $sColumn & "' AND TABLE_NAME = '" & $sTable & "';"
    __MSSQLLog("Query Debug", $str, $LOG_DEBUG)
    $quer = $oConnectionObj.execute($str)
    If Not $quer.EOF Then
    Return 1
    Else
    Return SetError(4, $str, 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, $iTimeOut = 240)
    Local $sqlCon
    $sqlCon = ObjCreate("ADODB.Connection")
    $sqlCon.Open("Provider=SQLOLEDB; Data Source=" & $scIP & "; User ID=" & $scUser & "; Password=" & $scPass & "; database=" & $scDB & ";")
    $sqlCon.CommandTimeout = $iTimeOut
    If @error Then Return SetError(1, 0, 0)
    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 <[email='LuBu@veytal.com'][/email]>
    ;
    ;===============================================================================
    Func _MSSQL_ListAllColumns($oConnectionObj, $sTable)
    Local $quer, $aResult, $iRows, $str
    If IsObj($oConnectionObj) And Not @error Then
    If Not _MSSQL_TableExist($oConnectionObj, $sTable) Then Return SetError(3, 0, 0)
    $str = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '" & $sTable & "';"
    __MSSQLLog("Query Debug", $str, $LOG_DEBUG)
    $quer = $oConnectionObj.execute($str)
    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, $str, 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 <[email='LuBu@veytal.com'][/email]>
    ;
    ;===============================================================================
    Func _MSSQL_ListAllTables($oConnectionObj)
    Local $quer, $aResult, $iColumns, $iRows, $str
    If IsObj($oConnectionObj) And Not @error Then
    $str = "SELECT TABLE_NAME, TABLE_TYPE FROM INFORMATION_SCHEMA.TABLES;"
    __MSSQLLog("Query Debug", $str, $LOG_DEBUG)
    $quer = $oConnectionObj.execute($str)
    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 <[email='LuBu@veytal.com'][/email]>
    ;
    ;===============================================================================
    Func _MSSQL_GetColumninfo($oConnectionObj, $sTable, $sColumn = "Allcolumns")
    Local $quer, $aResult, $iColumns, $iRows, $str
    If IsObj($oConnectionObj) And Not @error Then
    If $sColumn = "Allcolumns" Then
    $str = "SELECT COLUMN_NAME, IS_NULLABLE, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, COLLATION_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '" & $sTable & "';"
    __MSSQLLog("Query Debug", $str, $LOG_DEBUG)
    $quer = $oConnectionObj.execute($str)
    Else
    $str = "SELECT COLUMN_NAME, IS_NULLABLE, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, COLLATION_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '" & $sTable & "' AND COLUMN_NAME = '" & $sColumn & "';"
    __MSSQLLog("Query Debug", $str, $LOG_DEBUG)
    $quer = $oConnectionObj.execute($str)
    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 <[email='LuBu@veytal.com'][/email]>
    ;
    ;===============================================================================
    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 & ";"
    __MSSQLLog("Query Debug", $str, $LOG_DEBUG)
    $quer = $oConnectionObj.execute($str)
    Else
    $str = "UPDATE " & $sTable & " SET " & $sColumn & " = " & $sValue & " " & $condition & ";"
    __MSSQLLog("Query Debug", $str, $LOG_DEBUG)
    $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 <[email='LuBu@veytal.com'][/email]>
    ;
    ;===============================================================================
    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 & ";"
    __MSSQLLog("Query Debug", $str, $LOG_DEBUG)
    $quer = $oConnectionObj.execute($str)
    Else
    $str = "DELETE FROM " & $sTable & " " & $condition & ";"
    __MSSQLLog("Query Debug", $str, $LOG_DEBUG)
    $quer = $oConnectionObj.execute($str)
    EndIf
    Return 1
    Else
    Return SetError(1, 0, 0)
    EndIf
    EndFunc ;==>_MSSQL_DeleteRecord

    [/autoit] [autoit][/autoit] [autoit]

    ;===============================================================================
    ;
    ; 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 <[email='LuBu@veytal.com'][/email]>
    ;
    ;===============================================================================
    Func _MSSQL_DropTable($oConnectionObj, $sTable)
    Local $str, $quer
    If IsObj($oConnectionObj) And Not @error Then
    If Not _MSSQL_TableExist($oConnectionObj, $sTable) Then Return SetError(2, 0, 0)
    $str = "DROP TABLE " & $sTable & ";"
    __MSSQLLog("Query Debug", $str, $LOG_DEBUG)
    $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 <[email='LuBu@veytal.com'][/email]>
    ;
    ;===============================================================================
    Func _MSSQL_DropColumn($oConnectionObj, $sTable, $sColumn)
    Local $str, $quer
    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 & ";"
    __MSSQLLog("Query Debug", $str, $LOG_DEBUG)
    $quer = $oConnectionObj.execute($str)
    Return 1
    Else
    Return SetError(1, 0, 0)
    EndIf
    EndFunc ;==>_MSSQL_DropColumn

    [/autoit] [autoit][/autoit] [autoit]

    ; #FUNCTION# ====================================================================================================================
    ; Name ..........: _MSSQL_Logging
    ; Description ...:
    ; Syntax ........: _MSSQL_Logging([$bLevel = True[, $iTarget = $LOG_TARGET_CONSOLE[, $sFilename = @ScriptDir & "\" & @ScriptName & ".log"[,
    ; $bTiming = True]]]])
    ; Parameters ....: $bLevel - [optional] A boolean value. Enable or Disable Logging. Default is True.
    ; $iTarget - [optional] A numeric value. Default is $LOG_TARGET_CONSOLE.
    ; |0 - ($LOG_TARGET_CONSOLE) = Log to Console
    ; |1 - ($LOG_TARGET_TRAYTIP) = Log to Traytip
    ; |2 - ($LOG_TARGET_MSGBOX) = Log to MsgBox
    ; |3 - ($LOG_TARGET_FILE) = Log to File
    ; $sFilename - [optional] A Filepath. Default is @ScriptDir & "\" & @ScriptName & ".log".
    ; $bTiming - [optional] A boolean value. Enable or Disable Times for Loggingentries. Default is True.
    ; Return values .: None
    ; Author ........: TheLuBu <[email='LuBu@veytal.com'][/email]>
    ; Example .......: No
    ; ===============================================================================================================================
    Func _MSSQL_Logging($bLevel = True, $iTarget = $LOG_TARGET_CONSOLE, $sFilename = @ScriptFullPath, $bTiming = True)
    If $sFilename = @ScriptFullPath Then $sFilename = StringTrimRight(@ScriptFullPath, 3) & ".log"
    If $bLevel = True Then
    $log_level = $LOG_DEBUG
    Else
    $log_level = $LOG_FIX
    EndIf
    $log_target = $iTarget
    $log_filename = $sFilename
    $log_enable_timing = $bTiming
    EndFunc ;==>_MSSQL_Logging

    [/autoit] [autoit][/autoit] [autoit]

    Func __MSSQLLog($msg_title, $msg = "", $bLevel = $LOG_ERROR)
    ; Log-Level beachten
    If $bLevel < $log_level Then Return
    Local $tCur = _Date_Time_GetLocalTime()
    ; Log-Typ beachten
    Switch $log_target
    Case $LOG_TARGET_CONSOLE
    If $log_enable_timing Then
    $msg_title = StringFormat("[%s] %s", _Date_Time_SystemTimeToDateTimeStr($tCur), $msg_title)
    EndIf
    If $msg = "" Then
    ConsoleWrite(StringFormat("%s\n", $msg_title))
    Else
    ConsoleWrite(StringFormat("%s: %s\n", $msg_title, $msg))
    EndIf
    Case $LOG_TARGET_TRAYTIP
    If $log_enable_timing Then
    $msg_title = StringFormat("%s [%s]", $msg_title, _Date_Time_SystemTimeToDateTimeStr($tCur))
    EndIf
    TrayTip($msg_title, $msg, 20)
    Case $LOG_TARGET_MSGBOX
    If $log_enable_timing Then
    $msg_title = StringFormat("%s [%s]", $msg_title, _Date_Time_SystemTimeToDateTimeStr($tCur))
    EndIf
    MsgBox(64 + 4096 + 262144, $msg_title, $msg)
    Case $LOG_TARGET_FILE
    If $log_enable_timing Then
    $msg_title = StringFormat("[%s] %s", _Date_Time_SystemTimeToDateTimeStr($tCur), $msg_title)
    EndIf
    If $log_filename == "" Then
    ; Schreibrechte prüfen
    Local $log_file_paths[3] = [@ScriptDir & "\lastrun.log", @UserProfileDir & "\lastrun.log", @TempDir & "\lastrun.log"]
    For $i = 0 To UBound($log_file_paths) - 1
    $log_filename = $log_file_paths[$i]
    If FileWrite($log_filename, "") == 1 Then ExitLoop
    Next
    ConsoleWrite("Schreibe Log nach " & $log_filename & @CRLF)
    FileDelete($log_filename)
    EndIf
    Local $f = FileOpen($log_filename, 1) ; Write mode (append to end of file)
    If $f <> -1 Then
    If $msg = "" Then
    FileWrite($f, StringFormat("%s\n", $msg_title))
    Else
    FileWrite($f, StringFormat("%s: %s\n", $msg_title, $msg))
    EndIf
    EndIf
    FileClose($f)
    EndSwitch
    EndFunc ;==>__MSSQLLog

    [/autoit] [autoit][/autoit] [autoit]

    ; #FUNCTION# ====================================================================================================================
    ; Name ..........: _MSSQL_Errorhandler
    ; Description ...: Initialize MSSQL Error Handler
    ; Syntax ........: _MSSQL_Errorhandler([$iDisplay = 0[, $sFilepath = ""]])
    ; Parameters ....: $iDisplay - [optional] How to Display SQL Errors. Default is 1.
    ; 0|Ignore Error Messages
    ; 1|Debug Error Message to MsgBox
    ; 2|Debug Error Message to Console
    ; 3|Debug Error Message to File
    ; $sFilepath - [optional] Filepath if $iDisplay = 3.
    ; Return values .: None
    ; Author ........: TheLuBu <[email='LuBu@veytal.com'][/email]>
    ; Example .......: No
    ; ===============================================================================================================================
    Func _MSSQL_Errorhandler($iDisplay = 1, $sFilepath = "")
    $o_MyMSSQLError = ObjEvent("AutoIt.Error", "__MyMSSQLErrFunc")
    $o_MyMSSQLErrorShow = $iDisplay
    $o_MyMSSQLErrorPATH = $sFilepath
    EndFunc ;==>_MSSQL_Errorhandler

    [/autoit] [autoit][/autoit] [autoit]

    Func __MyMSSQLErrFunc()
    Switch $o_MyMSSQLErrorShow
    Case 0
    Return 0
    Case 1
    MsgBox(0, "AutoItCOM", "We intercepted a COM Error !" & @CRLF & @CRLF & _
    "err.description is: " & @TAB & $o_MyMSSQLError.description & @CRLF & _
    "err.windescription:" & @TAB & $o_MyMSSQLError.windescription & @CRLF & _
    "err.number is: " & @TAB & Hex($o_MyMSSQLError.number, 8) & @CRLF & _
    "err.lastdllerror is: " & @TAB & $o_MyMSSQLError.lastdllerror & @CRLF & _
    "err.scriptline is: " & @TAB & $o_MyMSSQLError.scriptline & @CRLF & _
    "err.source is: " & @TAB & $o_MyMSSQLError.source & @CRLF & _
    "err.helpfile is: " & @TAB & $o_MyMSSQLError.helpfile & @CRLF & _
    "err.helpcontext is: " & @TAB & $o_MyMSSQLError.helpcontext)
    Case 2
    ConsoleWrite("We intercepted a COM Error !" & @CRLF & @CRLF & _
    "err.description is: " & @TAB & $o_MyMSSQLError.description & @CRLF & _
    "err.windescription:" & @TAB & $o_MyMSSQLError.windescription & @CRLF & _
    "err.number is: " & @TAB & Hex($o_MyMSSQLError.number, 8) & @CRLF & _
    "err.lastdllerror is: " & @TAB & $o_MyMSSQLError.lastdllerror & @CRLF & _
    "err.scriptline is: " & @TAB & $o_MyMSSQLError.scriptline & @CRLF & _
    "err.source is: " & @TAB & $o_MyMSSQLError.source & @CRLF & _
    "err.helpfile is: " & @TAB & $o_MyMSSQLError.helpfile & @CRLF & _
    "err.helpcontext is: " & @TAB & $o_MyMSSQLError.helpcontext)
    Case 3
    _FileWriteLog($o_MyMSSQLErrorPATH, "We intercepted a COM Error !" & @CRLF & @CRLF & _
    "err.description is: " & @TAB & $o_MyMSSQLError.description & @CRLF & _
    "err.windescription:" & @TAB & $o_MyMSSQLError.windescription & @CRLF & _
    "err.number is: " & @TAB & Hex($o_MyMSSQLError.number, 8) & @CRLF & _
    "err.lastdllerror is: " & @TAB & $o_MyMSSQLError.lastdllerror & @CRLF & _
    "err.scriptline is: " & @TAB & $o_MyMSSQLError.scriptline & @CRLF & _
    "err.source is: " & @TAB & $o_MyMSSQLError.source & @CRLF & _
    "err.helpfile is: " & @TAB & $o_MyMSSQLError.helpfile & @CRLF & _
    "err.helpcontext is: " & @TAB & $o_MyMSSQLError.helpcontext)
    EndSwitch
    Return 1
    EndFunc ;==>_MyMSSQLErrFunc

    [/autoit]

    au3.user.calltips.api Code:

    Spoiler anzeigen
    Code
    _MSSQL_Con($scIP, $scUser, $scPass, $scDB) Connect to a Microsoft SQL Database
    _MSSQL_Query($iSQLCon, $iQuery) Sends a Query to a Microsoft SQL Database
    _MSSQL_End($sqlCon) Disconnect from a Microsoft SQL Database
    _MSSQL_CreateTable($oConnectionObj, $sTbl) Create a Table
    _MSSQL_CreateColumn($oConnectionObj, $sTable, $sColumn[, $Null = "NULL"[, $sDataType = "VARCHAR(45)]] Create one or more Columns in a given Table
    _MSSQL_AddRecord($oConnectionObj, $sTable, $Values) Add Values to a given Table
    _MSSQL_GetRecord($oConnectionObj, $sTable[, $Columns = "*"[, $condition = ""[, $order = "")]]] Get all Records matching the given conditions
    _MSSQL_TableExist($oConnectionObj, $sTable) Checks if a given Table exists
    _MSSQL_ColumnExist($oConnectionObj, $sTable, $sColumn) Checks if a given Column in a given Table exists

    Changelog:

    Spoiler anzeigen

    -EDIT- 21. März 2011
    - Bei _MSSQL_CreateTable überprüft, ob die Tabelle bereits existiert, falls ja @error
    -EDIT- 23. März 2011
    - _MSSQL_ColumnExist und _MSSQL_TableExist funktionieren jetzt korrekt und liefert auch die richtigen errorcodes zurück.
    - UDF funktioniert jetzt auch mit Opt("MustDeclareVars", 1)
    - au3.user.calltips.api hinzugefügt
    - _MSSQL_ListAllColumns hinzugefügt
    -EDIT- 31. März 2011
    ~ _MSSQL_ListAllTables hinzugefügt
    ~ _MSSQL_GetColumninfo hinzugefügt
    ~ _MSSQL_UpdateRecord hinzugefügt ; noch nicht 100% überprüft
    ~ _MSSQL_DeleteRecord hinzugefügt ; noch nicht 100% überprüft
    - Bei _MSSQL_CreateColumn wird jetzt überprüft, ob die Spalte schon existiert und ob doppelte Spaltennamen in der Query vorkommen (nur bei Arrays)
    - Bei _MSSQL_CreateTable kann jetzt ausgewählt werden, ob ein Primärschlüssel erstellt werden soll
    - Bei _MSSQL_AddRecord kann jetzt überprüft werden, ob bereits der selbe Wert bzw. die selben Werte vorhanden sind (UNIQUE Eigenschaft einer oder mehrerer Spalten)
    -EDIT- 02. Mai 2011
    -Minor Bugfixes


    Beispiel (Example):

    Spoiler anzeigen
    [autoit]

    #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

    [/autoit] [autoit][/autoit] [autoit][/autoit] [autoit]

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

    [/autoit] [autoit][/autoit] [autoit]

    _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")

    [/autoit] [autoit][/autoit] [autoit]

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

    [/autoit] [autoit][/autoit] [autoit]

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

    [/autoit] [autoit][/autoit] [autoit]

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

    [/autoit] [autoit][/autoit] [autoit]

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

    [/autoit] [autoit][/autoit] [autoit]

    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)

    [/autoit] [autoit][/autoit] [autoit]

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

    [/autoit] [autoit][/autoit] [autoit]

    _MSSQL_End($sqlCon)

    [/autoit] [autoit][/autoit] [autoit]

    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

    [/autoit]

    MfG TheLuBu

  • Hi,
    ich benutze zwar kein MSSQL aber du solltest in _MSSQL_CreateTable noch IF NOT EXISTS einfügen.

    Habs mir grade angeschaut, ich weiß nicht was du meinst, ich glaube ich hab alles auf Existenz geprüft ^^

    -EDIT-
    Ah jetzt, ja bau ich noch ein ^^

  • Hi Sprenger,
    Ich habe ja bereits eine Funktion zu TableExist und Column exist geschrieben.
    Wenn man die query direkt mit If Not Exist absendet, erhält man keinen Rückgabewert, den man in autoit nutzen kann. (Soweit ich das sehe wohlgemerkt ^^)
    Durch meine Funktion erhält man aber einen Rückgabewert, welcher in Autoit genutzt werden kann ;D

  • - _MSSQL_ColumnExist und _MSSQL_TableExist funktionieren jetzt korrekt und liefert auch die richtigen errorcodes zurück.
    - UDF funktioniert jetzt auch mit Opt("MustDeclareVars", 1)
    - au3.user.calltips.api hinzugefügt

  • Hallo TheLuBu,

    ich habe ein Problem mit dem Aufruf von _MSSQL_Query
    Bei mir wird immer ein Fehler ausgeworfen, sobald ich einen Abruf machen möchte. (Return $iSQLCon.execute($iQuery)^ ERROR) -> Zeile 434 der MSSQL.au3.

    Der Aufruf der Funktion sieht so aus :

    $fsql = _MSSQL_Con($mssql_host,$mssql_login,$mssql_pass,$mssql_db)

    $wert = _MSSQL_Query($fsql,"SELECT Feld FROM Database LIMIT 1")

    Ich habe die MSSQL.AU3 per #include eingebunden und fertig.
    Kannst du dir einen Reim darauf machen?

    Gruß

    Man hat's nicht leicht, aber leicht hat's einen.

  • Tut mir leid, das ich erst so spät antworte, aber ich w ar übers Wochenende nicht in Reichweite eines PC´s ;)

    An sich könntest du auch _MSSQL_GetRecord nutzen mit $condition = "Limit 1"

    Ansonsten ist deine SQL Syntax falsch.
    Wenn du _MSSQL_Query nutzt musst du ein abschließendes ";" setzen, da sonst die Befehlszeile nicht vollständig ist, also

    [autoit]

    $wert = _MSSQL_Query($fsql,"SELECT Feld FROM Database LIMIT 1;")

    [/autoit]
  • Nach langer Zeit mal wieder ein Update:

    Die aktualisierte UDF findet ihr im ersten Beitrag

    Es sind 2 neue Funktionen hinzugekommen,
    zum einen _MSSQL_Logging und _MSSQL_Errorhandler

    _MSSQL_Logging ermöglicht es, die generierten Queries auszugeben, um evtl. Fehler in der Syntax zu finden.
    Die Idee und der Code dazu sind von peethebee
    und seiner Logging UDF

    _MSSQL_Errorhandler erstellt einen Handler für evtl. auftretende Objektfehler, damit das Script weiterlaufen kann.

    Das Logging ist derzeit nicht in allen Funktionen eingebaut, wird aber noch nachgereicht

  • Servus,

    ich bekomme iwie keine Verbindung zum DB-Server hin:

    [autoit]


    $sqlCon = _MSSQL_Con("ServerIP\Instanz", "user", "passwort", "datenbank")

    [/autoit]

    liegt es vllt an dem, das ich hier "ServerIP\Instanz" verwende? ?(