Incorrect string value error when using system.db.runPrepUpdate

I am using system.db.runPrepUpdate to “send” messages to a forum-esque system I have set up in our Vision application.

A client tried copying and pasting this text into the message and sending it

"
I think you have to send the servers to us so we can configure the hardware properly. The VM configuration isn’t right and that is the basis on which the rest of the work will need to be done. We have attempted to make the changes remotely but the work required is hands on.

I am attaching a quote for the configuration labor and licensing costs, supposing you are able to ship the servers back. This will give you two clean servers with all hardware configured and tested and the right licensing & configuration for WonderWare to run up to 5 terminal server clients, with failover for runtime licenses and Distributed development setup.

I am happy to go over in detail each of the licenses referenced if you need me to do so. I know they seem like a lot, but you need all of the ones I list if you are to have the same communication paths as the standard ASU.
"

and it causes this error -

Traceback (most recent call last):
  File "<event:actionPerformed>", line 22, in <module>
  File "<event:actionPerformed>", line 22, in <module>
  File "<module:app.actions.helpdesk>", line 95, in updateThread
  File "<module:app.actions.helpdesk>", line 361, in sendMessage
  File "<module:app.helpdesk>", line 64, in sendMessage
I am attaching a quote for the configuration labor and licensing costs, supposing you are able to ship the servers back.  This will give you two clean servers with all hardware configured and tested and the right licensing & configuration for WonderWare to run up to 5 terminal server clients, with failover for runtime licenses and Distributed development setup.
I am happy to go over in detail each of the licenses referenced if you need me to do so.  I know they seem like a lot, but you need all of the ones I list if you are to have the same communication paths as the standard ASU., 2020-06-23 08:57:00, 0], , , true, false)
	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:362)
	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runPrepUpdate(AbstractDBUtilities.java:258)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.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 listhdmessages(parentThreadId, sendingUser, text, createdAt, adminMessage) VALUES (?, ?, ?, ?, ?), [12546, 1112, I think you have to send the servers to us so we can configure the hardware properly.  The VM configuration isn’t right and that is the basis on which the rest of the work will need to be done.  We have attempted to make the changes remotely but the work required is hands on.
I am attaching a quote for the configuration labor and licensing costs, supposing you are able to ship the servers back.  This will give you two clean servers with all hardware configured and tested and the right licensing & configuration for WonderWare to run up to 5 terminal server clients, with failover for runtime licenses and Distributed development setup.
I am happy to go over in detail each of the licenses referenced if you need me to do so.  I know they seem like a lot, but you need all of the ones I list if you are to have the same communication paths as the standard ASU., 2020-06-23 08:57:00, 0], , , true, false)

	at org.python.core.Py.JavaError(Py.java:552)
	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:362)
	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runPrepUpdate(AbstractDBUtilities.java:258)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
	at java.base/java.lang.reflect.Method.invoke(Unknown Source)
	at org.python.core.PyReflectedFunction.__call__(PyReflectedFunction.java:188)
	at com.inductiveautomation.ignition.common.script.ScriptManager$ReflectedInstanceFunction.__call__(ScriptManager.java:521)
	at org.python.core.PyObject.__call__(PyObject.java:413)
	at org.python.pycode._pyx117.sendMessage$3(<module:app.helpdesk>:66)
	at org.python.pycode._pyx117.call_function(<module:app.helpdesk>)
	at org.python.core.PyTableCode.call(PyTableCode.java:171)
	at org.python.core.PyBaseCode.call(PyBaseCode.java:308)
	at org.python.core.PyFunction.function___call__(PyFunction.java:471)
	at org.python.core.PyFunction.__call__(PyFunction.java:466)
	at org.python.pycode._pyx138.sendMessage$10(<module:app.actions.helpdesk>:387)
	at org.python.pycode._pyx138.call_function(<module:app.actions.helpdesk>)
	at org.python.core.PyTableCode.call(PyTableCode.java:171)
	at org.python.core.PyBaseCode.call(PyBaseCode.java:308)
	at org.python.core.PyFunction.function___call__(PyFunction.java:471)
	at org.python.core.PyFunction.__call__(PyFunction.java:466)
	at org.python.pycode._pyx138.updateThread$2(<module:app.actions.helpdesk>:97)
	at org.python.pycode._pyx138.call_function(<module:app.actions.helpdesk>)
	at org.python.core.PyTableCode.call(PyTableCode.java:171)
	at org.python.core.PyBaseCode.call(PyBaseCode.java:171)
	at org.python.core.PyFunction.__call__(PyFunction.java:434)
	at org.python.pycode._pyx137.f$0(<event:actionPerformed>:32)
	at org.python.pycode._pyx137.call_function(<event:actionPerformed>)
	at org.python.core.PyTableCode.call(PyTableCode.java:171)
	at org.python.core.PyCode.call(PyCode.java:18)
	at org.python.core.Py.runCode(Py.java:1614)
	at com.inductiveautomation.ignition.common.script.ScriptManager.runCode(ScriptManager.java:779)
	at com.inductiveautomation.factorypmi.application.binding.action.ActionAdapter.runActions(ActionAdapter.java:206)
	at com.inductiveautomation.factorypmi.application.binding.action.ActionAdapter.invoke(ActionAdapter.java:297)
	at com.inductiveautomation.factorypmi.application.binding.action.RelayInvocationHandler.invoke(RelayInvocationHandler.java:57)
	at com.sun.proxy.$Proxy48.actionPerformed(Unknown Source)
	at java.desktop/javax.swing.AbstractButton.fireActionPerformed(Unknown Source)
	at java.desktop/javax.swing.AbstractButton$Handler.actionPerformed(Unknown Source)
	at java.desktop/javax.swing.DefaultButtonModel.fireActionPerformed(Unknown Source)
	at java.desktop/javax.swing.DefaultButtonModel.setPressed(Unknown Source)
	at java.desktop/javax.swing.plaf.basic.BasicButtonListener.mouseReleased(Unknown Source)
	at java.desktop/java.awt.Component.processMouseEvent(Unknown Source)
	at java.desktop/javax.swing.JComponent.processMouseEvent(Unknown Source)
	at java.desktop/java.awt.Component.processEvent(Unknown Source)
	at java.desktop/java.awt.Container.processEvent(Unknown Source)
	at java.desktop/java.awt.Component.dispatchEventImpl(Unknown Source)
	at java.desktop/java.awt.Container.dispatchEventImpl(Unknown Source)
	at java.desktop/java.awt.Component.dispatchEvent(Unknown Source)
	at java.desktop/java.awt.LightweightDispatcher.retargetMouseEvent(Unknown Source)
	at java.desktop/java.awt.LightweightDispatcher.processMouseEvent(Unknown Source)
	at java.desktop/java.awt.LightweightDispatcher.dispatchEvent(Unknown Source)
	at java.desktop/java.awt.Container.dispatchEventImpl(Unknown Source)
	at java.desktop/java.awt.Window.dispatchEventImpl(Unknown Source)
	at java.desktop/java.awt.Component.dispatchEvent(Unknown Source)
	at java.desktop/java.awt.EventQueue.dispatchEventImpl(Unknown Source)
	at java.desktop/java.awt.EventQueue$4.run(Unknown Source)
	at java.desktop/java.awt.EventQueue$4.run(Unknown Source)
	at java.base/java.security.AccessController.doPrivileged(Native Method)
	at java.base/java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(Unknown Source)
	at java.base/java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(Unknown Source)
	at java.desktop/java.awt.EventQueue$5.run(Unknown Source)
	at java.desktop/java.awt.EventQueue$5.run(Unknown Source)
	at java.base/java.security.AccessController.doPrivileged(Native Method)
	at java.base/java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(Unknown Source)
	at java.desktop/java.awt.EventQueue.dispatchEvent(Unknown Source)
	at java.desktop/java.awt.EventDispatchThread.pumpOneEventForFilters(Unknown Source)
	at java.desktop/java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source)
	at java.desktop/java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
	at java.desktop/java.awt.EventDispatchThread.pumpEvents(Unknown Source)
	at java.desktop/java.awt.EventDispatchThread.pumpEvents(Unknown Source)
	at java.desktop/java.awt.EventDispatchThread.run(Unknown Source)
