7 replies to this topic

[sandeep]

HI,
We are trying to import our execution results to our existing SQL database.
From the existing forum and help i came to know that ,AUTOIT supports SQLite.

so can somebody help us in finding a solution how to connect AUTOIT to current SQL database.
I beleive it can be done using ADO via COM,but it would be really appreciated that if somebosy can help us giving more knowledge in implimenting this.


Thanks in Advance
Sandeep

Edited by sandeep, 13 June 2007 - 11:18 AM.

[Locodarwin]

A while ago someone etched out a few basic functions for SQL connections, queries, and disconnections via ADO COM. I rewrote/adapted them to suit my needs:

AutoIt        expandcollapse  popup

#include-once


;===============================================================================
;
; 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

Hopefully the comment headers will be enough to get you going. The functions work great, and I use them every day to connect to an enterprise MSSQL database to perform simple queries.

Good luck!

-S

[sandeep]

HI Thanks for the help.
But i dont see anything happening ,when i run the script.
i m currently using v3.2.4.9
should i need to try it on the beta release...
Can u xplain more on this ..

Thanks in advance
Sandi

A while ago someone etched out a few basic functions for SQL connections, queries, and disconnections via ADO COM. I rewrote/adapted them to suit my needs:

AutoIt        expandcollapse  popup

#include-once
;===============================================================================
;
; 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

Hopefully the comment headers will be enough to get you going. The functions work great, and I use them every day to connect to an enterprise MSSQL database to perform simple queries.

Good luck!

-S

[Locodarwin]

These are just functions that are called. On their own, they do nothing. Either you'd paste them verbatim into your main script, or you'd save them off to another file and #include said file in your main script.

Teaching you how to program in AutoIt is beyond the scope of my help, unfortunately. I would advise you to start learning with smaller, bite-sized examples and work your way up. In the meantime, if you need this script right away, I'd suggest you post your request in the General Help and Support area with an offer to make a small donation to AutoIt. Likely someone will be happy to work with you.

-S

[sandeep]

These are just functions that are called. On their own, they do nothing. Either you'd paste them verbatim into your main script, or you'd save them off to another file and #include said file in your main script.

Teaching you how to program in AutoIt is beyond the scope of my help, unfortunately. I would advise you to start learning with smaller, bite-sized examples and work your way up. In the meantime, if you need this script right away, I'd suggest you post your request in the General Help and Support area with an offer to make a small donation to AutoIt. Likely someone will be happy to work with you.

-S

Thanks for the help..
I m working on Autoit from past some time..

and i figured out the reason why it was not working also.
Nothing wrong with the code but my ADODB.connection
...

I m gonna ask a help last time form you.http
I am not able to conneect to my SQL server,it fails when i m trying to connect..
basically

$sqlCon = ObjCreate("ADODB.Connection")
$sqlCon.Open("ONEFLOWQA-08","qaFieryapps","Autoit","autoit")

if @error Then
MsgBox(0, "ERROR", "Failed to connect to the database")
Exit
Else
MsgBox(0, "Success!", "Connection to database successful!")
EndIf


Any help will be great ,if i can come to know how to figure out whats wrong?

Thnaks Sandi

Edited by sandeep, 18 June 2007 - 05:14 AM.

[Locodarwin]

The .Open() method accepts only one string as a representation of your connection.

Replace your .Open() line with this one:

$sqlCon.Open("DRIVER={SQL Server};SERVER=ONEFLOWQA-08;DATABASE=qaFieryapps;UID=Autoit;PWD=autoit;")

NOTE: The "DRIVER=" portion of the string above assumes you're wanting to connect with the MSSQL driver. You'll have to change that if you are using an engine that doesn't use that same string.

-S

[sandeep]

The .Open() method accepts only one string as a representation of your connection.

Replace your .Open() line with this one:

$sqlCon.Open("DRIVER={SQL Server};SERVER=ONEFLOWQA-08;DATABASE=qaFieryapps;UID=Autoit;PWD=autoit;")

NOTE: The "DRIVER=" portion of the string above assumes you're wanting to connect with the MSSQL driver. You'll have to change that if you are using an engine that doesn't use that same string.

-S

Thanks..looks like the code is working now...


Sandi

[Tomasz]

how to get e result of query?