0 Replies Latest reply on Jun 24, 2004 12:38 AM by 155274

    NotifyDBEvents Problem

      I have tried to implement database event notification using the NotifyDBEvents interface on a callback handler setup using the example in the Database Events section of the Oracle Objects for OLE Release 9.2 documentation.

      The Subscription.Add and .Register methods appear to be working but the database events are never raised on the callback handler.

      The following code does not generate any errors but also does not cause the database events to be raised.

      Has anyone figured out how to get this to work?

      Public DBEventsHdlr As DBEventCls
      Public gOraSession As Object
      Public gOraSubscriptions As OraSubscriptions
      Public gOraDatabase As OraDatabase
      Public gOraQueue As OraAQ

      Private Sub Command3_Click()
      On Error GoTo ErrHandler

      'First instantiate the dbevent handler. The dbevent notification
      'will fire the NotifyDBEvents on the callback handler.
      Set DBEventsHdlr = New DBEventCls

      'Create the OraSession Object
      Set gOraSession = CreateObject("OracleInProcServer.XOraSession")

      'Create the OraDatabase Object by opening a connection to Oracle.
      Set gOraDatabase = gOraSession.DbOpenDatabase("database", _
      "user/password", _

      Set gOraSubscriptions = gOraDatabase.Subscriptions

      gOraSubscriptions.Add "schema.queue:consumer", _
      DBEventsHdlr, _


      Command3.Enabled = False
      Command4.Enabled = True
      Exit Sub

      MsgBox "Last Database Error" & vbCrLf & _
      CStr(gOraDatabase.LastServerErr) & vbCrLf & _
      gOraDatabase.LastServerErrText & vbCrLf & _
      "Last Session Error" & vbCrLf & _
      CStr(gOraSession.LastServerErr) & vbCrLf & _
      GoTo ExitSub
      End Sub

      Private Sub Command4_Click()
      On Error GoTo ErrHandler
      Set gOraSubscriptions = Nothing
      Set gOraDatabase = Nothing
      Set gOraSession = Nothing

      Command4.Enabled = False
      Command3.Enabled = True
      Exit Sub

      MsgBox gOraDatabase.LastServerErrText
      Resume Next
      End Sub

      The CallBack code is:

      Public Sub NotifyDBEvents(Ctx As Variant, Payload As Variant)
      Dim OraQ As OraAq
      Dim OraDB As OraDatabase
      Dim OraMsg As OraAQMsg
      Dim OraObj As OraObject

      On Error GoTo ErrHandler

      Set OraDB = Ctx

      'Create an instance of the OraAQ object
      Set OraQ = OraDB.CreateAQ("schema.queue")

      'Set remove message from queue
      OraQ.DequeueMode = ORAAQ_DQ_REMOVE

      'Set do not wait if no message
      OraQ.Wait = ORAAQ_DQ_NOWAIT

      'Create an instance of the OraAQMsg object
      Set OraMsg = OraQ.AQMsg(ORATYPE_OBJECT, "MESSAGE_TYPE", "schema")

      'Get Message
      Set OraObj = OraMsg.Value
      MsgBox "SUBJECT Data = " & "|" & OraObj.Item(1).Value & "|" & OraObj("SUBJECT").Value & "|" & vbCrLf & _
      "TEXT Data = " & "|" & OraObj.Item(2).Value & "|" & OraObj("TEXT").Value & "|", vbOKOnly, "NotifyMe"

      Set OraObj = Nothing
      Set OraMsg = Nothing
      Exit Sub

      MsgBox "NotifyDBEvents Error"
      GoTo ExitSub
      End Sub