[RESOLVED] MSCOMM and Database Connectivity

[sengreen]

I have two AIS Systems connected on serial ports of a computer. I want to combine the output of both systems and want to send to a single serial port. For combining the output, I am sending the incoming serial data of both AIS to MS Access database. Baud rate of AIS is 38400. Data is coming very fast.
I am receiving the data from serial port and then on Timer event I am sending it to the database by spliting the strings line by line. But I am receiving the broken strings in database may be due to time missmatch of timer and MSCOMM receive event.
Then I cut the database connectivity code from timer event and pasted it into MSCOMM receive event. But the problem is still there. My code of mscomm receive event and timer event is as follows:
I tested the program by changing the time of timer. e.g 50 ms,100 ms, 500 ms,1000 ms.

Private Sub MSComm1_OnComm()
Case comEvReceive
Buffer = MSComm1.Input
Text1.Text = Text1.Text & Buffer
Text1.SelStart = Len(Text1.Text)
Timeout = Now() + 1 / 86400# '
Timer1.Enabled = True '
Exit Sub
End Select
End Sub

Private Sub Timer1_Timer()
'----------Database Connectivity----------
Dim pos As Integer
Dim entry() As String
Dim cn As ADODB.Connection
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\Database\DB.mdb"
entry = Split(Buffer, vbCrLf, , vbTextCompare)
pos = 0
Do While pos < UBound(entry)
If Trim$(entry(pos)) <> "" Then
cn.Execute ("insert into ais_data (data) Values ('" & entry(pos) & "')")
End If
pos = pos + 1
Loop
cn.Close
Buffer = "" 'delete the buffer
Timer1.Enabled = False 'turn off the timer
End Sub

After deleting Timer code I am receiving the data and sending it to database like this:

Private Sub MSComm1_OnComm()
Select Case MSComm1.CommEvent
'Capture any incoming data and write it to the output box.
Case comEvReceive
Buffer = MSComm1.Input
'----------Database Connectivity----------
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "Database\DB.mdb"
entry = Split(Buffer, vbCrLf, , vbTextCompare)
pos = 0
Do While pos < UBound(entry)
If Trim$(entry(pos)) <> "" Then
cn.Execute ("insert into ais_data (data) Values ('" & entry(pos) & "')")
End If
pos = pos + 1
Loop
cn.Close
Buffer = "" 'delete the buffer
End Select
End Sub

The normal AIS String is something like that

$PSHI,001,32.53823999,,02129,33591,113432.28,2,1220,-073*3B
!AIVDM,1,1,,A,B7P>o@002RUlU8rUV<b13wi5oP06,0*19
$PSHI,001,32.58856534,,04626,30936,113432.31,2,1222,-086*35
!AIVDM,1,1,,B,17OfAD5P0B:GF`abEgS<iww20000,0*56
$PSHI,001,32.66856502,,04622,30770,113432.35,2,1225,-086*36
!AIVDM,1,1,,B,17OfAD5P0B:GF`abEgS<iww20000,0*5
!AIVDM,1,1,,B,17OfAD5P0B:GF`abEgS<iww20000,0*6
$PSHI,001,32.66856502,,04622,30770,113432.35,2,1225,-086*36
!AIVDM,1,1,,A,B7P>o@002RUlU8rUV<b13wi5oP06,0*19
$PSHI,001,32.58856534,,04626,30936,113432.31,2,1222,-086*35
$PSHI,001,32.66856502,,04622,30770,113432.35,2,1225,-086*36
!AIVDM,1,1,,B,17OfAD5P0B:GF`abEgS<iww20000,0*6
$PSHI,001,32.66856502,,04622,30770,113432.35,2,1225,-086*36

But in databse I am receiving something like this

