Sign in to follow this  
Followers 0
EAM

Error running stored procedure via _sql.au3

2 posts in this topic

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

Share this post


Link to post
Share on other sites



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

#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

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