Store images in a database

Ignition
1

Hello,

How can I store images from my desktop (or another folder in my pc) in a database connected to Ignition from a project? can anybody help me, please? :prayer:

2

You can store/read images in a DB using a BLOB datatype, but for Ignition to handle that you will need to create temp files and use scripting functions in system.file. To get a better understanding of your situation, why are you referencing images directly from a database instead of using Ignitionā€™s built in image handling functionality?

3

Just as a application example, I may store images from a vision system to track it with the part being madeā€¦

4

Hello!

Iā€™m using the following script:

filename = system.file.openFile() # Ask the user to open a file if filename != None: filedata = system.file.readFileAsBytes(filename) system.db.runPrepUpdate("INSERT INTO Files (file_data) VALUES (?)", [filedata])

And when I select a file and store it in a database (the column name is blob and the datatype is blob) i recieve this error mesage:


Why is this happening? how could I solve that?

Thank for your answers

5

Your script actually has the correct table name and column name, right? Also, I need the following from you:

  • full error message (your image cuts off part of the error)
  • what type of file you tried to open

Using the script you provided, I was successfully able to put an image into the database, I just had to change the table name and column name to what I was using.

6

Hello!

Yes, my script has the correct table name and column table name. Iā€™m trying to store a png file. I attached a file with the full error message.

Thank you!
ErrorBlob.txt (5.84 KB)

7

I attach what I get if I cast it into a string.
array.txt (39 KB)

8

Which database are you using? Sometimes you have to cast the value as the appropriate data type. For example, if you are using Microsoft SQL Server you may have to do the following:CAST(? AS VARBINARY(MAX))

9

Hello,

Iā€™m using mysql and the type of column is BLOB

10

The error said it occurred on line 6, but the script that you posted is only 4 lines, can you post your whole script? Thanks.

11

Hello,

This is the whole script:

[code]path = system.file.openFile()

if path != None:
bytes = system.file.readFileAsBytes(path)
event.source.parent.getComponent(ā€˜Label 1ā€™).text = str(bytes)
system.db.runPrepUpdate(ā€œINSERT INTO blob2 (blob) VALUES (?)ā€, (bytes))[/code]

12

@Travis.Cox, Do I have to cast the value for mysql?

13

I was able to duplicate this.

The value for the PrepUpdate should be a list (enclosed in brackets instead of parentheses). BTW, this needs to be updated in the manualā€™s example for the readFileAsBytes section.

[code]path = system.file.openFile()

if path != None:
bytes = system.file.readFileAsBytes(path)
event.source.parent.getComponent(ā€˜Label 1ā€™).text = str(bytes)
system.db.runPrepUpdate(ā€œINSERT INTO blobtest (blobdata) VALUES (?)ā€, [bytes])[/code]

Doing that gets around the coercion issue, but this error now pops up (canā€™t just be an easy fix, right?) :laughing:

[code]Traceback (innermost last):
File ā€œevent:actionPerformedā€, line 6, in ?
java.lang.Exception: Error executing system.db.runPrepUpdate(INSERT INTO blobtest (blobdata) VALUES (?), [[B@1420e20], , , false)

at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:293)

at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runPrepUpdate(AbstractDBUtilities.java:218)

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)

at java.lang.reflect.Method.invoke(Unknown Source)

at org.python.core.PyReflectedFunction.__call__(PyReflectedFunction.java)

at com.inductiveautomation.ignition.common.script.ScriptManager$ReflectedInstanceFunction.__call__(ScriptManager.java:313)

at org.python.core.PyObject.__call__(PyObject.java)

at org.python.core.PyObject.invoke(PyObject.java)

at org.python.pycode._pyx11.f$0(<event:actionPerformed>:6)

at org.python.pycode._pyx11.call_function(<event:actionPerformed>)

at org.python.core.PyTableCode.call(PyTableCode.java)

at org.python.core.PyCode.call(PyCode.java)

at org.python.core.Py.runCode(Py.java)

at com.inductiveautomation.ignition.common.script.ScriptManager.runCode(ScriptManager.java:394)

at com.inductiveautomation.factorypmi.application.binding.action.ActionAdapter.runActions(ActionAdapter.java:139)

at com.inductiveautomation.factorypmi.application.binding.action.ActionAdapter.invoke(ActionAdapter.java:247)

at com.inductiveautomation.factorypmi.application.binding.action.RelayInvocationHandler.invoke(RelayInvocationHandler.java:55)

at $Proxy0.actionPerformed(Unknown Source)

at javax.swing.AbstractButton.fireActionPerformed(Unknown Source)

