Beiträge von gmmg

gmmg

wenn das nicht dein problem löst,

warum startest du outlook nicht einfach per script und schließt es danach wieder?

gruß gmmg

gmmg

Beispiel für ein Update einer access accdb ...

[autoit]

#include <Date.au3>

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

_access_write_update()

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

Func _access_write_update()

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

$dbname = "D:\Data\scripte\MS_AccessCom\access_01\db_test.accdb"
$tblname = "epayslip"
$s_data07 = _Now()

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

$FULL_MDB_FILE_NAME = $dbname
;$SQL_CODE = "select * from epayslip"
$CONN = ObjCreate("ADODB.Connection")
;$CONN.Open('Driver={Microsoft Access Driver (*.mdb)};Dbq=' & $FULL_MDB_FILE_NAME & ';')
$CONN.Open("Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & $FULL_MDB_FILE_NAME & ";")
$RecordSet = ObjCreate("ADODB.Recordset")

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

;Local $sQuery = "UPDATE " & $tblname & " SET check = 1, date = " & $s_data07 & " WHERE user = " & "'" & @UserName & "'"
Local $sQuery = "UPDATE " & $tblname & " SET " & $tblname & ".[check] = 1, " & $tblname & ".[date] = '" & $s_data07 & "' WHERE " & $tblname & ".[user] = " & "'" & @UserName & "'"
;ConsoleWrite($sQuery)
$CONN.Execute($sQuery)
$CONN.Close

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

EndFunc

[/autoit]

gruß gmmg

gmmg

ich dachte, hier im forum wurde das schonmal gefragt ...

hab hier mal paar beispiele aus meiner Sammlung ...

access read ... sollte ab Version 2007 gehen

Spoiler anzeigen

[autoit]

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

[/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] [autoit][/autoit] [autoit][/autoit]

Access Write ... für Access 2003

Spoiler anzeigen

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

_access_write($w1, $w2, $w3, $w4)

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

Func _access_write($w1, $w2, $w3, $w4)

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

$FULL_MDB_FILE_NAME = $dbname
;$SQL_CODE = "select * from epayslip"
$CONN = ObjCreate("ADODB.Connection")
$CONN.Open('Driver={Microsoft Access Driver (*.mdb)};Dbq=' & $FULL_MDB_FILE_NAME & ';')
$RecordSet = ObjCreate("ADODB.Recordset")

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

Local $sQuery = "INSERT INTO Reporting (`spalte1`,`spalte2`,`spalte3`,`spalte4`)" & _
"VALUES ('" & $w1 & "',"& _ ;
"'" & $w2 & "'," & _ ;
"'" & $w3 & "'," & _ ;
"'" & $w4 & "')" ;
;ConsoleWrite($sQuery)
$CONN.Execute($sQuery)
$CONN.Close

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

EndFunc

[/autoit]

Access Write Update ... Access 2003

Spoiler anzeigen

[autoit]

Func _access_write_update()

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

;$dbname = "d:\epayslip.mdb"
$tblname = "epayslip"
$s_data07 = _Now()

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

$FULL_MDB_FILE_NAME = $dbname
;$SQL_CODE = "select * from epayslip"
$CONN = ObjCreate("ADODB.Connection")
$CONN.Open('Driver={Microsoft Access Driver (*.mdb)};Dbq=' & $FULL_MDB_FILE_NAME & ';')
$RecordSet = ObjCreate("ADODB.Recordset")

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

;Local $sQuery = "UPDATE " & $tblname & " SET check = 1, date = " & $s_data07 & " WHERE user = " & "'" & @UserName & "'"
Local $sQuery = "UPDATE " & $tblname & " SET " & $tblname & ".[check] = 1, " & $tblname & ".[date] = '" & $s_data07 & "' WHERE " & $tblname & ".[user] = " & "'" & @UserName & "'"
;ConsoleWrite($sQuery)
$CONN.Execute($sQuery)
$CONN.Close

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

EndFunc

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

eventuell hilft dir das weiter ...
am besten auch vorher die SQL befehle im access selbst testen ...

gruß gmmg

gmmg

hallo Herbert,

das sollte dir weiterhelfen

[autoit]

#include <AD.au3>
#include <File.au3>

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

; Open Connection to the Active Directory
_AD_Open()
If @error Then Exit MsgBox(16, "Active Directory Example Skript", "Function _AD_Open encountered a problem. @error = " & @error & ", @extended = " & @extended)

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

Global $aObjects[1][1]
Global $sOU = "" ; Search all OUs
$aObjects = _AD_GetObjectsInOU($sOU, "(&(objectcategory=person)(objectclass=user)(name=*))", 2, "sAMAccountName,telephoneNumber")
If @error > 0 Then
MsgBox(64, "Active Directory Functions - Example 1", "No OUs could be found")
Else
;_ArrayDisplay($aObjects, "Active Directory Functions")
EndIf
; Close Connection to the Active Directory
_AD_Close()
; Array bearbeiten
For $i = 1 to $aObjects[0][0]

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

;ConsoleWrite($aObjects[$i][1])
If $aObjects[$i][1] = "+49 (89) 0000 0000" Then
; Benutzer verarbeiten
MsgBox(0,"",$aObjects[$i][0] & @crlf & $aObjects[$i][1])
Endif
Next

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

gruß gmmg

gmmg

da müsstest du ja jeden User im AD durchgehen und die _AD_GetObjectProperties($sSamAccountName) zur Telefonnummer abfragen oder falls die User alle in einer Gruppe sind, diese auslesen und den abgleich machen ..

beispiel zu _AD_GetObjectsInOU , kann aber sein, dass es an die neue AD Funktion angepast werden muss ...

[autoit]

$aObjects = _AD_GetObjectsInOU("OU=User,OU="&$aOUs[$x]&",DC=konzern,DC=de", "(&(objectcategory=person)(objectclass=user)(mail=*)(|(userAccountControl=512)(msExchHideFromAddressLists="")(msExchHideFromAddressLists=FALSE)(userAccountControl=66048)))", 2, "name,department,company,createTimeStamp")

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

$aObjects = _AD_GetObjectsInOU("OU=User,OU="&$aOUs[$x]&",DC=Konzern,DC=de", "(&(objectcategory=person)(objectclass=user)(mail=*)(|(userAccountControl=512)(msExchHideFromAddressLists="")(msExchHideFromAddressLists=FALSE)(userAccountControl=66048)))", 2, "name,description,company");"name,department,company")

