Unable to insert with a simple select statement

Ignition
1

Code is databaseConnection = “Ignition”

Insert missing tags from database

sqlString = “”"
insert into aws_log(tagpath,lasttime)
select distinct tagpath , (select MIN(start_time) from sqlth_partitions)
from dbo.sqlth_te t
where not exists(select * from aws_log where tagpath=t.tagpath)
“”"

system.db.runPrepUpdate(sqlString)
print sqlString

Error is : java.lang.Exception: java.lang.Exception: Error executing system.db.runPrepUpdate(
insert into aws_log(tagpath,lasttime)
select distinct tagpath , (select MIN(start_time) from sqlth_partitions)
from dbo.sqlth_te t
where not exists(select * from aws_log where tagpath=t.tagpath)
, [null], , , false, false)

Any idea what is wrong? Should I just do row by row with a insert with values?
New to this software, trying to find if a project is do-able, crashed at the first statement, oh well.

2

Take a look at the full error details, at the “Caused by” sections, for information on what your database doesn’t like. Be sure your SQL statement actually works in a SQL management studio before you expect Ignition to run it.
Aside from that, direct access to sqlt_* and sqlth_* tables is not for the faint of heart – certainly not recommended, and certainly not for beginners. Consider taking the (free) online training at Inductive University to get started.

1 Like
3

Hi, first, this statement has been tested in SQL Server, as a DBA, I know exactly how to write complex code. The SQL server is not the issue as this code was fully tested and now I need to implement this into Ignition.

So I changed the query to : insert into aws_log(tagpath,lasttime) select distinct tagpath , 1517461200000 from dbo.sqlth_te t where not exists (select * from aws_log where tagpath=t.tagpath)

x = system.db.runPrepUpdate(sqlString,[],“Ignition”,"",1,1)

The result is :

Caused by: java.lang.Exception: Error executing system.db.runPrepUpdate(insert into aws_log(tagpath,lasttime) select distinct tagpath , 1517461200000 from dbo.sqlth_te t where not exists (select * from aws_log where tagpath=t.tagpath), [], Ignition, , true, true)
Caused by: com.inductiveautomation.ignition.client.gateway_interface.GatewayException: Cannot run this function when in read-only mode.

is it possible that I cannot run an update from ignition?
What does this mean, read-only mode?

4

You need to change your designer to read/write mode. Its in the Project menu.

5

Read-only mode means the designer won’t write to the PLC or the Database. This is to protect you when opening the designer on a live project, as it could fire triggers of any kind.

The solution is just to change the mode with one of these buttons: (the modes are “offline”, “read-only” and “read-write”)

1 Like
6

Thanks

This is solved.

Still does not execute complex queries but I was able to do this using For-Loop, etc…

7

The queries are just passed on to the JDBC driver bundled with Ignition (though you can replace it with your custom JDBC driver).

Any query that can be executed by the driver can be executed on Ignition.

8

Unfortunately, that statement doesn’t tell me anything.

If a statement ran into SQL server is running and I pass it to ignition, it should run. This seems not valid. The JDBC driver is interpreting a statement instead of passing it to the database and letting the database run it.

Not going to complain about this, just need to find a way to make this happen. Java = very longer coding.

Thanks,

9

I just tried that query here (after creating a correct aws_log table), and this is the query I saw appearing in the SQL profiler.

So the JDBC driver does surround it a bit with some code to return keys or row counts, but the query itself succeeded.

I have no idea where your query goes wrong.