1. 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.

Who is viewing this page
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
  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
  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
  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.
  • Page :
  • 1


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