I am using a Text Area box that will let the operators leave notes for the next shift. But, it cannot seem to handle an apostrophe within the text. For instance, if they wrote “Screen Bill’s parts for a missing clamping ring”, it would fail.
I have bound this text area to a text field in SQL Server. If I enter the text with an apostrophe directly in the database, then it displays correctly on the screen. I only get the error when I write to the database. The error is “Gateway Error 301: SQL Error: Invalid SQL statement or JDBC escape, terminating ‘’’ not found.”
edit: While I’m here, I have another question about text area boxes. I am trying to display information that would typically be printed on an address label. The PLC sends a string to the printer that contains the escape sequences to create the individual lines of text (i.e. carriage returns, line feeds), and what I want to do is display the same info on the screen. I tried entering four lines of text in notepad, pasting it into my database, and the lines show up exactly how I need them to on the GUI. Would it be as simple to take my PLC string and strip out the printer-specific escape sequences and replace them with Windows CR? Ideas?
Ok, about the queries breaking with single-apostrophes. This is because the automatically-generated UPDATE query that FactoryPMI generates in a SQL query property binding is a normal String based query. So, the single-quote in your string breaks the syntax of the UPDATE query. The way to do this is to use prepared statements. Using prepared statements, you’d write a query like this:UPDATE mytable SET mycolumn=? WHERE id=? and then when you run the query you pass in parameters that get assigned in order where question marks appear in the query.
In FactoryPMI, you’d do this on the PropertyChange event of the text area, or add a “Save” button next to the text area. You run a prepared statement in a script using fpmi.db.runPrepStmt. Let me know if you need more guidance with exactly how to do this for your situation.
As far as displaying multi-line text goes, you have 2 options: Use a Label, and convert the newline characters to tags and prepend your text with the tag to use HTML rendering. Or use a non-editable Text Area and use newlines or CRLF (windows newline combo - Carriage Return + Line Feed) to break lines. You’d probably do any replacing in the SQL query itself, or in Jython code. Again, we can guide you more if necessary.