1. P Laceholder
  2. PowerBuilder
  3. Tuesday, 9 November 2021 08:16 AM UTC

Hello,

I am moving an old application from Powerbuilder 9.0.2 to Powerbuilder 2019 R3; part of the application collects pieces of RTF documents, and composes them together, into the Rich Text Editor.

In PB9 to achieve this we had queries using SELECTBLOB and then it gets inserted into the OLE object that handles the RTF.

In PB 2019 R3 we tried keeping the same functionality but the result is completely different: the SELECTBLOB gets absolutely no data.

SELECTBLOB text_column
INTO :lb_text /* blob containing the RTF text */
FROM table
WHERE ...
USING SQLCA;

// Now if I am to check the length of the text is always 0
MessageBox("Text Length", String(Len(lb_text)))

A solution I found was to use SELECT instead of SELECTBLOB and reading it into a String variable; while this seems to be doing the job, if the RTF contains images / is too long then it actually cuts off the data or doesn't display at all.

I tried profiling the SQL queries and I noticed a big difference:

/* PowerBuilder 9.0.2 Generated SQL Query */
SET TEXTSIZE 2147483647
SELECT text_column
FROM table
WHERE ...

/* PowerBuilder 2019 R3 Generated SQL Query */
declare @p1 int
set @p1=0
declare @p3 int
set @p3=229378
declare @p4 int
set @p4=294916
declare @p5 int
set @p5=0
exec sp_cursoropen @p1 output,N'select text_column from table ...',@p3 output,@p4 output,@p5 output
select @p1, @p3, @p4, @p5

While the code inside PB is the same on both sides, the latter doesn't retrieve any data (the blob has len 0).

How should I approach / fix this issue?

Ken Guo @Appeon Accepted Answer Pending Moderation
  1. Saturday, 2 April 2022 06:59 AM UTC
  2. PowerBuilder
  3. # 1

Hi P,

I tested and found that when PB is using the “SNC SQL Native Client(OLE DB)” and “MSOLEDBSQL SQL Server” driver, it only supports SelectBlob SQL statements of no more than 512 characters, while the “OLEDB” driver doesn’t have this limit.
So I suggest you use any of the following methods to work it around:
Method #1: Use OLE DB to connect database, for example:
SQLCA.DBMS = "OLE DB"

Method #2: Use Dynamic SQL Format 3 to execute SQL, for example:
integer ll_id = 1
blob lb_data
DECLARE my_cursor DYNAMIC CURSOR FOR SQLSA ;
PREPARE SQLSA FROM "Select image_data From table_1 Where id = ? and ......" ;
OPEN DYNAMIC my_cursor using :ll_id ;
FETCH my_cursor INTO :lb_data ;
CLOSE my_cursor ;


Regards,
Ken

Comment
  1. mike S
  2. Saturday, 2 April 2022 12:12 PM UTC
will the 512 sql characters limit be fixed?
  1. Helpful
There are no comments made yet.
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Wednesday, 16 March 2022 18:06 PM UTC
  2. PowerBuilder
  3. # 2

Hi P;

   The problem is that you cannot use the TEXT datatype as RTF data-streams contain "binary" data. You would have to switch to a VarBinary data type. Which is a "true" BLOB data type for RTF processing.

Regards ... Chris

Comment
There are no comments made yet.
P Laceholder Accepted Answer Pending Moderation
  1. Wednesday, 16 March 2022 13:40 PM UTC
  2. PowerBuilder
  3. # 3

I will post here what I wrote about the issue to the appeon support, perhaps some of you might have a solution.

Remarks:

I have done multiple test on this and I have almost completely concluded this is the case:
My connection string is the following:

SQLCA.DBMS = "MSOLEDBSQL SQL Server"
SQLCA.LogPass = <********>
SQLCA.ServerName = "***"
SQLCA.LogId = "***"
SQLCA.AutoCommit = False
SQLCA.DBParm = "Database='***',CursorScroll='FAST_FORWARD',DBTextLimit = '60000'"

