Hi,
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 IFMy 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.
Thanks.
Thanks for your answers, but I'm quite confused about them.
I was asking about EMBEDDED SELECT statement (i.e. NOT about DW). And I was asking about INPUT variable limitations (NOT about output). Are you absolutely sure that all your answers where you've mentioned 32767 characters are correct/relevant?
Either way, could you please clarify the answer to the 4th question: is there any possibility that supplied string input variable value will be silently (without throwing any error/exception) truncated, i. e. DBMS will receive incomplete string? Does your answer mean that such outcome is absolutely impossible, i. e. the only possible unexpected outcome is crashing?
Thanks.
According to our actual tests, SQL Anywhere's embedded SQL input parameters are not automatically truncated.
However, if the input parameters are too long and occupy too much memory, the application may Crash due to memory overflow.
If you want to use longer input parameters, then you can try using 64-bit platform. 64-bit applications can use more memory. I used the code you provided, in 64-bit, even if the input parameters use 500 million characters, it is normal, but it will take up to 4GB of memory, and a running time of about ten seconds.
In order to stabilize your application and improve the user experience, we recommend that you control the maximum character length of the input parameters according to the platform of your application and the results of actual tests.
In addition, using long strings in PB script is not very efficient and takes up a lot of memory, so using temp.table to work it around will be better in your case.
Best Regards,
Peter