So I'm most likely just being a bit thick today but i can't get my head around how to get this to work; The aim being to have an xml builder populated from a database.
this is my handler class, the top part of the XML is to be that for all instances, that in the loop should, for each device spit out a line of xml for each alarm reason in the alarmreason table therefore device 007077 should have multiple lines of xml (lampfault through unspecified fault) then device 007078 should have the same lines and so on.
MariaDB [liteip]> desc AlarmReason;
+------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+----------------+
| AlarmIndex | int(11) | NO | PRI | NULL | auto_increment |
| Reason | varchar(50) | YES | | NULL | |
| Type | int(11) | YES | | NULL | |
+------------+-------------+------+-----+---------+----------------+
MariaDB [liteip]> desc AlarmTable;
+-------------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------+------+-----+---------+-------+
| DeviceSN | int(11) | NO | PRI | NULL | |
| ReasonIndex | int(11) | YES | MUL | NULL | |
| Status | int(11) | YES | | NULL | |
+-------------+---------+------+-----+---------+-------+
MariaDB [liteip]> desc DeviceLive;
+--------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| DeviceSN | int(11) | NO | PRI | NULL | |
| SensorActive | varchar(20) | YES | | NULL | |
| LampOn | varchar(20) | YES | | NULL | |
| EmStatus | int(11) | YES | | NULL | |
+--------------+-------------+------+-----+---------+-------+
MariaDB [liteip]> SELECT * FROM AlarmReason;
+------------+-------------------+------+
| AlarmIndex | Reason | Type |
+------------+-------------------+------+
| 1 | Lamp Fault | 1 |
| 2 | Charge Fault | 1 |
| 3 | Battery Fault | 1 |
| 4 | Unspecified Fault | 1 |
| 5 | Lamp Fault | 1 |
| 6 | Charge Fault | 1 |
| 7 | Battery Fault | 1 |
| 8 | Unspecified Fault | 1 |
+------------+-------------------+------+
I'm currently going mad trying to work out how to do it for each deviceSN in DeviceLive...
Thankyou in advance!
B4X:
'Handler class
Sub Class_Globals
Private mreq As ServletRequest 'ignore
Private mresp As ServletResponse 'ignore
' Private sql1 As SQL
End Sub
Public Sub Initialize
If Not(Main.sql1.IsInitialized) Then
Log("ReInitialising SQL1")
Main.sql1.Initialize2("com.mysql.jdbc.Driver", "jdbc:mysql://localhost/liteip","Collator","liteip1234")
End If
End Sub
Sub Handle(req As ServletRequest, resp As ServletResponse)
mreq = req
mresp = resp
resp.ContentType = "text/xml"
'Subs.RespWriteStyle(resp)
If Not(Main.sql1.IsInitialized) Then
Log("SQL1 Failed")
Return
End If
Dim RS As ResultSet = Main.sql1.ExecQuery("SELECT * FROM DeviceStatus INNER JOIN DeviceLive ON DeviceStatus.DeviceSN = DeviceLive.DeviceSN ")
Dim Count As Int
Count = 0
Do While RS.NextRow
Count = Count + 1
Loop
Count = Count * 2
Count = Count + 8
'RS = Main.sql1.ExecQuery("SELECT * FROM DeviceStatus INNER JOIN DeviceLive ON DeviceStatus.DeviceSN = DeviceLive.DeviceSN ")
Dim ALRS As ResultSet = Main.sql1.ExecQuery("SELECT * FROM ((DeviceLive INNER JOIN AlarmTable ON DeviceLive.DeviceSN = AlarmTable.DeviceSN) INNER JOIN AlarmReason ON AlarmTable.ReasonIndex = AlarmReason.AlarmIndex) ORDER BY AlarmTable.DeviceSN, AlarmReason.Reason")
Dim al As XMLBuilder
al = al.create("CONTROLLER")
al = al.e("ALARMLIST").attribute("COUNT", Count)
al = al.e("ALARMITEM").attribute("REASON", "emergency faults > 0").attribute("TYPE", "1").up
al = al.e("ALARMITEM").attribute("REASON", "emergency faults > 2").attribute("TYPE", "2").up
al = al.e("ALARMITEM").attribute("REASON", "emergency faults > 5").attribute("TYPE", "3").up
al = al.e("ALARMITEM").attribute("REASON", "emergency faults > 10").attribute("TYPE", "4").up
al = al.e("ALARMITEM").attribute("REASON", "non-emergency faults > 0").attribute("TYPE", "5").up
al = al.e("ALARMITEM").attribute("REASON", "non-emergency faults > 2").attribute("TYPE", "6").up
al = al.e("ALARMITEM").attribute("REASON", "non-emergency faults > 5").attribute("TYPE", "7").up
al = al.e("ALARMITEM").attribute("REASON", "non-emergency faults > 10").attribute("TYPE", "8").up
Do While ALRS.NextRow
Log("loop")
al = al.e("ALARMITEM").attribute("REASON", Subs.SuffixSN(ALRS.GetString("Reason"),ALRS.GetInt("DeviceLive.DeviceSN"))).attribute("TYPE", ALRS.GetInt("AlarmReason.Type")).up
al = al.e("ALARMITEM").attribute("REASON", Subs.SuffixSN(ALRS.GetString("Reason "),ALRS.GetInt("DeviceLive.DeviceSN"))).attribute("TYPE", ALRS.GetInt("AlarmReason.Type")).up
Loop
al = al.up
Dim props As Map
props.Initialize
props.Put("standalone", "no")
props.Put("{http://xml.apache.org/xslt}indent-amount", "2")
props.Put("indent", "yes")
'resp.Write("Hello World")
'Log(xb.asString2(props))
Log(Count)
'Subs.AlarmsList
Log("--alarmlist--")
RS.Close
resp.Write(al.asString2(props).Replace($"<?xml version="1.0" encoding="UTF-8" standalone="no"?>"$, $"<?xml version="1.0" ?>"$))
End SubMariaDB [liteip]> desc AlarmReason;
+------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+----------------+
| AlarmIndex | int(11) | NO | PRI | NULL | auto_increment |
| Reason | varchar(50) | YES | | NULL | |
| Type | int(11) | YES | | NULL | |
+------------+-------------+------+-----+---------+----------------+
MariaDB [liteip]> desc AlarmTable;
+-------------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------+------+-----+---------+-------+
| DeviceSN | int(11) | NO | PRI | NULL | |
| ReasonIndex | int(11) | YES | MUL | NULL | |
| Status | int(11) | YES | | NULL | |
+-------------+---------+------+-----+---------+-------+
MariaDB [liteip]> desc DeviceLive;
+--------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| DeviceSN | int(11) | NO | PRI | NULL | |
| SensorActive | varchar(20) | YES | | NULL | |
| LampOn | varchar(20) | YES | | NULL | |
| EmStatus | int(11) | YES | | NULL | |
+--------------+-------------+------+-----+---------+-------+
MariaDB [liteip]> SELECT * FROM AlarmReason;
+------------+-------------------+------+
| AlarmIndex | Reason | Type |
+------------+-------------------+------+
| 1 | Lamp Fault | 1 |
| 2 | Charge Fault | 1 |
| 3 | Battery Fault | 1 |
| 4 | Unspecified Fault | 1 |
| 5 | Lamp Fault | 1 |
| 6 | Charge Fault | 1 |
| 7 | Battery Fault | 1 |
| 8 | Unspecified Fault | 1 |
+------------+-------------------+------+
I'm currently going mad trying to work out how to do it for each deviceSN in DeviceLive...
Thankyou in advance!