For non-disclosure agreement I have to censor the table names, the number of * will match the number of characters of the table names/columns.

That being said when I have the two following scenarios, the first one WORKS:

SELECTBLOB ***********.***********  
INTO :********  
FROM ***********  
WHERE ***********.***************** = :******************** AND  
     ***********.***************** = :********************* AND  
     ***********.**************** = :******************* AND  
     ***********.************ = 'S'
USING SQLCA;

It correctly translates to something like this:

declare @p1 int
set @p1=0
declare @p3 int
set @p3=229378
declare @p4 int
set @p4=294916
declare @p5 int
set @p5=0
exec sp_cursoropen @p1 output,N'select ***********.*********** [...] = '' S '' ',@p3 output,@p4 output,@p5 output
select @p1, @p3, @p4, @p5

This correctly returns results from the database.


The second example is a query that exceeds the "512 character limit", which when translated is truncated:

SELECTBLOB ***********.***********
INTO :********
FROM *********** (nolock),
     ***** (nolock),
     ***************** (nolock)
WHERE (*****.*********** = ***********.***********)
  AND (*****************.*********** = *****.***********)
  AND (*****************.*********** = :*************)
  AND (*****************.*********** = :*************************)
  AND ((***********.*********** =
          (SELECT Max(A.***********)
           FROM *********** AS A (nolock)
           WHERE (A.*********** = ***********.***********)
             AND (A.*********** <= :*********) ))
       AND (***********.************ = 'S'))
  AND *****.*********** = :**************
      /*   ^ truncated here */
USING SQLCA;

Which translates correctly all the way to the ` = 'S' ` part of the query, after that it gets truncated.

declare @p1 int
set @p1=0
declare @p3 int
set @p3=229378
declare @p4 int
set @p4=294916
declare @p5 int
set @p5=0
exec sp_cursoropen @p1 output,N'select ***********.*********** [...] = '' S '' )) AND *****.',@p3 output,@p4 output,@p5 output
select @p1, @p3, @p4, @p5
                                                                                           ^ truncated here
Comment
There are no comments made yet.
mike S Accepted Answer Pending Moderation
  1. Wednesday, 16 March 2022 13:32 PM UTC
  2. PowerBuilder
  3. # 4

what database driver are you using?  

I think odbc in particular will require you to retrieve it down in chunks.  use datalength() to get the size of the blob

Do 8k chunks and it works for all odbc and other (oleddb/native etc) drivers:

 

loop


SELECTBLOB substring( <blobcolumn>, :ll_start, :ll_maxsize)
INTO :lb_data

....

 

lb_all_data += lb_data

 

until ...

 

 

Comment
  1. P Laceholder
  2. Wednesday, 16 March 2022 13:41 PM UTC
Thank you for the reply but I don't think the size of the text in the database is the issue, but rather the query itself being truncated.

I added a response to the thread which displays more in detail what the issue is.
  1. Helpful
  1. mike S
  2. Wednesday, 16 March 2022 16:52 PM UTC
try not using cursors.
  1. Helpful
There are no comments made yet.
P Laceholder Accepted Answer Pending Moderation
  1. Wednesday, 16 March 2022 08:33 AM UTC
  2. PowerBuilder
  3. # 5

I reopen the discussion because I noticed something:

The cursor queries get truncated...

[Screenshot taken from SQL Profiler]

Anyone has an idea on how to fix this?

Thank you in advance.

Comment
  1. mike S
  2. Wednesday, 16 March 2022 13:25 PM UTC
screen shot is unreadable.

  1. Helpful
  1. P Laceholder
  2. Wednesday, 16 March 2022 13:33 PM UTC
you should be able to right click and open in a new tab page
  1. Helpful
There are no comments made yet.
Miguel Leeuwe Accepted Answer Pending Moderation
  1. Monday, 31 January 2022 16:53 PM UTC
  2. PowerBuilder
  3. # 6
