I have a standard group with an OPC item called 'RollProfile' and the contents are shown below. Dragging this item into a group creates a data type of 'array'. FSQL created varchar(255) in MS-SQL but I changed it to varchar(8000) because some of the data was being truncated.
I'm not sure why it quit working but I get the error shown below. I am currently using 4.2.6
[quote]Error executing update query: Disallowed implicit conversion from data type ntext to data type varchar, table 'PMI_Line1.factorypmi.L1_NDCRollProfile_Test', column 'RollProfile'. Use the CONVERT function to run this query.
at FactorySQL.LocalDBOperationProvider.ExecuteParameterNonQuery(DBConnectionID ConnectionID, String Query, IDbDataParameter Parameters, Int32 Timeout)
at FactorySQL.Groups.StandardGroup.DBItemExecutionCollection.WriteData()
at FactorySQL.Groups.FSQLGroup.InternalExecute(EvaluationToken EvalToken)
System.Data.SqlClient.SqlException: Disallowed implicit conversion from data type ntext to data type varchar, table 'PMI_Line1.factorypmi.L1_NDCRollProfile_Test', column 'RollProfile'. Use the CONVERT function to run this query.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at FactorySQL.LocalDBOperationProvider.ExecuteParameterNonQuery(DBConnectionID ConnectionID, String Query, IDbDataParameter Parameters, Int32 Timeout)
at FactorySQL.LocalDBOperationProvider.ExecuteParameterNonQuery(DBConnectionID ConnectionID, String Query, IDbDataParameter Parameters, Int32 Timeout)
at FactorySQL.Groups.StandardGroup.DBItemExecutionCollection.WriteData()
at FactorySQL.Groups.FSQLGroup.InternalExecute(EvaluationToken EvalToken)[/quote]
I made the change to the SQL table and started the group. There are no errors and it did write to the database. The line is down until later this week so I do not have any data changing but it looks promising.
Thank you!
Yeah, FactorySQL will flatten down arrays to strings and store them like that… looks like you just had to get your column type in line (unfortunately FSQL just creates all string columns as varchar(255)).
On a side note, I don’t know how you plan to use the array data, but you have a few options that I wanted to make sure you knew about:
Leave it as-is. The values are recorded in the DB, you can load them back like that, and potentially use some scripting on the display side to format them how you want.
Use a stored procedure instead which will take the string input, parse it up, and store it how you want.
Use a block group to store the array vertically - Most OPC servers will let you address the sub elements of an array. You can create a block item with a Template (pattern based) segment which will let you set it up quickly. Then the items will be stored individually as rows under the same column. If storing historically, I recommend turning on the option for “Store block id” in order to get a unique ID for each insert event.
Anyhow, just wanted to throw all that out there, since many people get a bit confused when they have to work with array data and don’t know exactly where to start.
And thanks Dravik for the post, and welcome to the forum!
I currently have a script in FPMI that parses the string and displays the data on a chart. This way the data storage requirements are lower (I think). The array consists of 1 integer and 514 floating point values.
I thought about using a stored procedure but was unsure about performance issues having that many fields. Wouldn’t I end up needing a script to extract the data from SQL and pass it to a chart?
The OPC item array is not from a plc and cannot be addressed by individual element. The system has only one address for the data.
Sounds like your current solution is perfect for your needs. Just wanted to throw that info up there for anyone looking for help on arrays, since we get calls from time to time.
The main reason to parse it out on the entry side in a stored procedure would be to have the information in a standard db layout, so you could take advantage of the SQL language when querying it. If you don’t need to query the data inside, but instead treat it as a single “value” in terms of selection, then your approach is good, and probably will save space on storage.
I have to append this topic since I have a new problem.
[quote]Error executing update query: String or binary data would be truncated.
The statement has been terminated.
at FactorySQL.LocalDBOperationProvider.ExecuteParameterNonQuery(DBConnectionID ConnectionID, String Query, IDbDataParameter[] Parameters, Int32 Timeout)
at FactorySQL.Groups.StandardGroup.DBItemExecutionCollection.WriteData()
at FactorySQL.Groups.FSQLGroup.InternalExecute(EvaluationToken EvalToken)
System.Data.SqlClient.SqlException: String or binary data would be truncated.
The statement has been terminated.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at FactorySQL.LocalDBOperationProvider.ExecuteParameterNonQuery(DBConnectionID ConnectionID, String Query, IDbDataParameter[] Parameters, Int32 Timeout)
at FactorySQL.LocalDBOperationProvider.ExecuteParameterNonQuery(DBConnectionID ConnectionID, String Query, IDbDataParameter[] Parameters, Int32 Timeout)
at FactorySQL.Groups.StandardGroup.DBItemExecutionCollection.WriteData()
at FactorySQL.Groups.FSQLGroup.InternalExecute(EvaluationToken EvalToken)
[/quote]
As a review I had changed the field type to nvarchar(4000) and it started working. Well now the string length is 4067. I have another decimal place to deal with that is making the string longer. There is a space between each value and the delimitter so I tried using the Trim() function but that only returns 255 characters.
At this point I see only two options:
Remove the spaces from the string thereby making the length under 4000
I am not sure how to do this since the string functions only return 255 characters
Parse out each value and map it to a separate field in the table.
The only method I came up with was to use the IndexOf funciton to find each delimitter and then use the substring function extract and map the value. This seems like it will be a very tedious process given the large number of values to process.
What do you think would be the best way to proceed?
You could probably cut out a lot by using the “Replace” function to remove spaces:
Replace({data_item}," ","")
Of course, instead of “{data_item}” you’ll hit CTRL-Space and add a reference to the correct item.
In my tests the string functions didn’t limit the results to 255… and they’re certainly not programmed to do that, as far as I know. Make sure you have it pointing to the correct column- when you set this up, you’ll now want to make your original OPC item “Read Only” (double click on the item to find this option) and then have the Action item write back to the exact column that was previously written to by the OPC item.
I implemented the Replace function but still only had 255 characters in the table. I added the Len() function before and after the replace function:
Original length was 4067
New length using Replace() is 3037
I noticed my result datatype was still set to unspecified so I tried string and no change. I then tried long text and it is writing the full string to the table.
I thought the problem was in the function but as you pointed out, the function is fine.