12/18/2020 2:40 AM | |
Joined: 10/22/2015 Last visit: 9/10/2024 Posts: 23 Rating: (4) |
Hello, I added more characters for SQL queries in this application example provided in this link: we have a limitation on the length of a string for queries, however if you need a larger query, I added 3 more strings. Before the block had only 1 string for query, now it has 4 strings, which are automatically concatenated, just write the query and when you reach the 254 character limit of the first string, keep writing in the next string. This is very useful for large queries. I tested it and it's working. I hope to help someone! Attachment109779336_SQL_S7_1500_CODE_V20.zip (1072 Downloads) |
Last edited by: Jen_Moderator at: 12/18/2020 11:58:25Optimized link. |
|
This contribution was helpful to9 thankful Users |
12/16/2022 5:24 PM | |
Joined: 4/28/2015 Last visit: 6/29/2024 Posts: 1 Rating: (0) |
For the V31 library do you have the same? Thanks a lot |
9/13/2024 2:17 PM | |
Joined: 1/24/2017 Last visit: 10/8/2024 Posts: 4 Rating: (2) |
Alright so continuing from yesterday, I'm now apparently connecting and sending my SQL command just fine to my database, but I get an error 8609 on my AnalyzeTokens FB after executing the command. My simple "Insert" command also isn't resulting in any data being sent to my database. I went back to a simple 1-string command and that works, so I haven't completely broken the LSql FB. However, trying my 2-string command that's only like 280 characters long, which worked on OP's modifications, now no longer works. I get an error 8609 in my AnalyzeTokens FB, and no data gets inserted into my database. So the stuff I did yesterday regarding increasing array sizes seems to have screwed something up when concatenating strings. But at least my PLC doesn't crash anymore! Looking at the AnalyzeTokens DB, the error token message I get is telling me it's a SQL command syntax error. What's weird is even after I switched to the shorter SQL commands, it's referencing a syntax error for text that isn't even in my command anymore. So it's possible that the commands aren't properly instantiating/clearing between LSql FB calls. I restarted/re-downloaded the entire PLC to clear all the data, and now when I execute the same command, everything seems OK (no error messages), but no data gets sent to my database. Looking into how TSend and TRcv blocks are structured for clues shows that in theory, if I understand the "Variant" datatype correctly, that they should be able to send 'any' type or size of data. So me modifying SQLBatchData to increase the size shouldn't be an issue when calling TSend:
But not getting an error code essentially means I'm stumbling in the dark now. If I clear all my commands and just send an empty string, I am getting the same response (i.e. no errors, no data actually sent). So it's almost like the strings I'm entering for my SQL commands have no effect on what actually happens inside the LSql FB. I've apparently screwed something up. I can confirm that no matter what string I send, I get the same response (even if the command isn't formatted correctly). Looking back at the mods I made to the LSql_WriteData FC, it seems like maybe my tempLen <= 512 condition may not be working the way I intended. Instead I'll remove that and add a boolean input called "asSqlCmd" and use it in a similar fashion to how "asPassword" is currently used:
It will only be TRUE for the actual SQL command, while all the login WriteData calls will use FALSE. Then in the WriteData logic, if asSqlCmd is TRUE, we use the dataLong tag, and if it's false we stick with the data tag. These 2 conditions are encased in their own REGIONs for ease-of-reading, following the format that Siemens originally set up = REGION SQLCommand and REGION Non-SQLCommand. Now executing my 1-string command works again. My 2-string command at ~280 characters works. And my ~750 characters 4-string command works now. Excellent! I did another restart/full re-download of the PLC to try to initialize everything and make sure it still works. Everything still working One thing worth noting is that if I want to go back to only using 1 string for my command, I need to fire an empty string into the other 3 inputs on the LSql FB, otherwise it's like it remembers/holds on to those strings until something else is entered. Anyway, for anyone jumping in here or who's followed along with this epic saga, attached is sample code. If you need it to work as much as I did, I sincerely hope it works for you. AttachmentLSQL_CODE_v31_1_With-4-String-Long-Query.zip (257 Downloads) |
This contribution was helpful to1 thankful Users |
Follow us on