[/autoit]

AD User aus einer bestimmten Gruppe auslesen ...

Spoiler anzeigen

[autoit]

; *****************************************************************************
#include <AD.au3>

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

Global $aGroups[1], $aMembers[1]

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

; Open Connection to the Active Directory
_AD_Open()
If @error Then Exit MsgBox(16, "Active Directory Example Skript", "Function _AD_Open encountered a problem. @error = " & @error & ", @extended = " & @extended)

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

; Get an array of group names (FQDN) that the user is immediately a member of with element 0 containing the number of groups.
$aGroups = _AD_GetUserGroups()
If @error > 0 Then
MsgBox(64, "Active Directory Functions - Example 1", "The current user is not a member of any group")
Else
; Get a sorted list of members for the first group the currently logged on user is a member of
$aMembers = _AD_GetGroupMembers("AD_Gruppe")
If @error > 0 Then
MsgBox(64, "Active Directory Functions - Example 1", "The group '" & $aGroups[1] & "' has no members")
Else
_ArraySort($aMembers, 0, 1)
_ArrayDisplay($aMembers, "Active Directory Functions - Example 1 - List of members for group '" & $aGroups[1] & "'")

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

for $i = 1 to $aMembers[0]

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

;MsgBox(0,"", $aMembers01)
ConsoleWrite(_AD_FQDNToSamAccountName($aMembers[$i]) & @CRLF)
Next

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

EndIf
EndIf

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

; Close Connection to the Active Directory
_AD_Close()

[/autoit]

gruß gmmg

gmmg

die telefonnummer ist ja ein attribute was zum user gehört!
hast du nur die telefonnummer oder auch den namen, eventuell aus einer anderen liste?

mit dem ADSI Editor die Attribute auslesen ...

Beispiel "telephoneNumber"

[autoit]

$aProperties = _AD_GetObjectProperties($sSamAccountName)
; _ArrayDisplay($aProperties, "Active Directory Functions - Example 1 - Properties for user '" & @UserName & "'")

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

For $x = 1 to $aProperties[0][0]
If $aProperties[$x][0] = ""telephoneNumber"l" then $telefonl = $aProperties[$x][1]
If $aProperties[$x][0] = "cn" then $name_cn = $aProperties[$x][1]
Next

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

gruß gmmg

gmmg

hallo,

hast du schon mal versucht mit _IEDocReadHTML (in der Hilfe) den html code auszulesen, ob da dein gesuchter text auftaucht?

gruß gmmg

gmmg

OK ... ich halt auch mal die Augen auf, wir brauchen aber sowas auf arbeit weniger ..

gruß gmmg

gmmg

eventuell gibts dazu was im englischen forum!
bei google findet man verschiedene VB bzw. Powershell codeschnipsel! habe mal nach "WTSEnumerateSessionsEx DLL VBS" gesucht ..

gruß gmmg

gmmg

woher kommt die id? nicht immer, wenn es z.b eine referenztabelle ist, kann die id auch mehrfach in einer anderen vorkommen!
du kliest die id's in ein array und lässt dieses in einer schleife durchlaufen ..
zählen kannst du in einer schleife

beispiel ...mal so aus dem kopf

[autoit]

dim $z
For $i = 1 to array[0]

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

$id = "text"
$x = 0
if $id = "text" Then $z += $x + 1

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

MsgBox(0,"",$z)

[/autoit]

gruß gmmg

gmmg

die ncpa.cpl für die netzwerkeinstellung ist aber vorhanden

gruß gmmg

gmmg

da gibt es doch mehrer möglichkeiten mit der windows taste oder der kurzbefehl referenz!

Link

