TheLuBu

UDF - MSSQL.au3

18 posts in this topic

#1 ·  Posted (edited)

Hi there,

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

Maybe you can use it.

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

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

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


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

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

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

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

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

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

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

TheLuBu

Edited by TheLuBu

Share this post


Link to post
Share on other sites



Example Script

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


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

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

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

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

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

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

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

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

_MSSQL_End($sqlCon)

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

Share this post


Link to post
Share on other sites

Hi TheLuBu,

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

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

:)


Regards,Hannes[spoiler]If you can't convince them, confuse them![/spoiler]

Share this post


Link to post
Share on other sites

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

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

TheLuBu

Share this post


Link to post
Share on other sites

@TheLuBu in case you stop by back these forums,

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

Share this post


Link to post
Share on other sites

Hi there,

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

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

I get this:

Posted Image

IF i tray do this with array:

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


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

Then i get nothing, no data and no error.

When i select all columns :

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

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

Share this post


Link to post
Share on other sites

I got an error when running script after upgrade to 3.3.12.0

MSSQL.au3" (324) : ==> Variable must be of type "Object".:
If Not $quer.EOF Then
If Not $quer^ ERROR

Share this post


Link to post
Share on other sites

Hey mintea,

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

Thanks

Share this post


Link to post
Share on other sites

Hey mintea,

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

Thanks

 

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

Share this post


Link to post
Share on other sites

#10 ·  Posted (edited)

Thanks.

I ended up using this array sort UDF from Melba23.

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

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

Edited by jazzyjeff

Share this post


Link to post
Share on other sites

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


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

#13 ·  Posted (edited)

Hey there,

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

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

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

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

Here is a sample of my code.

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

No problem can withstand the assault of sustained thinking.Voltaire

_Array2HTMLTable()_IEClassNameGetCollection()_IEquerySelectorAll()

Share this post


Link to post
Share on other sites

Hi there,

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

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

array.jpg

IF i tray do this with array:

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


$getrecord = _MSSQL_GetRecord($sqlCon,"Kontrahent kon join Sklep s on (s.SklepId = kon.KontrId)",$sColumn,"where 1 = 1","kon.kontrid desc")
_arrayDisplay ($getrecord)
Then i get nothing, no data and no error.

When i select all columns :

$getrecord = _MSSQL_GetRecord($sqlCon,"Kontrahent kon join Sklep s on (s.SklepId = kon.KontrId)","*","where 1 = 1","kon.kontrid desc")
_arrayDisplay ($getrecord)
That its ok, but i want to select columns. Sorry for my english.

 

 

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

Share this post


Link to post
Share on other sites

Has anyone tested this with Microsoft Azure SQL?  

Share this post


Link to post
Share on other sites

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

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

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

Then this line is wrong in _MSSQL_GetRecord. 

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

 

Hi there,

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

 

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

I get this:

Posted Image

IF i tray do this with array:

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


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

Then i get nothing, no data and no error.

When i select all columns :

 

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

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

Share this post


Link to post
Share on other sites

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

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

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

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

Share this post


Link to post
Share on other sites

#18 ·  Posted

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

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

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

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

bye

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now