Need Help on this SQLConnect Function

[Syed23]

Hi Friends,

I have downloaded _SQL.au3 from this forum. Currently i have Database server name , user credentials to connect but i am getting confused with the below function how to make it to work. can some one help me on this? Also does Auto it support ADO - ODBC? or do we need to install any driver or something ? please help me!

expandcollapse popup

; #FUNCTION# =================================================================== 
; Name ..........: _SQL_Connect 
; Description ...: Starts a Database Connection 
; Syntax.........: _SQL_Connect($ADODBHandle,$server, $db, $username, $password) 
; Parameters ....: $ADODBHandle - ADODB.Connection handle. 
; $server - The server to connect to. 
; $db - The database to open. 
; $username - username for database access. 
; $password - password for database user. 
; Return values .: On Success - Returns $SQL_OK 
; On Failure - Returns $SQL_ERROR and $SQLErr is set. 
; .Use _SQL_GetErrMsg() to get text error information 
; Author ........: Chris Lambert 
; Modified ......: 
; Remarks .......: 
; Related .......: 
; Link ..........; 
; Example .......; no 
; ============================================================================== 
Func _SQL_Connect($ADODBHandle, $server, $db, $username, $password, $SQLAuth = True) 

$SQLErr = "" 
If $ADODBHandle = -1 Then $ADODBHandle = $SQL_LastConnection 

If Not IsObj($ADODBHandle) Then 
$SQLErr = "Invalid ADODB.Connection object, use _SQL_Startup()" 
Return SetError($SQL_ERROR, 0, $SQL_ERROR) 
EndIf 

If $SQLAuth = True then 
$ADODBHandle.Open("DRIVER={SQL Server};SERVER=" & $server & ";DATABASE=" & $db & ";uid=" & $username & ";pwd=" & $password & ";") ;<==Connect with required credentials 
Else 
$ADODBHandle.Properties("Integrated Security").Value = "SSPI" 
$ADODBHandle.Properties("User ID") = $username 
$ADODBHandle.Properties("Password") = $password 
$ADODBHandle.Open("DRIVER={SQL Server};SERVER=" & $server & ";DATABASE=" & $db ) 
EndIf 


If Not @error Then 
Return SetError($SQL_OK, 0, $SQL_OK) 
Else 
$SQLErr = "Connection Error" 
Return SetError($SQL_ERROR, 0, $SQL_ERROR) 
EndIf 
EndFunc ;==>_SQL_Connect

Edited August 5, 2011 by Syed23

[hannes08]

Hi Syed,

try:

_SQL_RegisterErrorHandler()
$oADODB = _SQL_Startup()
If $oADODB = $SQL_ERROR Then
    MsgBox(0 + 16, "SQL Error", "Error: " & _SQL_GetErrMsg())
    Exit 3
EndIf

If _sql_Connect(-1, "host", "database", "user", "password") = $SQL_ERROR Then
    MsgBox(0 + 16 + 262144, "Error", _SQL_GetErrMsg())
    _SQL_Close()
    Exit 2
EndIf

[Syed23]

Hi Syed,

try:

_SQL_RegisterErrorHandler()
$oADODB = _SQL_Startup()
If $oADODB = $SQL_ERROR Then
    MsgBox(0 + 16, "SQL Error", "Error: " & _SQL_GetErrMsg())
    Exit 3
EndIf

If _sql_Connect(-1, "host", "database", "user", "password") = $SQL_ERROR Then
    MsgBox(0 + 16 + 262144, "Error", _SQL_GetErrMsg())
    _SQL_Close()
    Exit 2
EndIf

Thanks for the quick reply! i have a quick question, in this Example what is the Host = local machine name ? or what ?

Edited August 5, 2011 by Syed23

[Syed23]

Hi Hannes123,

I got the below error message. Could you please tell me why it happens?

###############################

err.description is: [Microsoft][ODBC SQL Server Driver][sql Server]Cannot open database requested in login 'Server Name'. Login fails.

