I have some questions about the length of string input variable value in PowerBuilder embedded SELECT statement (PB 2019 R3 + SQL Anywhere 17).
We have some legacy PB code which splits string into 65535 character pieces, writes them to a temp. table (concatenating all pieces into a single value) and then passes temp. table column reference to the SELECT statement instead of passing the value directly as an input variable. I doubt that this workaround is still necessary in current versions of PB, but I could not find any info about this. So here are my questions (the 4th being most important):
- What is the maximum "safe" length of string input variable value in embedded SELECT statement (given that DBMS itself accepts LONG VARCHAR, i. e. up to 2 GB)? By saying "safe" I mean, that it should not lead to any abnormalities like crash or text truncation, or whatever else.
- What exactly does that limit depend on?
- How is PowerBuilder expected (designed) to handle the situation when that limit is exceeded?
- Is there any possibility that supplied input string value will be silently truncated, i. e. DBMS will receive incomplete string (as is the case when talking about the opposite direction - output variables are truncated to 32767 characters)?
This is a code sample that I've experimented with:
String ls_In Long ll_Out ls_In = Fill("A", 50000000) SELECT length(:ls_In) INTO :ll_Out FROM sys.dummy; IF SQLCA.SQLDBCode = 0 THEN MessageBox("Result", String(ll_Out)) ELSE MessageBox("Error", String(SQLCA.SQLErrText)) END IF
My test results seem to be somewhat random. If I supply 150 million or more characters, the app crashes every time without any error message. When I supply 100 million characters, the app crashes often, but not always. With 50 million characters it seems to work fine most of the time, but sometimes it still crashes. With 10 million characters I haven't got a single crash yet. But I'd like to get answers which are more reliable than this guesswork.