Hey Jordan,
Here you go, but it’s kind of sloppy. I don’t know what the firmware requirements are, but this works for me. I’m collecting data from dozens of these devices once a day and it’s working well so far.
The xml returned from the http call is simple, but doesn’t give you column names in each row returned. I have to know what columns are in my query to be able to keep track of my processing in each row. Also, Vorne has some custom SQL functions for enum lookups and formatting dates.
I store the query strings, column lists, date formatting, device names, and device IPs in the database, but hopefully this will give you a good enough idea.
To start, you can query like this:
http://1.1.111.111/sql-request.do?response_type=text/xml&sql_statement=select%20availability,average_cycle_time,average_rate_total,down_time,efficiency,eighth_user_number,datetime_to_iso_string%28end_time%29,end_time_offset,end_time_day,fifth_user_number,first_user_number,first_user_string,fourth_user_number,fourth_user_string,goal_count,good_count,ideal_cycle_time,interval_id,oee,part_id,percent_down,percent_run,percent_setup,percent_standby,performance,quality,reject_count,run_time,second_user_number,second_user_string,sequence_number,setup_time,seventh_user_number,sixth_user_number,slow_cycles,slow_cycles_time,small_stops,small_stops_time,standard_cycles,standard_cycles_time,standby_time,datetime_to_iso_string%28start_time%29,start_time_offset,start_time_day,takt_time,target_count,third_user_number,third_user_string,total_count,total_time,type%20from%20interval_stream%20where%20start_time%20%3E=%20iso_string_to_datetime%28%222011-01-04T00:00:00Z%22%29%20and%20start_time%20%3C%20iso_string_to_datetime%28%222011-01-05T00:00:00Z%22%29
You should be able to use your device IP address, paste that into your browser, and see the data returned. Basically then you just change your SQL query, and know how to use some of the Vorne SQL functions.
The list of tables can be found at
http://1.1.111.111/sql-test.html
You can see what tables and columns are available to you there. Here is some example code that makes the call and parses out the data for the two tables I care about.
from java.io import File
from javax.xml.parsers import DocumentBuilder
from javax.xml.parsers import DocumentBuilderFactory
from org.w3c.dom import Document
from org.w3c.dom import Element
from org.w3c.dom import Node
from org.w3c.dom import NodeList
from java.io import ByteArrayInputStream
from java.io import InputStream
import system
import app
import sys
import time
deviceIP='1.1.111.111'
deviceName='SomeDevice'
##Table INTERVAL_STREAM - shift and job related data
#columnList="availability,average_cycle_time,average_rate_total,down_time,efficiency,eighth_user_number,end_time,end_time_offset,end_time_day,fifth_user_number,first_user_number,first_user_string,fourth_user_number,fourth_user_string,goal_count,good_count,ideal_cycle_time,interval_id,oee,part_id,percent_down,percent_run,percent_setup,percent_standby,performance,quality,reject_count,run_time,second_user_number,second_user_string,sequence_number,setup_time,seventh_user_number,sixth_user_number,slow_cycles,slow_cycles_time,small_stops,small_stops_time,standard_cycles,standard_cycles_time,standby_time,start_time,start_time_offset,start_time_day,takt_time,target_count,third_user_number,third_user_string,total_count,total_time,type"
##note the use of datetime_to_iso_string. I think if you don't use it, it returns seconds since 1900 or something rather unusable. I actually build this string in the db based on the table, but you should get the idea
#queryString='select availability,average_cycle_time,average_rate_total,down_time,efficiency,eighth_user_number,datetime_to_iso_string(end_time),end_time_offset,end_time_day,fifth_user_number,first_user_number,first_user_string,fourth_user_number,fourth_user_string,goal_count,good_count,ideal_cycle_time,interval_id,oee,part_id,percent_down,percent_run,percent_setup,percent_standby,performance,quality,reject_count,run_time,second_user_number,second_user_string,sequence_number,setup_time,seventh_user_number,sixth_user_number,slow_cycles,slow_cycles_time,small_stops,small_stops_time,standard_cycles,standard_cycles_time,standby_time,datetime_to_iso_string(start_time),start_time_offset,start_time_day,takt_time,target_count,third_user_number,third_user_string,total_count,total_time,type from interval_stream where start_time >= iso_string_to_datetime("2011-01-04T00:00:00Z") and start_time < iso_string_to_datetime("2011-01-05T00:00:00Z")'
#Table TIMELINE_STREAM - run and downtime event data
columnList="duration,reason,sequence_number,start_time,start_time_offset,start_time_day,state"
#uses datetime_to_iso_string like the above query, also vorne_enum_lookup to get meaningful values
queryString='select duration,vorne_enum_lookup("timeline_stream","reason",reason),sequence_number,datetime_to_iso_string(start_time),start_time_offset,start_time_day,vorne_enum_lookup("timeline_stream","state",state) from timeline_stream where start_time >= iso_string_to_datetime("2011-01-09T00:00:00Z") and start_time < iso_string_to_datetime("2011-01-10T00:00:00Z")'
#I do this because the XML the unit spits back does not identify columns. They are all called "cell". Having this data allows me to keep track of what I am reading in each row
cols = columnList.split(',')
#the spaces will cause an error in the SQL call
queryString = queryString.replace(' ','%20')
try:
deviceCall = "http://" + deviceIP + "/sql-request.do?response_type=text/xml&sql_statement=" + queryString
response = system.net.httpGet(deviceCall)
except:
#this can take 3 minutes to fail. Hence why a ping before the call would be nice
print "Device not responding to query!"
#return -1
#parse xml returned from http call
dbf = DocumentBuilderFactory.newInstance()
db = dbf.newDocumentBuilder()
doc = db.parse(ByteArrayInputStream(response))
doc.getDocumentElement().normalize()
nodeLst = doc.getElementsByTagName("row")
insertCount = 0
errorCount = 0
#for each <row>
for s in range(nodeLst.getLength()):
fstNode = nodeLst.item(s)
if (fstNode.getNodeType() == Node.ELEMENT_NODE):
fstElmnt = fstNode
fstNmElmntLst = fstElmnt.getElementsByTagName("cell")
colcount = 0
datalist = []
#for each column (<cell>) in <row>
for z in cols:
fstNmElmnt = fstNmElmntLst.item(colcount)
colcount = colcount + 1
fstNm = fstNmElmnt.getChildNodes()
#do this to avoid error on null <cell/> values
if fstNm.item(0):
datalist.append(fstNm.item(0).getNodeValue())
else:
datalist.append("")
#now at end of row
#append what we will use as asset_id, or any other data you may wish to identify the item by
cols.append("asset_id")
datalist.append(deviceName)
#cols.append("other_field")
#datalist.append(otherField)
try:
#I would insert rows into the database here.
#app.db.insertRow(tableName,cols, datalist)
insertCount = insertCount + 1
except:
#database error handling here
errorCount= errorCount + 1
pass
print str(insertCount) + " rows inserted"
print str(errorCount) + " rows did not insert"
It’s a little crude, and a good advertisement for using Ignition instead 
, but if they are out there and you want data collected centrally, you are able to do it.
I hope this helps.
Dan
