I’m using FSQL 4.2.11 to write back set point in a PLC by selecting a specific Block id. FactorySQL seems to be made for that but I can’t find a way to do it.
I have more than a thousand of value so I’m using block group. I have one block group “OPC to DB” running each hour to get the data and one block group “DB to OPC” writing the value when a trigger is activate. With that, I want to write back value from a specific date in the PLC. I’m using a stored procedure in Microsoft SQLserver to find in the table all the values from the selected date and time. This stored procedure is call by an action item in the “DB to OPC” group. I supposed that what is writting in the PLC is the part of the table selected by the stored procedure. Am I wrong?
My problem is…it’s seems that thing don’t go as I planned! I can’t see what I’m doing wrong because at no point I have an error message but nothing is written in the PLC. Do I have to use the Stored Procedure block instead of calling one with an action item? Do I have to first create a table with the selected data and then write it in the PLC? And most important, am I clear in my explanations?
I think I understand what you’re doing, but it sounds like you’re a little off.
The block group will map between the table specified on the action tab to the items specified in the group. Each block group has a “field name” - this is the column that it will map to in the table. The items will be mapped to the rows. Which rows are returned will depend on your group settings- first “block” (the first x number of rows), last block, or a block specified by a custom where clause.
Your description of what you’re doing with the stored procedure doesn’t really make sense. Well, it does in the sense that you have a stored procedure which generates a set of rows, but there’s no way in the block group to map that to the OPC items. Your group doesn’t throw an error because you probably have it pointed to a table that it generated, and so it’s just reading from there and executing happily.
You have several options, but given what you’ve described, the quickest thing to do would be the following:
Set the group to OPC-DB and set the table to some new name, like “block_transition”. Set the row mode to “update first block”. Run the group, this will create a table and rows for you.
Stop the group, change it to “DB-to-OPC”. Start it again.
Change your stored procedure to collect the rows to write and write them to that table you just created.
Now, trigger the stored procedure however you’d like (for example, a separate stored procedure group with no items). When the procedure runs, it will update the values of your intermediate table. The block group will see those, since it’s watching that block, and will write them to the PLC.
As I said, there are other ways to do this, for example, by working on a custom where clause. However, I would start with this and get a good idea of how the block group works, because a custom where clause can be tricky (for example, you can’t directly see what values are being written, or if you even have enough rows).
It works! You definitly understood what I wanted to do, even with my broken english. Thanks a lot for your help.
I did as you said and I can see that I was a little bit off. If I understand I can’t do in the same job the research in the complete data table and the writting in the PLC. It seems I have to separated these steps. The stored procedure now clear a temporary table and writes down the value I want to put in the PLC. I’ve made a Stored Procedure group calling mine and a Block group (DB to OPC) writting down in the PLC the data written in this temporary table. Both group are trigged on the same trigger and it’s looks to work pretty well.
Now, lets get a little bit more tricky. I may have to reboot several PLC at the same time so the way I’m doing it right now will certainly cause communication problems. PLC will have different number of tag so different number of rows for the table. Is it possible to directly send the data to the PLC which I detected the flag without creating a temporary table? I was working in a special where clause but I can’t figure out what to write in one row to specify which block of data I want to send to the PLC. My stored procedure do that but I can’t write the “exec stored_procedure” in the where clause. I’m pretty green in this domain and againt I’m a little bit lost.
If I understand, and you want to extend this idea to multiple PLCs, I would stay with the method you are using to have the stored procedure write to the table. The difference would be that I would add another column to the table, “PLC_ID”. You would then create a block group for each plc, and have it point to the table, with the custom where clause “PLC_ID=1” or 2, and so on.
Your stored procedure can delete the rows for the plc and insert new ones, or update what is already there. It’s ok if the different blocks have a different number of rows, as long as there are rows for all of the PLC addresses. Also, I would make sure that there was a numerical primary key column (auto-increment) on the table, so that the rows get returned in the correct order for each block. You have to imagine - the block group will first execute a select query with your custom where clause, and then will map those values to the lists of PLC addresses.
There isn’t a way to write to arbitrary plc addresses from a stored procedure. The address must first be mapped through a group, or at least to SQLTags. The possibility of using a stored procedure group to bypass the temporary table could work, but if you have thousands of values, I think the block group and table approach is the best.
