• You are here:  
  • Home
  • Q&A
  • Q&A
  • PowerBuilder
  • SQL select from PowerBuilder returns only part of data

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

Resolved SQL select from PowerBuilder returns only part of data

  1. Issue
0
Votes
Undo
  1. MARK APTEKAR
    MARK APTEKAR
  2. PowerBuilder
  3. Saturday, 18 February 2023 02:45 AM UTC

Hi,  Using PowerBuilder 2019 R3 build 2703 with SQL Server version ?  ( I know it is new. 'SELECT @@version' returns  Microsoft SQL Azure (RTM) - 12.0.2000.8   Jan 12 2023 05:25:39   Copyright (C) 2022 Microsoft Corporation )

DBMS=ODBC

DBParm=ConnectString='DSN=echarmsqa_az;UID=METAUSER;PWD=xxxxxx'

I am using  embedded  SQL statement in PowerBuilder code:

SELECT error_text  INTO :ls_text  FROM w_queued_event_error_log  WHERE queued_event_id = :ll_q_id;

Actual length of the data in the data base table that I am expecting to get, is 38657 characters. but length of the returned string (ls_text) is only 32766 characters,  Entire data, all 38657 characters returned when I run same SQL statement directly in the SQL Server Management Studio. 

Column 'error_text' has data type 'Text'. Is anyone know why data get chopped off  ?

Thank you in advance for your time and help.

 

  1. DataType
  2. Embedded SQL
  3. PowerBuilder (Appeon)
  4. Text
Is a 32-bit application built with PowerBuilder 20...
Issue with OLE control in PowerBuilder
Accepted Answer
MARK APTEKAR
MARK APTEKAR Accepted Answer Pending Moderation
  1. Friday, 28 April 2023 16:50 PM UTC
  2. PowerBuilder
  3. # Permalink

I solved this problem by retrieving data portion by portion in the DYNAMIC CURSOR using Substring function in the loop and incrementing ll_start 

ls_sql = "SELECT SUBSTRING(error_text, "+string(ll_start)+", 4000) FROM w_queued_event_error_log WHERE queued_event_id = ?"

Comment
There are no comments made yet.
Responses (3)
Olan Knight
Olan Knight Accepted Answer Pending Moderation
  1. Saturday, 18 February 2023 02:58 AM UTC
  2. PowerBuilder
  3. # 1

Use a cursor.

The max number of bytes that can be returned in one block is 32,765 + 1 x EOF character of a NULL. If you expect more data than that, and if more data is actually required (i.e. what you need is not contained within the fors block of data) then you need to use a cursor to get every block of data returned.

Comment
MARK APTEKAR
  1. MARK APTEKAR
  2. Saturday, 18 February 2023 16:43 PM UTC
Thank you, Olan for the quick response. I tried your suggestion to use a cursor.

DECLARE cur_text CURSOR FOR

SELECT error_text

FROM w_queued_event_error_log

WHERE queued_event_id = :ll_q_id;

OPEN cur_text;

DO

FETCH cur_text INTO :ls_part ;

IF SQLCA.SQLCode = 0 THEN

ls_text = ls_text + ls_part

ll_len_1 = Len(ls_part)

ll_len_2 = Len(ls_text)

END IF

LOOP WHILE SQLCA.SQLCode = 0

CLOSE cur_text ;



The first FETCH returs SQLCA.SQLCode = 0 and Len(ls_part) = 32766 characters, but second FETCH returs SQLCA.SQLCode = 100 and ls_part blank.

So I still have only partial string.
  1. Helpful
Chris Pollach @Appeon
  1. Chris Pollach @Appeon
  2. Saturday, 18 February 2023 17:12 PM UTC
Pssst .... SelectBlob. ;-)
  1. Helpful 1
There are no comments made yet.
Chris Pollach @Appeon
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Saturday, 18 February 2023 03:04 AM UTC
  2. PowerBuilder
  3. # 2

Hi Mark;

  PB & in particular DWI's can only handle a maximum of 32K of textual data. Say for example from a VarChar type SS column. 

   The Text data type though is not textual data. It's actually a Blob data type. That means that you'll have to use a SelectBlob command in order to get all the datum back into your PB App.

HTH

Regards ... Chris 

Comment
MARK APTEKAR
  1. MARK APTEKAR
  2. Sunday, 19 February 2023 01:12 AM UTC
Thank you Chris for the quick response. I used SELECTBLOB as you suggested..

SELECTBLOB error_text

INTO :lb_blob

FROM w_queued_event_error_log

WHERE queued_event_id = :ll_q_id;

ls_text = string(lb_blob)

ll_len = Len(ls_text)

Length (ll_len) of the returned data (ls_text) is 32764 characters. And also string data has Chinese characters after 16385 characters to the end of string

..........

<525>.[ue_process]: Total queue(s) found for rule: (ll_rq=1)

<538>.[ue_process]: 1). 䌀甀爀爀攀渀琀 儀甀攀甀攀 䤀搀㨀 氀氀开焀开椀搀㴀㄀㘀㤀Ⰰ 氀猀开愀挀琀椀漀渀开琀礀瀀攀 㴀 䄀䐀刀ഀ਀㰀㘀.........

  1. Helpful
Chris Pollach @Appeon
  1. Chris Pollach @Appeon
  2. Sunday, 19 February 2023 02:16 AM UTC
Ahhh ... I see the issue. Your using ODBC to connect to the DBMS. ODBC will truncate the data stream by default at 32K. It can also inadvertently change the datastream code page being used.

You need to modify the PBODB.ini file to allow > 32K to be returned.

https://docs.appeon.com/pb2022/connection_reference/PBMaxTextSize.html

Note that this setting must not be done in the DBparm field of SQLCA when using ODBC (OK for a native DB driver). Instead, you must do this in the PBODB.ini file. For example ...



[Microsoft SQL Server]

PBSyntax='MS_SQLSERVER_SYNTAX'

PBObjectIDs='YES'

PBMaxBlobSize='2147483647'

PBMaxTextSize='2147483647'

PBDWDynamic='NO'

PBTrimCharColumns='YES'



HTH

Regards ... Chris
  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.