Error running stored procedure via _sql.au3

[EAM]

I work for a radiology department. I have to create a utlity to allow authorized users to view medical images without having to create an account for each user who needs to view the images.

The health system has a system that provides web-based access to patient information. The managers of that system have provided me with SQL to query their system to determine if a particular user is authorized to view radiology images. So my utility would authenticate the users against the health system AD, then check to see if the username is allowed to view images. If they are permitted, it would log into our image viewing application using a generic account.

Here is the SQL:

DECLARE @RC int

DECLARE @p_ADUsername varchar(32)

DECLARE @o_HasMVClinicalAccess smallint

DECLARE @o_ErrorMsg varchar(1000)

select @p_ADUsername = '<username>'

EXECUTE @RC = [MVSEC].[radiology].[LookupUser]

@p_ADUsername

,@o_HasMVClinicalAccess OUTPUT

,@o_ErrorMsg OUTPUT

select @RC, @o_HasMVClinicalAccess, @o_ErrorMsg

I've tested the SQL via a perl script on one of my Linux boxes, and it works fine there.

When I tried to test running the SQL via an autoit script using _sql.au3, I get no result & the error, "Operation is not allowed when the object is closed.", when _SQL_FetchData is called.

If I change $sql in the autoit script to "SELECT 6", the script runs, returns the "6" in the array, and produces no error.

I've attached the autoit script I've been using

ImageAccessTest.au3

[EAM]

Never mind. Found a that showed me how things should work. Now I have a working function:

expandcollapse popup

#include <IE.au3>
#include <_ADODBConstants.au3>

ImageAccess("testuser")
Func ImageAccess($ADUsername)
    local $result
    local $oIE
    local $dsn="DRIVER={SQL SERVER};SERVER=mvsqlprod;DATABASE=MVSEC;uid=user;pwd=password;"
    local $sproc="[MVSEC].[radiology].[LookupUser]"
    local $objConn = ObjCreate("ADODB.Connection")
    local $objCmd = ObjCreate("ADODB.Command")
    local $objParam = ObjCreate("ADODB.Parameter")

    ; Connect to MV DB
    $objConn.ConnectionString = $dsn
    $objConn.Open

    ; Set up command
    With $objCmd
        .ActiveConnection=$objConn
        .CommandText = $sproc
        .CommandType = $adCmdStoredProc
    ; Set up parameters
        $objParam = .CreateParameter("@RC", $adInteger, $adParamReturnValue)
        .Parameters.Append($objParam)

        $objParam = .CreateParameter("@p_ADUsername",$adVarChar,$adParamInput,32)
        $objParam.Value = $ADUsername
        .Parameters.Append($objParam)

        $objParam = .CreateParameter("@o_HasMVClinicalAccess",$adSmallInt,$adParamOutput)
        .Parameters.Append($objParam)

        $objParam = .CreateParameter("@o_ErrorMsg",$adVarChar,$adParamOutput,1000)
        .Parameters.Append($objParam)

        .Execute
        $ret = @error

        $result = .Parameters(2).Value
        if (.Parameters(2).Value <> 1) Then
             $oIE = _IECreate("about:blank",0,0,0,0)
             _IEDocWriteHTML($oIE, .Parameters(3).Value)
             MsgBox(0,"No Image Access for "&$ADUsername,_IEBodyReadText($oIE))
        EndIf
    EndWith

    $objConn.Close
    Return($result)

EndFunc