Database ADO strangeness

[Jimwright]

Hi

Long time lurker, thanks to everyone on this forum for the excellent support.

I've been using the ADO object to select/insert/update databases very successfully with Autoit using inline sql. However I have a strange situation here where SQL statements held externally in a file execute against a DB (I can see from SQL Profiler that the SQL has been sent) but the results dont come back to me. Here is a simplified version of my Autoit code:

CODE

;;SQL Connection

$ConnectionString="DRIVER={SQL Server};SERVER=.;DATABASE=master;Trusted_Connection=yes;"

$oMyError = ObjEvent("AutoIt.Error","MyErrFunc")

$oConn = ObjCreate("ADODB.Connection")

$oConn.Open($ConnectionString)

;;;SQL Connection

;;Read in script

$FileContents=Fileread(@scriptdir&"\Test.sql")

;Send sql to DB

$oRS_FireSQL = ObjCreate("ADODB.Recordset")

$oRS_FireSQL.open($Filecontents, $oConn)

msgbox(0,"",$oRS_FireSQL.Fields("Results").value)

;Capture object errors

Func MyErrFunc()

$HexNumber=hex($oMyError.number,8)

Msgbox(16,"","A Database ADO error has occured." & @CRLF & _

"Error: " & $HexNumber & @CRLF & _

"(" & $oMyError.source & ")" & @CRLF & _

$oMyError.description)

Endfunc

and here is the current contents of my test script:

CODE

Declare @string as varchar(10)

Set @string='Hello'

Select @String as 'Results'

The sql is fired but my msgbox is blank! If i change the test sql to:

CODE

Select 'Hello' as 'Results'

then it works fine! Ive searched the microsoft site for help with the recordset object but no joy. Its really vital that I can use declare and set in the sql im reading in, I dont understand why the sql is sent but the result doesnt make it back to autoit!

Very grateful for any pointers, and thank you for your time

[Jimwright]

Well I think Ive "solved" the issue, but may have opened another can of worms for myself! The problem seems to be that because the opening line of the SQL (Declare) is not an action that returns a recordset the SQL (although executing on the server) does not return any results to the client. As a getaround Ive added the Execute command to my SQL:

Execute('Declare @string as varchar(10)

Set @string=''Hello''

Select @string as Results')

Of course this means that any strings need to be escaped with double " ' ". Ive also found that importing sql commands via a file causes an additional error due to carriage returns. To getaround this (rather then rewriting my SQL) Ive used this to run the SQL in Autoit:

$oRS_FireSQL.open(stringreplace($Filecontents,@crlf," "), $oConn)

Sadly the real SQL i'll be executing is an order of magnitude more complex, so this might get tricky!

Thanks to all who stopped by, I hope this is useful to other people at some point.