Scripting help: inserting data to a table, none type, dictionary, dynamic paths, f-string

Ignition
43

I called all the reads and printed them in script console

I can see them in the error though

I had thought a path was wrong

The data comes over

44

Post all of the error and just replace the data with place holders.

1 Like
45

In one sample I believe you had Green, Red for you operator, What happens if you try just the last name?

I canā€™t imagine the comma in the name throwing it off for a string, but itā€™s something to try.

1 Like
46

I wondered about the comma in the name.
In my database query though, there are entries with ā€œlastname,firstnameā€

I even tried the submit button with the same names on another machine, and it worked. I really need to work with the Ignition tech support ticket. I will update what was wrong. The entry uses dropdowns for the names and machines. Really bizarre that the data shows in the error.

Thanks for helping. I better work with the ticket system.
I had actually messed up earlier and posted a little bit of info that was better not to.
Donā€™t want to do that again.

I think I had made errors in the efforts to produce a version of the error that I could post.

47

I think my column for information might be set for too few of characters for what some have asked for.

I need to do some kind of datatype change.

It is varchar

so I am going to try using code like

ALTER TABLE Books ALTER COLUMN title VARCHAR (432)
48

You are missing this right after machine aka your column 3 and before 8393

[
Tue Apr 05 13:01:51 EDT 2022, - count 1
1, - count 2
machine,- count 3
1234, - count 4
5678, - count 5
0, - count 6
0.0, - count 7
0, - count 8
0.00, - count 9
X85, - count 10
Green, - count 11
Red, - count 12
Big red oak 2x4- count13
BACK, - count 14
test 5],- count 15

1 Like
49

that is supposed to be one column
lastname, firstname.

I just butchered the summary trying to do too many things at once.

Increasing my varchar to varchar(80) on the longer column did not solve the error.
It was only 33 characters long in excel using len()
came from an entry field on the page though, so wasn't sure how to check length in the script console

50

Ok. What of the 1234 column though? That is one you included in that post but didnā€™t include in your excel sheet. That would put you over as well (or under if you donā€™t have it.

Have you confirmed within ignition that the values match up? Like

query = "INSERT INTO someTable (col1, ...col14) VALUE (?, ..., ?"
values = [value1, ... valueN]
print query
print len(values)
print values
system.db.runPrepUpdate(query, values)

And as @pturmel mentioned you cut off the caused by clause of the error - this will tell you exactly what is wrong. That way you donā€™t have to try to guess that some column datatype is too short or something but instead we can help you target the actual problematic column or issue.

I understand you have proprietary info yo donā€™t want to post. If you could just cause the error again and post the lines that say caused by: it would help trouble shooting a lot.

2 Likes
51

Still waiting to hear back from the ticket.


Caused by: org.python.core.PyException: Traceback (most recent call last): File "", 
line 55, in runAction at com.inductiveautomation.ignition.common.script.builtin.
AbstractDBUtilities.error(AbstractDBUtilities.java:362) at 
com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.
runPrepUpdate(AbstractDBUtilties.java:258) at 
dk.internal.reflect.GeneratedMethodAccessor147.invoke(Unknown Source) at 
java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
 at java.base/java.lang.reflect.Method.invoke(Unknown Source) java.lang.Exception: 
java.lang.Exception: Error executing system.db.runPrepUpdate(INSERT INTO 
thistable (alpha, bravo, charlie, delta, echo, foxtrot, golf, hotel, India, Juliet,
 Kilo,
 Lima, Mike, November) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?), [Tue 
Apr 05 14:04:42 EDT 2022, bravoNumber, charlieString, DeltaNumber, 
EchoNumber,  FoxtrotNumber, GolfNumber, HotelNumber,
 IndiaNumber, JulietText, KiloText, LimaText, MikeText, NovemberText], 
databaseinfo, , false, false)
52

This morning, I tested and the length of the limatext must be be limited to 30.

If I put in short text, the insert works. If not, then it fails.

However, yesterday I had tried this:

ALTER TABLE mytable ALTER COLUMN limaColumn VARCHAR (80)

the text to put in there is 33 characters long

how do I get the datatype change to take for that column?
I am on 8.1.0

53

It isnā€™t a setting on the Ignition side. Youā€™ll have to make the change from SSMS.

You may not be able to run an Alter action through JDBC, you may not have the correct permissions for it on the user that connects from Ignition

2 Likes
54

Since the issue is different than the original one, you should make a new thread with a relevant title, so that people with a similar issue can find it when searching the forums. Link to this thread if you donā€™t want to repeat all this.

1 Like
55

It works now!
It is alive!

Thanks for helping me

I reviewed and updated the title name with my best guess of what to call it.
It was "Little Scripting Help" and now is "scripting-help-inserting-data-to-a-table-none-type-dictionary-dynamic-paths-f-string".

Thanks very much for helping to fill in the gaps in my knowledge for this stuff.