MSSQL Connection

Hallistorm1989 · June 14, 2013

hello guys, i tried to find a MSSQL connection script ...

but i don't get what to put in all of these scripts, anyone can help me out with that ?

Hallistorm1989 · June 14, 2013

would be great if anyone is able to help me

JLogan3o13 · June 14, 2013

Hallistorm1989, please do not bump your post without waiting at least 24 hours. This is not a 24 hour support forum, the person who can best assist you may not be online right now

michaelslamet · June 14, 2013

Hi Hallistorm1989,

Please post your code.

And which MySQL UDF do you use?

Zedna · June 14, 2013

As was said, post what you have tried and also search this forum.

There are many examples for using SQL in AutoIt and also some UDFs in Examples section simplifiing the whole thing.

Hallistorm1989 · June 14, 2013

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

i tried that but it don't work for some reason ...

and i try to connect to MSSQL, not mysql

Hallistorm1989 · June 19, 2013

#push

Hallistorm1989 · June 20, 2013

#push

jdelaney · June 20, 2013

Do you have the driver installed? Do you have the connection string? Pleanty of examples out there, do a "sql" search

ZombieKillz · June 20, 2013

Hallistorm,

Post your code, blocking out all passwords and such that you don't want us to see. This code works! I'm using it successfully on a MSSQL Server 2008 R2.

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

i tried that but it don't work for some reason ...

and i try to connect to MSSQL, not mysql

Hallistorm1989 · June 20, 2013

i found something here

The MSSQL.au3 i use is the one out of the link...

i will post it here too :

; #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 <LuBu@veytal.com>
;
;===============================================================================

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

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

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

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

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

And the MSSQL Script i tried to create is this :

#include <MSSQL.au3>
#include <Array.au3>
$oMyError = ObjEvent("AutoIt.Error", "MyErrFunc")
$sqlCon = _MSSQL_Con("IP", "UserName", "PW", "Database")

_MSSQL_Query($sqlCon,"SELECT * FROM Table WHERE Name='hihi'")

_MSSQL_End($sqlCon)

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

WITH THE ONE I SENT BEFORE

I get this Error ...

C:UsersJulianDesktopScriptAutoIt v3 Script (neu).au3(9,25) : WARNING: $SQL_ERROR: possibly used before declaration.
If $OADODB = $SQL_ERROR Then
~~~~~~~~~~~~~~~~~~~~~~~~^
C:UsersJulianDesktopScriptAutoIt v3 Script (neu).au3(15,16) : ERROR: syntax error
Local $fullSQL If
~~~~~~~~~~~~~~~^
C:UsersJulianDesktopScriptAutoIt v3 Script (neu).au3(9,25) : ERROR: $SQL_ERROR: undeclared global variable.
If $OADODB = $SQL_ERROR Then
~~~~~~~~~~~~~~~~~~~~~~~~^
C:UsersJulianDesktopScriptAutoIt v3 Script (neu).au3(7,27) : ERROR: _SQL_RegisterErrorHandler(): undefined function.
_SQL_RegisterErrorHandler()
~~~~~~~~~~~~~~~~~~~~~~~~~~^
C:UsersJulianDesktopScriptAutoIt v3 Script (neu).au3(8,24) : ERROR: _SQL_Startup(): undefined function.
$OADODB = _SQL_Startup()
~~~~~~~~~~~~~~~~~~~~~~~^
C:UsersJulianDesktopScriptAutoIt v3 Script (neu).au3(9,78) : ERROR: _SQL_GetErrMsg(): undefined function.
If $OADODB = $SQL_ERROR Then MsgBox(0 + 16 + 262144, "Error", _SQL_GetErrMsg()
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
C:UsersJulianDesktopScriptAutoIt v3 Script (neu).au3(10,84) : ERROR: _sql_Connect(): undefined function.
If _sql_Connect(-1, $ServerAddress, $DatabaseName, $ServerUserName, $ServerPassword)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
C:UsersJulianDesktopScriptAutoIt v3 Script (neu).au3(12,12) : ERROR: _SQL_Close(): undefined function.
_SQL_Close()
~~~~~~~~~~~^
C:UsersJulianDesktopScriptAutoIt v3 Script (neu).au3 - 7 error(s), 1 warning(s)
!>18:52:28 AU3Check ended. Press F4 to jump to next error.rc:2

Edited June 20, 2013 by Hallistorm1989

ZombieKillz · June 20, 2013

Hallistorm,

I'm not sure about that UDF. Please have a look at this one -> '?do=embed' frameborder='0' data-embedContent>>

Then check out this post () by llewxam explaining how to put it together. At least that's how I got mine working.

ZombieKillz · June 20, 2013

Those seem to be errors from the UDF, MSSQL.au3. Check out the other I sent and let us know how you get along with that one.

ZombieKillz · June 20, 2013

#include <_sql.au3>
 
Local $ServerAddress = "INSERT YOUR SQL SERVER IP HERE"
Local $ServerUserName = "INSERT YOUR USERNAME HERE"
Local $ServerPassword = "INSERT YOUR PASSWORD HERE"
Local $DatabaseName = "INSERT YOUR DATABASE NAME HERE"
 
_SQL_RegisterErrorHandler();register the error handler to prevent hard crash on COM error
 
$OADODB = _SQL_Startup()
 
If $OADODB = $SQL_ERROR Then MsgBox(0 + 16 + 262144, "Error", _SQL_GetErrMsg())
If _sql_Connect(-1, $ServerAddress, $DatabaseName, $ServerUserName, $ServerPassword) = $SQL_ERROR Then
MsgBox(0 + 16 + 262144, "Error 1", _SQL_GetErrMsg())
_SQL_Close()
Exit
EndIf
 
Local $fullSQL
 
If _Sql_GetTableAsString(-1, "SELECT * FROM Table WHERE Name='hihi';", $fullSQL) = $SQL_OK Then
Else
MsgBox(0 + 16 + 262144, "SQL Error", _SQL_GetErrMsg())
EndIf

Edited June 20, 2013 by ZombieKillz

Hallistorm1989 · July 1, 2013

###############################
err.description is:    [Microsoft][ODBC SQL Server Driver][sql Server]Cannot open database "Database" requested by the login. The login failed.
err.windescription:   Unbekannter Fehler

err.number is:    80020009
err.lastdllerror is:    0
err.scriptline is:    220
err.source is:    Microsoft OLE DB Provider for ODBC Drivers
err.helpfile is:
err.helpcontext is:    0###############################
###############################
err.description is:    Der Vorgang ist für ein geschlossenes Objekt nicht zugelassen.
err.windescription:
err.number is:    80020009
err.lastdllerror is:    0
err.scriptline is:    373
err.source is:    ADODB.Connection
err.helpfile is:    C:WindowsHELPADO270.CHM

Hallistorm1989 · July 2, 2013

#PUSH

ZombieKillz · July 11, 2013

Hallistorm,

See this (post #14)...

Or, if you really really really want to use that UDF (MSSQL.au3), then it seems as though your error is the database name. In Microsoft SQL Server Management Studio, connect, then expand Databases, then enter your database name exactly as you see it in that list. If the SQL DB is on your local machine and is SQLEXPRESS, then use your computer nameSQLEXPRESS or whatever you called your server name during installation. "IP" --> "COMPUTERNAMESQLExpress"

Also, make sure the username and password you're using does actually have access to that database.

$sqlCon = _MSSQL_Con("0.0.0.0", "dbusername", "dbpassword", "Database Name Here")

MSSQL Connection

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Create an account or sign in to comment

Create an account

Sign in

Recently Browsing 0 members