Is it possible to create SQL tables from within a Designer script?

General Discussion
1

I have tried the following code and it cannot find the driver:

def sqlTest(): # does not work, annot find jdbc driver and stops.

from com.ziclix.python.sql import zxJDBC			
url = "jdbc:sqlserver://localhost\SQLEXPRESS"
user = "sa"
password = "password"
driver = "com.microsoft.sqlserver.jdbc"
mysqlConn = zxJDBC.connect(url, user, password, driver)
mysqlConn = con.cursor()
mysqlConn.execute("SELECT * FROM deviationV6")

print user
for a in mysql.fetchall():
   print a
2

Is there a compelling reason to not just use system.db.runUpdateQuery?

3

I am looking to automate the creation of a SQL table based on reading in a Word text document. I know I can create a file to run in management studio, but it would be more elegant to do it from within the script.

I get errors with “Create” in the supplied functions.

4

Ignition doesn’t use the system’s installed packages. Just its own, plus any configured by third-party modules. JDBC drivers are only accessible through the system.db.* functions, as they are loaded privately (isolated java classloaders) in the gateway, and only in the gateway.

I don’t think you’re going to get zxJDBC to work under Ignition.

5

Please share the errors you are getting with “Create”, and how you called it from your scripts.

6

Thanks for looking!

I run this code:
system.db.runQuery(“SELECT * FROM testTableName”)
system.db.runQuery(“CREATE TABLE formOne”)

The first one works, but the CREATE gives this error:
Traceback (most recent call last):

File “event:actionPerformed”, line 326, in

java.lang.Exception: java.lang.Exception: Error executing system.db.runQuery(CREATE TABLE formOne, , )

caused by Exception: Error executing system.db.runQuery(CREATE TABLE formOne, , )
caused by GatewayException: Incorrect syntax near 'formOne'.
caused by SQLServerException: Incorrect syntax near 'formOne'.

Ignition v7.9.10 (b2018112821)
Java: Oracle Corporation 1.8.0_231

7

You are using the wrong function. Queries called with system.db.runQuery() must return a result. Update/Insert queries and data definition statements generally don’t. Use system.db.runUpdateQuery() instead. If you need to pass values to these queries, use runPrepQuery() and runPrepUpdate() instead.

8

And be sure to supply the entire create statement. Just giving a table name with no column definitions is not valid SQL.

3 Likes
9

Thanks. I was able to DROP a table I had created from the script. I appreciate your assistance.

10

Personally I love examples for how to do things. On the Ignition Exchange there is an ad hoc trend template for Vision. It is set up to create tables if they don’t exist. If you open the template and look at the “Add Annotation” button, the script under mouse pressed uses system.db.runPrepUpdate() to create a table based on the database type which is found using an expression from and internal template property. There are examples for mySQL, msSQL, Oracle and Postgres databases. I’m assuming they all work, I’ve only use it on mySQL and msSQL.

1 Like
11

In my case, I have some complex code I'm inheriting which has DB stuff but no other system.* calls through Ignition. If I could do something like what the original poster is trying to do, I could run the code in Eclipse and have a full step-by-step debugger and object viewer, unlike if I run it in Ignition.

12

Getting your system.db.* function implementations outside Ignition to behave sufficiently like Ignition's actual functions will be a considerable chore. But please blaze this trail for us. It might be valuable.