Jump to content
Sign in to follow this  
Jimwright

Database ADO strangeness

Recommended Posts

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

Share this post


Link to post
Share on other sites
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.

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  

×