|
Development | Database
WinCE database application for MS SQL Server 7 using eVB and XML
Written by Shakil Siraj
[author's bio]
[read 71545 times]
Edited by Derek
  
Page 1
Page 2
Page 3
Page 4
The article is based on one of many
experimental things I do when I am bored. The concept is
pretty simple. I wanted my eVB application to talk to MS
SQL Sever 7 over the TCP/IP network, just the kind of the
thing I do with my desktop database applications. I couldn't
find any SQL Server ODBC driver for WinCE, and was not interested
in writing one. But I could find different ways to connect
my WinCE device to a TCP/IP based network. So I went for
a different approach. I created an intermediate gateway
(simply an ASP file on IIS), which has all the necessary
drivers and tools to connect and execute statements on a
server based database and needed to find a language that
is commonly understood and spoken by both my eVB application
and the gateway on the server. And there it was, "eXtentiable
Markup Language" or XML for short.
XML document can be used only to hold
data. For visual representation on the data, you apply stylesheets,
another form of XML document. It is platform neutral and
there are no rules on how you write the document structure.
There are a few basic rules (i.e. "<" becomes
"<", etc. ) that you need to apply so the
parsers do not get confused when going through the data.
Just the thing I needed to transfer the recordset into a
document structure that all the XML parsers can understand.
And on Windows platform, MSXML parser is a freeware and
comes with all IE 4.0+ installations. So there is no need
to worry about where to get an XML parser for WinCE.
XML representation of the recordset
object
In order to represent a recordset to
an XML doc, I have used the following XML document structure.
The root node lets me know if the last SQL operation was
success by naming the root node as 'recordset' .
If the SQL statement generated an error on the server, the
root node will be named as 'errormessage'. Look for
error message representation later in this article. This
XML document is the recordset representation of the SQL
statement "SELECT employeeID, LastName, FirstName,
Title FROM Employees" of the "Northwind"
database that comes with SQL Server 7 installation.
The attribute 'effected' on the root
node tells me how many records this document contains. This
gives me the opportunity to decide if I have reached the
end of recordset. I could have also used the method 'number
of childnodes of the documentElement less one' as my
maximum record number, but I kept it for easy understanding.
The first child node of the root contains
the column names. I have used an extra attribute "id"
which is used for locating the respective child node number
in the "records" nodes. For example, if I want
to get the value of node "FirstName" of
the record number 5, I simply tell the XML parser
to look into the "records" nodes list with the
attribute id="5" and return me the value of its
child node number 2 which is the id of the "FirstName"
column. Simple, isn't it?
The rest of the nodes are the records.
Did you notice the attribute "id" on each "records"
node? It is to simply point out the single node with the
appropriate node id number. In the case of "NULL"
values in the "column" nodes, the ASP page will
create empty nodes. So, whenever XML parser looks for the
text value of the "NULL" node, it will simply
return a blank string. This way I can also maintain the
column number sequence in the "records" nodes.
So, here goes the XML document:
<?xml version="1.0" encoding="Windows-1252"
?>
<recordset effected="9">
<columns>
<column id="0" name="employeeID"
/>
<column id="1" name="LastName" />
<column id="2" name="FirstName" />
<column id="3" name="Title" />
</columns>
<records id="0">
<col>1</col>
<col>Davolio</col>
<col>Nancy</col>
<col>Sales Representative</col>
</records>
<records id="1">
<col>2</col>
<col>Fuller</col>
<col>Andrew</col>
<col>Vice President, Sales</col>
</records>
<records id="2">
<col>3</col>
<col>Leverling</col>
<col>Janet</col>
<col>Sales Representative</col>
</records>
<records id="3">
<col>4</col>
<col>Peacock</col>
<col>Margaret</col>
<col>Sales Representative</col>
</records>
<records id="4">
<col>5</col>
<col>Buchanan</col>
<col>Steven</col>
<col>Sales Manager</col>
</records>
<records id="5">
<col>6</col>
<col>Suyama</col>
<col>Michael</col>
<col>Sales Representative</col>
</records>
<records id="6">
<col>7</col>
<col>King</col>
<col>Robert</col>
<col>Sales Representative</col>
</records>
<records id="7">
<col>8</col>
<col>Callahan</col>
<col>Laura</col>
<col>Inside Sales Coordinator</col>
</records>
<records id="8">
<col>9</col>
<col>Dodsworth</col>
<col>Anne</col>
<col>Sales Representative</col>
</records>
</recordset>
And here is the error message representation.
For the purposes of this example I changed the table name
in my SQL statement from "Employee" to "Employee1"
and generated this XML doc:
<?xml version="1.0"?>
<errormessage>
<state>S0002</state>
<description>[Microsoft][ODBC SQL Server Driver][SQL
Server]Invalid object name 'Employees1'.</description>
<nativeerror>208</nativeerror>
</errormessage>
Next
Page
Back to Database | [Article Index]
|
WinCE database application for MS SQL Server 7 using eVB and XML