Caused by: java.lang.Exception: Error executing system.db.runPrepUpdate(INSERT INTO listhdmessages(parentThreadId, sendingUser, text, createdAt, adminMessage) VALUES (?, ?, ?, ?, ?), [12546, 1112, I think you have to send the servers to us so we can configure the hardware properly.  The VM configuration isn’t right and that is the basis on which the rest of the work will need to be done.  We have attempted to make the changes remotely but the work required is hands on.
I am attaching a quote for the configuration labor and licensing costs, supposing you are able to ship the servers back.  This will give you two clean servers with all hardware configured and tested and the right licensing & configuration for WonderWare to run up to 5 terminal server clients, with failover for runtime licenses and Distributed development setup.
I am happy to go over in detail each of the licenses referenced if you need me to do so.  I know they seem like a lot, but you need all of the ones I list if you are to have the same communication paths as the standard ASU., 2020-06-23 08:57:00, 0], , , true, false)
	... 71 more
Caused by: com.inductiveautomation.ignition.client.gateway_interface.GatewayException: SQL error for "INSERT INTO listhdmessages(parentThreadId, sendingUser, text, createdAt, adminMessage) VALUES (?, ?, ?, ?, ?)": (conn=454) Incorrect string value: '\xC2\x80\xC2\x99t ...' for column 'text' at row 1
	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.newGatewayException(GatewayInterface.java:339)
	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.sendMessage(GatewayInterface.java:313)
	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.sendMessage(GatewayInterface.java:266)
	at com.inductiveautomation.ignition.client.script.ClientDBUtilities._runPrepStmt(ClientDBUtilities.java:282)
	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runPrepUpdate(AbstractDBUtilities.java:256)
	... 69 more
