How to Display Object

[bourny]

I am using a script from

http://www.autoitscript.com/forum/index.php?showtopic=47585

To 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-once

Dim $sUsername = "sa"

Dim $sPassword = "Password"

Dim $sDatabase = "DBNAME"

Dim $sServer = "MYSERVER"

Dim $fAuthMode = 0

Dim $sDriver = "{SQL Server}"

$Connect = _SQLConnect($sServer, $sDatabase, $fAuthMode, $sUsername, $sPassword, $sDriver)

If $Connect = 0 Then

MsgBox(0, "Error", "Failed to connect" & @lf & $Connect)

Exit

Else

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)

[PsaltyDS]

I am using a script from

http://www.autoitscript.com/forum/index.php?showtopic=47585

To 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-once

Dim $sUsername = "sa"

Dim $sPassword = "Password"

Dim $sDatabase = "DBNAME"

Dim $sServer = "MYSERVER"

Dim $fAuthMode = 0

Dim $sDriver = "{SQL Server}"

$Connect = _SQLConnect($sServer, $sDatabase, $fAuthMode, $sUsername, $sPassword, $sDriver)

If $Connect = 0 Then

MsgBox(0, "Error", "Failed to connect" & @lf & $Connect)

Exit

Else

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 Object

You don't show where you got your _SQL* UDFs from, so we can't be specific.

[bourny]

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 Object

You 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 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

**********

#include <array.au3>

#include-once

Dim $sUsername = "sa"

Dim $sPassword = "lan"

Dim $sDatabase = "LDDB"

Dim $sServer = "SERVERNAME"

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

[PsaltyDS]

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

[bourny]

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.

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.

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