I’ve got a problem with string handling, and I’ve got most of it figured out except for one little part. I went over this with Bobby earlier and I checked out a few things. Thanks again Bobby!
This is what I’m doing: I have a table with a varchar(100) field. I wrote a socket program that grabs udp messages and writes them to this table. My messages are 44 bytes long, and I verified that all 44 bytes are making it to the table by querying the column with “SELECT len(Message) FROM Messages WHERE MessageID = 1”.
The problem is that the string will often contain null characters in the middle, and for some reason fpmi truncates the string at the first null. This is what I have in my project:
There is a dynamic property called “Message” of type string. I bind it to a query with “SELECT Message FROM Messages WHERE Message ID = 1”.
I have another dynamic property called “MessageLength”. I bind it to another query as above, “SELECT len(Message) FROM Messages WHERE Message ID = 1”.
In the component, I have a property change event set up to run some script whenever the content of the message changes:
if event.propertyName == 'Message':
Message = event.source.Message
print len(Message)
But, when I bind the message to the dynamic property “Message”, it is truncated at ten bytes (the 11th byte is null). But I know the table data is correct, because the other dynamic property “MessageLength” shows 44. And although the string is jibberish, I can see that there are many more than 10 characters in the field.
Bobby had me look at prepared statements for the insert, but since then I’ve verified that the data is making it to the table correctly with my exisiting code. I just need to get it back out into the fpmi environment. I’ve dealt with this when reading files in Python by using the ‘rb’ parameter, but don’t know if there is something I can do in my query to emulate that. I thought of using the varbinary type, but didn’t know how to bind that to any of the fpmi data types.
I hope this makes sense, and I’m probably missing something obvious. Any ideas?
I can’t escape them, because even a null byte means something (it’s a representation of the IO status from a PLC) and each byte can have the full 0-255 range if values.
If I do a select in Management Studio, it selects the whole string, so the query itself isn’t the problem. It has something to do with the way it is parsed to a string in python. I use the struct module to put the same string in the database, so maybe I need to get it out the same way and use something else, such as a MessageID, to trigger the script. But if there was a simple work around that I’m missing, that would be best.
This is an array of data of different data types from the plc in a single blob. I compress it in the plc and use the struct module to unpack it and put it in the database. I wanted to evaluate the parts of the array in fpmi in its raw form. Varbinary would have been a better choice of a data type, but I ran into other problems (see below).
I could have used either. I used strings (varchar()) because SQLServer handled them ok and I thought the string data type would be the easiest to bind to in fpmi and iterate through. If I use varbinary instead, which data type do I use to bind to the query? I also ran into another problem where I get two different lengths when using the varbinary data type if I run the "Select len(MessageBytes) from Messages" in each system. In SQLServer I get 44, but in fpmi I get 60.
I really don’t think strings are a good idea. I’m suprised that the len() of that string is giving you 44 - null (0x00) is the classic terminator character for a variable length string, so you can’t really hope to pretend that it’ll get represented as a character.
Unfortunately you can’t create a byte[] dynamic property, so you can’t use bindings. But you can use scripting to select the BLOB data.
Just got off the phone with Travis and Bobby, and they hammered something out. We ended up creating a Java byte array from the varbinary data, and it works like a charm. Don’t know how useful it would be for anyone else, but it’s a good thing to have in the toolbox.
We had to pull in the byte array in a dataset property and use a python script to convert the raw data to a string. For example, use this script on a component with dataset and stringval dynamic properties to convert the raw data of the byte array to a string.
On a PropertyChange event:
from java.lang import String
if event.propertyName == "dataset":
bytes = event.newValue.getValueAt(0,0)
event.source.stringval = String(bytes)