bourny Posted November 20, 2008 Share Posted November 20, 2008 I am using a script from http://www.autoitscript.com/forum/index.php?showtopic=47585To query my SQL DB for a number from a table..... I appear to beable to get connected and run a query however I cannot figure out how to display what the object returns ..... I tried a message box but dont get anything .... My script is below and I have simply got the script from above below my script.#include-onceDim $sUsername = "sa"Dim $sPassword = "Password"Dim $sDatabase = "DBNAME"Dim $sServer = "MYSERVER"Dim $fAuthMode = 0Dim $sDriver = "{SQL Server}"$Connect = _SQLConnect($sServer, $sDatabase, $fAuthMode, $sUsername, $sPassword, $sDriver)If $Connect = 0 Then MsgBox(0, "Error", "Failed to connect" & @lf & $Connect) ExitElse MsgBox(0, "OK", "Got Connection" & @lf & $Connect) $sQuery = "Select * From Computer" $oQuery = _SQLQuery($Connect, $sQuery) If $oQuery = 0 Then MsgBox(0, "Query Failed", "" & @lf & $oQuery) Else MsgBox(0, "Query Worked", "" & @lf & $oQuery) ;i NEED TO DISPLAY THE RESULTS OF $oQuery somehow EndIf EndIf_SQLDisconnect($Connect) Link to comment Share on other sites More sharing options...
PsaltyDS Posted November 20, 2008 Share Posted November 20, 2008 I am using a script from http://www.autoitscript.com/forum/index.php?showtopic=47585To query my SQL DB for a number from a table..... I appear to beable to get connected and run a query however I cannot figure out how to display what the object returns ..... I tried a message box but dont get anything .... My script is below and I have simply got the script from above below my script.#include-onceDim $sUsername = "sa"Dim $sPassword = "Password"Dim $sDatabase = "DBNAME"Dim $sServer = "MYSERVER"Dim $fAuthMode = 0Dim $sDriver = "{SQL Server}"$Connect = _SQLConnect($sServer, $sDatabase, $fAuthMode, $sUsername, $sPassword, $sDriver)If $Connect = 0 Then MsgBox(0, "Error", "Failed to connect" & @lf & $Connect) ExitElse MsgBox(0, "OK", "Got Connection" & @lf & $Connect) $sQuery = "Select * From Computer" $oQuery = _SQLQuery($Connect, $sQuery) If $oQuery = 0 Then MsgBox(0, "Query Failed", "" & @lf & $oQuery) Else MsgBox(0, "Query Worked", "" & @lf & $oQuery) ;i NEED TO DISPLAY THE RESULTS OF $oQuery somehow EndIf EndIf_SQLDisconnect($Connect)The result of your query should be a RecordSet object. You will have to use methods and properties for a RecordSet object to access it: ADO Recordset ObjectYou don't show where you got your _SQL* UDFs from, so we can't be specific. Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law Link to comment Share on other sites More sharing options...
bourny Posted November 20, 2008 Author Share Posted November 20, 2008 The result of your query should be a RecordSet object. You will have to use methods and properties for a RecordSet object to access it: ADO Recordset ObjectYou don't show where you got your _SQL* UDFs from, so we can't be specific. Not sure what you mean - Never used methoda or Properties let alone Objects much in the past - Here is my full script I am Expecting to get a value back from the database from the $oQuery Object but I cannot figure out how I get the return Values The Value is comming from the Computer_IDN Row of the Computer TableI have even Tried Select * From Computer and this appears to work but I cannot fugure again how to display the results. I am either getting no results even though the function states the Object is valid or getting something I cannot display or break down.FULL SCRIPT**********#include <array.au3>#include-onceDim $sUsername = "sa"Dim $sPassword = "lan"Dim $sDatabase = "LDDB"Dim $sServer = "SERVERNAME"Dim $fAuthMode = 0Dim $sDriver = "{SQL Server}"$oConn = _SQLConnect($sServer, $sDatabase, $fAuthMode, $sUsername, $sPassword, $sDriver)If $oConn = 0 Then MsgBox(0, "Error", "Failed to connect" & @lf & $oConn & @lf & @error ) ExitElse MsgBox(0, "OK", "Got Connection" & @lf & $oConn & @lf & @error) $sQuery = "Select * From Computer" ;$sQuery = "Select Computer_IDN From Computer Where DeviceName='COMPUTER1'" ;$sQuery = "Select *" $oQuery = _SQLQuery($oConn, $sQuery) If $oQuery = 0 Then MsgBox(0, "Query Failed", "" & @lf & $oQuery) Else MsgBox(0, "Query Worked", "" & @lf & $oQuery) *****HOW DO I DISPLAY THE RESULTS OF THE OBJECT $oQuery EndIf EndIf_SQLDisconnect($oConn);===============================================================================;; Function Name: _SQLConnect; Description: Initiate a connection to a SQL database; Syntax: $oConn = _SQLConnect($sServer, $sDatabase, $fAuthMode = 0, $sUsername = "", $sPassword = "", _; $sDriver = "{SQL Server}"); Parameter(s): $sServer - The server your database is on; $sDatabase - Database to connect to; $fAuthMode - Authorization mode (0 = Windows Logon, 1 = SQL) (default = 0); $sUsername - The username to connect to the database with (default = ""); $sPassword - The password to connect to the database with (default = ""); $sDriver (optional) the ODBC driver to use (default = "{SQL Server}"); Requirement(s): Autoit 3 with COM support; Return Value(s): On success - returns the connection object for subsequent SQL calls; On failure - returns 0 and sets @error:; @error=1 - Error opening database connection; @error=2 - ODBC driver not installed; @error=3 - ODBC connection failed; Author(s): SEO and unknown; Note(s): None;;===============================================================================Func _SQLConnect($sServer, $sDatabase, $fAuthMode = 0, $sUsername = "", $sPassword = "", $sDriver = "{SQL Server}") Local $sTemp = StringMid($sDriver, 2, StringLen($sDriver) - 2) Local $sKey = "HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\ODBC Drivers", $sVal = RegRead($sKey, $sTemp) If @error or $sVal = "" Then Return SetError(2, 0, 0) $oConn = ObjCreate("ADODB.Connection") If NOT IsObj($oConn) Then Return SetError(3, 0, 0) If $fAuthMode Then $oConn.Open ("DRIVER=" & $sDriver & ";SERVER=" & $sServer & ";DATABASE=" & $sDatabase & ";UID=" & $sUsername & ";PWD=" & $sPassword & ";") If NOT $fAuthMode Then $oConn.Open("DRIVER=" & $sDriver & ";SERVER=" & $sServer & ";DATABASE=" & $sDatabase) If @error Then Return SetError(1, 0, 0) Return $oConnEndFunc ;==>_SQLConnect;===============================================================================;; Function Name: _SQLConnect; Description: Send a query to a SQL database and return the results as an object; Syntax: $oQuery = _SQLQuery($oConn, $sQuery); Parameter(s): $oConn - A database connection object created by a previous call to _SQLConnect; $sQuery - The SQL query string to be executed by the SQL server; Requirement(s): Autoit 3 with COM support; Return Value(s): On success - returns the query result as an object; On failure - returns 0 and sets @error:; @error=1 - Unable to process the query; Author(s): SEO and unknown; Note(s): None;;===============================================================================Func _SQLQuery($oConn, $sQuery) If IsObj($oConn) Then Return $oConn.Execute($sQuery) Return SetError(1, 0, 0)EndFunc ;==>_SQLQuery;===============================================================================;; Function Name: _SQLDisconnect; Description: Disconnect and close an existing connection to a SQL database; Syntax: _SQLDisconnect($oConn); Parameter(s): $oConn - A database connection object created by a previous call to _SQLConnect; Requirement(s): Autoit 3 with COM support; Return Value(s): On success - returns 1 and closes the ODBC connection; On failure - returns 0 and sets @error:; @error=1 - Database connection object doesn't exist; Author(s): SEO and unknown; Note(s): None;;===============================================================================Func _SQLDisconnect($oConn) If NOT IsObj($oConn) Then Return SetError(1, 0, 0) $oConn.Close Return 1EndFunc ;==>_SQLDisconnect Link to comment Share on other sites More sharing options...
PsaltyDS Posted November 20, 2008 Share Posted November 20, 2008 (edited) bourny said: Not sure what you mean - Never used methoda or Properties let alone Objects much in the past - Here is my full script I am Expecting to get a value back from the database from the $oQuery Object but I cannot figure out how I get the return Values The Value is comming from the Computer_IDN Row of the Computer Table I have even Tried Select * From Computer and this appears to work but I cannot fugure again how to display the results. I am either getting no results even though the function states the Object is valid or getting something I cannot display or break down. FULL SCRIPT ********** CODE #include <array.au3> #include-once Dim $sUsername = "sa" Dim $sPassword = "lan" Dim $sDatabase = "LDDB" Dim $sServer = "SERVERNAME" Quote Dim $fAuthMode = 0 Dim $sDriver = "{SQL Server}" $oConn = _SQLConnect($sServer, $sDatabase, $fAuthMode, $sUsername, $sPassword, $sDriver) If $oConn = 0 Then MsgBox(0, "Error", "Failed to connect" & @lf & $oConn & @lf & @error ) Exit Else MsgBox(0, "OK", "Got Connection" & @lf & $oConn & @lf & @error) $sQuery = "Select * From Computer" ;$sQuery = "Select Computer_IDN From Computer Where DeviceName='COMPUTER1'" ;$sQuery = "Select *" $oQuery = _SQLQuery($oConn, $sQuery) If $oQuery = 0 Then MsgBox(0, "Query Failed", "" & @lf & $oQuery) Else MsgBox(0, "Query Worked", "" & @lf & $oQuery) *****HOW DO I DISPLAY THE RESULTS OF THE OBJECT $oQuery EndIf EndIf _SQLDisconnect($oConn) ;=============================================================================== ; ; Function Name: _SQLConnect ; Description: Initiate a connection to a SQL database ; Syntax: $oConn = _SQLConnect($sServer, $sDatabase, $fAuthMode = 0, $sUsername = "", $sPassword = "", _ ; $sDriver = "{SQL Server}") ; Parameter(s): $sServer - The server your database is on ; $sDatabase - Database to connect to ; $fAuthMode - Authorization mode (0 = Windows Logon, 1 = SQL) (default = 0) ; $sUsername - The username to connect to the database with (default = "") ; $sPassword - The password to connect to the database with (default = "") ; $sDriver (optional) the ODBC driver to use (default = "{SQL Server}") ; Requirement(s): Autoit 3 with COM support ; Return Value(s): On success - returns the connection object for subsequent SQL calls ; On failure - returns 0 and sets @error: ; @error=1 - Error opening database connection ; @error=2 - ODBC driver not installed ; @error=3 - ODBC connection failed ; Author(s): SEO and unknown ; Note(s): None ; ;=============================================================================== Func _SQLConnect($sServer, $sDatabase, $fAuthMode = 0, $sUsername = "", $sPassword = "", $sDriver = "{SQL Server}") Local $sTemp = StringMid($sDriver, 2, StringLen($sDriver) - 2) Local $sKey = "HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\ODBC Drivers", $sVal = RegRead($sKey, $sTemp) If @error or $sVal = "" Then Return SetError(2, 0, 0) $oConn = ObjCreate("ADODB.Connection") If NOT IsObj($oConn) Then Return SetError(3, 0, 0) If $fAuthMode Then $oConn.Open ("DRIVER=" & $sDriver & ";SERVER=" & $sServer & ";DATABASE=" & $sDatabase & ";UID=" & $sUsername & ";PWD=" & $sPassword & ";") If NOT $fAuthMode Then $oConn.Open("DRIVER=" & $sDriver & ";SERVER=" & $sServer & ";DATABASE=" & $sDatabase) If @error Then Return SetError(1, 0, 0) Return $oConn EndFunc ;==>_SQLConnect ;=============================================================================== ; ; Function Name: _SQLConnect ; Description: Send a query to a SQL database and return the results as an object ; Syntax: $oQuery = _SQLQuery($oConn, $sQuery) ; Parameter(s): $oConn - A database connection object created by a previous call to _SQLConnect ; $sQuery - The SQL query string to be executed by the SQL server ; Requirement(s): Autoit 3 with COM support ; Return Value(s): On success - returns the query result as an object ; On failure - returns 0 and sets @error: ; @error=1 - Unable to process the query ; Author(s): SEO and unknown ; Note(s): None ; ;=============================================================================== Func _SQLQuery($oConn, $sQuery) If IsObj($oConn) Then Return $oConn.Execute($sQuery) Return SetError(1, 0, 0) EndFunc ;==>_SQLQuery ;=============================================================================== ; ; Function Name: _SQLDisconnect ; Description: Disconnect and close an existing connection to a SQL database ; Syntax: _SQLDisconnect($oConn) ; Parameter(s): $oConn - A database connection object created by a previous call to _SQLConnect ; Requirement(s): Autoit 3 with COM support ; Return Value(s): On success - returns 1 and closes the ODBC connection ; On failure - returns 0 and sets @error: ; @error=1 - Database connection object doesn't exist ; Author(s): SEO and unknown ; Note(s): None ; ;=============================================================================== Func _SQLDisconnect($oConn) If NOT IsObj($oConn) Then Return SetError(1, 0, 0) $oConn.Close Return 1 EndFunc ;==>_SQLDisconnect You are using objects, you just don't know it because it is done in the UDF functions you are using. Try the full _SQL.au3 UDF by ChrisL: SQL ADODB.Connection After performing the query with that UDF, you would use _SqlGetDataAsString() or _SqlGetData2D() to get the data from the object. Edited October 3, 2016 by JLogan3o13 Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law Link to comment Share on other sites More sharing options...
bourny Posted November 20, 2008 Author Share Posted November 20, 2008 You are using objects, you just don't know it because it is done in the UDF functions you are using.Try the full _SQL.au3 UDF by ChrisL: SQL ADODB.ConnectionAfter performing the query with that UDF, you would use _SqlGetDataAsString() or _SqlGetData2D() to get the data from the object. You are using objects, you just don't know it because it is done in the UDF functions you are using.Try the full _SQL.au3 UDF by ChrisL: SQL ADODB.ConnectionAfter performing the query with that UDF, you would use _SqlGetDataAsString() or _SqlGetData2D() to get the data from the object. Thanks - I will try this - It appears the script I found states the error value has connected OK to the data source - I tried putting garbage as the server and login details and it still stated I was connecting OK - Seems I am using the function incorrectly or it does not work at all..I will look at the above SQL.au3 Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now