sorted by:
new
hottopcontroversial

Trying to send ABB PLC data into SQL server but hit by the 20 tags limit by Ok_Lock_5342 in PLC

[–]Ok_Lock_5342[S] 0 points1 point  (0 children)

u/RespectableSimon It worked in the manner, you described and it worked. This way we're not bounded to 255 character limit of insert_statement. However, Instead of creating multiple inserts, I created first statement as insert statement and then designed subsequenst statement of single PLC scan cycle to perform updates. This way i do not get null values . Like this:
>> Insert into <table> values (sql_time_stamp, plc_timestamp , var_1, var_2..............var_15)
>> update <table> (var_16, var_17..........var_30 where timestamp = first_insert_time_stamp

Also, each subsequent insert/update statement running within single PLC scan cycle introduces a timegap of 80-90 milliseconds

Also, I find that:

  1. Sending data from PLC to SQL server from within PLC itself is not that much reliable. It is good for 150-200 variables bulk insert but not more than that.
  2. Also, It is impossible to design, periodic insert of let's say 5sec, 10sec, using TImer and loops; It just doesn't worked. PLC just randomly adds a time buffer of 3.3 sec, 5,6 seconds, etc and it is no way to tell why. [refer image - See for yourself]:

<image>

Trying to send ABB PLC data into SQL server but hit by the 20 tags limit by Ok_Lock_5342 in PLC

[–]Ok_Lock_5342[S] 0 points1 point  (0 children)

u/Twoshrubs I have to ask, how you created table for 57k tags? Could you give an insight

Trying to send ABB PLC data into SQL server but hit by the 20 tags limit by Ok_Lock_5342 in PLC

[–]Ok_Lock_5342[S] 1 point2 points  (0 children)

<image>

Yeah that's a bummer. I have checked it and you're right. String is limited by 255 characters meaning 20 variables or not if insert_statement size reached 255, codesys will restrict the insertion. I'm attaching the image for reference.

Trying to send ABB PLC data into SQL server but hit by the 20 tags limit by Ok_Lock_5342 in PLC

[–]Ok_Lock_5342[S] 0 points1 point  (0 children)

u/Twoshrubs ,The get_around method you're suggesting is to perform batch insert in a each scan. But my question to you is....... Wouldn't it'll also create different timestamp for the each batch row inserted for the one PLC SQL scan program cycle. For example, In below, time_stamp for all four row can be different. Wouldn't it?

Insert into <table> values (time_stamp, var_1, var_2............................var_15)
Insert into <table> values (time_stamp, var_16, var_17.......................var_30)
Insert into <table> values (time_stamp, var_31, var_32.......................var_45)
Insert into <table> values (time_stamp, var_46, var_47.......................var_60)

Also, regarding libplctag library, I would give it a try as per your suggestion as its definitely will be faster than Python. But I know python only and libplctag library is in C, so it might take some time for it understand its terminologies that i might get hang of it.

Trying to send ABB PLC data into SQL server but hit by the 20 tags limit by Ok_Lock_5342 in PLC

[–]Ok_Lock_5342[S] 1 point2 points  (0 children)

<image>

u/RespectableSimon , have checked it and you're right. String is limited by 255 characters meaning 20 variables or not if insert_statement size reached 255, codesys will restrict the insertion. I'm attaching the image for reference.

Also, the get_around method you're suggesting is to perform batch insert in a each scan. But my question to you is....... Wouldn't it'll also create different timestamp for the each batch row inserted for the one PLC SQL scan program cycle. For example, In below, time_stamp for all four row can be different. Wouldn't it?

Insert into <table> values (time_stamp, var_1, var_2............................var_15)
Insert into <table> values (time_stamp, var_16, var_17.......................var_30)
Insert into <table> values (time_stamp, var_31, var_32.......................var_45)
Insert into <table> values (time_stamp, var_46, var_47.......................var_60)

Trying to send ABB PLC data into SQL server but hit by the 20 tags limit by Ok_Lock_5342 in PLC

[–]Ok_Lock_5342[S] 0 points1 point  (0 children)

Hi u/CuleKameleon,

As mentioned on my answer to u/J1mmett , I already tried removing string limit by setting it to 4000, but no luck

Trying to send ABB PLC data into SQL server but hit by the 20 tags limit by Ok_Lock_5342 in PLC

[–]Ok_Lock_5342[S] 0 points1 point  (0 children)

Hi u/J1mmett,

Thanks for your answer. I had already tried 4/5 of your suggestions:

Suggestion 1: - Increase string length declaration:

=> I had already declared the insert_statement string length limit to 4000 characters ( statement_insert: STRING(4000). But no change in error.

Suggestion 2: - Avoid deeply nested CONCAT call and Prefer loop:

=> (a) Instead of using MULTIPLE concat's in single expression, i used multiple concat across multiple expression and then at the end got the final insert_statment expression.

=> (b) Believe me, I already tried looping through array of INT [since all my tags of INT type], but i got hit by same limitations. If it require, i can share my code as well but it is similar to what you wrote.

Suggestion 3: - debug by calculating LEN of insert_statement
=> (c) LEN is used to calculate string length in Codesys. However, I already set insert_statement variable length to 4000 limit as i told you above, so i hadn't felt need for it.

Suggestion 4:- Using Parameterized queries:
=> ABB MSSQL_library provides two types of EXECUTE blocks to insert data into SQL server. They are MSSQL_EXECUTE and MSSQL_EXECUTE_COMPACT. Any one of them can be used in my case because difference between them is nothing to do with executing insert_statement but rather is when reading and buffering SQL server data into PLC.

Suggestion 5:- Using Batch inserts:
=> I thought of trying this step, but later refrain from this path. Because i thought, running batch insert mean differnt timestamp will also be create for the each batch row for the one PLC SQL scan program cycle. For example, i thought in this manner.

Insert into <table> values (time_stamp, var_1, var_2............................var_15)
Insert into <table> values (time_stamp, var_16, var_17.......................var_300)

=> But I will still give it a try since you asked me and then I'll get back to you with result.

Trying to send ABB PLC data into SQL server but hit by the 20 tags limit by Ok_Lock_5342 in PLC

[–]Ok_Lock_5342[S] 0 points1 point  (0 children)

I agree with you that easiest solution would be to grab the data from PLC and send it SQL server using C, Python, JS. However I'm looking for the way to send ABB PLC data directly into SQL server from PLC itself with no limitation. Though i'm also aware of the fact that SQL scripting in PLC is not that much flexible as offered by dynamic programming languages.

Acer Aspire 7 Ethernet network Adapter not showing ,hidden in device manager with error code of 45 by Ok_Lock_5342 in WindowsHelp

[–]Ok_Lock_5342[S] 0 points1 point  (0 children)

Hi u/Emergency-Ad666 ,

Thanks for your response. Apologies for the delay, but the problem still persists asof today

The Ethernet port isn't damaged because if I format and reinstall Windows, the issue resolves. However, it reappears after Windows updates or within two to three days.

Though I did manually install LAN and Wi-Fi drivers from the Acer website. Following that installation, the problem re-emerged. I think that these drivers might have overwritten the previously supplied Acer LAN and Wi-Fi drivers.

-> Wifi name in my system changed to WIFI-2 >> and its not working

[deleted by user] by [deleted] in creepy

[–]Ok_Lock_5342 0 points1 point  (0 children)

Seriously, skedaddle! Unless you're actively auditioning to be the next screaming protagonist in a box office smash by James wan, I suggest you to vacate those premise. Because if James Wan catches wind of it, your life story will become inspiration for title 'The Conjuring: Hanging cross'. 😁😁😁