system.db.runQuery

General Discussion
1

Hello
I’m trying to calculate Cpk and sigma at a certain time. Specifically at 6:00. I have achieved this but once I want to read from the database it gives me errors

import time
import system
from java.util import Calendar
cal = Calendar.getInstance()
# set year, month, day, hour, minute, second in one call

# This sets it to Feb 25th, 1:05:00 PM, 2010

#cal.set(2010, 1, 25, 13, 5, 0)
myDate = cal.getTime() 
cal.setTime(myDate)

cal.add(Calendar.HOUR, -24)

MyNewDate = cal.getTime()

ahoraHORA = system.db.dateFormat(myDate, "H:mm:ss")
AhoraDIA = system.db.dateFormat(myDate, "yyyy-MM-dd" )
Primera = '06:00:00'
Segunda = '05:59:59'
Ayer = system.db.dateFormat(MyNewDate, "yyyy-MM-dd" )


if (ahoraHORA >  "14:00:00" and ahoraHORA < "18:15:00"):
	B1 = system.db.runQuery("Select B1 from Bisagras where t_stamp between '%s' '%s'  AND  '%s' '%s'" %(Ayer,Primera,AhoraDIA,Segunda))
2

Without knowing the error, it’s a bit more difficult, but I’d say there are too many single quotes around the datetimes in the query.

try:

Select B1 from Bisagras where t_stamp between '%s %s'  AND  '%s %s'
3

Thank you I have done it and it does not give error.

1 Like
4

estoy haciendo una llamada a system.db.runQuery y el el query estoy poniendo “as” para poder salvar en un csv con encabezados cambiados y da un error en el as qu eno lo reconoce. Entiendo que es por como estoy escribiendolo.

Traceback (innermost last):

File “event:mouseClicked”, line 1, in ?

File “module:myfuncs”, line 212, in CSV

java.lang.Exception: Error executing system.db.runQuery(SELECT (t_stamp as H_t_stamp from Bisagras where t_stamp BETWEEN ‘2018-05-29 06:00:00’ AND ‘2018-06-30 05:59:59’) VALUES(K4), , )

Caused by: com.inductiveautomation.ignition.client.gateway_interface.GatewayException: Gateway Error 500: Incorrect syntax near the keyword ‘as’.

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near the keyword ‘as’.

java.lang.Exception: java.lang.Exception: Error executing system.db.runQuery(SELECT (t_stamp as H_t_stamp from Bisagras where t_stamp BETWEEN ‘2018-05-29 06:00:00’ AND ‘2018-06-30 05:59:59’) VALUES(K4), , )

Ignition v7.2.6 (b128)
Java: Sun Microsystems Inc. 1.6.0_45

5

Sorry, I put it in English

I’m making a call to system.db.runQuery and the query is putting “as” to be able to save in a csv with changed headers and it gives an error in the way it recognizes it. I understand that is how I am writing it.

6

The problem here is the VALUES portion of the query. SELECT does not use this.