Sign in to follow this  
Followers 0
bourny

How to Display Object

5 posts in this topic

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)

Share this post


Link to post
Share on other sites



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.

:mellow:


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

Share this post


Link to post
Share on other sites

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.

:mellow:

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

Share this post


Link to post
Share on other sites

#4 ·  Posted (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.

:mellow:

Edited 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

Share this post


Link to post
Share on other sites

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.

:mellow:

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

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

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

Create an account

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


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0