at javax.swing.AbstractButton$Handler.actionPerformed(Unknown Source)

at javax.swing.DefaultButtonModel.fireActionPerformed(Unknown Source)

at javax.swing.DefaultButtonModel.setPressed(Unknown Source)

at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(Unknown Source)

at java.awt.Component.processMouseEvent(Unknown Source)

at javax.swing.JComponent.processMouseEvent(Unknown Source)

at java.awt.Component.processEvent(Unknown Source)

at java.awt.Container.processEvent(Unknown Source)

at java.awt.Component.dispatchEventImpl(Unknown Source)

at java.awt.Container.dispatchEventImpl(Unknown Source)

at java.awt.Component.dispatchEvent(Unknown Source)

at java.awt.LightweightDispatcher.retargetMouseEvent(Unknown Source)

at java.awt.LightweightDispatcher.processMouseEvent(Unknown Source)

at java.awt.LightweightDispatcher.dispatchEvent(Unknown Source)

at java.awt.Container.dispatchEventImpl(Unknown Source)

at java.awt.Window.dispatchEventImpl(Unknown Source)

at java.awt.Component.dispatchEvent(Unknown Source)

at java.awt.EventQueue.dispatchEventImpl(Unknown Source)

at java.awt.EventQueue.access$000(Unknown Source)

at java.awt.EventQueue$1.run(Unknown Source)

at java.awt.EventQueue$1.run(Unknown Source)

at java.security.AccessController.doPrivileged(Native Method)

at java.security.AccessControlContext$1.doIntersectionPrivilege(Unknown Source)

at java.security.AccessControlContext$1.doIntersectionPrivilege(Unknown Source)

at java.awt.EventQueue$2.run(Unknown Source)

at java.awt.EventQueue$2.run(Unknown Source)

at java.security.AccessController.doPrivileged(Native Method)

at java.security.AccessControlContext$1.doIntersectionPrivilege(Unknown Source)

at java.awt.EventQueue.dispatchEvent(Unknown Source)

at java.awt.EventDispatchThread.pumpOneEventForFilters(Unknown Source)

at java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source)

at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)

at java.awt.EventDispatchThread.pumpEvents(Unknown Source)

at java.awt.EventDispatchThread.pumpEvents(Unknown Source)

at java.awt.EventDispatchThread.run(Unknown Source)

Caused by: com.inductiveautomation.ignition.client.gateway_interface.GatewayException: Gateway Error 601: Cannot run prepared statement when Designer is in read-only mode.

at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.newGatewayException(GatewayInterface.java:280)

at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.newGatewayException(GatewayInterface.java:276)

at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.runPrepStmt(GatewayInterface.java:443)

at com.inductiveautomation.ignition.client.script.ClientDBUtilities._runPrepStmt(ClientDBUtilities.java:71)

at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runPrepUpdate(AbstractDBUtilities.java:216)

... 54 more