5119409,,03598,24855,034722.21,0,1685*0B
VDM,1,1,,B,18154n001m4>Fpn=f1E93GCb0HNm,0*64
,0,1794*03
fk1b2`P?,0*5A

Please give me some expert opinion to resolve this problem. I will be very thankful to you.

[Doogle]

You are assuming that all the data for a record arrives in a singe comEvReceive event. You should have some sort of Application Protocol so you know when a complete record has arrived. Does the sender of the data terminate a record with a line terminator (eg a vbCr or vbCrLf)?

[DataMiser]

In your second piece of code above you should be checking the buffer string to see if it contains a vbCRLF before you process.

Ideally you should buffer up all the incoming data and then call a routine to process the data after the transmission is complete.

[goma_23]

In your second piece of code above you should be checking the buffer string to see if it contains a vbCRLF before you process.

Ideally you should buffer up all the incoming data and then call a routine to process the data after the transmission is complete.

I agree with DataMiser.

Ideally you should perform all your validation upon receiving the data,

assuming that there are 2 patterns , you can start by validating the "$" and the "!" , wherein "$" should be the one line , and "!' should be on another line, and so on .....

$PSHI,001,32.53823999,,02129,33591,113432.28,2,1220,-073*3B
!AIVDM,1,1,,A,B7P>o@002RUlU8rUV<b13wi5oP06,0*19

Timer and Comm are 2 different things, and In your specific case, they can't be used together as the data arrival would depend on the speed of your comm device, hence, the timer is not designed to do that.

[Doogle]

Not sure why you're using a timer to store the data, Why not store it as soon as it arrives?

As mentioned previously, you should buffer the data until a complete record has arrived, then process it and wait for the next record. Assuming your AIS receiver is conforming to standards, the $PSHI record and !ADIVDM sentence both terminate with a CR LF pair. You can use that knowledge to unblock the data appropriately.

Below is an idea you could work on

Option Explicit

Private cn As ADODB.Connection

Private Sub Form_Load()
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\Database\DB.mdb"
MSComm1.Settings = "38400,N,8,1"
MSComm1.CommPort = 1
MSComm1.RThreshold = 1
MSComm1.PortOpen = True
End Sub

Private Sub Form_QueryUnload(Cancel As Integer, UnloadMode As Integer)
cn.Close
Set cn = Nothing
MSComm1.PortOpen = False
End Sub

Private Sub MSComm1_OnComm()
Static strBuffer As String
Dim strData As String
Dim strSQL As String
Dim intPos As Integer
Dim boComplete As Boolean
Select Case MSComm1.CommEvent
    Case comEvReceive
        '
        ' Read the data, append it to the buffer
        '
        strData = MSComm1.Input
        strBuffer = strBuffer & strData
        Do
            intPos = InStr(strBuffer, vbCrLf)
            '
            ' Check if a complete record has been received
            '
            If intPos > 0 Then
                '
                ' Yes - add it to the table
                '
                strSQL = "INSERT INTO ais_data (data) VALUES ('" & Mid$(strBuffer, 1, intPos - 1) & "'"
                cn.Execute strSQL
                '
                ' Is there anything else in the buffer?
                '
                If intPos + 2 > Len(strBuffer) Then
                    '
                    ' Yes, move it to the front and go round the loop again
                    '
                    strBuffer = Mid$(strBuffer, intPos + 2)
                Else
                    '
                    ' No, flush the buffer and exit
                    '
                    strBuffer = ""
                    boComplete = True
                End If
            Else
                '
                ' Complete record not yet received, wait for the next comEvReceive event
                '
                boComplete = True
            End If
        Loop Until boComplete = True
End Select
End Sub

[Doogle]

BTW, looking at the AIS message standards, I think you may run into some issues with the 'payload' of the actual message. It looks as if it can contain reserved characters (e.g. ') which Access (and other DMBS) will reject when you try to perform the Insert. (Something like "Syntax Error near INTO")

I think you've got some options:

1. Examine the data and replace any reserved characters prior to performing the Insert. Perhaps adopt something like '&amp:XX' where XX is the hexadecimal representation of the character in question. e.g. if you came upon a Single Quote, you'd replace it with &amp:27 (of course there's a risk that '&amp:27' might be in the actual data stream, so you may have to study the AIS message standards carefully to see if there'a a combination of characters that will never be in the stream).

2. Define Column 'data' in your ais_data Table as a 'Blob'.

3. Decode the payload before writing it to the Table. (Even then, there's a risk that, when decoded, the message may contain reserved characters e.g. an apostrophy in a Ship's name)

4. Write the data to a flat file rather than a Database. If you have two applications, each processing an AIS stream, then you'd have to 'manage' the file sharing issues if both are attempting to write to the same file, or else, let each write to a separate file and then merge the files after you've collected the samples. Alternatively, have a 3rd application, to which the others send the data, and it then serialises the writing to the file.

Also, you may have to be careful when processing the data. AIS messages can be multi-part and if you've combined two streams the first part of a given message may not be immediately followed by the second part (e.g. it may be interleaved with a message from the other stream.) Also, from what I can make out, the $PSHI message is a 'wrapper' for the following !AIVDM message. When you come to analyse the data you can't assume that the messsages are in the original order. You'll need to check the preamble to see 'what belongs to what' and / or be careful with the ORDER BY clause when SELECTing rows from the Table.

IMHO, each option has pros and cons. The AIS data stream looks as if it's designed for real-time processing rather than storing and analysing later. I think I'd re-examine exactly what I was trying to achieve by merging the two streams in the first place and see what alternative Application designs might be available. At the moment, with the approach you're taking you may be making a rod for your own back when it comes to analysis of the data down the line. (all IMHO)

Of course, there may be other options I haven't thought of, but it's early here and I've only had one cup of Tea so far, so the little grey cells are not too active yet

[sengreen]

Thanks for replies.......
According to GOMA 23 I refined my code in this way:

    
    Dim entry() As String
    cn.Open "driver={SQL Server};" & "server=abc; database=db;" & "uid=;pwd="
    entry = Split(buffer, vbCrLf)
    For i = 0 To UBound(entry)
        j = InStr(buffer, "$PSHI")
        If j Then
            k = InStr(buffer, "!AIVDM")
            If k Then
                a = entry(i)
                If Trim$(a) <> "" Then
                    cn.Execute ("insert into ais_data (data) Values ('" & a & "')")
                End If
            End If
        End If
    Next i
    cn.Close

@DOOGLE....I will check your options as well. I changed my database from MS Access to MS SQL Server But so far database is giving me no error with respect to comma or special characters.

[sengreen]

Hi DOOGLE.... Today I applied the changes you mentioned in your post.

Select Case MSComm1.CommEvent
    Case comEvReceive
        '
        ' Read the data, append it to the buffer
        '
        strData = MSComm1.Input
        strBuffer = strBuffer & strData
        Do
            intPos = InStr(strBuffer, vbCrLf)
            '
            ' Check if a complete record has been received
            '
            If intPos > 0 Then
                '
                ' Yes - add it to the table
                '
                strSQL = "INSERT INTO ais_data (data) VALUES ('" & Mid$(strBuffer, 1, intPos - 1) & "'"
                cn.Execute strSQL
                '
                ' Is there anything else in the buffer?
                '
                If intPos + 2 > Len(strBuffer) Then
                    '
                    ' Yes, move it to the front and go round the loop again
                    '
                    strBuffer = Mid$(strBuffer, intPos + 2)
                Else
                    '
                    ' No, flush the buffer and exit
                    '
                    strBuffer = ""
                    boComplete = True
                End If
            Else
                '
                ' Complete record not yet received, wait for the next comEvReceive event
                '
                boComplete = True
            End If
        Loop Until boComplete = True
End Select
End Sub

This is I think right way to handle the program. But still the program is missing some characters. e-g
Always drop characters in red colour.
$PSHI,001,32.53823999,,02129,33591,113432.28,2,1220,-073*3B
!AIVDM,1,1,,A,B7P>o@002RUlU8rUV<b13wi5oP06,0*19
$PSHI,001,32.58856534,,04626,30936,113432.31,2,1222,-086*35
!AIVDM,1,1,,B,17OfAD5P0B:GF`abEgS<iww20000,0*56
$PSHI,001,32.66856502,,04622,30770,113432.35,2,1225,-086*36
!AIVDM,1,1,,B,17OfAD5P0B:GF`abEgS<iww20000,0*5
!AIVDM,1,1,,B,17OfAD5P0B:GF`abEgS<iww20000,0*6
$PSHI,001,32.66856502,,04622,30770,113432.35,2,1225,-086*36
!AIVDM,1,1,,A,B7P>o@002RUlU8rUV<b13wi5oP06,0*19
$PSHI,001,32.58856534,,04626,30936,113432.31,2,1222,-086*35
$PSHI,001,32.66856502,,04622,30770,113432.35,2,1225,-086*36
!AIVDM,1,1,,B,17OfAD5P0B:GF`abEgS<iww20000,0*6
$PSHI,001,32.66856502,,04622,30770,113432.35,2,1225,-086*36

Plz give me some suggestion why the program is showing such behaviour. Thanks

[DataMiser]

I think your problem is here

If intPos + 2 > Len(strBuffer) Then
                    '
                    ' Yes, move it to the front and go round the loop again
                    '
                    strBuffer = Mid$(strBuffer, intPos + 2)
                Else
                    '
                    ' No, flush the buffer and exit
                    '
                    strBuffer = ""
                    boComplete = True
                End If

If intPos + 2 > Len(strBuffer) Then ' Would mean that there is nothing else in the buffer so when there is something else there your code is executing the else and flushing the buffer resulting in data loss.

If you change that to

If intPos + 2 < Len(strBuffer) Then

You should see better results

[Doogle]

Thanks, DataMiser, my stupid error. Blame my age !

[DataMiser]

No problem, happens to the best of us.

[sengreen]

Thanks to all of you......... My problem is now resolved.