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