java.lang.Exception: java.lang.Exception: Error executing system.db.runPrepUpdate(INSERT INTO blobtest (blobdata) VALUES (?), [[B@1420e20], , , false)

at org.python.core.Py.JavaError(Py.java)
at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:293)
at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runPrepUpdate(AbstractDBUtilities.java:218)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.python.core.PyReflectedFunction.__call__(PyReflectedFunction.java)
at com.inductiveautomation.ignition.common.script.ScriptManager$ReflectedInstanceFunction.__call__(ScriptManager.java:313)
at org.python.core.PyObject.__call__(PyObject.java)
at org.python.core.PyObject.invoke(PyObject.java)
at org.python.pycode._pyx11.f$0(<event:actionPerformed>:6)
at org.python.pycode._pyx11.call_function(<event:actionPerformed>)
at org.python.core.PyTableCode.call(PyTableCode.java)
at org.python.core.PyCode.call(PyCode.java)
at org.python.core.Py.runCode(Py.java)
at com.inductiveautomation.ignition.common.script.ScriptManager.runCode(ScriptManager.java:394)
at com.inductiveautomation.factorypmi.application.binding.action.ActionAdapter.runActions(ActionAdapter.java:139)
at com.inductiveautomation.factorypmi.application.binding.action.ActionAdapter.invoke(ActionAdapter.java:247)
at com.inductiveautomation.factorypmi.application.binding.action.RelayInvocationHandler.invoke(RelayInvocationHandler.java:55)
at $Proxy0.actionPerformed(Unknown Source)
at javax.swing.AbstractButton.fireActionPerformed(Unknown Source)
at javax.swing.AbstractButton$Handler.actionPerformed(Unknown Source)
at javax.swing.DefaultButtonModel.fireActionPerformed(Unknown Source)
at javax.swing.DefaultButtonModel.setPressed(Unknown Source)
at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(Unknown Source)
at java.awt.Component.processMouseEvent(Unknown Source)
at javax.swing.JComponent.processMouseEvent(Unknown Source)
at java.awt.Component.processEvent(Unknown Source)
at java.awt.Container.processEvent(Unknown Source)
at java.awt.Component.dispatchEventImpl(Unknown Source)
at java.awt.Container.dispatchEventImpl(Unknown Source)
at java.awt.Component.dispatchEvent(Unknown Source)
at java.awt.LightweightDispatcher.retargetMouseEvent(Unknown Source)
at java.awt.LightweightDispatcher.processMouseEvent(Unknown Source)
at java.awt.LightweightDispatcher.dispatchEvent(Unknown Source)
at java.awt.Container.dispatchEventImpl(Unknown Source)
at java.awt.Window.dispatchEventImpl(Unknown Source)
at java.awt.Component.dispatchEvent(Unknown Source)
at java.awt.EventQueue.dispatchEventImpl(Unknown Source)
at java.awt.EventQueue.access$000(Unknown Source)
at java.awt.EventQueue$1.run(Unknown Source)
at java.awt.EventQueue$1.run(Unknown Source)
at java.security.AccessController.doPrivileged(Native Method)
at java.security.AccessControlContext$1.doIntersectionPrivilege(Unknown Source)
at java.security.AccessControlContext$1.doIntersectionPrivilege(Unknown Source)
at java.awt.EventQueue$2.run(Unknown Source)
at java.awt.EventQueue$2.run(Unknown Source)
at java.security.AccessController.doPrivileged(Native Method)
at java.security.AccessControlContext$1.doIntersectionPrivilege(Unknown Source)
at java.awt.EventQueue.dispatchEvent(Unknown Source)
at java.awt.EventDispatchThread.pumpOneEventForFilters(Unknown Source)
at java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source)
at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.awt.EventDispatchThread.run(Unknown Source)

Ignition v7.2.11 (b209)
Java: Sun Microsystems Inc. 1.6.0_30
[/code]

14

SamJaimes, you shouldnā€™t need to cast for MySQL.

JordanCClark is correct, fixing the end of the prepUpdate call to [bytes] should fix that error. Jordan, it looks like the reason your getting your error is because your designer is in read only mode, if you change it to full read/write that error will go away.

15

I tried what JordanCClarck suggested and this is what I got:

[code]Traceback (most recent call last):
File ā€œevent:actionPerformedā€, line 6, in
at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:322)
at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runPrepUpdate(AbstractDBUtilities.java:245)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)

