I have an application where we’re logging data from a wind generator. While they’s working on adding features to their web appliance in the inverter, we can get data out of it in an xml format:
<response>
<exportstate>Export 105 W </exportstate>
<gridvsetting> 208.0 V</gridvsetting>
</response>
Like I said, not much to it, yet.
So, while waiting for firmware updates, I wrote some code to parse the xml and write it to SQLTags. Easy enough, since there’s a total of two. Then I thouht “how cool would it be to dynamically add the tags, so when new items are added to the firmware, the tags are automatically created and populated?” Here’s the result:
[code]def xml(folderRoot,node,IP):
import system,re
folderRoot+="/"
# Check for folder exists
query="SELECT count(name) from sqlt_core WHERE name='"+node+"' and path='"+folderRoot+"' and deleted='0'"
SQLTagExists=system.db.runScalarQuery(query)
#Add folder if not exist
if SQLTagExists==0:
query="INSERT INTO sqlt_core(name,path,drivername,tagtype,datatype,enabled,accessrights,scanclass,configchange,deleted)"+\
"VALUES('"+node+"','"+folderRoot+"','WindGendrv','6','2','1','1','2',current_timestamp,0)"
system.db.runUpdateQuery(query)
#Get XML Data from WindGen
path=folderRoot+node+"/"
url="http://"+IP+"/index.xml"
xmlIn=system.net.httpGet(url)
#Find all items in the xml file
patternString='.<.*?>*?</*?>'
pattern = re.compile(patternString, re.DOTALL)
match=pattern.findall(xmlIn)
if match:
for x in match:
#Extract the xml parameter name and value
patternString='.<.*?>'
pattern = re.compile(patternString, re.DOTALL)
match1=pattern.search(x)
nodeName=(match1.group())[1:]
baseTagName=(match1.group())[2:-1].strip()
tagName="[MySQL]"+path+(match1.group())[2:-1].strip()
value=(x.replace("/","")).replace(nodeName,"")[1:].strip()
#find out if a current tag exists
query="SELECT count(name) from sqlt_core WHERE name='"+baseTagName+"' and path='"+path+"' and deleted='0'"
SQLTagExists=system.db.runScalarQuery(query)
if SQLTagExists==0: #If no tag exists, add it to the database
query="INSERT INTO sqlt_core(name,path,drivername,tagtype,datatype,enabled,accessrights,scanclass,configchange,deleted)"+\
"VALUES('"+baseTagName+"','"+path+"','WindGendrv','1','7','1','1','2',current_timestamp,0)"
system.db.runUpdateQuery(query)
else: #If the tag exists
#Update the value...
query="UPDATE sqlt_core SET StringValue='"+value+"', valuechange=current_timestamp, dataintegrity='192' "+\
"WHERE name='"+baseTagName+"' and path='"+path+"'"
system.db.runUpdateQuery(query)
#...then update the executon table so we don't get any stale tags
query="UPDATE sqlt_sci SET lastexecrate=1000, lastexec=current_timestamp, nextexec=date_add(current_timestamp,interval 1 second) "+\
"WHERE driverName='WindGendrv'"
system.db.runUpdateQuery(query)
else: #If no xml data exists
pass
[/code]
A note here: I create the tag first, but not populate/update at the same time, because I would get an error that I don’t quite remember. But since I’m running this once a second, the inconvenience of a null value is negligible.