How to run the exported DDL SQL file in Ignition

Ignition Early Access
1

Hi,
I want to create my whole DB schema in the Ignition. To do that I simply export my created schema from SQL IDE (Export To DDL) and it generates multiple SQL queries.
As system.db.runPrepUpdate() doesn't allow to have multiple queries I split(";") and looped through all.

	db = "CMMS"
	queries = self.view.custom.queries.strip().split(";")

	for query in queries:
		system.db.runPrepUpdate(query, [], db)

This method works if I don't have any stored procedures or trigger code blocks in my exported DDL file. ( As the system.db.runPrepUpdate() doesn't execute stored procedures statement)
So I want to know how can I run the whole SQL statements in Ignition.
@pturmel Is it possible to do this by importing some JAVA classes?

2

No, vanilla JDBC just does single statements.

However, system.db.runPrepUpdate should be able to run complete CREATE TRIGGER or CREATE PROCEDURE statement, even though they are composed of multiple statements, because the multiple statements are not executed at that point in time.

1 Like
3

How does a company like Sepasoft do this then? They generate the whole schema with their module in Ignition.

This is a simple trigger example and as it uses IF we need to close it with END IF;
and that ";" makes a problem with system.db.runPrepUpdate().

  CREATE OR REPLACE TRIGGER "IGNITION"."ASSETS_TRG1" 
BEFORE INSERT ON IGNITION.ASSETS 
FOR EACH ROW 
BEGIN
  <<COLUMN_SEQUENCES>>
  BEGIN
    IF INSERTING AND :NEW.ID IS NULL THEN
      SELECT ASSETS_SEQ2.NEXTVAL INTO :NEW.ID FROM SYS.DUAL;
    END IF;
  END COLUMN_SEQUENCES;
END;
/
ALTER TRIGGER "IGNITION"."ASSETS_TRG1" ENABLE;
4

I don't see any AS clause the delimits the CREATE from the body. That usually includes unambiguous delimiters. What I mean is that your CREATE doesn't look complete.

5

This is generated by Oracle SQL developer IDE. The AS clause is for MSSQL.

6

Ok. I haven't played with Oracle in many years. I'm not sure what you should try next.

7

The AdHoc Trending Template has code in there for Oracle tables and does create triggers.

Have you tried wrapping it in triple quotes?


		elif event.source.parent.databaseType == "ORACLE":	
			system.db.runPrepUpdate("""CREATE TABLE chart_annotations (
			  id number(10) NOT NULL,
			  penname varchar2(45) NOT NULL,
			  xvalue date NOT NULL,
			  yvalue number NOT NULL,
			  note clob NOT NULL,
			  enteredby varchar2(45) NOT NULL,
			  lastupdated date NOT NULL,
			  PRIMARY KEY (id))""", [])
			system.db.runPrepUpdate("""CREATE SEQUENCE chart_annotations_seq START WITH 1 INCREMENT BY 1""", [])
			system.db.runUpdateQuery("""CREATE OR REPLACE TRIGGER chart_annotations_seq_tr
				 BEFORE INSERT ON chart_annotations FOR EACH ROW
				 WHEN (NEW.id IS NULL)
				BEGIN
				 SELECT chart_annotations_seq.NEXTVAL INTO :NEW.id FROM DUAL;
				END;""")
			system.db.runPrepUpdate("""CREATE INDEX idx_tstamp ON chart_annotations (xvalue)""", [])
1 Like