String overflow during concatenation

This one is pretty simple error message (really simple), but sometimes people tend to not keep this on mind when coding. I am one among them and I did face this issue today.


I had to pass a SQL query from a custom dex code to get some value based on a complex calculation. It involved so many fields and an intricate JOIN. I decided to go against PURE DEX code, which would have ideally got RANGEs and GET TABLEs.


I did something like this:


local text l_tQuery;
...
...

l_tQuery = "SELECT [an expression of calculation which involves more fields] FROM [custom table] JOIN... ... ..."

The above query is really long. Once done, I compiled this script and faced no errors. When I installed the chunk and ran the process, the following errors greeted me:

Initially I was surprised to see this, as I had declared the variable as TEXT and it should ideally hold upto 32000 characters. My query was also pretty less than 32000 characters. And when I referred to the Dexterity Help Manual on this error, this is what it tells us:


--
String overflow during concatenation.
Situation
A concatenation of two or more strings resulted in a string longer than 255 characters.
Solution
Check the script where strings are concatenated (using the + operator) to determine whether the 255-character limit for strings is being exceeded.
--


Well, it actually means: We cannot concatenate and assign a string value at one stretch that would exceed 255 characters.


For instance, my SQL query would have got nearly 1000 characters and I had written it in one single assignment. When I pass this SQL Statement from Dex, I have to first construct it as a typical text value and then execute it using SQL_Execute().


When I construct the text value, I should have done like below, if my SQL statement is more than 255 characters long:


l_tQuery = "";
l_tQuery = l_tQuery + "SELECT [expression] ";
l_tQuery = l_tQuery + "FROM [custom table] ";
... ... and so on.


Thereby, at one stretch we would not have concatenated more than 255 characters length string.


I thought this would be useful for developers out there wondering with this error.


VAIDY