Recurrence &#101;vents with MySQL

Thanks(0)

Quote

Topic: Recurrence events with MySQL
Posted: 04 October 2006 at 5:55pm

I'm using custom provider with a MySQL database and Visual Basic .NET. I have done the function which create and save in DB a recurrence event. But when I try to retrive the recurrence events I get this error in calendarcontrol.populate():

"FatalExecutionEngineError was detected
Message: The runtime has encountered a fatal error. The address of the error was at 0x79f1c189, on thread 0xf94. The error code is 0xc0000005. This error may be a bug in the CLR or in the unsafe or non-verifiable portions of user code. Common sources of this bug include user marshaling errors for COM-interop or PInvoke, which may corrupt the stack."

Some ideas?

luisluis

Newbie

Joined: 04 October 2006
Status: Offline
Points: 11

Post Options

Thanks(0)

Quote

Posted: 05 October 2006 at 6:49pm

Does someone have an example in Visual Basic .NET of DoRetrieveDayEvents function?

I have done this

Private Sub CalendarControl_DoRetrieveDayEvents(ByVal sender As Object, ByVal e As AxXtremeCalendarControl._DCalendarControlEvents_DoRetrieveDayEventsEvent) Handles m_pCalendar.DoRetrieveDayEvents
        Try
            abrir_DB()

            Dim strSQL As String
            strSQL = m_pHelper.MakeRetrieveDayEventsSQL(e.dtDay)

            myCommand.Connection = miconexion
            myCommand.CommandText = strSQL

            MyDataReader = myCommand.ExecuteReader

            Dim pEvent As CalendarEvent

            While MyDataReader.Read()
                pEvent = m_pHelper.CreateEventFromRS(MyDataReader, False)
                If Not pEvent Is Nothing Then
                    e.events.Add(pEvent)
                End If
            End While

        Catch myerror As OdbcException
            MessageBox.Show(myerror.Message, "Error conectando a la base de datos", MessageBoxButtons.OK, MessageBoxIcon.Error)
            dispose_DB()
        Catch myerror As Exception
            MessageBox.Show(myerror.Message, "Error al leer evento", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Finally
            If estado_DB <> ConnectionState.Closed Then cerrar_DB()
        End Try

    End Sub

Public Function MakeRetrieveDayEventsSQL(ByVal dtDay As Date) As String

        Dim nYear As Long, nMonth As Long, nDay As Long
        Dim strSQL As String

        nYear = Year(dtDay)
        nMonth = Month(dtDay)
        nDay = DatePart("d", dtDay)

        strSQL = "SELECT * FROM CalendarEvents WHERE " & vbCrLf

        strSQL = strSQL + " ( RecurrenceState = " & CalendarEventRecurrenceState.xtpCalendarRecurrenceNotRecurring & " OR " & vbCrLf
        strSQL = strSQL + "   RecurrenceState = " & CalendarEventRecurrenceState.xtpCalendarRecurrenceMaster & ") AND " & vbCrLf

        strSQL = strSQL + "( YEAR(StartDateTime) < " & nYear & vbCrLf
        strSQL = strSQL + " OR ( YEAR(StartDateTime) = " & nYear & " AND " & vbCrLf
        strSQL = strSQL + "       MONTH(StartDateTime) < " & nMonth & " OR "
        strSQL = strSQL + "       MONTH(StartDateTime) = " & nMonth & " AND " & vbCrLf
        strSQL = strSQL + "       DAY(StartDateTime) <= " & nDay & vbCrLf

        strSQL = strSQL + "     ) ) AND " & vbCrLf

        strSQL = strSQL + "( YEAR(EndDateTime) > " & nYear & vbCrLf
        strSQL = strSQL + " OR ( YEAR(EndDateTime) = " & nYear & " AND " & vbCrLf
        strSQL = strSQL + "       MONTH(EndDateTime) > " & nMonth & " OR "
        strSQL = strSQL + "       MONTH(EndDateTime) = " & nMonth & " AND " & vbCrLf
        strSQL = strSQL + "       DAY(EndDateTime) >= " & nDay & vbCrLf
        strSQL = strSQL + "     ) ) " & vbCrLf

        'Debug.Print strSQL

        MakeRetrieveDayEventsSQL = strSQL
    End Function

sserge

Moderator Group

Joined: 01 December 2004
Status: Offline
Points: 1297

Post Options

Thanks(0)

Quote

Posted: 06 October 2006 at 9:17am

Hi,

Could you provide some additional information about problem.
Only with code it's hard to tell somthing.

Does non-recurrence events works fine for you?

Try to comment some parts of code to find which of them give an error.

For a test try to write a code for DoRetrieveDayEvents which will not use DB, just create and return a single event for each day with start time as "now".

Let's go step by step to localize a problem.

--
WBR,
Serge

luisluis

Newbie

Joined: 04 October 2006
Status: Offline
Points: 11

Post Options

Thanks(0)

Quote

Posted: 06 October 2006 at 11:25am

sserge wrote:

Hi,
Does non-recurrence events works fine for you?

Yes, it works perfectly in non-recurrence events.

I will try to be more specific. To make my example I use as template the Visual Basic 6 example that comes with Codejock Xtreme SuitePro ActiveX 2006.

----------------------

I have defined a class "clsproviderMySQL"

Public Sub abrir_DB()
        conn_string = "DSN=ejemplo;" & _
        "Uid=root;" & _
        "Pwd=112233;"
        miconexion = New OdbcConnection(conn_string)
        miconexion.Open()
    End Sub

Public Sub SetCalendar(ByVal pCalendar As AxXtremeCalendarControl.AxCalendarControl)
        m_pCalendar = pCalendar
        m_pHelper.SetCalendar(pCalendar)
    End Sub

m_pHelper is another class.

-----------------------------

When the main form is loaded:

        Public Connectionstring As String = "Provider=Custom;DSN=ejemplo"
        Private mysql As New clsproviderMySQL
        mysql.abrir_DB()
        mysql.SetCalendar(calendarcontrol)
        calendarcontrol.SetDataProvider(Connectionstring)
        calendarcontrol.DataProvider.CacheMode = CalendarDataProviderCacheMode.xtpCalendarDPCacheModeOnRepeat

        'If the Data Source can not be opened, then create a new data source
        If Not calendarcontrol.DataProvider.Open Then
            'Create a new data source to use.
            calendarcontrol.DataProvider.Create()
        End If

        'm_eActiveDataProvider = eDataProviderType

        calendarcontrol.DayView.ScrollToWorkDayBegin()
        calendarcontrol.Populate() <------- ERROR

-----------------------

Tell me if I was clear enough.

sserge

Moderator Group

Joined: 01 December 2004
Status: Offline
Points: 1297

Post Options

Thanks(0)

Quote

Posted: 07 October 2006 at 5:49am

Hi,

Well, I suspect that the recurrence data is not stored/readed correctly.
Calendar itself does not analyze recurrence pattern properties for having incorrect values, and if there are some "crazy" values it might work like this (validation of input values is performed by user interface method - when Ok button at form is pressed, and so on).

I would suggest you to debug your DoCreateRPattern, DoUpdateRPattern, DoReadRPattern handlers (did you implemented them?) and check that all pattern data is correctly stored in DB.

Validate that recurrence pattern properties, specifically Options, have a correct meaning.

Also you can add some error checking handlers.

Note that recurrence event has a complex structure. Briefly:
<Master Event>
|- <Recurrence Pattern>
|- <Recurrence exceptions collection>

For more information on recurrence events you can search this forum for 'recurrence' keyword.

--
WBR,
Serge

luisluis

Newbie

Joined: 04 October 2006
Status: Offline
Points: 11

Post Options

Thanks(0)

Quote

Posted: 09 October 2006 at 11:58am

In the Visual Basic 6 example there is this sub:

Public Sub ReadRPatternExceptions(pPattern As CalendarRecurrencePattern, pConnection As ADODB.Connection)

    Dim nPatternID As Long
    Dim strSQL As String

    nPatternID = pPattern.Id

    strSQL = "SELECT * FROM CalendarEvents WHERE " & vbCrLf

    strSQL = strSQL + " RecurrenceState = " & xtpCalendarRecurrenceException & " AND " & vbCrLf
    strSQL = strSQL + " RecurrencePatternID = " & nPatternID

    Dim rsEvents As ADODB.Recordset
    Set rsEvents = pConnection.Execute(strSQL)

    Dim pEvent As CalendarEvent

    While Not rsEvents.EOF
        Set pEvent = CreateEventFromRS(rsEvents, True)
        If Not pEvent Is Nothing Then
            pPattern.SetException pEvent
        End If

        rsEvents.MoveNext
    Wend

End Sub

Which is the handler I have to implement for this sub?

sserge

Moderator Group

Joined: 01 December 2004
Status: Offline
Points: 1297

Post Options

Thanks(0)

Quote

Posted: 10 October 2006 at 7:47am

As it is described in a previous message, recurrence pattern has its own exceptions collection.
|- <Recurrence Pattern>
     |- <Recurrence exceptions collection>

When you create a new recurrence event it consists of a master event and a recurrence pattern.
Master event is defined by corresponding RecurrenceState property value.
Also start/end time properties define whole series period - from the first occurrence to the last one. (They are updated automatically by data provider and you shouldn't change them, only save/load)
Ocurrences are generated at run-time by the data provider and do have some temporary event ID.

When user changes such generated event it is stored in a recurrence pattern exceptions collection and called a recurrence exception. It is also defined by corresponding RecurrenceState property value and stored in events table.

Ther are 2 ways of loading pattern for master event:
    1 - read master event, read recurrence pattern, read recurrence exceptions for this pattern. This let you completely configure master event.

    2 - read master event, set 2 custom properties (see example) with pattern ID and recurrence state 'master' and return such incomplete master event. Then data provider find those custom properties in master event, it will fire DoReadRPattern and update event itself.

The second variant is a bit easier to understand (and it avoids some redundant code).

DoReadRPattern notification have to read recurrence pattern and exceptions collection.
ReadRPatternExceptions is called from reading pattern chain.

--
WBR,
Serge

luisluis

Newbie

Joined: 04 October 2006
Status: Offline
Points: 11

Post Options

Thanks(0)

Quote

Posted: 10 October 2006 at 3:15pm

I'm using the second variant, but I can�t get DoReadRPattern fired.

He is what I'm doing:

Private Sub CalendarControl_DoRetrieveDayEvents(ByVal sender As Object, ByVal e As AxXtremeCalendarControl._DCalendarControlEvents_DoRetrieveDayEventsEvent) Handles m_pCalendar.DoRetrieveDayEvents
        Try
            abrir_DB()

            Dim strSQL As String
            strSQL = m_pHelper.MakeRetrieveDayEventsSQL(e.dtDay)

            myCommand.Connection = miconexion
            myCommand.CommandText = strSQL

            MyDataReader = myCommand.ExecuteReader

            Dim pEvent As CalendarEvent

            While MyDataReader.Read()
                pEvent = m_pHelper.CreateEventFromRS(MyDataReader, False)
                If Not pEvent Is Nothing Then
                    e.events.Add(pEvent)
                End If
            End While

        Catch myerror As OdbcException
            MessageBox.Show(myerror.Message, "Error conectando a la base de datos", MessageBoxButtons.OK, MessageBoxIcon.Error)
            dispose_DB()
        Catch myerror As Exception
            MessageBox.Show(myerror.Message, "Error al leer evento", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Finally
            If estado_DB <> ConnectionState.Closed Then cerrar_DB()
        End Try

End Sub

Public Function CreateEventFromRS(ByVal pEventRS As OdbcDataReader, ByVal bRException As Boolean) As CalendarEvent
        Try
            CreateEventFromRS = Nothing

            Dim pEvent As CalendarEvent
            Dim nEventID As Long

            nEventID = pEventRS.GetValue(pEventRS.GetOrdinal("EventID")) 'pEventRS("EventID")

            pEvent = m_pCalendar.DataProvider.CreateEventEx(nEventID)

            If pEvent Is Nothing Then
                Exit Function
            End If

            pEvent.Subject = pEventRS("Subject")
            pEvent.Location = pEventRS("Location")
            pEvent.Body = pEventRS("Body")

            pEvent.MeetingFlag = IIf(pEventRS("IsMeeting") <> 0, True, False)
            pEvent.PrivateFlag = IIf(pEventRS("IsPrivate") <> 0, True, False)

            pEvent.Label = pEventRS("LabelID")
            pEvent.BusyStatus = pEventRS("BusyStatus")
            pEvent.Importance = pEventRS("ImportanceLevel")

            pEvent.StartTime = pEventRS("StartDateTime")
            pEvent.EndTime = pEventRS("EndDateTime")

            pEvent.AllDayEvent = IIf(pEventRS("IsAllDayEvent") <> 0, True, False)

            pEvent.Reminder = IIf(pEventRS("IsReminder") <> 0, True, False)
            pEvent.ReminderMinutesBeforeStart = pEventRS("ReminderMinutesBeforeStart")
            pEvent.ReminderSoundFile = pEventRS("RemainderSoundFile")

            pEvent.CustomProperties.LoadFromString(pEventRS("CustomPropertiesXMLData"))

            If bRException Then
                pEvent.MakeAsRException()

                pEvent.RExceptionStartTimeOrig = pEventRS("RExceptionStartTimeOrig")
                pEvent.RExceptionEndTimeOrig = pEventRS("RExceptionEndTimeOrig")
                pEvent.RExceptionDeleted = IIf(pEventRS("ISRecurrenceExceptionDeleted") <> 0, True, False)
            End If

           If Not bRException Then

                ' "process_RecurrenceState" and "process_RecurrencePatternID" properties
                ' are used to process master events.
                '
                ' If they are set and RecurrenceStaie is Master Data provider will
                ' fier DoReadRPattern event and make event as Master.
                ' And it will also generate ocurrences for RetrieveDayEvents method.
                '
                ' Thise properties are temporary and they will be removed by data provider.
                '
                ' If these properties are not set data provider expect that master event
                ' is already compleated - CreateRecurrence method is called and
                ' Recurrence pattern is set.
                '
                ' This mechanism is usefull for DB data providers, when events and patterns
                ' are stored separately (in tables).
                ' But if events stored in some memory collection or array
                ' it should not be used because master event store recurrence pattern inside.
                '
                pEvent.CustomProperties("process_RecurrenceState") = pEventRS("RecurrenceState")
                pEvent.CustomProperties("process_RecurrencePatternID") = pEventRS("RecurrencePatternID")
            End If

            CreateEventFromRS = pEvent
            Exit Function

        Catch myerror As Exception
            MessageBox.Show(myerror.Message, "Error al crear evento desde la base de datos", MessageBoxButtons.OK, MessageBoxIcon.Error)
            CreateEventFromRS = Nothing
        End Try

    End Function

When I reach the end of DoRetrieveDayEvents, DoReadRPattern is not called. Why???

sserge

Moderator Group

Joined: 01 December 2004
Status: Offline
Points: 1297

Post Options