1. Dashboard
  2. Mitglieder
    1. Letzte Aktivitäten
    2. Benutzer online
    3. Team
    4. Mitgliedersuche
  3. Forenregeln
  4. Forum
    1. Unerledigte Themen
  • Anmelden
  • Registrieren
  • Suche
Alles
  • Alles
  • Artikel
  • Seiten
  • Forum
  • Erweiterte Suche
  1. AutoIt.de - Das deutschsprachige Forum.
  2. Mitglieder
  3. gmmg

Beiträge von gmmg

  • MSSQL 2008 Abfrage = keine Ergebnisse, bzw. kein Update

    • gmmg
    • 14. Mai 2014 um 14:24

    hallo,

    habe eben mal das folgende script getestet! scheint auch zu funktionieren ..
    1. im mssql manager meinen windows user eingerichtet und die rechte vergeben
    2. im windows7 x64 -> Systemsteuerung -> Verwaltung -> ODBC die Verbindung eingerichtet und getestet ...

    musste im script den User und das Kennwort wegen der Windows Authentifizierung leer lassen ...

    _sql.au3

    Spoiler anzeigen
    [autoit]


    #include-once

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

    ; #INDEX# ========================================================================
    ; Title .........: _SQL.au3
    ; AutoIt Version : 3.2
    ; Language ......: English
    ; Description ...: Some SQL stuff to use with an MSDE database
    ; Author ........: Chris Lambert
    ; ================================================================================

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

    ; #VARIABLES# ====================================================================
    Global $SQL_LastConnection ; enables the use of -1 to access the last opened connection
    Global $SQLErr ; Plain text error message holder
    Global $MSSQLObjErr ; For COM error handler
    Global Const $SQL_OK = 0 ; Successful result
    Global Const $SQL_ERROR = 1 ; SQL error
    Global Const $SQLUDFVersion = "2.0.3"
    ; ==============================================================================

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

    ; #FUNCTION# ===================================================================
    ; Name ..........: _SQL_RegisterErrorHandler
    ; Description ...: Register COM error handler
    ; Syntax.........: _SQL_RegisterErrorHandler($Func = "_SQL_ErrFunc")
    ; Parameters ....: $Func - String variable with the name of a user-defined COM error handler defaults to the _SQL_ErrFunc()
    ; Return values .: On Success - Returns $SQL_OK
    ; On Failure - Returns $SQL_ERROR and $SQLErr is set.
    ; .Use _SQL_GetErrMsg() to get text error information
    ; Author ........: Chris Lambert
    ; Modified ......:
    ; Remarks .......: AutoIt3 V3.2 or higher
    ; Related .......:
    ; Link ..........;
    ; Example .......; no
    ; ==============================================================================
    Func _SQL_RegisterErrorHandler($Func = "_SQL_ErrFunc")
    $SQLErr = ""
    If ObjEvent("AutoIt.Error") = "" Then
    $MSSQLObjErr = ObjEvent("AutoIt.Error", $Func)
    Return SetError($SQL_OK, 0, $SQL_OK)
    Else
    $SQLErr = "An Error Handler is already registered"
    Return SetError($SQL_ERROR, 0, $SQL_ERROR)
    EndIf

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

    EndFunc ;==>_SQL_RegisterErrorHandler

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

    Func _ADOVersion($ADODBHandle = -1)

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

    If $ADODBHandle = -1 Then $ADODBHandle = $SQL_LastConnection

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

    If Not IsObj($ADODBHandle) Then
    $SQLErr = "Invalid ADODB.Connection object, use _SQL_Startup()"
    Return SetError($SQL_ERROR, 0, $SQL_ERROR)
    EndIf

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

    Return SetError($SQL_OK,0,$ADODBHandle.Version)

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

    EndFunc

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

    ; #FUNCTION# ===================================================================
    ; Name ..........: _SQL_UnRegisterErrorHandler()
    ; Description ...: Disable a registered error handler
    ; Syntax.........: _SQL_UnRegisterErrorHandler()
    ; Parameters ....: None
    ; Return values .: On Success - Returns $SQL_OK
    ; On Failure - None
    ; Author ........: Chris Lambert
    ; Modified ......:
    ; Remarks .......: AutoIt3 V3.2 or higher
    ; Related .......:
    ; Link ..........;
    ; Example .......; no
    ; ==============================================================================
    Func _SQL_UnRegisterErrorHandler()

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

    $SQLErr = ""
    $MSSQLObjErr = ""
    Return SetError($SQL_OK, 0, $SQL_OK)

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

    EndFunc ;==>_SQL_UnRegisterErrorHandler

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

    ; #FUNCTION# ===================================================================
    ; Name ..........: _SQL_Startup
    ; Description ...: Creates ADODB.Connection object
    ; Syntax.........: _SQL_Startup()
    ; Parameters ....: None
    ; Return values .: On Success - Returns Object handle
    ; On Failure - Returns $SQL_ERROR and $SQLErr is set.
    ; .Use _SQL_GetErrMsg() to get text error information
    ; Author ........: Chris Lambert
    ; Modified ......:
    ; Remarks .......:
    ; Related .......:
    ; Link ..........;
    ; Example .......; no
    ; ==============================================================================
    Func _SQL_Startup()

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

    $SQLErr = ""
    Local $adCN = ObjCreate("ADODB.Connection")
    If IsObj($adCN) Then
    $SQL_LastConnection = $adCN
    Return SetError($SQL_OK, 0, $adCN)
    Else
    $SQLErr = "Failed to Create ADODB.Connection object"
    Return SetError($SQL_ERROR, 0, $SQL_ERROR)
    EndIf

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

    EndFunc ;==>_SQL_Startup

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

    ; #FUNCTION# ===================================================================
    ; Name ..........: _SQL_ConnectionTimeout
    ; Description ...: Sets and retrieves SQL ConnectionTimeout
    ; Syntax.........: _SQL_ConnectionTimeout($ADODBHandle,$iTimeout)
    ; Parameters ....: $ADODBHandle - ADODB.Connection handle.
    ; $iTimeout - The timeout period to set if left blank the current value will be retrieved
    ; Return values .: On Success - Returns Connection timeout period
    ; On Failure - Returns $SQL_ERROR and $SQLErr is set.
    ; .Use _SQL_GetErrMsg() to get text error information
    ; Author ........: Chris Lambert
    ; Modified ......:
    ; Remarks .......:
    ; Related .......:
    ; Link ..........;
    ; Example .......; no
    ; ==============================================================================
    Func _SQL_ConnectionTimeout($ADODBHandle = -1,$iTimeOut = "")

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

    $SQLErr = ""
    If $ADODBHandle = -1 Then $ADODBHandle = $SQL_LastConnection

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

    If Not IsObj($ADODBHandle) Then
    $SQLErr = "Invalid ADODB.Connection object, use _SQL_Startup()"
    Return SetError($SQL_ERROR, 0, $SQL_ERROR)
    EndIf

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

    If $iTimeOut = "" then Return SetError($SQL_OK, 0, $ADODBHandle.ConnectionTimeout)

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

    If NOT isInt($iTimeOut) then
    $SQLErr = "ConnectionTimeOut value must be an integer"
    Return SetError($SQL_ERROR, 0, $SQL_ERROR)
    EndIf

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

    $ADODBHandle.ConnectionTimeout = $iTimeOut
    Return SetError($SQL_OK, 0, $ADODBHandle.ConnectionTimeout)

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

    EndFunc ;==>_SQL_Startup

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

    ; #FUNCTION# ===================================================================
    ; Name ..........: _SQL_CommandTimeout
    ; Description ...: Sets and retrieves SQL CommandTimeout
    ; Syntax.........: _SQL_CommandTimeout($ADODBHandle,$iTimeout)
    ; Parameters ....: $ADODBHandle - ADODB.Connection handle.
    ; $iTimeout - The timeout period to set if left blank the current value will be retrieved
    ; Return values .: On Success - Returns SQL Command timeout period
    ; On Failure - Returns $SQL_ERROR and $SQLErr is set.
    ; .Use _SQL_GetErrMsg() to get text error information
    ; Author ........: Chris Lambert
    ; Modified ......:
    ; Remarks .......:
    ; Related .......:
    ; Link ..........;
    ; Example .......; no
    ; ==============================================================================
    Func _SQL_CommandTimeout($ADODBHandle = -1,$iTimeOut = "")

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

    $SQLErr = ""
    If $ADODBHandle = -1 Then $ADODBHandle = $SQL_LastConnection

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

    If Not IsObj($ADODBHandle) Then
    $SQLErr = "Invalid ADODB.Connection object, use _SQL_Startup()"
    Return SetError($SQL_ERROR, 0, $SQL_ERROR)
    EndIf

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

    If $iTimeOut = "" then Return SetError($SQL_OK, 0, $ADODBHandle.CommandTimeout)

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

    If NOT isInt($iTimeOut) then
    $SQLErr = "CommandTimeOut value must be an integer"
    Return SetError($SQL_ERROR, 0, $SQL_ERROR)
    EndIf

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

    $ADODBHandle.CommandTimeout = $iTimeOut
    Return SetError($SQL_OK, 0, $ADODBHandle.CommandTimeout)

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

    EndFunc ;==>_SQL_Startup

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

    ; #FUNCTION# ===================================================================
    ; Name ..........: _SQL_Connect
    ; Description ...: Starts a Database Connection
    ; Syntax.........: _SQL_Connect($ADODBHandle,$server, $db, $username, $password)
    ; Parameters ....: $ADODBHandle - ADODB.Connection handle.
    ; $server - The server to connect to.
    ; $db - The database to open.
    ; $username - username for database access.
    ; $password - password for database user.
    ; Return values .: On Success - Returns $SQL_OK
    ; On Failure - Returns $SQL_ERROR and $SQLErr is set.
    ; .Use _SQL_GetErrMsg() to get text error information
    ; Author ........: Chris Lambert
    ; Modified ......:
    ; Remarks .......:
    ; Related .......:
    ; Link ..........;
    ; Example .......; no
    ; ==============================================================================
    Func _SQL_Connect($ADODBHandle, $server, $db, $username, $password, $SQLAuth = True)

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

    $SQLErr = ""
    If $ADODBHandle = -1 Then $ADODBHandle = $SQL_LastConnection

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

    If Not IsObj($ADODBHandle) Then
    $SQLErr = "Invalid ADODB.Connection object, use _SQL_Startup()"
    Return SetError($SQL_ERROR, 0, $SQL_ERROR)
    EndIf

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

    If $SQLAuth = True then
    $ADODBHandle.Open("DRIVER={SQL Server};SERVER=" & $server & ";DATABASE=" & $db & ";uid=" & $username & ";pwd=" & $password & ";") ;<==Connect with required credentials
    Else
    $ADODBHandle.Properties("Integrated Security").Value = "SSPI"
    $ADODBHandle.Properties("User ID") = $username
    $ADODBHandle.Properties("Password") = $password
    $ADODBHandle.Open("DRIVER={SQL Server};SERVER=" & $server & ";DATABASE=" & $db )
    EndIf

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

    If Not @error Then
    Return SetError($SQL_OK, 0, $SQL_OK)
    Else
    $SQLErr = "Connection Error"
    Return SetError($SQL_ERROR, 0, $SQL_ERROR)
    EndIf
    EndFunc ;==>_SQL_Connect

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

    ; #FUNCTION# ===================================================================
    ; Name ..........: _SQL_JetConnect
    ; Description ...: Starts a Database Connection to a Jet Database
    ; Syntax.........: _SQL_JetConnect($ADODBHandle,$sFilePath1)
    ; Parameters ....: $ADODBHandle - ADODB.Connection handle
    ; $sFilePath1 - Path to Jet Database file
    ; Return values .: On Success - Returns $SQL_OK
    ; On Failure - Returns $SQL_ERROR and $SQLErr is set.
    ; .Use _SQL_GetErrMsg() to get text error information
    ; Author ........: Chris Lambert
    ; Modified ......:
    ; Remarks .......:
    ; Related .......:
    ; Link ..........;
    ; Example .......; no
    ; ==============================================================================
    Func _SQL_JetConnect($ADODBHandle, $sFilePath1)

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

    $SQLErr = ""
    If $ADODBHandle = -1 Then $ADODBHandle = $SQL_LastConnection

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

    If Not IsObj($ADODBHandle) Then
    $SQLErr = "Invalid ADODB.Connection object, use _SQL_Startup()"
    Return SetError($SQL_ERROR, 0, $SQL_ERROR)
    EndIf

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

    $ADODBHandle.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & $sFilePath1 & ";")
    If @error Then
    $SQLErr = "Connection Error"
    Return SetError($SQL_ERROR, 0, $SQL_ERROR)
    EndIf

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

    Return SetError($SQL_OK, 0, $SQL_OK)
    EndFunc ;==>_SQL_JetConnect

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

    ; #FUNCTION# ===================================================================
    ; Name ..........: _SQL_AccessConnect
    ; Description ...: Starts a Database Connection to an Access Database
    ; Syntax ........: _SQL_AccessConnect($ADODBHandle,$sFilePath1)
    ; Parameters ....: $ADODBHandle - ADODB.Connection handle - Optional
    ; $sFilePath1 - Path to an Access Database file
    ; Return values .: Success - Returns $SQL_OK
    ; On Failure - Returns $SQL_ERROR and $SQLErr is set.
    ; .Use _SQL_GetErrMsg() to get text error information
    ; Author ........: Chris Lambert
    ; Modified.......:
    ; Remarks .......:
    ; Related .......:
    ; Link ..........;
    ; Example .......: no
    ; ================================================================================
    Func _SQL_AccessConnect($ADODBHandle = -1, $sFilePath1 = "")
    $SQLErr = ""
    If $ADODBHandle = -1 Then $ADODBHandle = $SQL_LastConnection

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

    If Not IsObj($ADODBHandle) Then
    $SQLErr = "Invalid ADODB.Connection object, use _SQL_Startup()"
    Return SetError($SQL_ERROR, 0, $SQL_ERROR)
    EndIf

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

    $ADODBHandle.Open("Driver={Microsoft Access Driver (*.mdb)};Dbq=" & $sFilePath1 & ";")
    If @error Then
    $SQLErr = "Connection Error"
    Return SetError($SQL_ERROR, 0, $SQL_ERROR)
    EndIf

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

    Return SetError($SQL_OK, 0, $SQL_OK)

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

    EndFunc ;==>_SQL_AccessConnect

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

    ; #FUNCTION# ===================================================================
    ; Name ..........: _SQL_ExcellConnect
    ; Description ...: Starts a Database Connection to an Excel WorkBook
    ; Syntax ........: _SQL_AccessConnect($ADODBHandle,$sFilePath1)
    ; Parameters ....: $ADODBHandle - ADODB.Connection handle - Optional
    ; $sFilePath1 - Path to an Excel file
    ; Return values .: Success - Returns $SQL_OK
    ; On Failure - Returns $SQL_ERROR and $SQLErr is set.
    ; .Use _SQL_GetErrMsg() to get text error information
    ; Author ........: Chris Lambert
    ; Modified.......: CarlH
    ; Remarks .......:
    ; Related .......:
    ; Link ..........;
    ; Example .......: no
    ; ================================================================================
    Func _SQL_ExcelConnect($ADODBHandle = -1, $sFilePath1 = "", $HDR = "Yes")
    $SQLErr = ""
    If $ADODBHandle = -1 Then $ADODBHandle = $SQL_LastConnection

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

    If Not IsObj($ADODBHandle) Then
    $SQLErr = "Invalid ADODB.Connection object, use _SQL_Startup()"
    Return SetError($SQL_ERROR, 0, $SQL_ERROR)
    EndIf

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

    $ADODBHandle.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source="& $sFilePath1 & ";" & _
    "Extended Properties='Excel 8.0;HDR=" & $HDR & "';")
    If @error Then
    $SQLErr = "Connection Error"
    Return SetError($SQL_ERROR, 0, $SQL_ERROR)
    EndIf

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

    Return SetError($SQL_OK, 0, $SQL_OK)

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

    EndFunc ;==>_SQL_AccessConnect

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

    ; #FUNCTION# ===================================================================
    ; Name ..........: _SQL_Close
    ; Description ...: Closes an open ADODB.Connection
    ; Syntax.........: _SQL_Close ($ADODBHandle = -1)
    ; Parameters ....: $ADODBHandle - Optional Database Handle
    ; Return values .: On Success - Returns $SQL_OK
    ; On Failure - Returns $SQL_ERROR and $SQLErr is set.
    ; .Use _SQL_GetErrMsg() to get text error information
    ; Author ........: Chris Lambert
    ; Modified ......:
    ; Remarks .......:
    ; Related .......:
    ; Link ..........;
    ; Example .......; no
    ; ==============================================================================
    Func _SQL_Close($ADODBHandle = -1)

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

    $SQLErr = ""

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

    If $ADODBHandle = -1 Then $ADODBHandle = $SQL_LastConnection

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

    If Not IsObj($ADODBHandle) Then
    $SQLErr = "Invalid ADODB.Connection object, use _SQL_Startup()"
    Return SetError($SQL_ERROR, 0, $SQL_ERROR)
    EndIf

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

    $ADODBHandle.Close
    If $ADODBHandle = $SQL_LastConnection Then $SQL_LastConnection = ""

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

    Return SetError($SQL_OK, 0, $SQL_OK)

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

    EndFunc ;==>_SQL_Close

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

    ; #FUNCTION# ===================================================================
    ; Name ..........: _SQL_Execute()
    ; Description ...: Executes an SQL Query
    ; Syntax.........: _SQL_Execute([ $hConHandle = -1[,$vQuery = "" ]])
    ; Parameters ....: $hConHandle - An Open Database, Use -1 To use Last Opened Database
    ; $vQuery - SQL Statement to be executed
    ; Return values .: On Success - Returns a query handle
    ; On Failure - Returns $SQL_ERROR and $SQLErr is set.
    ; .Use _SQL_GetErrMsg() to get text error information
    ; Author ........: Chris Lambert
    ; Modified ......:
    ; Remarks .......:
    ; Related .......:
    ; Link ..........;
    ; Example .......; no
    ; ==============================================================================
    Func _SQL_Execute($hConHandle = -1, $vQuery = "")

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

    $SQLErr = ""
    Local $hQuery

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

    If $hConHandle = -1 Then $hConHandle = $SQL_LastConnection
    $hQuery = $hConHandle.Execute($vQuery)

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

    If @error Then
    Return SetError($SQL_ERROR, 0, $SQL_ERROR)
    Else
    Return SetError($SQL_OK, 0, $hQuery)
    EndIf
    EndFunc ;==>_SQL_Execute

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

    ; #FUNCTION# ===================================================================
    ; Name ..........: _SQL_GetErrMsg
    ; Description ...: Get SQL error as text
    ; Syntax.........: _SQL_GetErrMsg()
    ; Parameters ....: None
    ; Return values .: On Success - Returns the text string from $SQLErr
    ; On Failure - None
    ; Author ........: Chris Lambert
    ; Modified ......: Stephen Podhajecki (eltorro)
    ; Remarks .......:
    ; Related .......:
    ; Link ..........;
    ; Example .......; no
    ; ==============================================================================
    Func _SQL_GetErrMsg()
    ;added temp var to return and clear $SQLErr
    ;returns the current errmsg and clears it.
    Local $SQLErr_TMP = $SQLErr
    $SQLErr = ""
    Return SetError($SQL_OK, 0, $SQLErr_TMP)
    EndFunc ;==>_SQL_GetErrMsg

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

    ; #FUNCTION# ===================================================================
    ; Name ..........: _SQL_GetTable2d()
    ; Description ...: Passes Out a 2Dimensional Array Containing Tablenames and Data of Executed Query
    ; Syntax.........: _SQL_GetTable2D($hConHandle, $vQuery, ByRef $aResult, ByRef $iRows, ByRef $iColumns)
    ; Parameters ....: $hConHandle - An Open Database, Use -1 To use Last Opened Database
    ; $vQuery - SQL Statement to be executed
    ; $aResult - Passes out the Result
    ; $iRows - Passes out the amount of 'data' Rows
    ; $iColumns - Passes out the amount of Columns
    ; Return values .: On Success - Returns $SQL_OK
    ; On Failure - Returns $SQL_ERROR and $SQLErr is set.
    ; .Use _SQL_GetErrMsg() to get text error information
    ; Author ........: Chris Lambert
    ; Modified ......: Stephen Podhajecki (eltorro)
    ; Remarks .......:
    ; Related .......:
    ; Link ..........;
    ; Example .......; no
    ; ==============================================================================
    Func _SQL_GetTable2D($hConHandle, $vQuery, ByRef $aResult, ByRef $iRows, ByRef $iColumns)

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

    $SQLErr = ""
    Local $i, $x, $y, $objquery

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

    $iRows = 0
    $iColumns = 0
    ;sp mod removed handle check here use function.
    $objquery = _SQL_Execute($hConHandle, $vQuery)
    ;end mod
    If @error Then
    $SQLErr = "Query Error"
    Return SetError($SQL_ERROR, 0, $SQL_ERROR)
    EndIf

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

    If $objquery.eof Then
    $SQLErr = "Query has no data"
    $objquery = 0 ;sp mod
    Return SetError($SQL_ERROR, 0, $SQL_ERROR)
    EndIf

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

    With $objquery

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

    $aResult = .GetRows()

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

    If IsArray($aResult) Then
    $iColumns = UBound($aResult, 2)
    $iRows = UBound($aResult)

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

    ReDim $aResult[$iRows + 1][$iColumns];Adjust the array to fit the column names and move all data down 1 row

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

    For $x = $iRows To 1 Step -1
    For $y = 0 To $iColumns - 1
    $aResult[$x][$y] = $aResult[$x - 1][$y]
    Next
    Next
    ;Add the coloumn names
    For $i = 0 To $iColumns - 1 ;get the column names and put into 0 array element
    $aResult[0][$i] = .Fields($i).Name
    Next

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

    Else
    $SQLErr = "Unable to retreive data"
    $objquery = 0 ;sp mod
    Return SetError($SQL_ERROR, 0, $SQL_ERROR)
    EndIf;IsArray()

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

    EndWith
    $objquery = 0
    Return SetError($SQL_OK, 0, $SQL_OK)
    EndFunc ;==>_SQL_GetTable2D

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

    ; #FUNCTION# ===================================================================
    ; Name ..........: _SQL_FetchNames()
    ; Description ...: Read out the Tablenames of a _SQL_Query() based query
    ; Syntax.........: _SQL_FetchNames($hQuery,ByRef $aNames)
    ; Parameters ....: $hQuery - Query Handle Generated by _SQL_Execute()
    ; $aNames - variable to store the Table Names
    ; Return values .: On Success - Returns $SQL_OK
    ; On Failure - Returns $SQL_ERROR and $SQLErr is set.
    ; .Use _SQL_GetErrMsg() to get text error information
    ; Author ........: Chris Lambert
    ; Modified ......:
    ; Remarks .......:
    ; Related .......:
    ; Link ..........;
    ; Example .......; no
    ; ==============================================================================
    Func _SQL_FetchNames($hQuery, ByRef $aNames)

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

    Local $i, $SQL_Delim = "¬&~"
    Local $iDelLen = StringLen($SQL_Delim)
    $SQLErr = ""

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

    If Not IsObj($hQuery) Then
    $SQLErr = "Invalid Query Handle"
    Return SetError($SQL_ERROR, 0, $SQL_ERROR)
    EndIf

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

    $aNames = ""
    For $i = 0 To $hQuery.Fields.Count - 1 ;get the column names and put into 0 array element
    $aNames &= $hQuery.Fields($i).Name & $SQL_Delim
    Next

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

    If StringRight($aNames, $iDelLen) = $SQL_Delim Then $aNames = StringTrimRight($aNames, $iDelLen)

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

    $aNames = StringSplit($aNames, $SQL_Delim, 3)

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

    Return SetError($SQL_OK, 0, $SQL_OK)

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

    EndFunc ;==>_SQL_FetchNames

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

    ; #FUNCTION# ===================================================================
    ; Name ..........: _SQL_GetTable()
    ; Description ...: Passes Out a 1Dimensional Array Containing Tablenames and Data of Executed Query
    ; Syntax.........: _SQL_GetTable($hConHandle, $vQuery, ByRef $aData, ByRef $iRows, ByRef $iColumns)
    ; Parameters ....: $hConHandle - An Open Database, Use -1 To use Last Opened Database
    ; $vQuery - SQL Statement to be executed
    ; $aResult - Passes out the Result
    ; $iRows - Passes out the amount of 'data' Rows
    ; $iColumns - Passes out the amount of Columns
    ; Return values .: On Success - Returns $SQL_OK
    ; On Failure - Returns $SQL_ERROR and $SQLErr is set.
    ; .Use _SQL_GetErrMsg() to get text error information
    ; Author ........: Chris Lambert
    ; Modified ......: Stephen Podhajecki (eltorro)
    ; Remarks .......:
    ; Related .......:
    ; Link ..........;
    ; Example .......; no
    ; ==============================================================================
    Func _SQL_GetTable($hConHandle, $vQuery, ByRef $aData, ByRef $iRows, ByRef $iColumns)

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

    Local $i, $objquery, $aNames
    $SQLErr = ""
    $iRows = 0
    $iColumns = 0
    Dim $aData[1] = [0]

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

    $objquery = _SQL_Execute($hConHandle, $vQuery)

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

    If @error Then
    $SQLErr = "Query Error"
    Return SetError($SQL_ERROR, 0, $SQL_ERROR)
    EndIf

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

    If $objquery.eof Then
    $SQLErr = "Query has no data"
    $objquery = 0 ;sp mod
    Return SetError($SQL_ERROR, 0, $SQL_ERROR)
    EndIf

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

    _SQL_FetchNames($objquery, $aNames)

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

    $iColumns = UBound($aNames)

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

    ReDim $aData[$iColumns + 1]

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

    $aData[0] = $iColumns

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

    For $i = 0 To $iColumns - 1
    $aData[$i + 1] = $aNames[$i]
    Next

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

    $aNames = 0

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

    While Not $objquery.eof
    $iRows += 1
    For $i = 0 To $objquery.Fields.Count - 1
    ReDim $aData[$aData[0] + 2]
    $aData[0] += 1
    $aData[$aData[0]] = $objquery.Fields($i).Value
    Next
    $objquery.MoveNext; Move to next row
    WEnd
    $objquery = 0 ;sp mod
    Return SetError($SQL_OK, 0, $SQL_OK)

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

    EndFunc ;==>_SQL_GetTable

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

    ; #FUNCTION# ===================================================================
    ; Name ..........: _SQL_FetchData()
    ; Description ...: Fetches 1 Row of Data from an _SQL_Execute() based query
    ; Syntax.........: _SQL_FetchData($hQuery,ByRef $aRow)
    ; Parameters ....: $hQuery - Queryhandle passed out by _SQL_Execute()
    ; $aRow - A 1 dimensional Array containing a Row of Data
    ; Return values .: On Success - Returns $SQL_OK
    ; On Failure - Returns $SQL_ERROR and $SQLErr is set.
    ; .Use _SQL_GetErrMsg() to get text error information
    ; Author ........: Chris Lambert
    ; Modified ......:
    ; Remarks .......:
    ; Related .......:
    ; Link ..........;
    ; Example .......; no
    ; ==============================================================================
    Func _SQL_FetchData($hQuery, ByRef $aRow)

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

    Local $i, $SQL_Delim = "¬&~"
    Local $iDelLen = StringLen($SQL_Delim)
    $SQLErr = ""

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

    If Not IsObj($hQuery) Then
    $SQLErr = "Invalid Query Handle"
    Return SetError($SQL_ERROR, 0, $SQL_ERROR)
    EndIf

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

    If $hQuery.EOF Then
    $SQLErr = "End of Data Stream"
    Return SetError($SQL_ERROR, 0, $SQL_ERROR)
    EndIf

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

    $aRow = ""
    For $i = 0 To $hQuery.Fields.Count - 1
    $aRow &= $hQuery.Fields($i).Value & $SQL_Delim
    Next

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

    If StringRight($aRow, $iDelLen) = $SQL_Delim Then $aRow = StringTrimRight($aRow, $iDelLen)
    $hQuery.MoveNext; Move to next row
    $aRow = StringSplit($aRow, $SQL_Delim, 3)
    Return SetError($SQL_OK, 0, $SQL_OK)

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

    EndFunc ;==>_SQL_FetchData

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

    ; #FUNCTION# ===================================================================
    ; Name ..........: _SQL_QuerySingleRow()
    ; Description ...: Read out the first Row of the Result from the Specified query
    ; Syntax.........: _SQL_QuerySingleRow($hConHandle, $sSQL, ByRef $aRow)
    ; Parameters ....: $hConHandle - An Open Database, Use -1 To use Last Opened Database.
    ; $sSQL - SQL Statement to be executed.
    ; $aRow - Array to hold return results.
    ; Return values .: On Success - Returns $SQL_OK
    ; On Failure - Returns $SQL_ERROR and $SQLErr is set.
    ; .Use _SQL_GetErrMsg() to get text error information
    ; Author ........: Chris Lambert
    ; Modified ......:
    ; Remarks .......: $SQLErr will already be set by _SQL_GetTable2D
    ; Related .......:
    ; Link ..........;
    ; Example .......; no
    ; ==============================================================================
    Func _SQL_QuerySingleRow($hConHandle, $sSQL, ByRef $aRow)

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

    Local $aResult, $iRows, $iColumns, $Ret, $i
    $aRow = ""
    Dim $aRow[1]
    $Ret = _SQL_GetTable2D($hConHandle, $sSQL, $aResult, $iRows, $iColumns)
    If $Ret = $SQL_ERROR Then SetError($SQL_ERROR, 0, $SQL_ERROR) ;$SQLErr will already be set by _SQL_GetTable2D

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

    If $Ret = $SQL_OK And UBound($aResult, 0) > 0 Then
    ReDim $aRow[UBound($aResult, 2)]
    For $i = 0 To UBound($aResult, 2) - 1
    $aRow[$i] = $aResult[1][$i]
    Next
    EndIf

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

    Return SetError($SQL_OK, 0, $SQL_OK)

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

    EndFunc ;==>_SQL_QuerySingleRow

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

    ; #FUNCTION# ===================================================================
    ; Name ..........: _SQL_GetTableAsString
    ; Description ...: Passes Out a string of results
    ; Syntax.........: _SQL_GetTableAsString( $hConHandle, $vQuery, ByRef $vStr[, $delim= "|"[, $ReturnColumnNames = 1]])
    ; Parameters ....: $hConHandle - An Open Database, Use -1 To use Last Opened Database
    ; $vQuery - SQL Statement to be executed
    ; $vStr - Passes out the Result
    ; $delim= "|" - The deliminator to use between columns
    ; $ReturnColumnNames - Use 1 to show column names and 0 without
    ; Return values .: On Success - Returns $SQL_OK
    ; On Failure - Returns $SQL_ERROR and $SQLErr is set.
    ; .Use _SQL_GetErrMsg() to get text error information
    ; Author ........: Chris Lambert
    ; Modified ......: Stephen Podhajecki (eltorro)
    ; Remarks .......:
    ; Related .......:
    ; Link ..........;
    ; Example .......; no
    ; ==============================================================================

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

    Func _SQL_GetTableAsString($hConHandle, $vQuery, ByRef $vStr, $delim = "|", $ReturnColumnNames = 1)

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

    $SQLErr = ""
    Local $i, $objquery

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

    $objquery = _SQL_Execute($hConHandle, $vQuery)

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

    If @error Then
    $SQLErr = "Query Error"
    Return SetError($SQL_ERROR, 0, $SQL_ERROR)
    EndIf

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

    If $objquery.eof Then
    $SQLErr = "Query has no data"
    $objquery = 0 ;sp mod
    Return SetError($SQL_ERROR, 0, $SQL_ERROR)
    EndIf

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

    With $objquery
    If $ReturnColumnNames Then
    For $i = 0 To .Fields.Count - 1 ;get the column names and put into 0 array element
    $vStr &= .Fields($i).Name & $delim
    Next
    If StringRight($vStr, 1) = $delim Then $vStr = StringTrimRight($vStr, 1)
    $vStr &= @CRLF
    EndIf

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

    While Not .EOF
    For $i = 0 To .Fields.Count - 1
    $vStr &= .Fields($i).Value & $delim
    Next
    If StringRight($vStr, 1) = $delim Then $vStr = StringTrimRight($vStr, 1)
    $vStr &= @CRLF
    .MoveNext; Move to next row
    WEnd
    EndWith
    $objquery = 0 ;sp mod
    Return SetError($SQL_OK, 0, $SQL_OK)
    EndFunc ;==>_SQL_GetTableAsString

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

    ; #FUNCTION# ===================================================================
    ; Name ..........: _SQL_GetTableName()
    ; Description ...: Get Table List Of Open Data Base
    ; Syntax.........: _SQL_GetTableName([ $hConHandle = -1[,$Type = "TABLE" ]])
    ; Parameters ....: $hConHandle - An Open Database, Use -1 To use Last Opened Database
    ; $Type - Table Type "TABLE" (Default), "VIEW", "SYSTEM TABLE", "ACCESS TABLE"
    ; $Type = "*" - Return All Tables in a Array2D $aTable[n][2] $aTable[n][0] = Table Name $aTable[n][1] = Table Type
    ;
    ; Return values .: On Success - Returns a 1D Array Of Table Names / 2D Array is $Type = "*"
    ; On Failure - Returns $SQL_ERROR and $SQLErr is set.
    ; .Use _SQL_GetErrMsg() to get text error information
    ; Author ........: Elias Assad Neto
    ; Modified ......: ChrisL
    ; Remarks .......:
    ; Related .......:
    ; Link ..........;
    ; Example .......; no
    ; ==============================================================================
    Func _SQL_GetTableName($hConHandle = -1, $Type = "TABLE")

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

    $SQLErr = ""
    If $hConHandle = -1 Then $hConHandle = $SQL_LastConnection

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

    Local $rs = $hConHandle.OpenSchema(20) ; adSchemaTables = 20

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

    If Not IsObj($rs) Then
    $SQLErr = "The Data Base is Not Open"
    Return SetError($SQL_ERROR, 0, $SQL_ERROR)
    EndIf

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

    Local $oField = $rs.Fields("TABLE_NAME")
    local $aTable

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

    If $Type = "*" Then ; All Table
    Do ;Check for a user table object
    If UBound($aTable) = 0 Then
    Dim $aTable[1][2]
    Else
    ReDim $aTable[UBound($aTable)+1][2]
    EndIf
    $aTable[UBound($aTable)-1][0] = $oField.Value
    $aTable[UBound($aTable)-1][1] = $rs.Fields("TABLE_TYPE" ).Value
    $rs.MoveNext
    Until $rs.EOF
    Else ; Selected Table
    Do ;Check for a user table object
    If $rs.Fields("TABLE_TYPE" ).Value = $Type Then
    If UBound($aTable) = 0 Then
    Dim $aTable[1]
    Else
    ReDim $aTable[UBound($aTable)+1]
    EndIf
    $aTable[UBound($aTable)-1] = $oField.Value
    EndIf
    $rs.MoveNext
    Until $rs.EOF
    EndIf

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

    If UBound($aTable) = 0 Then
    $SQLErr = "Table Not Found"
    Return SetError($SQL_ERROR, 0, $SQL_ERROR) ; Table Not Found
    EndIf

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

    Return SetError($SQL_OK, 0, $aTable)
    EndFunc ;==>GetTableName

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

    ; #FUNCTION# ===================================================================
    ; Name ..........: _SQL_UDFVersion
    ; Description ...: Get UDF Version
    ; Syntax ........: _SQL_UDFVersion
    ; Parameters ....: None.
    ; Return values .: $SQLUDFVersion
    ; Author ........:
    ; Modified.......:
    ; Remarks .......:
    ; Related .......:
    ; Link ..........:
    ; Example .......: no
    ; ================================================================================

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

    Func _SQL_UDFVersion()

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

    Return $SQLUDFVersion

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

    EndFunc

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

    ; #FUNCTION# ===================================================================
    ; Name ..........: _SQL_ErrFunc
    ; Description ...: Autoit Error handler function
    ; Syntax ........: _SQL_ErrFunc()
    ; Parameters ....: None.
    ; Return values .: $SQLErr and @error set to $SQL_ERROR
    ; Author ........:
    ; Modified.......:
    ; Remarks .......: COM error handler function.
    ; Related .......:
    ; Link ..........:
    ; Example .......: no
    ; ================================================================================
    Func _SQL_ErrFunc()
    Local $HexNumber = Hex($MSSQLObjErr.number, 8)
    $SQLErr = "err.description is: " & @TAB & $MSSQLObjErr.description & @CRLF & _
    "err.windescription:" & @TAB & $MSSQLObjErr.windescription & @CRLF & _
    "err.number is: " & @TAB & $HexNumber & @CRLF & _
    "err.lastdllerror is: " & @TAB & $MSSQLObjErr.lastdllerror & @CRLF & _
    "err.scriptline is: " & @TAB & $MSSQLObjErr.scriptline & @CRLF & _
    "err.source is: " & @TAB & $MSSQLObjErr.source & @CRLF & _
    "err.helpfile is: " & @TAB & $MSSQLObjErr.helpfile & @CRLF & _
    "err.helpcontext is: " & @TAB & $MSSQLObjErr.helpcontext
    ConsoleWrite("###############################" & @CRLF & $SQLErr & "###############################" & @CRLF)
    SetError($SQL_ERROR, 0, $SQLErr)
    EndFunc ;==>_SQL_ErrFunc

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


    Beispiel ...

    Spoiler anzeigen
    [autoit]


    #include <_sql.au3>
    #include <array.au3>

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

    $sServer = "servername"
    $sDatabase = "db_name"

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

    Opt ("trayIconDebug",1)

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

    Msgbox(0,"","Start the Script and load the error handler")
    _SQL_RegisterErrorHandler();register the error handler to prevent hard crash on COM error

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

    $oADODB = _SQL_Startup()
    If $oADODB = $SQL_ERROR then Msgbox(0 + 16 +262144,"Error",_SQL_GetErrMsg())

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

    If _sql_Connect(-1,$sServer,"","","") = $SQL_ERROR then
    Msgbox(0 + 16 +262144,"Error",_SQL_GetErrMsg())
    _SQL_Close()
    Exit
    EndIf

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

    If _SQL_Execute(-1,"Create database My_SQL_Test;") = $SQL_ERROR then Msgbox(0 + 16 +262144,"Error",_SQL_GetErrMsg())
    _SQL_Close()

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

    Msgbox(0,"","Created datatbase logging out and back in again")
    $oADODB = _SQL_Startup()

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

    If _SQL_Connect(-1,"localhost","My_SQL_Test","sa","Superartcore") = $SQL_ERROR then Msgbox(0 + 16 +262144,"Error",_SQL_GetErrMsg())
    If _SQL_Execute(-1, "CREATE TABLE BBKS (ID INT NOT NULL IDENTITY(1,1),ComputerName VARCHAR(20) UNIQUE,Status VARCHAR(10),Error VARCHAR(10)Primary Key (ID));") = $SQL_ERROR then Msgbox(0 + 16 +262144,"Error",_SQL_GetErrMsg())
    If _SQL_Execute(-1,"INSERT INTO BBKS (ComputerName,Status,Error) VALUES ('"& @computername & "','On;li''ne','None');") = $SQL_ERROR then Msgbox(0 + 16 +262144,"Error",_SQL_GetErrMsg())
    If _SQL_Execute(-1,"INSERT INTO BBKS (ComputerName,Status,Error) VALUES ('1"& @computername & "','On;li''ne','None');") = $SQL_ERROR then Msgbox(0 + 16 +262144,"Error",_SQL_GetErrMsg())
    If _SQL_Execute(-1,"INSERT INTO BBKS (ComputerName,Status,Error) VALUES ('2"& @computername & "','Online','None');") = $SQL_ERROR then Msgbox(0 + 16 +262144,"Error",_SQL_GetErrMsg())

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

    ; this one will cause an error because the computername is not unique!
    If _SQL_Execute(-1,"INSERT INTO BBKS (ComputerName,Status,Error) VALUES ('2"& @computername & "','Online','None');") = $SQL_ERROR then Msgbox(0 + 16 +262144,"Error","Example Error this was meant to happen!" & @crlf & @crlf & _SQL_GETErrMsg())

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

    Msgbox(0,"","Created table and added data so lets get some data out first as a 2dArray")

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

    Local $aData,$iRows,$iColumns;Variables to store the array data in to and the row count and the column count
    $iRval = _SQL_GetTable2D(-1,"SELECT * FROM BBKS;",$aData,$iRows,$iColumns)
    If $iRval = $SQL_OK then _arrayDisplay($aData,"2D (" & $iRows & " Rows) (" & $iColumns & " Columns)" )

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

    Msgbox(0,"","Next as a 1dArray")

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

    Local $aData,$iRows,$iColumns;Variables to store the array data in to and the row count and the column count
    $iRval = _SQL_GetTable(-1,"SELECT * FROM BBKS;",$aData,$iRows,$iColumns)
    If $iRval = $SQL_OK then _arrayDisplay($aData,"1D (" & $iRows & " Rows) (" & $iColumns & " Columns)" )

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

    Msgbox(0,"","And now the same data returned 1 row at a time")

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

    $hData = _SQL_Execute(-1,"SELECT * FROM BBKS;")

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

    Local $aNames;Variable to store the array data in to
    $iRval = _SQL_FetchNames ($hData, $aNames); Read out Column Names
    If $iRval = $SQL_OK then ConsoleWrite(StringFormat(" %-10s %-10s %-10s %-10s ", $aNames[0], $aNames[1], $aNames[2], $aNames[3]) & @CR)
    _ArrayDisplay($aNames,"Column Names")

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

    Local $aRow;Variable to store the array data in to
    While _SQL_FetchData ($hData, $aRow) = $SQL_OK; Read Out the next Row
    ConsoleWrite(StringFormat(" %-10s %-10s %-10s %-10s ", $aRow[0], $aRow[1], $aRow[2], $aRow[3]) & @CR)
    _ArrayDisplay($aRow,"Single Row of Data")
    WEnd

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

    Msgbox(0,"","And now the same data returned as a string")

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

    Local $vString
    If _Sql_GetTableAsString(-1,"SELECT * FROM BBKS;",$vString) = $SQL_OK then
    Msgbox(0,"Data as a String",$vString)
    Else
    Msgbox(0 + 16 +262144,"SQL Error",_SQL_GetErrMsg() )
    EndIf

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

    Msgbox(0,"","Now just a single row")

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

    Local $aRow;Variable to store the row array data in
    $iRval = _SQL_QuerySingleRow(-1,"SELECT * FROM BBKS;",$aRow)
    If $iRval = $SQL_OK then _arrayDisplay($aRow,"1 Row" )

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

    Msgbox(0,"","Now drop the tables and the database")
    If _SQL_Execute(-1, "DROP TABLE BBKS;") = $SQL_ERROR then Msgbox(0 + 16 +262144,"Error",_SQL_GetErrMsg())

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

    If _SQL_Close() <> $SQL_OK then Msgbox(0 + 16 +262144,"Error",_SQL_GetErrMsg() )

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

    ;Just being lazy and not putting any error checking in now!
    $oADODB = _SQL_Startup()
    _SQL_Connect(-1,"localhost","","sa","Superartcore")
    _SQL_Execute(-1,"DROP database My_SQL_Test;")
    _SQL_Close()

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

    Msgbox(0,"","Example Finished")

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


    vielleicht hilft das weiter ...

    gruß gmmg ;)

  • Access (.accdb) Datenbank auslesen etc.

    • gmmg
    • 13. Mai 2014 um 10:12

    kannst du nicht einfach ein (Select ID FROM "&$tabellenname&" WHERE Stick = '" &$iStick&"'" AND Box = '" &$iBox&"'" ) machen?

    [autoit]


    $datenbankpfad = "d:\Test.accdb"
    $tabellenname = "Tabellentest"
    $Spalte0 = "ID"
    $Spalte1 = "Stick"
    $Spalte2 = "Box"

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

    $query = "SELECT * FROM " & $tabellenname & " WHERE Box = '12' AND Stick = '3'"
    $strData1 = _ReadOneFld($query, $datenbankpfad,$Spalte0)

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

    ConsoleWrite($query& @CRLF &$strData1)
    MsgBox(0,"",$query & @CRLF & "Ergebnis: " & $strData1)

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

    Func _ReadOneFld($_sql, $_datenbankpfad, $_field)
    Dim $_output
    $adoCon = ObjCreate("ADODB.Connection")
    $adoCon.Open("Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & $datenbankpfad & ";")
    $adoRs = ObjCreate("ADODB.Recordset")
    $adoRs.CursorType = 1
    $adoRs.LockType = 3
    $adoRs.Open($_sql, $adoCon)

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

    With $adoRs
    If .RecordCount Then
    While Not .EOF
    $_output = $_output & .Fields($_field).Value
    .MoveNext
    WEnd
    EndIf
    EndWith
    $adoCon.Close
    Return $_output
    EndFunc

    [/autoit]

    so in etwa ...

    gruß gmmg

  • MSSQL 2008 Abfrage = keine Ergebnisse, bzw. kein Update

    • gmmg
    • 12. Mai 2014 um 15:32

    funktioniert den dein SQL Statement in der "SQL Server Management Studio" Console?

    gruß gmmg

  • Access (.accdb) Datenbank auslesen etc.

    • gmmg
    • 7. Mai 2014 um 12:30

    super wenn es jetzt geht :)

    Gruß gmmg ;)

  • Access (.accdb) Datenbank auslesen etc.

    • gmmg
    • 6. Mai 2014 um 20:26

    hallo MadCatz ...

    hast du denn mal eine exe daraus erstellt und die dann getestet?

    meine exe mit datenbank hat ja bei dir auch funktioniert ...

    Was hast du denn Überhaupt für ein Betriebssystem? Ich habe Win7 Professional X64.

    im englischen Forum gibts noch den Hinweis, dass man folgenden code am anfang des scriptes einfügen soll!

    [autoit]


    #region ;**** Directives created by AutoIt3Wrapper_GUI ****
    #AutoIt3Wrapper_UseX64=n
    #endregion ;**** Directives created by AutoIt3Wrapper_GUI ****

    [/autoit]

    und hier noch ein weiteres Thema dazu [ gelöst ] Verbindung zu Access 2010 DB nicht möglich

    2007 Office System Driver: Data Connectivity Components
    http://www.microsoft.com/en-us/download/details.aspx?displaylang=en&id=23734

    gruß gmmg

  • Access (.accdb) Datenbank auslesen etc.

    • gmmg
    • 6. Mai 2014 um 09:17

    OK ...

    Falls du nochmal hilfe benötigst, schreib einfach ...

    Gruß gmmg ;)

  • Access (.accdb) Datenbank auslesen etc.

    • gmmg
    • 6. Mai 2014 um 08:40

    ok ...

    wenn das script bei anderen auch geht, ist das schon seltsam!
    hast du das mal an einem anderen client getestet?

    mit der ID in der Datenbank hat das eigentlich nichts zu tun! Könntest aber die DB mal bearbeiten und die Splate ID entfernen ...
    und bei dir geht das auch nicht, wenn du eine exe aus deinem script erstellst und diese dann startest?

    gruß gmmg :)

  • Access (.accdb) Datenbank auslesen etc.

    • gmmg
    • 5. Mai 2014 um 15:36

    welche autoit version benutzt du denn?

    ich habe die Autoit 3.3.10.2 (siehe Bild) und Scite Version 3.3.7

    gruß gmmg ;)

    Dateien

    version.PNG 23,03 kB – 0 Downloads
  • Access (.accdb) Datenbank auslesen etc.

    • gmmg
    • 5. Mai 2014 um 15:23

    hallo MadCatz ...

    auch mit der au3 und test datenbank von mir bekommst du den fehler?

    entferne mal das "@crlf" bei "$_output = $_output & .Fields($_field).Value @crlf"

    [autoit]


    $_output = $_output & .Fields($_field).Value

    [/autoit]

    leerzeichen hast du hoffentlich nicht in den feldwerten ... kommt ja manchmal beim kopieren und einfügen vor ...
    ansonsten, würde ich nochmal aud der Test.accdb eine Test.mdb machen und den code wie folgt ändern

    [autoit]


    $adoCon.Open("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & $_dbname)

    [/autoit]

    gruß gmmg

  • Access (.accdb) Datenbank auslesen etc.

    • gmmg
    • 5. Mai 2014 um 12:21

    hab dir mal meine dateien als zip per privater nachricht gesendet ..

    gruß gmmg

  • Access (.accdb) Datenbank auslesen etc.

    • gmmg
    • 5. Mai 2014 um 10:47

    dazu gibt es im netz jede menge anleitungen :)

    http://www.youtube.com/watch?v=QVX0VsE7_Xk

    siehe Bilder ...

    Dateien

    Abfrage.jpg 845,85 kB – 0 Downloads abfrage2.PNG 42,83 kB – 0 Downloads
  • Access (.accdb) Datenbank auslesen etc.

    • gmmg
    • 5. Mai 2014 um 09:47

    Also bei mir funktioniert das!

    hast du mal im access selbst die SQL Abfrage gemacht? (siehe Bild)

    [autoit]


    $datenbankpfad = "d:\Test.accdb"
    $tabellenname = "Tabellentest"
    $Spalte1 = "Stick" ;nicht ID Spalte
    $Spalte2 = "Box" ;nicht ID Spalte

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

    $query = "SELECT * FROM " & $tabellenname & " WHERE Box = '22'"
    $strData1 = _ReadOneFld($query, $datenbankpfad,$Spalte1)

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

    ConsoleWrite($query& @CRLF &$strData1)
    MsgBox(0,"",$query & @CRLF & "Ergebnis: " & $strData1)

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

    Func _ReadOneFld($_sql, $_datenbankpfad, $_field)
    Dim $_output
    $adoCon = ObjCreate("ADODB.Connection")
    $adoCon.Open("Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & $datenbankpfad & ";")
    $adoRs = ObjCreate("ADODB.Recordset")
    $adoRs.CursorType = 1
    $adoRs.LockType = 3
    $adoRs.Open($_sql, $adoCon)

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

    With $adoRs
    If .RecordCount Then
    While Not .EOF
    $_output = $_output & .Fields($_field).Value & @CRLF
    .MoveNext
    WEnd
    EndIf
    EndWith
    $adoCon.Close
    Return $_output
    EndFunc

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

    gruß gmmg

    Dateien

    Abfrage_Access.PNG 82,09 kB – 0 Downloads
  • Access (.accdb) Datenbank auslesen etc.

    • gmmg
    • 5. Mai 2014 um 09:11

    Hallo MadCatz :)

    deine Abfrage (Select + From ...) ist falsch, zumindest hier in deinem angegeben Quelltext!
    Muss heißen Select * From ....

    Gruß gmmg ;)

  • Kompiliertes Script soll "sich selbst" löschen können.

    • gmmg
    • 30. April 2014 um 15:25

    kannst du nicht deine exe in die Ausnahmeliste in den Bitdefender einfügen!
    oder hast du das schon gemacht!

    gruß gmmg

  • Access (.accdb) Datenbank auslesen etc.

    • gmmg
    • 26. April 2014 um 18:18

    Hallo MadCatz,

    1. bitte alle Leerzeichen aus den Datenbanknamen, Tabellennamen, Spaltenüberschriften entfernen!
    2. Umlaute gehören da auch nicht rein ü, ä, ö usw ... :)

    Teste mal deine Abfrage mit MS Access ... Im SQL Modus ...

    Erstelle mal eine Access DB nach meinem Beispiel, mit den paar feldern und teste mal den geposteten Code ...

    Gruß gmmg ;)

  • Access (.accdb) Datenbank auslesen etc.

    • gmmg
    • 25. April 2014 um 11:51

    hallo,

    hier nochmal ein beispiel zum auslesen einer access accdb :)

    Spoiler anzeigen
    [autoit][/autoit] [autoit][/autoit] [autoit]

    ;--------- Modify the variables below as desired or applicable -------
    $dbname = "d:\dbtest.accdb"
    $tblname = "Tabelle1"
    $fldname01 = "Feld1"
    $format = "Text(50)"
    $fldname02 = "Feld2"
    $format = "Text(50)"

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

    $query = "SELECT * FROM "& $tblname & " WHERE Feld2 = 'test2'"
    $strData1 = _ReadOneFld($query, $dbname, $fldname01)

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

    ConsoleWrite( $query & @crlf & $strData1)
    MsgBox(0,"",$query & @crlf & "ergebnis: " & $strData1)

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

    ;------access DB auslesen -------------
    Func _ReadOneFld($_sql, $_dbname, $_field)
    Dim $_output
    $adoCon = ObjCreate("ADODB.Connection")
    $adoCon.Open("Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & $dbname & ";")
    $adoRs = ObjCreate("ADODB.Recordset")
    $adoRs.CursorType = 1
    $adoRs.LockType = 3
    $adoRs.Open($_sql, $adoCon)

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

    With $adoRs
    If .RecordCount Then
    While Not .EOF
    $_output = $_output & .Fields($_field).Value & @CRLF
    .MoveNext
    WEnd
    EndIf
    EndWith
    $adoCon.Close
    Return $_output
    EndFunc ;==>_ReadOneFld

    [/autoit]

    gruß gmmg :)

    Bilder

    • TestACCDB.PNG
      • 11,94 kB
      • 450 × 169
  • Aktualisierung der Exe

    • gmmg
    • 24. April 2014 um 14:48

    hallo zusammen,

    ich mach das immer wie folgt!
    entweder über eine ini datei, die dann beim start auf dem server und local überprüft wird oder wie unten im script zu sehen, über die fileversion, die beim kompilieren vergeben wird!

    Beispiel des Inhaltes einer ini datei

    [INFO]
    VERSION=1.0.2.3

    der grobe Ablauf wäre wie folgt ...
    man startet das programm, dieses prüft nun die fileversion oder ini lokal und auf dem server und je nach ergebnis, wird eine updater exe gestartet, die dann die neue programmversion in den programmpfad kopiert ...

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

    _Programm_pruefen()

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

    Func _Programm_pruefen()

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

    ;---Fileversion abrufen------
    $sV1 = $vlw_path & "\Programm.exe" ; exe auf dem server
    $sV2 = "C:\Programm.exe" ;exe auf dem client

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

    $v1 = FileGetVersion($sV1) ;server File 1
    $v2 = FileGetVersion($sV2) ;local File 2

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

    $v1split = StringSplit($v1, ".")
    $v2split = StringSplit($v2, ".")

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

    ;----------- prüfe ----
    $spl_1 = $v1split[1] & $v1split[2] & $v1split[3] & $v1split[4]
    $spl_2 = $v2split[1] & $v2split[2] & $v2split[3] & $v2split[4]

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

    $spl_erg = $spl_2 - $spl_1

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

    If $spl_erg < 0 Then
    Msgbox(0,"",'... Update vorhanden ...')
    Sleep(1000)
    Run($Scriptdir & "\Updater.exe")
    Exit
    EndIf

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

    EndFunc

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


    ;updater
    Sleep(2000)

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

    $sV1 = "\\server\Programm.exe"

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

    FileCopy($sV1, "C:\Programm.exe",1)
    Sleep(2000)

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

    Run("C:\Programm.exe")

    [/autoit]

    du müsstest das halt dann umbauen, das dein updater die version prüft und die entsprechende exe dann lokal kopiert!
    somit hat jeder user beim neustart der anwendung die aktuelle version vom server ...

    gruß gmmg :)

  • Run Problem

    • gmmg
    • 13. März 2014 um 16:50

    man muss den backslash auch nicht einzeln angeben, sondern kann ihn ja wie folgt übergeben ...

    @ScriptDir & "\Meine.au3"

    [autoit]


    $ProgLink = @ScriptDir & "\Meine.au3" ; hier ist das Script das gestartet werden soll
    MsgBox(0,"", @AutoItExe & ' /AutoIt3ExecuteScript ' & $ProgLink)

    [/autoit]

    gruß gmmg

  • Text aktualisieren

    • gmmg
    • 12. März 2014 um 13:54

    wie schnitzel schon schreibt, du machst oben im Case $bnFilecode eine fileclose rein und im Case $btnsave ein fileopen mit entsprechendem parameter und wieder ein fileclose ..

    sollte doch so gehen ..


    gruß gmmg

  • Verfügbare Papierfachcodes von Drucker ermitteln

    • gmmg
    • 12. März 2014 um 10:01

    hallo Maxxer,

    wir hatten auch schon mal so ein problem, bei druckern mit mehreren fächern!
    ich habe dann den drucker einfach für jedes fach nochmal dem printserver z.b. als HP Laserjet Fach 1 und HP Laserjet Fach 2 angelegt.

    wenn ich nach "change paper tray" oder "set paper tray" kommt einiges ..ob das jetzt für dich passend ist, musst du mal schauen :)

    https://github.com/frogmore/print…ript%20file.vbs

    eventuell hat hier Water noch ne idee, er hat ja im engl. Forum gepostet ...

    gruß gmmg

Spenden

Jeder Euro hilft uns, Euch zu helfen.

Download

AutoIt Tutorial
AutoIt Buch
Onlinehilfe
AutoIt Entwickler
  1. Datenschutzerklärung
  2. Impressum
  3. Shoutbox-Archiv
Community-Software: WoltLab Suite™