java.lang.Exception: java.lang.Exception: Error executing system.db.runPrepUpdate(INSERT INTO blob2 (blob) VALUES (?), [[B@163d01d], , , false)

at org.python.core.PyException.fillInStackTrace(PyException.java:70)
at java.lang.Throwable.<init>(Throwable.java:181)
at java.lang.Exception.<init>(Unknown Source)
at java.lang.RuntimeException.<init>(Unknown Source)
at org.python.core.PyException.<init>(PyException.java:46)
at org.python.core.PyException.<init>(PyException.java:43)
at org.python.core.Py.JavaError(Py.java:481)
at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:322)
at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runPrepUpdate(AbstractDBUtilities.java:245)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.python.core.PyReflectedFunction.__call__(PyReflectedFunction.java:186)
at com.inductiveautomation.ignition.common.script.ScriptManager$ReflectedInstanceFunction.__call__(ScriptManager.java:455)
at org.python.core.PyObject.__call__(PyObject.java:404)
at org.python.core.PyObject.__call__(PyObject.java:408)
at org.python.pycode._pyx10.f$0(<event:actionPerformed>:6)
at org.python.pycode._pyx10.call_function(<event:actionPerformed>)
at org.python.core.PyTableCode.call(PyTableCode.java:165)
at org.python.core.PyCode.call(PyCode.java:18)
at org.python.core.Py.runCode(Py.java:1261)
at com.inductiveautomation.ignition.common.script.ScriptManager.runCode(ScriptManager.java:539)
at com.inductiveautomation.factorypmi.application.binding.action.ActionAdapter.runActions(ActionAdapter.java:152)
at com.inductiveautomation.factorypmi.application.binding.action.ActionAdapter.invoke(ActionAdapter.java:261)
at com.inductiveautomation.factorypmi.application.binding.action.RelayInvocationHandler.invoke(RelayInvocationHandler.java:55)
at $Proxy13.actionPerformed(Unknown Source)
at javax.swing.AbstractButton.fireActionPerformed(Unknown Source)
at javax.swing.AbstractButton$Handler.actionPerformed(Unknown Source)
at javax.swing.DefaultButtonModel.fireActionPerformed(Unknown Source)
at javax.swing.DefaultButtonModel.setPressed(Unknown Source)
at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(Unknown Source)
at java.awt.Component.processMouseEvent(Unknown Source)
at javax.swing.JComponent.processMouseEvent(Unknown Source)
at java.awt.Component.processEvent(Unknown Source)
at java.awt.Container.processEvent(Unknown Source)
at java.awt.Component.dispatchEventImpl(Unknown Source)
at java.awt.Container.dispatchEventImpl(Unknown Source)
at java.awt.Component.dispatchEvent(Unknown Source)
at java.awt.LightweightDispatcher.retargetMouseEvent(Unknown Source)
at java.awt.LightweightDispatcher.processMouseEvent(Unknown Source)
at java.awt.LightweightDispatcher.dispatchEvent(Unknown Source)
at java.awt.Container.dispatchEventImpl(Unknown Source)
at java.awt.Window.dispatchEventImpl(Unknown Source)
at java.awt.Component.dispatchEvent(Unknown Source)
at java.awt.EventQueue.dispatchEventImpl(Unknown Source)
at java.awt.EventQueue.access$000(Unknown Source)
at java.awt.EventQueue$1.run(Unknown Source)
at java.awt.EventQueue$1.run(Unknown Source)
at java.security.AccessController.doPrivileged(Native Method)
at java.security.AccessControlContext$1.doIntersectionPrivilege(Unknown Source)
at java.security.AccessControlContext$1.doIntersectionPrivilege(Unknown Source)
at java.awt.EventQueue$2.run(Unknown Source)
at java.awt.EventQueue$2.run(Unknown Source)
at java.security.AccessController.doPrivileged(Native Method)
at java.security.AccessControlContext$1.doIntersectionPrivilege(Unknown Source)
at java.awt.EventQueue.dispatchEvent(Unknown Source)
at java.awt.EventDispatchThread.pumpOneEventForFilters(Unknown Source)
at java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source)
at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.awt.EventDispatchThread.run(Unknown Source)

Caused by: java.lang.Exception: Error executing system.db.runPrepUpdate(INSERT INTO blob2 (blob) VALUES (?), [[B@163d01d], , , false)
ā€¦ 56 more
Caused by: com.inductiveautomation.ignition.client.gateway_interface.GatewayException: Unable to read response from Gateway.
at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.newGatewayException(GatewayInterface.java:302)
at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.getResponse(GatewayInterface.java:388)
at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.sendMessage(GatewayInterface.java:238)
at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.sendMessage(GatewayInterface.java:229)
at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.runPrepStmt(GatewayInterface.java:544)
at com.inductiveautomation.ignition.client.script.ClientDBUtilities._runPrepStmt(ClientDBUtilities.java:72)
at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runPrepUpdate(AbstractDBUtilities.java:243)
ā€¦ 54 more
Caused by: org.xml.sax.SAXParseException: An invalid XML character (Unicode: 0x1) was found in the CDATA section.
at com.sun.org.apache.xerces.internal.parsers.AbstractSAXParser.parse(Unknown Source)
at com.sun.org.apache.xerces.internal.jaxp.SAXParserImpl$JAXPSAXParser.parse(Unknown Source)
at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.getResponse(GatewayInterface.java:386)
ā€¦ 59 more

Ignition v7.4.2 (b953)
Java: Sun Microsystems Inc. 1.6.0_24
[/code]

For sure iā€™m doing something wrong, but I donā€™t know what. What Iā€™m trying is:

1 - Button with this script:

[code]path = system.file.openFile()

if path != None:
bytes = system.file.readFileAsBytes(path)
event.source.parent.getComponent(ā€˜Label 1ā€™).text = str(bytes)
system.db.runPrepUpdate(ā€œINSERT INTO blobtest (blobdata) VALUES (?)ā€, [bytes])[/code]
2 - Selecting ā€˜Dibujo.PNGā€™ from the Desktop
3 - Inserting it in a database with two columns: Index(int) and Blob(Blob)

16

This is the file iā€™m trying to store:


I made it using paint just to try storing in databases.

17

Just to post the resolution, the blob column had been named ā€˜blobā€™ which was causing the query to throw an error. Once the column name was changed everything began to work as expected.

2 Likes