Comment
There are no comments made yet.
Miguel Leeuwe Accepted Answer Pending Moderation
  1. Monday, 24 January 2022 07:52 AM UTC
  2. PowerBuilder
  3. # 7

Have you tried doing this?

// first convert to string and then get the len():
//// Now if I am to check the length of the text is always 0
//MessageBox("Text Length", String(Len(lb_text)))
MessageBox("Text Length", Len(String(lb_text)))
Comment
  1. Miguel Leeuwe
  2. Monday, 24 January 2022 08:18 AM UTC
Okay, so maybe it's because you are using a TEXT column type for binary data? I'm using Oracle with NCLOB datatypes and everything works perfectly.

Maybe you should be using the 'image' datatype in your database:

Binary string data types



The binary data types stores fixed and variable length binary data.

Data Type Lower limit Upper limit Memory

binary 0 bytes 8000 bytes n bytes

varbinary 0 bytes 8000 bytes The actual length of data entered + 2 bytes

image 0 bytes 2,147,483,647 bytes
  1. Helpful 1
  1. P Laceholder
  2. Monday, 24 January 2022 13:51 PM UTC
Thank you very much for the quick reply, I will be doing a couple of tests by changing the column to "varbinary" as it seems to be the most fitting.

Will let you know in the following days if it fixed it for us.

Regards, A.
  1. Helpful
  1. Miguel Leeuwe
  2. Monday, 24 January 2022 14:08 PM UTC
That one should allow you to store 2GB in such a field.
  1. Helpful
There are no comments made yet.
P Laceholder Accepted Answer Pending Moderation
  1. Monday, 24 January 2022 07:47 AM UTC
  2. PowerBuilder
  3. # 8

Hello, I apologize for the very late reply but I got busy with other matters in this period.

The DBMS we are using (in the testing environment) is the following:

select @@version

+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
¦ Microsoft SQL Server 2019 (RTM) - 15.0.2000.5 (X64)   Sep 24 2019 13:48:23   Copyright (C) 2019 Microsoft Corporation  Developer Edition (64-bit) on Windows 10 Pro 10.0 <X64> (Build 19041: ) ¦
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

The column type in the database is of type "text" and we're extracting RTF content (written in the following way):

{\rtf1\ansi\deflang1033\ftnbj\uc1\deff1  {\fonttbl{\f0 \froman \fcharset0 Times New Roman;}{\f1 \fswiss \fcharset0 Arial;}{\f2 \fmodern \fcharset0 Courier New;}{\f3 \fdecor \fcharset2 Wingdings;}{\f4 \froman \fcharset2 Symbol;}{\f5 \fnil Verdana;}}  {\colortbl ;\red255\green255\blue255 ;\red0\green0\blue0 ;\red0\green0\blue255 ;\red128\green0\blue128 ;}  {\stylesheet{\fs20\cf0\cb1\ulc0 Normal;}{\cs1\cf0\cb1\ulc0 Default Paragraph Font;}{\s2\f1\fs16\b\cf2\cb1\ulc2\keepn heading 1;}{\s3\f1\fs20\b\cf2\cb1\ulc2\keepn heading 2;}{\s4\fs20\cf2\cb1\ulc2\tqc\tx4819\tqr\tx9638 header;} ...

The big issue is:

  1. SELECTBLOB doesn't get any content whatsoever (an empty blob)
  2. If I use a normal SELECT into a string variable; if the column data exceeds a certain amount (I assume 2^32) then it cuts off at some point.

Any advices on how I should solve this problem?

Best regards, A.

Comment
There are no comments made yet.
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Tuesday, 9 November 2021 19:32 PM UTC
  2. PowerBuilder
  3. # 9

Hi Adrijan ;

  First of all ... PB9 was ANSI vs PB10.0 and higher is Unicode. So that is always a factor when handling String or Blob data types.

  The SelectBlob command is the only real safe way to handle Image data but you need to make sure that you are using the correct BLOB data type in your DBMS. You did not mention your DB software & version in the original post.

Regards ... Chris

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