Syste.db.runQuery problem

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.

def CSV(Linea):
import system
from java.util import Date
#*********************** CABECERA *********************
H_t_stamp = “K4”
H_B1 = “VDD/B1”
H_B2 = “VDD/B2”
H_B3 = “VDD/B3”
H_B4 = “VDD/B4”
H_B5= “VDD/B5”
H_B6= “VDD/B6”
H_P1 = “VDD/P12”
H_P2 = “VDD/P2”
H_B1B3 = “VDD/B1B3”
H_B4B6 = “VDD/B4B6”
H_B2B1 = “VDD/B2B1”
H_B5B4 = “VDD/B5B4”
#*******************************************************
now = Date() # creates a new date, for right now
Fecha = system.db.dateFormat(now, “yyyy-MM-dd HH:mm:ss”)

C_CSV = system.db.runQuery("SELECT (t_stamp as H_t_stamp, B1 as H_B1, B2 as H_B2,B3 as H_B3, B4 as H_B4, B5  as H_B5, B6 as H_B6 from Bisagras where t_stamp BETWEEN '2018-05-29 06:00:00' AND '2018-06-30 05:59:59')  VALUES(%s,%s,%s,%s,%s,%s,%s)" %(H_t_stamp, H_B1, H_B2, H_B3, H_B4, H_B5, H_B6))
system.dataset.exportExcel("data.csv", 1, C_CSV)

As mentioned in your other post, SELECT does not use VALUES

C_CSV = system.db.runQuery("SELECT t_stamp as H_t_stamp, B1 as H_B1, B2 as H_B2,B3 as H_B3, B4 as H_B4, B5  as H_B5, B6 as H_B6 from Bisagras where t_stamp BETWEEN '2018-05-29 06:00:00' AND '2018-06-30 05:59:59'")

Sorry, my English is not good. What I’m saying is that co accepts a query using the SQL AS statement. Variables in the query I have already used other times

import system
from java.util import Date
#*********************** CABECERA *********************
H_t_stamp = “K4”
H_B1 = “VDD/B1”
H_B2 = “VDD/B2”
H_B3 = “VDD/B3”
H_B4 = “VDD/B4”
H_B5= “VDD/B5”
H_B6= “VDD/B6”
H_P1 = “VDD/P12”
H_P2 = “VDD/P2”
H_B1B3 = “VDD/B1B3”
H_B4B6 = “VDD/B4B6”
H_B2B1 = “VDD/B2B1”
H_B5B4 = “VDD/B5B4”
#*******************************************************
now = Date() # creates a new date, for right now
Fecha = system.db.dateFormat(now, “yyyy-MM-dd HH:mm:ss”)

#C_XML = system.db.runQuery("SELECT (t_stamp AS H_t_stamp, B1 as H_B1, B2 as H_B2 , B3 as  H_B3, B4 as H_B4, B5 as H_B5, B6 as H_B6, P1 as H_P1, P2 as H_P2, B1B3 as H_B1B3, B4B6 as H_B4B6, B2B1 as H_B2B1, B5B4 as H_B5B4 ) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)" %(H_t_stamp, H_B1, H_B2, H_B3, H_B4, H_B5, H_B6, H_P1, H_P2, H_B1B3, H_B4B6, H_B2B1, H_B5B4))
#C_CSV = system.db.runQuery("SELECT (t_stamp as H_t_stamp, B1 as H_B1, B2 as H_B2,B3 as H_B3, B4 as H_B4, B5  as H_B5, B6 as H_B6 from Bisagras where t_stamp BETWEEN '2018-05-29 06:00:00' AND '2018-06-30 05:59:59')  VALUES(%s,%s,%s,%s,%s,%s,%s)" %(H_t_stamp, H_B1, H_B2, H_B3, H_B4, H_B5, H_B6))
C_CSV = system.db.runPrepQuery("SELECT (t_stamp as 'K4' from Bisagras where t_stamp BETWEEN '2018-05-29 06:00:00' AND '2018-06-30 05:59:59')  VALUES(%s)" %(H_t_stamp))
table = C_CSV

