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

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

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.

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

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.

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

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

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