Using system.db.runPrepUpdate wrong

Ignition
1

I am trying to use system.db.runPrepUpdate to update a table in the database using data from a table:

stuff = event.source.parent.getComponent(‘Table’).data
system.db.runPrepUpdate(“INSERT INTO awt_well_list (awt1) VALUES(?)”, stuff)

All I get is this error:

[quote]Traceback (most recent call last):

File “event:actionPerformed”, line 7, in

java.lang.ClassCastException: java.lang.ClassCastException: Cannot coerce value ‘Dataset [3R ? 1C]’ into type: class [Ljava.lang.Object;

caused by ClassCastException: Cannot coerce value 'Dataset [3R ⅹ 1C]' into type: class [Ljava.lang.Object;

Ignition v7.7.0 (b2014071516)
Java: Oracle Corporation 1.8.0_25
[/quote]

It seems like it is not actually taking the data from the dataset, but the text that is in the data property of the dataset (rows and columns).
Can someone please point out my error. Thanks.

2

The stuff object must be a python list containing the exact quantity of items as same the quantity of placeholders (? characters) in the query.

If you trying to insert the three rows of the dataset, you must do something like this:

stuff = event.source.parent.getComponent('Table').data
for row in range(0, stuff.rowCount):
     system.db.runPrepUpdate("INSERT INTO awt_well_list (awt1) VALUES(?)", [stuff.getValueAt(row, 0)])

This is simplifying a couple of things:

  • The object type of the dataset column must be compatible with the datatype of the awt1 column.
  • Also, this asumming that all the inserts can be do it in a non atomic way… if not, you must use db transactions.

Look this (at the bottom of the page) for dataset access

Look this for runPrepUpdate examples.

I hope I was clear enough…

regards,

3

Yeah, I guess I want to do an UPDATE then because this script actually adds the same data over and over to the table. I will experiment further. Thanks for the info, though.

4

I have a feeling that you want to enter all the data from the table component into a database table retaining the format of the different columns and rows. Basically what you are going to have to create your database table to have the same number of columns as your table, then you'll have to loop through the data property one row at a time pulling out each of the column values. Once you have all these values you'll have to run the query to insert all these values into the appropriate columns in the database.

Something like this if your table had 3 columns:[code]
data = system.dataset.toPyDataSet(event.source.parent.getComponent('Table').data)

for row in data:
newRow=
for value in row:
newRow.append(value)
system.db.runPrepUpdate("INSERT INTO awt_well_list (col1,col2,col3) VALUES(?,?,?)", newRow)
[/code]

I haven't tested this but that should be the general idea.