INDIGOplotD -- a plot tool for indigo

A Plugin to create Plots from INDIGO data and other sources
Post Reply
User avatar
kw123
Posts: 8705
Joined: Sun May 12, 2013 4:44 pm
Location: Dallas, TX
Contact:
Contact kw123

Re: INDIGOplotD -- a plot tool for indigo

Post by kw123 »

Richard,

could you use 3.0.2 (might work with 2.6.1 too) and replace

def getSQLHistory1 ...

with
and run it . It will print the sql statement and the SQL return(first 500 characters into the log file

Code: Select all

	def getSQLHistory1(self, devORvar, deviceId, property, maxRecords=10, startId=0,lastID=999999999):
		sqlConn = None
		try:
			sqlConn = self.getDatabaseConnection()
			tableName = devORvar + str(long(deviceId))
			if sqlConn.TableExists(tableName) == False:
				return
			else:
				sqlCommandText=                 " SELECT id, datetime(ts,'localtime'), "+property+" from " + tableName+" t1 "
				sqlCommandText= sqlCommandText+	" WHERE   t1.id > "+str(startId)
				sqlCommandText= sqlCommandText+	" and   t1.id <= "+str(lastID)
				sqlCommandText= sqlCommandText+ " and  NOT EXISTS ( SELECT  id, ts, "+property+" t1"
				sqlCommandText= sqlCommandText+	" FROM  "+tableName+" t2"
				sqlCommandText= sqlCommandText+	" WHERE t2.ts = t1.ts"
				sqlCommandText= sqlCommandText+	" and   t2.id = t1.id -1  "
				sqlCommandText= sqlCommandText+	" and   t2."+property+" = t1."+property
				sqlCommandText= sqlCommandText+ " ) "
				sqlCommandText= sqlCommandText+ " LIMIT " + str(maxRecords)

## this is the change
				self.logMessage(u" sqlcmd= " + str(sqlCommandText),1)
				sqlConn.ExecuteSQL(sqlCommandText)
				ret = sqlConn.FetchAll()
				self.logMessage(u" returned string= " + unicode(ret)[:500],1)
				return ret
## this is the change end

		except Exception, e:
			return
Top
User avatar
RogueProeliator
Posts: 2538
Joined: Tue Nov 13, 2012 3:54 pm
Location: Baton Rouge, LA

Re: INDIGOplotD -- a plot tool for indigo

Post by RogueProeliator »

It is likely the "datetime(..." call that is likely messing you up -- I actually have a modification to the code that you are using that solves this exact issue/discrepancy. I can email it to you tonight when I get home.

Adam
Top
Devildog0331
Posts: 72
Joined: Wed Jan 22, 2014 11:44 pm
Location: Baton Rouge, LA

Re: INDIGOplotD -- a plot tool for indigo

Post by Devildog0331 »

Updated to 3.0.2 and replaced the code you referenced... no dice. :cry:

Before I updated the code, my log quickly filled with:

Code: Select all

 
 INDIGOplotD                     connected to indigo_data as indigo on 127.0.0.1
  INDIGOplotD                     connected to indigo_data as indigo on 127.0.0.1
  INDIGOplotD                                                          no records returned from database, skipping     ...
  INDIGOplotD                     connected to indigo_data as indigo on 127.0.0.1
  INDIGOplotD                     connected to indigo_data as indigo on 127.0.0.1
  INDIGOplotD                                                          no records returned from database, skipping     ...
  INDIGOplotD                     SQL import             2014-06-18 16:07:51.675533 ... finished, total number of records imported: 0
  INDIGOplotD                      main loop stopped
  INDIGOplotD                     stopConcurrentThread  ...
After I updated the code, it returned this error (tried reloading it as well):

Code: Select all

Jun 18, 2014, 4:17:07 PM
  Enabling plugin "INDIGOplotD 3.0.2"
  Starting plugin "INDIGOplotD 3.0.2" (pid 58933)
  INDIGOplotD Error               Error in plugin execution InitializeMain:

Traceback (most recent call last):
<type 'exceptions.SyntaxError'>: ('invalid syntax', ('plugin.py', 4340, 20, '            \tsqlConn.ExecuteSQL(sqlCommandText)\n'))

  Stopping plugin "INDIGOplotD 3.0.2" (pid 58933)
  Stopped plugin "INDIGOplotD 3.0.2"
  Error                           plugin "INDIGOplotD 3.0.2" (pid 58933) failed to start -- restarting in 5 seconds
  Reloading plugin "INDIGOplotD 3.0.2"
  Starting plugin "INDIGOplotD 3.0.2" (pid 58939)
  INDIGOplotD Error               Error in plugin execution InitializeMain:

Traceback (most recent call last):
<type 'exceptions.SyntaxError'>: ('invalid syntax', ('plugin.py', 4340, 20, '            \tsqlConn.ExecuteSQL(sqlCommandText)\n'))

  Stopping plugin "INDIGOplotD 3.0.2" (pid 58939)
  Stopped plugin "INDIGOplotD 3.0.2"
  Error                           plugin "INDIGOplotD 3.0.2" (pid 58939) failed to start -- restarting in 20 seconds

Jun 18, 2014, 4:17:37 PM
  Reloading plugin "INDIGOplotD 3.0.2"
  Starting plugin "INDIGOplotD 3.0.2" (pid 58949)
  INDIGOplotD Error               Error in plugin execution InitializeMain:

Traceback (most recent call last):
<type 'exceptions.SyntaxError'>: ('invalid syntax', ('plugin.py', 4340, 20, '            \tsqlConn.ExecuteSQL(sqlCommandText)\n'))

  Stopping plugin "INDIGOplotD 3.0.2" (pid 58949)
  Stopped plugin "INDIGOplotD 3.0.2"
  Error                           plugin "INDIGOplotD 3.0.2" (pid 58949) failed to start -- restarting in 20 seconds

Jun 18, 2014, 4:18:00 PM
  Reloading plugin "INDIGOplotD 3.0.2"
  Starting plugin "INDIGOplotD 3.0.2" (pid 58963)
  INDIGOplotD Error               Error in plugin execution InitializeMain:

Traceback (most recent call last):
<type 'exceptions.SyntaxError'>: ('invalid syntax', ('plugin.py', 4340, 20, '            \tsqlConn.ExecuteSQL(sqlCommandText)\n'))

  Stopping plugin "INDIGOplotD 3.0.2" (pid 58963)
  Stopped plugin "INDIGOplotD 3.0.2"
  Error                           plugin "INDIGOplotD 3.0.2" (pid 58963) failed to start -- restarting in 20 seconds
  Z-Wave                          received "Luminance, Garage" sensor update to 1 lux
  Z-Wave                          received "Humidity, Garage" sensor update to 46%
  Z-Wave                          received "Temperature, Garage" sensor update to 96.6 °F
  Disabling plugin "INDIGOplotD 3.0.2"
I REALLY appreciate the effort and I hope I'm not being to taxing on you! I most certainly can wait and I just want to be timely in my response back to you. [zero expectations here - just hope!]
Top
User avatar
kw123
Posts: 8705
Joined: Sun May 12, 2013 4:44 pm
Location: Dallas, TX
Contact:
Contact kw123

Re: INDIGOplotD -- a plot tool for indigo

Post by kw123 »

Adam,

ok thx. will wait for that one.

Karl
Top
User avatar
RogueProeliator
Posts: 2538
Joined: Tue Nov 13, 2012 3:54 pm
Location: Baton Rouge, LA

Re: INDIGOplotD -- a plot tool for indigo

Post by RogueProeliator »

Karl:

Add these functions to the classes...

To the IndigoSql class:

Code: Select all

	#/////////////////////////////////////////////////////////////////////////////////////
	# Utility Routines
	#/////////////////////////////////////////////////////////////////////////////////////
	#-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
	# This routine can format a timestamp column to retrieve the value in the locals
	# time (as opposed to UNC); by default we do no changes
	#-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
	def getLocalTimestampColumn(self, tsColumnName):
		return tsColumnName
To the IndigoSqlite class:

Code: Select all

	#/////////////////////////////////////////////////////////////////////////////////////
	# Utility Routines
	#/////////////////////////////////////////////////////////////////////////////////////
	#-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
	# This routine can format a timestamp column to retrieve the value in the local
	# time (as opposed to UNC)
	#-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
	def getLocalTimestampColumn(self, tsColumnName):
		return "datetime(" + tsColumnName + ", 'localtime') as " + tsColumnName
To use, in your code where you need a timestamp:

Code: Select all

"SELECT col1, col2, " + dbConn.getLocalTimestampColumn("ts") + ", col3"...
Adam
Top
User avatar
kw123
Posts: 8705
Joined: Sun May 12, 2013 4:44 pm
Location: Dallas, TX
Contact:
Contact kw123

Re: INDIGOplotD -- a plot tool for indigo

Post by kw123 »

edited indigosql.py and inserted the Def's

then the SQL statement looks like :

Code: Select all

#				sqlCommandText=                 " SELECT id, datetime(ts,'localtime'), "+property+" from " + tableName+" t1 "   ### old 
				sqlCommandText=                 " SELECT id, dbConn.getLocalTimestampColumn(\"ts\"), "+property+" from " + tableName+" t1 "   ### new 
				sqlCommandText= sqlCommandText+	" WHERE   t1.id > "+str(startId)
				sqlCommandText= sqlCommandText+	" and   t1.id <= "+str(lastID)
				sqlCommandText= sqlCommandText+ " and  NOT EXISTS ( SELECT  id, ts, "+property+" t1"
				sqlCommandText= sqlCommandText+	" FROM  "+tableName+" t2"
				sqlCommandText= sqlCommandText+	" WHERE t2.ts = t1.ts"
				sqlCommandText= sqlCommandText+	" and   t2.id = t1.id -1  "
				sqlCommandText= sqlCommandText+	" and   t2."+property+" = t1."+property
				sqlCommandText= sqlCommandText+ " ) "
				sqlCommandText= sqlCommandText+ " LIMIT " + str(maxRecords)
resulting in:

Code: Select all

"SELECT id, dbConn.getLocalTimestampColumn("ts"), accumEnergyTotal from device_history_465200529  t1  WHERE   t1.id > 0 and   t1.id <= 30000 and  NOT EXISTS ( SELECT  id, ts, accumEnergyTotal t1 FROM  device_history_465200529 t2 WHERE t2.ts = t1.ts and   t2.id = t1.id -1   and   t2.accumEnergyTotal = t1.accumEnergyTotal )  LIMIT 20000"
then executing:

Code: Select all

sqlConn.ExecuteSQL(sqlCommandText)
ret = sqlConn.FetchAll()
results in ret= "None" as result


all still with sqLite

thanks

Karl

ps I replaced ("ts") with (\"ts\") in your example
Top
User avatar
RogueProeliator
Posts: 2538
Joined: Tue Nov 13, 2012 3:54 pm
Location: Baton Rouge, LA

Re: INDIGOplotD -- a plot tool for indigo

Post by RogueProeliator »

This is a function call that you use in building the query -- take a look at my example again and you will see that the function call is generating the column name, NOT part of the query itself. Maybe this would make it clearer:

Code: Select all

            sqlCommandText=  " SELECT id, " + sqlConn.getLocalTimestampColumn("ts") + ", "+property+" from " + tableName+" t1 "   ### new 
            sqlCommandText= sqlCommandText+   " WHERE   t1.id > "+str(startId)
            sqlCommandText= sqlCommandText+   " and   t1.id <= "+str(lastID)
            sqlCommandText= sqlCommandText+ " and  NOT EXISTS ( SELECT  id, ts, "+property+" t1"
            sqlCommandText= sqlCommandText+   " FROM  "+tableName+" t2"
            sqlCommandText= sqlCommandText+   " WHERE t2.ts = t1.ts"
            sqlCommandText= sqlCommandText+   " and   t2.id = t1.id -1  "
            sqlCommandText= sqlCommandText+   " and   t2."+property+" = t1."+property
            sqlCommandText= sqlCommandText+ " ) "
            sqlCommandText= sqlCommandText+ " LIMIT " + str(maxRecords)
sqlConn will be your normal connection that you already have...
Top
User avatar
kw123
Posts: 8705
Joined: Sun May 12, 2013 4:44 pm
Location: Dallas, TX
Contact:
Contact kw123

Re: INDIGOplotD -- a plot tool for indigo

Post by kw123 »

works for sqLite.
to play this back: the new version will be using the postgreSQL syntax also for sqLite. But the function will be replacing the postgreSQl to sqLite if the DB is sqlLite ?.. cool.
Then I should be able to do the Batch version too. There I use the command prompt version.


---------
So here the new version that should work with postgreSQL: https://www.dropbox.com/s/kxqyx4l2mupfi ... -3-0-5.zip V3-0-5
----------

==> now some has to test it with postgreSQL


Thanks so much

Karl
Top
User avatar
RogueProeliator
Posts: 2538
Joined: Tue Nov 13, 2012 3:54 pm
Location: Baton Rouge, LA

Re: INDIGOplotD -- a plot tool for indigo

Post by RogueProeliator »

to play this back: the new version will be using the postgreSQL syntax also for sqLite. But the function will be replacing the postgreSQl to sqLite if the DB is sqlLite ?.. cool.
I'm not sure if I follow you there... basically, this makes a call to the database connection object (IndigoSql) to get the proper column name to put in a query... by default, it simply returns the column name as you send it in. So... getLocalTimestampColumn("ts") will just return "ts". This is what happens for the PostgreSQL connection.

HOWEVER, since we have overridden the function in the SQLite database connection class, the same call returns "datetime(ts, 'localtime') as ts" for the column name.

Adam
Top
User avatar
kw123
Posts: 8705
Joined: Sun May 12, 2013 4:44 pm
Location: Dallas, TX
Contact:
Contact kw123

Re: INDIGOplotD -- a plot tool for indigo

Post by kw123 »

that's what I wanted to say...
Top
Devildog0331
Posts: 72
Joined: Wed Jan 22, 2014 11:44 pm
Location: Baton Rouge, LA

Re: INDIGOplotD -- a plot tool for indigo

Post by Devildog0331 »

Don't have good news with the new version... Please let me know if you want me to change something locally or not.

Thanks,
Richard

Code: Select all

***UPDATED THE PLUGIN****
Jun 19, 2014, 11:00:40 AM
  Upgrading plugin "INDIGOplotD" to newer version 3.0.5 (previous version moved to trash)
  Loading plugin "INDIGOplotD 3.0.5"
  Starting plugin "INDIGOplotD 3.0.5" (pid 38314)
  Started plugin "INDIGOplotD 3.0.5"
  INDIGOplotD                     initializing  ... 3-0-5
  INDIGOplotD                     indigo version found: 6
  INDIGOplotD                      read file /Users/richard/Documents/INDIGOPlotD/data/minute.dat lines: 2016
  INDIGOplotD                      read file /Users/richard/Documents/INDIGOPlotD/data/hour.dat lines: 888
  INDIGOplotD                      read file /Users/richard/Documents/INDIGOPlotD/data/day.dat lines: 389
  INDIGOplotD                     connected to indigo_data as indigo on 127.0.0.1
  INDIGOplotD                     connected to indigo_data as indigo on 127.0.0.1
  INDIGOplotD                     connected to indigo_data as indigo on 127.0.0.1
  INDIGOplotD Error               Error in plugin execution startup:

Traceback (most recent call last):
  File "plugin.py", line 389, in startup
  File "plugin.py", line 4217, in getSQLdataDynamic
  File "plugin.py", line 4112, in getsqldataFromSQL
  File "plugin.py", line 4343, in getSQLHistory1
  File "/Library/Application Support/Perceptive Automation/Indigo 6/Plugins/INDIGOplotD.indigoPlugin/Contents/Server Plugin/dataAccess/indigosql.py", line 294, in ExecuteSQL
  File "/Library/Application Support/Perceptive Automation/Indigo 6/Plugins/INDIGOplotD.indigoPlugin/Contents/Server Plugin/dataAccess/bpgsql.py", line 1014, in execute
  File "/Library/Application Support/Perceptive Automation/Indigo 6/Plugins/INDIGOplotD.indigoPlugin/Contents/Server Plugin/dataAccess/bpgsql.py", line 799, in _execute
<class 'dataAccess.bpgsql.DatabaseError'>: ERROR:  missing FROM-clause entry for table "zonestate" at character 16


  INDIGOplotD                     connected to indigo_data as indigo on 127.0.0.1
  INDIGOplotD                     connected to indigo_data as indigo on 127.0.0.1
  INDIGOplotD                     connected to indigo_data as indigo on 127.0.0.1
  INDIGOplotD Error               Error in plugin execution runConcurrentThread:

Traceback (most recent call last):
  File "plugin.py", line 3130, in runConcurrentThread
  File "plugin.py", line 4217, in getSQLdataDynamic
  File "plugin.py", line 4112, in getsqldataFromSQL
  File "plugin.py", line 4343, in getSQLHistory1
  File "/Library/Application Support/Perceptive Automation/Indigo 6/Plugins/INDIGOplotD.indigoPlugin/Contents/Server Plugin/dataAccess/indigosql.py", line 294, in ExecuteSQL
  File "/Library/Application Support/Perceptive Automation/Indigo 6/Plugins/INDIGOplotD.indigoPlugin/Contents/Server Plugin/dataAccess/bpgsql.py", line 1014, in execute
  File "/Library/Application Support/Perceptive Automation/Indigo 6/Plugins/INDIGOplotD.indigoPlugin/Contents/Server Plugin/dataAccess/bpgsql.py", line 799, in _execute
<class 'dataAccess.bpgsql.DatabaseError'>: ERROR:  missing FROM-clause entry for table "zonestate" at character 16


  INDIGOplotD Error               plugin runConcurrentThread function returned or failed (will attempt again in 10 seconds)
  INDIGOplotD                     connected to indigo_data as indigo on 127.0.0.1
  INDIGOplotD                     connected to indigo_data as indigo on 127.0.0.1
  INDIGOplotD                     connected to indigo_data as indigo on 127.0.0.1
  INDIGOplotD Error               Error in plugin execution runConcurrentThread:

Traceback (most recent call last):
  File "plugin.py", line 3130, in runConcurrentThread
  File "plugin.py", line 4217, in getSQLdataDynamic
  File "plugin.py", line 4112, in getsqldataFromSQL
  File "plugin.py", line 4343, in getSQLHistory1
  File "/Library/Application Support/Perceptive Automation/Indigo 6/Plugins/INDIGOplotD.indigoPlugin/Contents/Server Plugin/dataAccess/indigosql.py", line 294, in ExecuteSQL
  File "/Library/Application Support/Perceptive Automation/Indigo 6/Plugins/INDIGOplotD.indigoPlugin/Contents/Server Plugin/dataAccess/bpgsql.py", line 1014, in execute
  File "/Library/Application Support/Perceptive Automation/Indigo 6/Plugins/INDIGOplotD.indigoPlugin/Contents/Server Plugin/dataAccess/bpgsql.py", line 799, in _execute
<class 'dataAccess.bpgsql.DatabaseError'>: ERROR:  missing FROM-clause entry for table "zonestate" at character 16


  INDIGOplotD Error               plugin runConcurrentThread function returned or failed (will attempt again in 10 seconds)
  INDIGOplotD                     connected to indigo_data as indigo on 127.0.0.1
  INDIGOplotD                     connected to indigo_data as indigo on 127.0.0.1
  INDIGOplotD                     connected to indigo_data as indigo on 127.0.0.1
  INDIGOplotD Error               Error in plugin execution runConcurrentThread:


****DELETED REOCCURRING ERRORS - RESTARTED THE PLUGIN****

  Reloading plugin "INDIGOplotD 3.0.5"
  Stopping plugin "INDIGOplotD 3.0.5" (pid 38314)
  INDIGOplotD                     stopConcurrentThread  ... 
  INDIGOplotD                      main loop stopped
  INDIGOplotD                     stopConcurrentThread  ... 
  Stopped plugin "INDIGOplotD 3.0.5"
  Starting plugin "INDIGOplotD 3.0.5" (pid 38365)
  Started plugin "INDIGOplotD 3.0.5"
  INDIGOplotD                     initializing  ... 3-0-5
  INDIGOplotD                     indigo version found: 6
  INDIGOplotD                      read file /Users/richard/Documents/INDIGOPlotD/data/minute.dat lines: 2016
  INDIGOplotD                      read file /Users/richard/Documents/INDIGOPlotD/data/hour.dat lines: 888
  INDIGOplotD                      read file /Users/richard/Documents/INDIGOPlotD/data/day.dat lines: 389
  INDIGOplotD                     connected to indigo_data as indigo on 127.0.0.1
  INDIGOplotD                     connected to indigo_data as indigo on 127.0.0.1
  INDIGOplotD                     connected to indigo_data as indigo on 127.0.0.1
  INDIGOplotD Error               Error in plugin execution startup:

Traceback (most recent call last):
  File "plugin.py", line 389, in startup
  File "plugin.py", line 4217, in getSQLdataDynamic
  File "plugin.py", line 4112, in getsqldataFromSQL
  File "plugin.py", line 4343, in getSQLHistory1
  File "/Library/Application Support/Perceptive Automation/Indigo 6/Plugins/INDIGOplotD.indigoPlugin/Contents/Server Plugin/dataAccess/indigosql.py", line 294, in ExecuteSQL
  File "/Library/Application Support/Perceptive Automation/Indigo 6/Plugins/INDIGOplotD.indigoPlugin/Contents/Server Plugin/dataAccess/bpgsql.py", line 1014, in execute
  File "/Library/Application Support/Perceptive Automation/Indigo 6/Plugins/INDIGOplotD.indigoPlugin/Contents/Server Plugin/dataAccess/bpgsql.py", line 799, in _execute
<class 'dataAccess.bpgsql.DatabaseError'>: ERROR:  missing FROM-clause entry for table "zonestate" at character 16


  INDIGOplotD                     connected to indigo_data as indigo on 127.0.0.1
  INDIGOplotD                     connected to indigo_data as indigo on 127.0.0.1
  INDIGOplotD                     connected to indigo_data as indigo on 127.0.0.1
  INDIGOplotD Error               Error in plugin execution runConcurrentThread:

Traceback (most recent call last):
  File "plugin.py", line 3130, in runConcurrentThread
  File "plugin.py", line 4217, in getSQLdataDynamic
  File "plugin.py", line 4112, in getsqldataFromSQL
  File "plugin.py", line 4343, in getSQLHistory1
  File "/Library/Application Support/Perceptive Automation/Indigo 6/Plugins/INDIGOplotD.indigoPlugin/Contents/Server Plugin/dataAccess/indigosql.py", line 294, in ExecuteSQL
  File "/Library/Application Support/Perceptive Automation/Indigo 6/Plugins/INDIGOplotD.indigoPlugin/Contents/Server Plugin/dataAccess/bpgsql.py", line 1014, in execute
  File "/Library/Application Support/Perceptive Automation/Indigo 6/Plugins/INDIGOplotD.indigoPlugin/Contents/Server Plugin/dataAccess/bpgsql.py", line 799, in _execute
<class 'dataAccess.bpgsql.DatabaseError'>: ERROR:  missing FROM-clause entry for table "zonestate" at character 16


  INDIGOplotD Error               plugin runConcurrentThread function returned or failed (will attempt again in 10 seconds)
  INDIGOplotD                     connected to indigo_data as indigo on 127.0.0.1
  INDIGOplotD                     connected to indigo_data as indigo on 127.0.0.1
  INDIGOplotD                     connected to indigo_data as indigo on 127.0.0.1
  INDIGOplotD Error               Error in plugin execution runConcurrentThread:

Traceback (most recent call last):
  File "plugin.py", line 3130, in runConcurrentThread
  File "plugin.py", line 4217, in getSQLdataDynamic
  File "plugin.py", line 4112, in getsqldataFromSQL
  File "plugin.py", line 4343, in getSQLHistory1
  File "/Library/Application Support/Perceptive Automation/Indigo 6/Plugins/INDIGOplotD.indigoPlugin/Contents/Server Plugin/dataAccess/indigosql.py", line 294, in ExecuteSQL
  File "/Library/Application Support/Perceptive Automation/Indigo 6/Plugins/INDIGOplotD.indigoPlugin/Contents/Server Plugin/dataAccess/bpgsql.py", line 1014, in execute
  File "/Library/Application Support/Perceptive Automation/Indigo 6/Plugins/INDIGOplotD.indigoPlugin/Contents/Server Plugin/dataAccess/bpgsql.py", line 799, in _execute
<class 'dataAccess.bpgsql.DatabaseError'>: ERROR:  missing FROM-clause entry for table "zonestate" at character 16


  INDIGOplotD Error               plugin runConcurrentThread function returned or failed (will attempt again in 10 seconds)
  Disabling plugin "INDIGOplotD 3.0.5"
  Stopping plugin "INDIGOplotD 3.0.5" (pid 38365)
  INDIGOplotD                     stopConcurrentThread  ... 
  INDIGOplotD                     stopConcurrentThread  ... 
  Stopped plugin "INDIGOplotD 3.0.5"
Top
User avatar
kw123
Posts: 8705
Joined: Sun May 12, 2013 4:44 pm
Location: Dallas, TX
Contact:
Contact kw123

Re: INDIGOplotD -- a plot tool for indigo

Post by kw123 »

could you rerun with "debug on" and post the sql statement after : sqlcmd= "

its going to be a lot of lines, just get the first sql statement.
and the result "ret="
Top
Devildog0331
Posts: 72
Joined: Wed Jan 22, 2014 11:44 pm
Location: Baton Rouge, LA

Re: INDIGOplotD -- a plot tool for indigo

Post by Devildog0331 »

Nothing to post. That's where it bombs out and throws the 'Error in plugin execution Startup.'

Code: Select all

  
 INDIGOplotD                     connected to indigo_data as indigo on 127.0.0.1
  INDIGOplotD                      sqlcmd=  SELECT id,ts, zoneState.faulted from device_history_129866978 t1  WHERE   t1.id > 0 and   t1.id <= 30000 and  NOT EXISTS ( SELECT  id, ts, zoneState.faulted t1 FROM  device_history_129866978 t2 WHERE t2.ts = t1.ts and   t2.id = t1.id -1   and   t2.zoneState.faulted = t1.zoneState.faulted )  LIMIT 20000
  INDIGOplotD Error               Error in plugin execution startup:
Top
User avatar
RogueProeliator
Posts: 2538
Joined: Tue Nov 13, 2012 3:54 pm
Location: Baton Rouge, LA

Re: INDIGOplotD -- a plot tool for indigo

Post by RogueProeliator »

sqlcmd= SELECT id,ts, zoneState.faulted from device_history_129866978 t1 WHERE t1.id > 0 and t1.id <= 30000 and NOT EXISTS ( SELECT id, ts, zoneState.faulted t1 FROM device_history_129866978 t2 WHERE t2.ts = t1.ts and t2.id = t1.id -1 and t2.zoneState.faulted = t1.zoneState.faulted ) LIMIT 20000
I ran that query against my PostgreSQL database with a device that I had (had to change device ID and the name of the field) and it returned results ok, so the primary structure of the query does not *appear* to be at fault. Was that more error detail after that in the log?

Adam
Top
Devildog0331
Posts: 72
Joined: Wed Jan 22, 2014 11:44 pm
Location: Baton Rouge, LA

Re: INDIGOplotD -- a plot tool for indigo

Post by Devildog0331 »

The red error text is the same as my last log output post. I can post or email the full text if you'd like.
I'll reset the device config to the default settings, define a new device, reload, etc and see what happens or changes in the log. Would anything from the console help? Also, I'm still using the database I created and not the default one that SQL Logger did. I'll try the above first then I'll switch the db back over to the default one and see if it has an effect.
Top
Post Reply

Return to “INDIGOplotD”