MSSQL Connection Check

[Warmonger]

How would I go about checking a connection with SQL server, without autoit throwing a "AutoIt Error". Below is the connection string im using to connect to MSSQL. Basicly I want to check if SQL exists and is connect-to-able before running the querys.

$sqlCon = ObjCreate('ADODB.Connection')
$sqlCon.Open('DRIVER={SQL Server};SERVER=SERVER-0BEF6B28;DATABASE=AutoIt;UID=test;PWD=test;')
$Query_1 = "Insert Into VERSION Values (1, 'patch1.zip', 'patch1.zip', 0)"
$sqlCon.Execute($Query_1)

Error:

"The requested action with this object has failed"

$sqlCon.Open('DRIVER={SQL Server};SERVER=' & $Server & ';DATABASE=Character;UID=' & $User & ';PWD=' & $Pass & ';')

Edited May 7, 2011 by Warmonger

[smartee]

hi Warmonger,

Search the helpfile for ObjEvent(), you may want to do something like:

ObjEvent ( "AutoIt.Error" [, "function name"] )

Hope this helps,

-smartee

[Warmonger]

hi Warmonger,

Search the helpfile for ObjEvent(), you may want to do something like:

ObjEvent ( "AutoIt.Error" [, "function name"] )

Hope this helps,

-smartee

I'm still lost, having massive brain farts today, and not too familiar with objects yet. Maybe you could provide an example?

Edit: Would checking ADO states be of any help?

Example:

;~ Check If SQL Is Ready
    If $sqlCon.State = 0 Then 
        MsgBox(16, 'Error', 'Cannot Connect To SQL Server')
        Exit
    EndIf

Edited May 7, 2011 by Warmonger

[smartee]

hi,

Here's an example:

$oMyError = ObjEvent("AutoIt.Error", "MyErrFunc")
$ObjConn = ObjCreate("ADODB.Connection")
$ObjConn.open("SmarteeWackyMalformedParameters")
If @error Then
    ConsoleWrite("ObjOpen Failed dude :(")
Else
    ConsoleWrite("ObjOpen Success!! hahaha we did it :), Query away :)")
EndIf

Func MyErrFunc()
    ConsoleWrite("AutoItCOM Test" & @CRLF & "We intercepted a COM Error !" & @CRLF & _
            "err.description is: " & @TAB & $oMyError.description & @CRLF & _
            "err.windescription:" & @TAB & $oMyError.windescription & @CRLF & _
            "err.number is: " & @TAB & Hex($oMyError.number, 8) & @CRLF & _
            "err.lastdllerror is: " & @TAB & $oMyError.lastdllerror & @CRLF & _
            "err.scriptline is: " & @TAB & $oMyError.scriptline & @CRLF & _
            "err.source is: " & @TAB & $oMyError.source & @CRLF & _
            "err.helpfile is: " & @TAB & $oMyError.helpfile & @CRLF & _
            "err.helpcontext is: " & @TAB & $oMyError.helpcontext & @CRLF)
    SetError(1)
EndFunc   ;==>MyErrFunc

Hope this helps,

-smartee

[Warmonger]

hi,

Here's an example:

$oMyError = ObjEvent("AutoIt.Error", "MyErrFunc")
$ObjConn = ObjCreate("ADODB.Connection")
$ObjConn.open("SmarteeWackyMalformedParameters")
If @error Then
    ConsoleWrite("ObjOpen Failed dude :(")
Else
    ConsoleWrite("ObjOpen Success!! hahaha we did it :), Query away :)")
EndIf

Func MyErrFunc()
    ConsoleWrite("AutoItCOM Test" & @CRLF & "We intercepted a COM Error !" & @CRLF & _
            "err.description is: " & @TAB & $oMyError.description & @CRLF & _
            "err.windescription:" & @TAB & $oMyError.windescription & @CRLF & _
            "err.number is: " & @TAB & Hex($oMyError.number, 8) & @CRLF & _
            "err.lastdllerror is: " & @TAB & $oMyError.lastdllerror & @CRLF & _
            "err.scriptline is: " & @TAB & $oMyError.scriptline & @CRLF & _
            "err.source is: " & @TAB & $oMyError.source & @CRLF & _
            "err.helpfile is: " & @TAB & $oMyError.helpfile & @CRLF & _
            "err.helpcontext is: " & @TAB & $oMyError.helpcontext & @CRLF)
    SetError(1)
EndFunc   ;==>MyErrFunc

Hope this helps,

-smartee

Thank you, this works. I just couldnt understand the help files at the time. Now my next question is. How do you end a function?

Example:

Func _Function()
    $oMyError = ObjEvent("AutoIt.Error", "MyErrFunc")
    $sqlCon = ObjCreate('ADODB.Connection')
    $sqlCon.Open('DRIVER={SQL Server};SERVER=SERVER-0BEF6B28;DATABASE=AutoIt;UID=test;PWD=test;')
        If @error Then TCPSend($sSocket[$x], 'Server Error Code: 10060')
            ;~ End The Function Here
    $Query_1 = "Insert Into VERSION Values (1, 'patch1.zip', 'patch1.zip', 0)"
    $sqlCon.Execute($Query_1)
EndFunc

Is this the best approach? Or is there a "ExitFunc" lol

Global $z = 0

Func _Function()
    Do
        $oMyError = ObjEvent("AutoIt.Error", "MyErrFunc")
        $sqlCon = ObjCreate('ADODB.Connection')
        $sqlCon.Open('DRIVER={SQL Server};SERVER=SERVER-0BEF6B28;DATABASE=AutoIt;UID=test;PWD=test;')
            If @error Then TCPSend($sSocket[$x], 'Server Error Code: 10060')
                ;~ ExitLoop?
        $Query_1 = "Insert Into VERSION Values (1, 'patch1.zip', 'patch1.zip', 0)"
        $sqlCon.Execute($Query_1)
    Until $z = 0
EndFunc

Edited May 8, 2011 by Warmonger

[smartee]

hi again,

Glad it worked .

Functions automatically exit when the statements within it are finished, however you can force a function to exit anytime (and optionally return values) by using the "Return" keyword.

You can find more info on the structure of a function in the help file.

Hope this clears things up

-smartee

[ChrisL]

Have a look in my signature below there is some SQL stuff in there

[Warmonger]

Thanks. Problems solved.