#system.dataset.exportCSV("data.csv", 1, table)
system.dataset.exportExcel("data.csv", 1, table)

As Jordan has already pointed out, your SQL is not valid. This is not an Ignition problem. There is no SELECT operation that accepts a VALUES clause. The reason your DB is choking on ‘AS’ is because you are also making a syntax error for SELECT by putting your SELECT column list in parenthesis. You should not have an open parenthesis – ‘(’ – between SELECT and t_stamp.

1 Like

To go further, your first one works because the VALUES gets used as a table constructor (there is no FROM statement). You don’t need it for your final one because you are already getting it from a table with a FROM statement. This is why VALUES won’t work.

Hello Jordan

My complete code is this.

def CSV(Linea):
import system
from java.util import Date
#*********************** CABECERA *********************
H_t_stamp = “K4”
H_B1 =Linea + “VDD/B1”
H_B2 = Linea + “VDD/B2”
H_B3 = Linea + “VDD/B3”
H_B4 = Linea + “VDD/B4”
H_B5= Linea + “VDD/B5”
H_B6= Linea + “VDD/B6”

#*******************************************************
now = Date() # creates a new Fecha = system.db.dateFormat(now, “yyyy-MM-dd HH:mm:ss”)

C_CSV = system.db.runQuery("SELECT (t_stamp as H_t_stamp, B1 as H_B1, B2 as H_B2,B3 as H_B3, B4 as H_B4, B5  as H_B5, B6 as H_B6 from Bisagras where t_stamp BETWEEN '2018-05-29 06:00:00' AND '2018-06-30 05:59:59')  VALUES(%s,%s,%s,%s,%s,%s,%s)" %(H_t_stamp, H_B1, H_B2, H_B3, H_B4, H_B5, H_B6))
system.dataset.exportExcel("data.csv", 1,  C_CSV)

The same Query, I do it in SQL and it works, without the variables.

This is the error, in Ignition v7.2.6 (b128)

Traceback (innermost last):
File “event:mouseClicked”, line 1, in ?
File “module:myfuncs”, line 206, in CSV
java.lang.Exception: Error executing system.db.runQuery(SELECT (t_stamp as H_t_stamp, B1 as H_B1, B2 as H_B2,B3 as H_B3, B4 as H_B4, B5 as H_B5, B6 as H_B6 from Bisagras where t_stamp BETWEEN ‘2018-05-29 06:00:00’ AND ‘2018-06-30 05:59:59’) VALUES(K4,VDDVDD/B1,VDDVDD/B2,VDDVDD/B3,VDDVDD/B4,VDDVDD/B5,VDDVDD/B6), , )
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, B1 as H_B1, B2 as H_B2,B3 as H_B3, B4 as H_B4, B5 as H_B5, B6 as H_B6 from Bisagras where t_stamp BETWEEN ‘2018-05-29 06:00:00’ AND ‘2018-06-30 05:59:59’) VALUES(K4,VDDVDD/B1,VDDVDD/B2,VDDVDD/B3,VDDVDD/B4,VDDVDD/B5,VDDVDD/B6), , )

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

I don’t understand how your query can possibly work, but I’m going to guess that it has to do with the addition of the variables to the query. Show us what you are running “without the variables” so we can see what does work.

Also consider assigning your final SQL string (the argument to runQuery) to a temporary variable and printing it on the console. And taking that string and running it in your SQL environment.

It might help if you also showed what the output is supposed to look like, as it isn’t clear (to me) what you are attempted to retrieve from the database, and what you are attempting to do with the VALUES clause. You have a parenthesis in SELECT (t_stamp that can’t be in that position and allow a following ‘as’ clause. SELECT and AS must be at the same expression nesting level.