Subscribe via RSS
Toggle Submenu
  1. Categories
  2. Recent
  3. Tags
View Replies (2)

Resolved Embedded SQL input variable value max length

  1. Misc
0
Votes
Undo
  1. Arturas Jurevicius
    Arturas Jurevicius
  2. PowerBuilder
  3. Monday, 22 November 2021 14:11 PM UTC

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):

  1. 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.
  2. What exactly does that limit depend on?
  3. How is PowerBuilder expected (designed) to handle the situation when that limit is exceeded?
  4. 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.

Thanks.

  1. Embedded SQL
NotoSans
Powerbuilder - User break out of a loop
Responses (2)
Miguel Leeuwe
Miguel Leeuwe Accepted Answer Pending Moderation
  1. Wednesday, 24 November 2021 04:41 AM UTC
  2. PowerBuilder
  3. # 1

I think there's a limit of the length of a string that you can pass in from powerbuilder when running embedded sql.

For example, when running this from the database painter:

select 'abc' + space(999999997) from dummy;

it works, but if I add another 9, it fails:

select 'abc' + space(9999999997) from dummy;

---------------------------
Select 1 Results (PB Demo DB V2019R3) - ISQL Session 1
---------------------------
Select Error: SQLSTATE = 22003
[Sybase][ODBC Driver][SQL Anywhere]Value 9999999997 out of range for destination
---------------------------
OK
---------------------------

Anyway, to do whatever you want to do, you should use SELECTBLOB and UPDATEBLOB on the field in question (assuming the field in the table is of a binary or similar type).

regards

Comment
Load more comments
Miguel Leeuwe
  1. Miguel Leeuwe
  2. Wednesday, 24 November 2021 09:10 AM UTC
I see, in that case I don't know the answer. Sorry my friend.
  1. Helpful
Miguel Leeuwe
  1. Miguel Leeuwe
  2. Wednesday, 24 November 2021 09:13 AM UTC
I guess that when you speak of 100 000 000 characters in a string, your crashes might not only be caused by limits of SQL, but maybe also to powerbuilder not having enought available RAM to run your statement. That would explain the inconsitency of your test results?

regards.
  1. Helpful
Miguel Leeuwe
  1. Miguel Leeuwe
  2. Wednesday, 24 November 2021 09:15 AM UTC
What kind of "crash" are you experiencing? Is there any information in the event viewer logs when it happens?
  1. Helpful
There are no comments made yet.
Peter Pang @Appeon
Peter Pang @Appeon Accepted Answer Pending Moderation
  1. Tuesday, 9 January 2024 09:32 AM UTC
  2. PowerBuilder
  3. # 2

Hi Arturas,

Thanks for reporting this issue. This issue can be reproduced in PB12.6 too.
My verification result is as follows:
1. TextSize is restricted by 32767. The restriction is made inside the SELECT/dw.Retrieve retrieval string. ODBC is the same as the direct-connected driver.
2. BlobSize can retrieve more than 32767 binary (string) data, but requires using SELECTBLOB to retrieve, and if it is a string, converts it through the string() function.
We do not recommend using SELECT to retrieve a single field larger than 32767 (long nvarchar). We suggest splitting it into multiple fields or using SELECTBLOB to retrieve (binary image data).

1. 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.
Answer: The maximum "safe" length is suggested not over 32767.

2.What exactly does that limit depend on?
Answer: Internal logical security Settings. Other drivers can use pbodb.ini to adjust PBMaxBlobSize=0 and PBMaxTextSize=0. Of course, PBMaxBlobSize=0 is still capped at 32767.

3.How is PowerBuilder expected (designed) to handle the situation when that limit is exceeded?
Answer: Segmentation or using SELECTBLOB/UpdateBlob is recommended.

4.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)?
Answer: According to the current verification, there is no limit to the value of the input string, but for larger input string the memory consumption will be larger too, and in serious cases, it can lead to application crash. We recommend keeping it below 32767, otherwise, unexpected situations may occur.


Best Regards,
Peter

Comment
Arturas Jurevicius
  1. Arturas Jurevicius
  2. Tuesday, 9 January 2024 12:11 PM UTC
Hi, Peter,

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.
  1. Helpful
Peter Pang @Appeon
  1. Peter Pang @Appeon
  2. Wednesday, 10 January 2024 05:10 AM UTC
Hi Arturas,



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

  1. Helpful 1
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.