Codejock Forums Homepage
Forum Home Forum Home > Codejock Products > ActiveX COM > Calendar
  New Posts New Posts RSS Feed - Recurrence events with MySQL
  FAQ FAQ  Forum Search   Events   Register Register  Login Login

Recurrence events with MySQL

 Post Reply Post Reply
Author
Message
luisluis View Drop Down
Newbie
Newbie


Joined: 04 October 2006
Status: Offline
Points: 11
Post Options Post Options   Thanks (0) Thanks(0)   Quote luisluis Quote  Post ReplyReply Direct Link To This Post 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?

Back to Top
luisluis View Drop Down
Newbie
Newbie


Joined: 04 October 2006
Status: Offline
Points: 11
Post Options Post Options   Thanks (0) Thanks(0)   Quote luisluis Quote  Post ReplyReply Direct Link To This Post 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
Back to Top
sserge View Drop Down
Moderator Group
Moderator Group


Joined: 01 December 2004
Status: Offline
Points: 1297
Post Options Post Options   Thanks (0) Thanks(0)   Quote sserge Quote  Post ReplyReply Direct Link To This Post 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
Back to Top
luisluis View Drop Down
Newbie
Newbie


Joined: 04 October 2006
Status: Offline
Points: 11
Post Options Post Options   Thanks (0) Thanks(0)   Quote luisluis Quote  Post ReplyReply Direct Link To This Post Posted: 06 October 2006 at 11:25am
Originally posted by sserge 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.
Back to Top
sserge View Drop Down
Moderator Group
Moderator Group


Joined: 01 December 2004
Status: Offline
Points: 1297
Post Options Post Options   Thanks (0) Thanks(0)   Quote sserge Quote  Post ReplyReply Direct Link To This Post 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
Back to Top
luisluis View Drop Down
Newbie
Newbie


Joined: 04 October 2006
Status: Offline
Points: 11
Post Options Post Options   Thanks (0) Thanks(0)   Quote luisluis Quote  Post ReplyReply Direct Link To This Post 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?
Back to Top
sserge View Drop Down
Moderator Group
Moderator Group


Joined: 01 December 2004
Status: Offline
Points: 1297
Post Options Post Options   Thanks (0) Thanks(0)   Quote sserge Quote  Post ReplyReply Direct Link To This Post 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
Back to Top
luisluis View Drop Down
Newbie
Newbie


Joined: 04 October 2006
Status: Offline
Points: 11
Post Options Post Options   Thanks (0) Thanks(0)   Quote luisluis Quote  Post ReplyReply Direct Link To This Post 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???
Back to Top
sserge View Drop Down
Moderator Group
Moderator Group


Joined: 01 December 2004
Status: Offline
Points: 1297
Post Options Post Options   Thanks (0) Thanks(0)   Quote sserge Quote  Post ReplyReply Direct Link To This Post 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
Back to Top
 Post Reply Post Reply
  Share Topic   

Forum Jump Forum Permissions View Drop Down



This page was generated in 0.078 seconds.