Autoit connection with SQL database

[Sonia]

Hi,

I want Autoit connection with SQL server database. I tried this by two ways, but connection didnt established even with one way. Please have review of my codes and tell me where is problem?

Following is my Ist code:

#include <SQLite.au3>

#include <SQLite.dll.au3>

Local $hQuery, $aRow, $sMsg

$conn = ObjCreate( "ADODB.Connection" )

$DSN = "DRIVER={SQL Server};SERVER=ORANGE;DATABASE=Northwind;UID=sa;PWD=sa;"

$open =$conn.Open($DSN)

_SQLite_Exec (-1, "create table uni(id (varchar(20));") ; INSERT Data

_SQLite_Exec (-1, "INSERT INTO dbo.uni VALUES ('ccc');") ; INSERT Data

_SQlite_Query (-1, "SELECT id FROM uni ;", $hQuery) ; the query

While _SQLite_FetchData ($hQuery, $aRow) = $SQLITE_OK

$sMsg &= $aRow[0]

WEnd

MsgBox(0,"SQLite","Get Data using a Query : " & $sMsg )

$conn.close

I also tried this code but connection didn't established. Following is 2nd code:

#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

MsgBox(0, "Connection object does not exists", "")

Return SetError(3, 0, 0)

EndIf

MsgBox(0, "Information", $sDriver & @CRLF & $sServer & @CRLF & $sDatabase & @CRLF & $sUsername)

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

$sServer = "ORANGE"

$sDatabase = "Northwind"

$fAuthMode = 0

$sUsername = "sa"

$sPassword = "sa"

$sDriver = "{SQL Server}"

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

$rs = _SQLQuery($oConn, "select * from dbo.abc")

Sonia,

[Spiff59]

You're getting an @error condition on the .open statement?

I assume SQLite is installed and running on the remote machine (server)?

And you've installed the ODBC driver and made an ODBC entry to the database on the local machine?

If you can't make the ODBC entry, checked for firewall isuues?

Edited January 20, 2009 by Spiff59

[Sonia]

You're getting an @error condition on the .open statement?

I assume SQLite is installed and running on the remote machine (server)?

And you've installed the ODBC driver and made an ODBC entry to the database on the local machine?

If you can't make the ODBC entry, checked for firewall isuues?

Yeah! I m getting error on the following statement in __SQLConnect function:

If NOT $fAuthMode Then $oConn.Open("DRIVER=" & $sDriver & ";SERVER=" & $sServer & ";DATABASE=" & $sDatabase)

information of my machine is:

$sServer = "ORANGE"

$sDatabase = "Northwind"

$fAuthMode = 0

$sUsername = "sa"

$sPassword = "sa"

$sDriver = "{SQL Server}"

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

I have another question that if database server is "Postgres" then _SQLConnect function would work for database connection or not? And if this funtion would not work then how can we have "Postgres" connection with Autoit?

[Spiff59]

Ouch... those includes are written for SQLite.

I have no experience with Postgres, their website says:

3.2) How do I control connections from other hosts?

By default, PostgreSQL only allows connections from the local machine using Unix domain sockets or TCP/IP connections. Other machines will not be able to connect unless you modify listen_addresses in the postgresql.conf file, enable host-based authentication by modifying the $PGDATA/pg_hba.conf file, and restart the database server.

I've had success using ADO in Autoit to access an Advantage Database Server using first ODBC, then later OLEDB. I'm not sure what Postgres will require. Maybe someone else will chime in.

Edited January 20, 2009 by Spiff59