this code is very easy to read and shows exactly how the XML is handled.
Mysaql database and mysql drivers needs to be preinstalled on your server
- Code: Select all
<%Dim my_conn, strSQL, rs, xmlDoc
Dim points, uplpoints, totpoints, Speed, Angle, travelname, travelid, traveltime, travellenght, siphoneid, username, password, remainingPoints, maxPointsPerTrip, maxTrips
set xmlDoc=Server.CreateObject("Microsoft.XMLDOM")
xmlDoc.async="false"
xmlDoc.load(Request)
username = xmlDoc.documentElement.selectSingleNode("//bwiredtravel/username").text
password = xmlDoc.documentElement.selectSingleNode("//bwiredtravel/password").text
siphoneid = xmlDoc.documentElement.selectSingleNode("//bwiredtravel/devId").text
travelname = xmlDoc.documentElement.selectSingleNode("//bwiredtravel/travel/description").text
Set my_conn = createobject("ADODB.Connection")
my_conn.open = "DRIVER={MySQL ODBC 5.1 Driver};SERVER=localhost;DATABASE=xxx;UID=xxx;PWD=xxx;OPTION=35;"
Set rs = CreateObject("ADODB.Recordset")
rs.CursorLocation = 3
uplpoints = xmlDoc.documentElement.selectSingleNode("//bwiredtravel/travel/uplpoints").text
totpoints = xmlDoc.documentElement.selectSingleNode("//bwiredtravel/travel/tpoints").text
travelid = xmlDoc.documentElement.selectSingleNode("//bwiredtravel/travel/id").text
traveltime = xmlDoc.documentElement.selectSingleNode("//bwiredtravel/travel/time").text
travellenght = xmlDoc.documentElement.selectSingleNode("//bwiredtravel/travel/length").text
If isnumeric(travellenght) then
travellenght = travellenght / 1000
else
travellenght = 0
End if
strSQL = "Select * from gps LIMIT 1"
rs.Open strSQL, my_conn, 1, 3
On Error Resume Next
my_conn.BeginTrans
For Each Item In xmlDoc.documentElement.selectNodes("travel/point")
rs.addnew
rs("travelname") = travelname
rs("travelid") = travelid
rs("travellenght") = formatnumber(travellenght,2)
rs("totpoints") = totpoints
rs("uplpoints") = uplpoints
rs("traveltime") = fix(traveltime / 60)
rs("pointdistance") = Item.selectSingleNode("rdist").Text
rs("pointdistancetotal") = Item.selectSingleNode("tdist").Text
rs("traveltimepoint") = fix(Item.selectSingleNode("ttime").Text / 60)
rs("datum") = epoch2date(Item.selectSingleNode("date").Text)
rs("pointid") = Item.selectSingleNode("id").Text
rs("lat") = Item.selectSingleNode("lat").Text
rs("slong") = Item.selectSingleNode("lon").Text
Speed = Item.selectSingleNode("speed").Text
If isnumeric(Speed) then
If Speed > 0 Then
Speed = (Speed * 3.6)
Else
Speed = 0
End if
Else
Speed = 0
End if
rs("speed") = formatnumber(Speed,2)
Angle = Item.selectSingleNode("course").Text
if left(Angle, 1) = "-" then
Angle = "0"
End If
rs("angle") = formatnumber(Angle,1)
If isnumeric(Item.selectSingleNode("bat").Text) then
rs("Phone_BattStat") = Round((CSng(Item.selectSingleNode("bat").Text) * 100))
else
rs("Phone_BattStat") = 0
end If
rs("altitude") = Item.selectSingleNode("altitude").Text
rs("haccu") = Item.selectSingleNode("haccu").Text
rs("vaccu") = Item.selectSingleNode("vaccu").Text
rs("continous") = Item.selectSingleNode("continous").Text
rs("Iphoneid") = siphoneid
rs("Username") = username
rs("Password") = password
rs("datumupload") = now()
points = points + Item.selectSingleNode("id").Text + ","
rs.update
Next
'Remove the last colon in the points
points = left(points, len(points) - 1 )
If Err.Number <> 0 Then
my_conn.RollBack
response.write "{"& chr(34) & "error" & chr(34) & ":true," & chr(34) & "id" & chr(34) & ":900," & chr(34) & "message" & chr(34) & ":" & chr(34) & err.number & chr(34) & "," & chr(34) & "showonce" & chr(34) & ":true," & chr(34) & "valid" & chr(34) & ":true}"
Else
my_conn.CommitTrans
response.write "{"& chr(34) & "id" & chr(34) & ":0," & chr(34) & "tripid" & chr(34) & ":" & travelid & "," & chr(34) & "points" & chr(34) & ":[" & points & "]," & chr(34) & "valid" & chr(34) & ":true}"
End if
rs.close
my_conn.close
set my_conn = nothing
set xmlDoc = nothing
function epoch2date(myEpoch)
epoch2date = DateAdd("s", myEpoch, "01/01/1970 00:00:00")
end function
MySQL Script for creating the Mysql Database
- Code: Select all
CREATE TABLE gps(
Id INT(11) NOT NULL AUTO_INCREMENT,
Datum DATETIME DEFAULT NULL,
Travelid VARCHAR(10) DEFAULT NULL,
Travelname VARCHAR(50) DEFAULT NULL,
Travellenght VARCHAR(20) DEFAULT NULL,
Traveltime VARCHAR(20) DEFAULT NULL,
Traveltimepoint VARCHAR(20) DEFAULT NULL,
Pointid VARCHAR(20) DEFAULT NULL,
Pointdistance VARCHAR(20) DEFAULT NULL,
Pointdistancetotal VARCHAR(20) DEFAULT NULL,
Totpoints VARCHAR(15) DEFAULT NULL,
Uplpoints VARCHAR(20) DEFAULT NULL,
Continous CHAR(2) DEFAULT NULL,
Lat VARCHAR(40) DEFAULT NULL,
sLong VARCHAR(40) DEFAULT NULL,
Speed VARCHAR(12) DEFAULT NULL,
Angle VARCHAR(10) DEFAULT NULL,
Altitude VARCHAR(15) DEFAULT NULL,
Haccu VARCHAR(20) DEFAULT NULL,
Vaccu VARCHAR(20) DEFAULT NULL,
Phone_BattStat VARCHAR(10) DEFAULT NULL,
Iphoneid VARCHAR(50) DEFAULT NULL,
Username VARCHAR(50) DEFAULT NULL,
Password` VARCHAR(50) DEFAULT NULL,
Datumupload DATETIME DEFAULT NULL,
PRIMARY KEY (Id),
)
ENGINE = MYISAM
AUTO_INCREMENT = 0
AVG_ROW_LENGTH = 207
CHARACTER SET latin1
COLLATE latin1_swedish_ci;