Making a SQL Call from a Batch Handler

Ernest Noa
Ernest Noa
Fifth Anniversary Kudos 1 Name Dropper Participant
in Blackbaud CRM Developer Discussions

Is it possible to make a SQL call from a batch handler? I've got a line of code that looks like this.

Using conn As SqlConnection = Me.GetRequestContext.OpenAppDBConnection

This and the subsequent lines compile, but they do not work. When I run them I get a ‘stored procedure not found’. The stored procedure I am calling does work, I feel like the issue is there really is no OpenAppDBConnection I am grabbing here.

Thanks,

Ernie

Comments

  • Ernest Noa
    Ernest Noa
    Fifth Anniversary Kudos 1 Name Dropper Participant

    I got around my initial problem. I am not sure why the syntax is a little different but in an import handler this works:

    Using conn As SqlConnection = Me.GetRequestContext.OpenAppDBConnection

    In a batch handler, I need to change GetRequestContext to RequestContext like this.

    Using conn As SqlConnection = Me.RequestContext.OpenAppDBConnection()

    With that tiny change out of the way I am able to call the database as expected. My next problem is permissions \\ security related on the new stored procedure I want to call. If I deploy the procedure through TSQL then the procedure is not even found in my code, although I know it exists. If I deploy it through loadspec it shows up just fine but complains I do not have rights to run the procedure.

    I have reached out to someone responsible for permissions where I work. I am wondering does this require GRANT EXECUTE on the stored procedure at the database level or is Blackbaud somehow managing this. Things seem to work better when I operate using Blackbaud objects \\ and wrapers. I guess I will find out soon!

  • Ernest Noa
    Ernest Noa
    Fifth Anniversary Kudos 1 Name Dropper Participant

    Continuing to make lots of progress with my project. I have my data call moved into calls, that can be called from multiple batch handlers or import handlers. It needs to be tidied up but it works.

    I also have a bulk edit form. Essentially an edit form with two nested collection controls. I can call the class, and make the data call from this form, but it's causing major problems. I do not know where to make my call. I want to make my DB call and refresh one subfield in my collection on my edit form if an only if the user has changed another collection field.

    The user changes CONSTITUENTGUID field, then ALT ID Field is auto refreshed on the collection record.

    If I place this in the field changed event handler, the data refresh call is called with every keystroke (I think) and the page just spins and becomes non-responsive.

    If I place this call in the validated event handler, it works, but only if validation fails.

    Seems to me I want to place this call in a field or row loses or changes focus, but I don't see a handler like this.

    Most of my goals have been accomplished, I just can figure this last bit out.

    Here's the working code, that I am trying to add to the correct place.

    For Each row In GROUPMEMBERS.Value
    If row.Selected = True Then
    Try
    Dim myCONSTITUENTID As New Guid
    myCONSTITUENTID = Guid.Parse(row.MEMBERID.Value.ToString)

    If String.IsNullOrEmpty(myCONSTITUENTID.ToString) = False Then
    row.LOOKUPID.Enabled = True
    Dim conn As SqlConnection = Me.GetRequestContext.OpenAppDBConnection() 'Me.RequestContext.OpenAppDBConnection()
    Dim myUSRConstituent As New usr_myConstituent(myCONSTITUENTID, conn)

    row.LOOKUPID.Value = myUSRConstituent.LOOKUPID
    row.LOOKUPID.Enabled = False
    End If
    Catch
    End Try

    End If
    Next

  • Ernest Noa
    Ernest Noa
    Fifth Anniversary Kudos 1 Name Dropper Participant

    Looking at some other posts about accessing collection fields I was able to move this around and getting running. Here's my revised code.

    Private Sub _groupmembers_ActiveFieldChanged(sender As Object, e As ActiveFieldChangedEventArgs) Handles _groupmembers.ActiveFieldChanged
    For Each row In GROUPMEMBERS.Value
    If row.IsDirty = True Then
    Try

    Dim myCONSTITUENTID As New Guid
    myCONSTITUENTID = Guid.Parse(row.MEMBERID.Value.ToString)

    If String.IsNullOrEmpty(myCONSTITUENTID.ToString) = False Then
    Dim conn As SqlConnection = Me.GetRequestContext.OpenAppDBConnection()
    Dim myUSRConstituent As New usr_myConstituent(myCONSTITUENTID, conn)

    row.LOOKUPID.Value = myUSRConstituent.LOOKUPID
    row.ISDECEASED.Value = myUSRConstituent.ISDECEASED
    End If
    Catch
    End Try
    End If

    Next
    End Sub

  • Chris Whisenhunt
    Chris Whisenhunt
    Fourth Anniversary Name Dropper Participant Facilitator 1

    Set the following property at the top of your sproc spec.

    GrantServiceRolePermission="true"

  • Ernest Noa
    Ernest Noa
    Fifth Anniversary Kudos 1 Name Dropper Participant

    That got it done. Thank you!!!

Categories