Sign in to follow this  
Followers 0
gogo

Running a SQL script

3 posts in this topic

I'm not very familiar with Autoit but I'm hoping to get some feedback to help me get through this.

I recently found a script online and I'm getting some errors:

C:\Documents and Settings\My Documents\_sql.au3 (71) : ==> Only Object-type variables allowed in a "With" statement.:

With $objquery

With ^ ERROR

>Exit code: 1 Time: 10.681"

I need to start an SQL instance and run a script. Here's what I have so far:

;#include-once

Local $ConHandle,$adCN,$sqllastConnection

Func _DisplayError($vText)

MsgBox(0 + 16 + 262144, "SQL Error", $vText)

ConsoleWrite($vText)

EndFunc ;==>_DisplayError

Func _SQLRegisterErrorHandler($Func = "_SQLErrFunc")

If ObjEvent("AutoIt.Error") = "" Then $MSSQLObjErr = ObjEvent("AutoIt.Error", $Func)

EndFunc ;==>_SQLRegisterErrorHandler

Func _SQLUnRegisterErrorHandler()

$MSSQLObjErr = ""

EndFunc ;==>_SQLUnRegisterErrorHandler

Func _SQLStartup()

Dim $SQLErr

$adCN = ObjCreate("ADODB.Connection");<==Create SQL connection

If IsObj($adCN) Then

$sqlLastConnection = $adCN

Return $adCN

Else

Return SetError(1, 0, 0)

EndIf

EndFunc ;==>_SQLStartup

Func _SQLConnect($ConHandle, $server, $db, $username, $password)

Dim $SQLErr

If $ConHandle = -1 Then $ConHandle = $sqlLastConnection

If $username = "" And $password = "" Then

$ConHandle.Open("DRIVER={SQL Server Native Client 10.0};SERVER=" & $server & ";DATABASE=" & $db & ";uid=" & $username & ";pwd=" & $password & ";");<==Connect with required credentials

Else

$ConHandle.Open ("DRIVER={SQL Server};SERVER=" & $server & ";DATABASE=" & $db & ";uid=" & $username & ";pwd=" & $password & ";") ;<==Connect with required credentials

Endif

If Not @error Then Return 1

Return SetError(1, 0, 0)

EndFunc ;==>_SQLConnect

Func _SQLClose($ConHandle = -1)

If $ConHandle = -1 Then $ConHandle = $sqlLastConnection

$ConHandle.Close

EndFunc ;==>_SQLClose

Func _SQLExecute($ConHandle = -1, $query = "")

Dim $SQLErr

Local $ret

If $ConHandle = -1 Then $ConHandle = $sqlLastConnection

$ret = $ConHandle.Execute($query)

If @error Then Return SetError(1, 0, 0)

Return $ret

EndFunc ;==>_SQLExecute

Func _SQLGetDataAsString($objquery, $ReturnColumnNames = 1, $delim = "|")

If Not IsObj($objquery) Then

$SQLErr = "Data passed is an invalid object"

Return SetError(1, 0, 0)

EndIf

Dim $ret

Local $i

With $objquery

If $ReturnColumnNames Then

For $i = 0 To .Fields.Count - 1;get the column names and put into 0 array element

$ret &= .Fields($i).Name & $delim

Next

If StringRight($ret, 1) = $delim Then $ret = StringTrimRight($ret, 1)

$ret &= @CRLF

EndIf

While Not .EOF

For $i = 0 To .Fields.Count - 1

$ret &= .Fields($i).Value & $delim

Next

If StringRight($ret, 1) = $delim Then $ret = StringTrimRight($ret, 1)

$ret &= @CRLF

.MoveNext; Move to next row

WEnd

EndWith

Return $ret

EndFunc ;==>_SQLGetDataAsString

Func _SQLGetData2D($objquery, $ReturnColumnNames = 1)

If Not IsObj($objquery) Then

$SQLErr = "Data passed is an invalid object"

Return SetError(1, 0, 0)

EndIf

Dim $ret

Dim $SQLErr

Local $i, $aTmp

With $objquery

$ret = .GetRows()

If IsArray($ret) Then

$Dims = UBound($ret, 2)

$Rows = UBound($ret)

If $ReturnColumnNames Then

ReDim $ret[$Rows + 1][$Dims];Adjust the array to fit the column names and move all data down 1 row

For $x = $Rows To 1 Step -1

For $y = 0 To $Dims - 1

$ret[$x][$y] = $ret[$x - 1][$y]

Next

Next

;Add the coloumn names

For $i = 0 To $Dims - 1;get the column names and put into 0 array element

$ret[0][$i] = .Fields($i).Name

Next

EndIf;$ReturnColumnNames

Else

SetError(2)

$SQLErr = "Unable to retreive data"

EndIf;IsArray()

;Old method not used anymore but left in commented out until the new method is proven to have no issues

;While NOT .EOF

; ReDim $ret[uBound($ret, 1) + 1][ubound($ret,2)]; get each row of data

; For $i = 0 To .Fields.Count - 1

; $ret[uBound($ret, 1) - 1][$i] = .Fields($i).Value

; Next

;.MoveNext; Move to next row

;WEnd

EndWith

Return $ret

EndFunc ;==>_SQLGetData2D

;custom error handler

Func _SQLErrFunc()

$SQLErr = $MSSQLObjErr.description

SetError(1)

EndFunc ;==>_SQLErrFunc

Share this post


Link to post
Share on other sites



So, when you say you found that script online, did you mean you got it from these forums, cos that looks like my UDF set.

Also there is no way that script will do anything on its own it's just bits of my UDF set.

The latest version and an example can be found here http://www.autoitscript.com/forum/index.php?showtopic=51952

Share this post


Link to post
Share on other sites

So, when you say you found that script online, did you mean you got it from these forums, cos that looks like my UDF set.

Also there is no way that script will do anything on its own it's just bits of my UDF set.

The latest version and an example can be found here http://www.autoitscript.com/forum/index.php?showtopic=51952

Yes the script was from the forums but as you said it's an older one. I will try out the new script and see if that works.

thx

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