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

**URL:** <https://forum.inductiveautomation.com/t/is-it-possible-to-create-sql-tables-from-within-a-designer-script/31829>\
**Category:** General Discussion\
**Created:** [January 31, 2020, 5:52pm UTC](https://forum.inductiveautomation.com/t/is-it-possible-to-create-sql-tables-from-within-a-designer-script/31829 "2020-01-31T17:52:20Z")\
**Posts on this page:** 12\
**Page:** 1

<div class="post-metadata">

**Author:** ![lkruper](https://avatars.discourse-cdn.com/v4/letter/l/a88e4f/32.png) [@lkruper](https://forum.inductiveautomation.com/u/lkruper)\
**Post date:** [January 31, 2020, 5:52pm UTC](https://forum.inductiveautomation.com/t/is-it-possible-to-create-sql-tables-from-within-a-designer-script/31829/1 "2020-01-31T17:52:20Z")

</div>

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
```

---

<div class="post-metadata">

**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\
**Post date:** [January 31, 2020, 5:53pm UTC](https://forum.inductiveautomation.com/t/is-it-possible-to-create-sql-tables-from-within-a-designer-script/31829/2 "2020-01-31T17:53:19Z")

</div>

Is there a compelling reason to not just use [`system.db.runUpdateQuery`](https://docs.inductiveautomation.com/display/DOC80/system.db.runUpdateQuery)?

---

<div class="post-metadata">

**Author:** ![lkruper](https://avatars.discourse-cdn.com/v4/letter/l/a88e4f/32.png) [@lkruper](https://forum.inductiveautomation.com/u/lkruper)\
**Post date:** [January 31, 2020, 5:54pm UTC](https://forum.inductiveautomation.com/t/is-it-possible-to-create-sql-tables-from-within-a-designer-script/31829/3 "2020-01-31T17:54:46Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [January 31, 2020, 5:57pm UTC](https://forum.inductiveautomation.com/t/is-it-possible-to-create-sql-tables-from-within-a-designer-script/31829/4 "2020-01-31T17:57:13Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [January 31, 2020, 5:58pm UTC](https://forum.inductiveautomation.com/t/is-it-possible-to-create-sql-tables-from-within-a-designer-script/31829/5 "2020-01-31T17:58:20Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![lkruper](https://avatars.discourse-cdn.com/v4/letter/l/a88e4f/32.png) [@lkruper](https://forum.inductiveautomation.com/u/lkruper)\
**Post date:** [January 31, 2020, 6:05pm UTC](https://forum.inductiveautomation.com/t/is-it-possible-to-create-sql-tables-from-within-a-designer-script/31829/6 "2020-01-31T18:05:07Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [January 31, 2020, 6:09pm UTC](https://forum.inductiveautomation.com/t/is-it-possible-to-create-sql-tables-from-within-a-designer-script/31829/7 "2020-01-31T18:09:09Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [January 31, 2020, 6:10pm UTC](https://forum.inductiveautomation.com/t/is-it-possible-to-create-sql-tables-from-within-a-designer-script/31829/8 "2020-01-31T18:10:45Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![lkruper](https://avatars.discourse-cdn.com/v4/letter/l/a88e4f/32.png) [@lkruper](https://forum.inductiveautomation.com/u/lkruper)\
**Post date:** [January 31, 2020, 6:20pm UTC](https://forum.inductiveautomation.com/t/is-it-possible-to-create-sql-tables-from-within-a-designer-script/31829/9 "2020-01-31T18:20:13Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![bpreston](https://avatars.discourse-cdn.com/v4/letter/b/4af34b/32.png) [@bpreston](https://forum.inductiveautomation.com/u/bpreston)\
**Post date:** [January 31, 2020, 6:28pm UTC](https://forum.inductiveautomation.com/t/is-it-possible-to-create-sql-tables-from-within-a-designer-script/31829/10 "2020-01-31T18:28:44Z")

</div>

Personally I love examples for how to do things. On the Ignition Exchange there is an [ad hoc trend](https://inductiveautomation.com/exchange/92/overview) 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()](https://docs.inductiveautomation.com/display/DOC80/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.

---

<div class="post-metadata">

**Author:** ![Daniel\_Webb](https://avatars.discourse-cdn.com/v4/letter/d/67e7ee/32.png) [@Daniel\_Webb](https://forum.inductiveautomation.com/u/Daniel_Webb)\
**Post date:** [February 24, 2024, 5:21pm UTC](https://forum.inductiveautomation.com/t/is-it-possible-to-create-sql-tables-from-within-a-designer-script/31829/11 "2024-02-24T17:21:20Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [February 24, 2024, 5:28pm UTC](https://forum.inductiveautomation.com/t/is-it-possible-to-create-sql-tables-from-within-a-designer-script/31829/12 "2024-02-24T17:28:36Z")

</div>

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.