Calendar - Recurrence events with MySQL

Print Page | Close Window

Recurrence events with MySQL

Printed From: Codejock Forums
Category: Codejock Products
Forum Name: Calendar
Forum Description: Topics Related to Codejock Calendar
URL: http://forum.codejock.com/forum_posts.asp?TID=5221
Printed Date: 22 November 2024 at 8:02pm
Software Version: Web Wiz Forums 12.04 - http://www.webwizforums.com

Topic: Recurrence events with MySQL

Posted By: luisluis
Subject: Recurrence events with MySQL
Date 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?

Replies:

Posted By: luisluis
Date 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

Posted By: sserge
Date 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

Posted By: luisluis
Date 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.

Posted By: sserge
Date 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

Posted By: luisluis
Date 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?

Posted By: sserge
Date 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

Posted By: luisluis
Date 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???

Posted By: sserge
Date Posted: 10 October 2006 at 5:54pm

DoReadRPattern is called after you exit DoRetrieveDayEvents.

Try to debug DoRetrieveDayEvents and CreateEventFromRS and check that it works as expected. Specially see reading data code.

Before my example started working I had to debug line by line and fix something with data reading like different types in DB Field and event properties or invalid property name.

CalendarDataProvider.RetrieveDayEvents logic is following (in pseudo code):


class CalendarDataProvider
 
Public Function RetrieveDayEvents(dtDay) As CalendarEvents
    if Not IsOpen() then  
        return Nothing
    end if
    
    Dim ptrResult as CalendarEvents 
 
    if m_cache.Enabled And m_cache.IsDayInCache(dtDay) then
        ptrResult = m_cache.RetrieveDayEvents(dtDay)
    else
        ptrResult = DoRetrieveDayEvents(dtDay)
        if ptrResult is Nothing then
            return Nothing
        end if
 
        for Each pEvent in ptrResult
            patternID = pEvent.CustomProperties("process_RecurrencePatternID")
 
            if pEvent.CustomProperties("process_RecurrenceState") = xtpCalendarRecurrenceMaster and
               patternID <> Empty then 
            
                pPattern = DoRead_RPattern(patternID)
 
                if pPattern is Nothing then
                    return Nothing
                end if
 
                pEvent->MakeEventAsRecurrence()
                pEvent->SetRecurrencePatternID(RPatternID)
                pEvent->UpdateRecurrence(pPattern)
            end if 
        next
        
        if m_cache.Enabled then
            m_cache.AddToCache(ptrResult, dtDay);
        end if
    end if
    
    return ptrResult
End Function

--
WBR,
Serge