Caused by: java.sql.SQLException: (conn=454) Incorrect string value: '\xC2\x80\xC2\x99t ...' for column 'text' at row 1
	at org.mariadb.jdbc.internal.util.exceptions.ExceptionMapper.get(ExceptionMapper.java:255)
	at org.mariadb.jdbc.internal.util.exceptions.ExceptionMapper.getException(ExceptionMapper.java:165)
	at org.mariadb.jdbc.MariaDbStatement.executeExceptionEpilogue(MariaDbStatement.java:238)
	at org.mariadb.jdbc.MariaDbPreparedStatementClient.executeInternal(MariaDbPreparedStatementClient.java:232)
	at org.mariadb.jdbc.MariaDbPreparedStatementClient.execute(MariaDbPreparedStatementClient.java:159)
	at org.mariadb.jdbc.MariaDbPreparedStatementClient.executeUpdate(MariaDbPreparedStatementClient.java:194)
	at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:136)
	at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:136)
	at com.inductiveautomation.ignition.gateway.datasource.SRConnectionWrapper$SRPreparedStatement.executeUpdate(SRConnectionWrapper.java:1015)
	at com.inductiveautomation.ignition.gateway.servlets.gateway.functions.RunPrepStmt.runUpdateQuery(RunPrepStmt.java:64)
	at com.inductiveautomation.ignition.gateway.servlets.gateway.functions.AbstractUpdateQuery.run(AbstractUpdateQuery.java:25)
	at com.inductiveautomation.ignition.gateway.servlets.gateway.functions.AbstractDBAction.invoke(AbstractDBAction.java:68)
	at com.inductiveautomation.ignition.gateway.servlets.Gateway.doPost(Gateway.java:412)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:707)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:790)
	at com.inductiveautomation.ignition.gateway.bootstrap.MapServlet.service(MapServlet.java:86)
	at org.eclipse.jetty.servlet.ServletHolder$NotAsyncServlet.service(ServletHolder.java:1391)
	at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:760)
	at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:547)
	at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:143)
	at org.eclipse.jetty.security.SecurityHandler.handle(SecurityHandler.java:590)
	at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:127)
	at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:235)
	at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:1607)
	at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:233)
	at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1297)
	at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:188)
	at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:485)
	at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:1577)
	at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:186)
	at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1212)
	at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:141)
	at org.eclipse.jetty.server.handler.HandlerList.handle(HandlerList.java:59)
	at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:127)
	at org.eclipse.jetty.rewrite.handler.RewriteHandler.handle(RewriteHandler.java:322)
	at org.eclipse.jetty.server.handler.HandlerList.handle(HandlerList.java:59)
	at org.eclipse.jetty.server.handler.HandlerCollection.handle(HandlerCollection.java:146)
	at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:127)
	at org.eclipse.jetty.server.Server.handle(Server.java:500)
	at org.eclipse.jetty.server.HttpChannel.lambda$handle$1(HttpChannel.java:383)
	at org.eclipse.jetty.server.HttpChannel.dispatch(HttpChannel.java:547)
	at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:375)
	at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:270)
	at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:311)
	at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:103)
	at org.eclipse.jetty.io.ChannelEndPoint$2.run(ChannelEndPoint.java:117)
	at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.runTask(EatWhatYouKill.java:336)
	at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.doProduce(EatWhatYouKill.java:313)
	at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.tryProduce(EatWhatYouKill.java:171)
	at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.run(EatWhatYouKill.java:129)
	at org.eclipse.jetty.util.thread.ReservedThreadExecutor$ReservedThread.run(ReservedThreadExecutor.java:388)
	at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:806)
	at org.eclipse.jetty.util.thread.QueuedThreadPool$Runner.run(QueuedThreadPool.java:938)
	at java.lang.Thread.run(null)