gruß gmmg

gmmg

misterspeed

das machen dann die kollegen
wir haben da erstmal alle fallbeispiele aufgenommen und sind immer dran da was zu optimieren!

gruß gmmg

gmmg

@ NewProgger

soll das sowas wie ein IT-Helpdesk Tool werden?

Sowas habe ich auch schon umgesetzt (inclusive optionalem Screenshot) und ist bei uns im unternehmen bei über 300 Mitarbeitern im Einsatz!
Da ist aber die _INetSmtpMailCom eingebunden, für den SMTP Versand über unseren Mailserver.

durch klick auf die Icons, kann man das system wählen, darunter sind dann unterpunkte möglich, die aus einer ini datei gelesen werden.

so mal als anreiz

gruß gmmg

gmmg

MadCatz

eigentlich ganz einfach ... sollte für Access 2007 /2010 gehen ...

[autoit]

$s_db_pwd = "12345678"

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

_access_write()

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

Func _access_write()

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

$s_dbname = "D:\TestDB.accdb"
$datenbankpfad = $s_dbname

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

$s_data01 = @username
$s_data02 = @ComputerName

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

$adoCon = ObjCreate("ADODB.Connection")
$adoCon.Open("Provider=Microsoft.ACE.OLEDB.12.0; Jet OLEDB:Database Password=" & $s_db_pwd & "; Data Source=" & $datenbankpfad & ";")
$adoRs = ObjCreate("ADODB.Recordset")
;$adoRs.CursorType = 1
;$adoRs.LockType = 3

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

Local $sQuery = "INSERT INTO TB1 (`feld1`,`feld2`)" & _
"VALUES ('" & $s_data01 & "',"& _ ;User
"'" & $s_data02 & "')" ;PC

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

$adoCon.Execute($sQuery)
$adoCon.Close

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

EndFunc

[/autoit]

gmmg · und du benutzt auch die autoit version 3.3.10.2? gruß gmmg

und du benutzt auch die autoit version 3.3.10.2?

gruß gmmg

gmmg

@ Sheik

[$oForm = _IEFormGetCollection($oIE, 0)]
ja, mach ich sonst auch so, wenn sich das Formular auslesen lässt!

wenn der aufruf bei 20 anderen funktioniert und bei dir nicht, stimmt eventuell auch was mit deinem System nicht!
hast du die Möglichkeit das mal an einem anderen Client zu testen? Eventuell mal ne Exe erstellen ..

sag mal, funktioniert denn der aufruf einer anderen URL? welche Fehlermeldung erhälst du genau?

als Beispiel :
http://www.heise.de
https://www.amazon.de

gruß gmmg

gmmg

folgendes geht bei mir ...

Spoiler anzeigen

[autoit]

#include <IE.au3>

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

$user = "xxx"
$pwd = "xxx"

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

_login($user, $pwd)

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

Func _login($user, $pwd)

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

;MsgBox(0,"",$user & $pwd)
$seite_state = "Postbank Online-Banking - Windows Internet Explorer"
$oIE = _IECreate ("https://banking.postbank.de/rai/login")
WinActivate($seite_state)
WinSetState ($seite_state, "", @SW_MAXIMIZE )

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

_IELoadWait($oIE)

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

Send($user)
Send("{TAB}")
Send($pwd)
Send("{ENTER}")
Sleep (1000)

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

EndFunc

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

gruß gmmg

gmmg

hallo,

hast du dir mein script richtig angesehen?
in deinem _ReadOneFld sind 4 parameter angegeben, sollten aber nur 3 sein!
auf welche spalte bezieht sich deine variable $Spalte2? das ist mit sicherheit nicht die ID!

[autoit]

$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]

poste bitte nochmal dein komplettes script!

gruß gmmg

gmmg

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,
$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

Beiträge von gmmg

OutlookEx UDF + _OL_ItemSend

Access 2007 Eintrag erstellen (UPDATE)

Access 2007 Eintrag erstellen (UPDATE)

Active Directory Funktionen - Neue Version 1.5.0.0 freigegeben!

Active Directory Funktionen - Neue Version 1.5.0.0 freigegeben!

Active Directory Funktionen - Neue Version 1.5.0.0 freigegeben!

_IEFormGetObjByName, $_IESTATUS_NoMatch

Terminal Server alle Session IDs ermitteln

Terminal Server alle Session IDs ermitteln

Buttons zählen?

cpl / GUID Windows 8.1 - EInstellungen/PC-EInstellungen/Netzwerk/Arbeitsplatz

cpl / GUID Windows 8.1 - EInstellungen/PC-EInstellungen/Netzwerk/Arbeitsplatz

Suche elegante Lösung zum Datenimport

Suche elegante Lösung zum Datenimport

.ACCDB Einträge hinzufügen

Postbank Seite kann nicht aufgerufen werden

Postbank Seite kann nicht aufgerufen werden

Postbank Seite kann nicht aufgerufen werden

Access (.accdb) Datenbank auslesen etc.

MSSQL 2008 Abfrage = keine Ergebnisse, bzw. kein Update