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
#include-once
#include <Date.au3>
#include <File.au3>
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
; Default
Global $log_level = $LOG_FIX
Global $log_target = $LOG_TARGET_CONSOLE
Global $log_filename = @ScriptDir & "\" & @ScriptName & ".log"
Global $log_enable_timing = True
; #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
; ===============================================================================================================================
;===============================================================================
;
; 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]>
;
;===============================================================================
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
;===============================================================================
;
; 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]>
;
;===============================================================================
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
;===============================================================================
;
; 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
; #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
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
; #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
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, & @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, & @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, & @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
au3.user.calltips.api Code:
Spoiler anzeigen
_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
#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")
[/autoit] [autoit][/autoit] [autoit]$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)
[/autoit] [autoit][/autoit] [autoit]Func MyErrFunc()
Local $HexNumber
$HexNumber = Hex($oMyError.number,
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
MfG TheLuBu