Ignition v8.0.12 (b2020042115)
Java: Azul Systems, Inc. 11.0.6

This Incorrect string value: '\xC2\x80\xC2\x99t ...' for column 'text' at row 1 made me think it had to do with the line returns, but removing those I still get the same error. Any ideas?’

To recreate, make a table in MySQL 5.6 with a text column, and us system.db.runPrepUpdate to insert the aforementioned string.

The best guess is - You are missing single quotation mark on text column value.

Can you please try - ‘I think you have to send the servers to us…’

If still doesn’t work,
Can you please post the script initiating the transaction and where the values are coming from.

Thanks

Here is my full script

def sendMessage(parentThread, sendingUser, text, admin=0, key = 0):
	'''
		This function inserts a message into the chat room from a user.
		Arguments:
			parentThread: int, parentThreadId of listhdmessages, what thread does the messgae belong to?
			sendingUser: int, who sent the message?
			text: str, what is the message?
			admin: bit(1), 0 is default (normal message), 1 is admin (black text)
			attachment: str, path of attachment file
		Returns:
			Nothing, unless key = 1 then returns id of message
	'''
	import system
    print text
	createdAt = system.db.dateFormat(system.date.now(), "yyyy-MM-dd HH:mm:ss")
	insertMessageQuery = "INSERT INTO listhdmessages(parentThreadId, sendingUser, text, createdAt, adminMessage) VALUES (?, ?, ?, ?, ?)"
	# Insert message and refresh "chat room"
	returnKey = system.db.runPrepUpdate(insertMessageQuery,[parentThread, sendingUser, text, createdAt, admin], getKey = key)
	if key == 1:
		return returnKey

It works for regular messages, and it is definitely called correctly. Here’s what the print text shows -

text: I think you have to send the servers to us so we can configure the hardware properly.  The VM configuration isn’t right and that is the basis on which the rest of the work will need to be done.  We have attempted to make the changes remotely but the work required is hands on.

 

I am attaching a quote for the configuration labor and licensing costs, supposing you are able to ship the servers back.  This will give you two clean servers with all hardware configured and tested and the right licensing & configuration for WonderWare to run up to 5 terminal server clients, with failover for runtime licenses and Distributed development setup.

 

I am happy to go over in detail each of the licenses referenced if you need me to do so.  I know they seem like a lot, but you need all of the ones I list if you are to have the same communication paths as the standard ASU.

So it looks like it’s fed correctly.

Have a look here to see if the top answer applies to you

1 Like

Interesting. It looks like I already have everything in utf8

image

Just running ALTER DATABASE databasename CHARACTER SET utf8 COLLATE utf8_unicode_ci;
didn’t change anything since I am already in utf8.

However, running ALTER TABLE tablename CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci; did fix the problem.

So that I understand this, even though my server variables say utf8, my tables actually are NOT utf8, or at least are not the “right” utf8? Very weird. But thanks for the help!

1 Like

MySQL is, to put it gently, bad at UTF-8.

3 Likes