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