err.windescription: Unspecified error

err.number is: 80020009

err.lastdllerror is: 0

err.scriptline is: 220

err.source is: Microsoft OLE DB Provider for ODBC Drivers

err.helpfile is:

err.helpcontext is: 0###############################

###############################

err.description is: Operation is not allowed when the object is closed.

err.windescription:

err.number is: 80020009

err.lastdllerror is: 0

err.scriptline is: 373

err.source is: ADODB.Connection

err.helpfile is: C:\Windows\HELP\ADO270.CHM

err.helpcontext is: 1240653###############################

!>17:04:51 AutoIT3.exe ended.rc:2

>Exit code: 2 Time: 6.387

Edited August 5, 2011 by Syed23

[hannes08]

Host is the IP or DNS name of the PC/Server running the SQL Database server service.

[Syed23]

quote name='Hannes123' timestamp='1312544608' post='916031']

Host is the IP or DNS name of the PC/Server running the SQL Database server service.

Edited August 5, 2011 by Syed23

[hannes08]

Well, I'd say you've forgotten to set the database name.

_sql_Connect(-1, "servername", "my_database", "username", "password")

[Syed23]

Well, I'd say you've forgotten to set the database name.

_sql_Connect(-1, "servername", "my_database", "username", "password")

you are correct it worked now Thanks a lot i will get back to you soon with another question

[Syed23]

Hannes123,

Now i am executing the SQL query with the function _SQLExecute() the code executes without error but i don't how to display the result..... please help me!

[hannes08]

Hello Syed,

depending on what you are doing, you'll need _SQLExecute or _SQL_Query* functions.

Use _SQLExecute for statements like UPDATE, INSERT or DELETE.

Use _SQL_Query* functionS for SELECT statements. Typically you'll recieve a 1 or 2 dimensional array containing the results.

[Syed23]

Hello Syed,

depending on what you are doing, you'll need _SQLExecute or _SQL_Query* functions.

Use _SQLExecute for statements like UPDATE, INSERT or DELETE.

Use _SQL_Query* functionS for SELECT statements. Typically you'll recieve a 1 or 2 dimensional array containing the results.

Hi Hannes,

As you suggested i found a function _SQL_QuerySingleRow(). But the problem here is it returns only a single row of the data. But where i have multiples rows on it. How to overcome this? Also i have found a query like parsing with XML like

"SQL Query" FOR XML AUTO....

is there a way to parse or save it as a XML ?

[hannes08]

Here's a little example of what I did:

Dim $iRval, $aData, $iRows, $iColumns, $t
$t = TimerInit()
$iRval = _SQL_GetTable2D($oADODB,"SELECT * FROM FDB WHERE MAC='001122334455';",$aData,$iRows,$iColumns)
If $iRval = $SQL_OK then
    $t = _Timer_Diff($t)
    GUICtrlSetData($Label4,"Query took " & Round($t_1,2)/1000 & " Seconds")
    For $i = 1 To $iRows
    ConsoleWrite:($aData[$i][0] & "|" & $aData[$i][2] & "|" & $aData[$i][1] & @CRLF)
    Next
Else
    $t = 0
EndIf

[Syed23]

Here's a little example of what I did:

Dim $iRval, $aData, $iRows, $iColumns, $t
$t = TimerInit()
$iRval = _SQL_GetTable2D($oADODB,"SELECT * FROM FDB WHERE MAC='001122334455';",$aData,$iRows,$iColumns)
If $iRval = $SQL_OK then
    $t = _Timer_Diff($t)
    GUICtrlSetData($Label4,"Query took " & Round($t_1,2)/1000 & " Seconds")
    For $i = 1 To $iRows
    ConsoleWrite:($aData[$i][0] & "|" & $aData[$i][2] & "|" & $aData[$i][1] & @CRLF)
    Next
Else
    $t = 0
EndIf

Hey Hannes123,

Thanks a lot This what i wanted and it works perfectly