Development | Database

Remote Data Access using the Winsock control
Written by Pete Vickers  [author's bio]  [read 86717 times]
Edited by Derek

         

Page 1  Page 2  Page 3 

There are a couple of options for remote database access from the Pocket PC, but with my interest in TCP/IP, I decided to investigate a 'roll your own' solution. The basis for the design is a data server on the PC, a client on the Pocket PC, and our old friend the Winsock control (in spite of its well known problems on the Pocket PC). The client will pass requests to the server, the server will carry out the request and return the results to the client. True Client/Server computing, and remote data access.

The server in this case will use ADO to access a test MS Access database, although we could be talking to literally any database. We will pass simple requests to the server, in this case, open the database, read a table, and close the database. Further possibilities will be outlined at the end of the article.

The Server

Our server will consist of a VB6 form with 3 controls, a winsock control and 2 listboxes. The server will listen for a connection on port 9875, and the process any requests given to it. You can start it with a command line option of -L and log all the events in a listbox.

Probably the trickiest part of the operation, is catering for the 'features' of the winsock control, which does not always send and receive all of the data in one chunk. To allow for this, we use a 'terminator' character to make things a bit easier. Hopefully to make it clear (more likely to muddy the water!), the 'dataarrival' code for the server is:-

If Trec = 0 Then
  Rbuff = ""
End If
Trec = Trec + 1
Dim wkbuff As String
tcpServer.GetData wkbuff

If InStr(wkbuff, "~") <> 0 Then
  Trec = 0
  Rbuff = Rbuff & Left(wkbuff, Len(wkbuff) - 1)
  Process_The_Data
Else
  Rbuff = Rbuff & wkbuff
End If

We clear out the buffer if our counter (trec) is 0, and keep receiving data until we receive an end character, in this example the tilde (~). We then process the data. The same method is used on the client. This way, we ensure we have ALL the data before processing. We are using the first 4 characters of the input buffer for a function code, and are acting on 'OPEN', 'READ' and 'CLOS'. When sending back, we use the first 6 characters, so we can send back 'OPENOK' for a succesful data base open, and 'OPENER' for a failure.

To process our received data, we do as follows:-

Select Case Mid$(Rbuff, 1, 4)
Case "OPEN"
  Open_Database
Case "READ"
  Read_Data
Case "CLOS"
  Close_Database
End Select

So for opening the database:-

Dim Cstring As String
Cstring = Mid$(Rbuff, 5, Len(Rbuff) - 4)

Set Db = New Connection

On Error Resume Next
With Db
  .ConnectionString = Cstring
  .Open
End With
If Err.Number <> 0 Then
  Sbuff = "OPENER" & Err.Number & _
    "," & Err.Description & "~"
Else
  Sbuff = "OPENOK~"
End If
On Error GoTo 0
If Logging Then Log_Event Sbuff
tcpServer.SendData Sbuff

We strip off the first 4 characters (OPEN), and the rest is the connection string for the database. We are using MS Access, but it could be Oracle or SQL Server. We test the error condition, and if we fail to open, send back the error number and status, otherwise we send back 'OPENOK'. If we look at the 'READ' function, all we are doing is passing an SQL string to the server, and processing the results:-

Private Sub Read_Data()

Set Rs1 = New Recordset
Dim Cstring As String
Cstring = Mid$(Rbuff, 5, Len(Rbuff) - 4)
On Error Resume Next
Rs1.Open Cstring, Db, adOpenDynamic, _
adLockReadOnly
If Err.Number <> 0 Then
  Sbuff = "READER" & Err.Number _
    & "," & Err.Description & "~"
If Logging Then Log_Event Sbuff
  On Error GoTo 0
  tcpServer.SendData Sbuff
  Exit Sub
End If

On Error GoTo 0
Dim wkbuff As String
Dim Ict As Integer
Dim Irecs As Long
Dim Ifields As Integer
While Not Rs1.EOF
  Ifields = Rs1.Fields.Count
  For Ict = 0 To Rs1.Fields.Count - 1
    wkbuff = wkbuff & _
    Rs1.Fields(Ict).Value & "|"
  Next
  Irecs = Irecs + 1
  Rs1.MoveNext
Wend
Rs1.Close
Set Rs1 = Nothing
wkbuff = wkbuff & "~"
Sbuff = "READOK" & Format(Irecs, "000000") & _
  "," & Format(Ifields, "0000") & "," _
  & wkbuff & "~"
If Logging Then Log_Event _
  "Sending " & Left(Sbuff, 40)
tcpServer.SendData Sbuff
End Sub

Again, we strip off the first 4 characters of the buffer, and open a recordset with therest of the string. If we get an error, we send back 'READER' and the error number and description. Otherwise, we start reading the recordset. We keep a count of the number of records, and build up a string of data, separated by the pipe (|) character. When we reach the end of therecordset, We send back the function, (READOK), the number of records formatted to a six bytestring, the number of fields, formatted to a 4 byte string, and the data, terminated by a '~'.

That is basically the 'guts' of the server. The full code is available for download with the article.

Next Page 

Back to Database | [Article Index]