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

Resolved PB and Oracle's BFILE datatype

  1. How-to
0
Votes
Undo
  1. Kari Paukku
    Kari Paukku
  2. PowerBuilder
  3. Friday, 19 May 2023 09:14 AM UTC

Hi,

I have a table where one of the columns datatype is BFILE (the file is stored on a file server).

My question is how to use the field in PB?

Here is my test case

       SELECT  DOCUMENT_NAME, PNTR
         INTO :FILENAME,  :DOCUMENTFILE
         FROM FS_DOCUMENT_LIST
         WHERE DOCUMENT_KEY  = 3;

The idea is then to open the file in the application, using the ShellExecute commantd.


I tried to have as :DOCUMENTFILE as Ulong or String, but I get from Oracle this type of error(data type mismatch)
ORA-00932: inconsistent datatypes: expected LONG got BFILE


When the DOCUMENTFILE is blob, no error is returned but how should I use the blob value to open the document (e.g. pdf, xlsx) ?

Thanks,
Kari

 

 

 

 

  1. Oracle
  2. Type
testing PB 2022 64 bits with Sybase ASE 16 64 bits...
Trigger PB Function by if you know its name by str...
Responses (4)
René Ullrich
René Ullrich Accepted Answer Pending Moderation
  1. Friday, 19 May 2023 09:33 AM UTC
  2. PowerBuilder
  3. # 1

I have never used BFILE but maybe SELECTBLOB command could do the job.

https://docs.appeon.com/pb2022/powerscript_reference/xREF_57113_Using_SQL_in.html#xREF_86244_SELECTBLOB

 

Comment
There are no comments made yet.
Kari Paukku
Kari Paukku Accepted Answer Pending Moderation
  1. Wednesday, 24 May 2023 08:49 AM UTC
  2. PowerBuilder
  3. # 2

Hi,

 

in the end I created an Oracle function that retrieves the file from the BFILE and returns a BLOB to the PB Application.

Not perhaps ideal, but works for now.

kp

Comment
Miguel Leeuwe
  1. Miguel Leeuwe
  2. Wednesday, 24 May 2023 10:23 AM UTC
I'm not sure if there's a limit on the size of a blob that can be returned from a function to PB (considering the limited package sizes for TCP)?
  1. Helpful
Miguel Leeuwe
  1. Miguel Leeuwe
  2. Wednesday, 24 May 2023 10:23 AM UTC
Try the function with files bigger than 32 KB.
  1. Helpful
Miguel Leeuwe
  1. Miguel Leeuwe
  2. Wednesday, 24 May 2023 10:25 AM UTC
Like René Ullrich suggested. Have you tried SELECTBLOB?
  1. Helpful
There are no comments made yet.
Kari Paukku
Kari Paukku Accepted Answer Pending Moderation
  1. Friday, 26 May 2023 05:45 AM UTC
  2. PowerBuilder
  3. # 3

Hi,

the function appeaoch works ok and the largest file I used for testing was a 90MB video file.

It worked surprisingly well considering the size.

Kari

 

Comment
Miguel Leeuwe
  1. Miguel Leeuwe
  2. Friday, 26 May 2023 07:24 AM UTC
Ok, great news Kari!
  1. Helpful
There are no comments made yet.
Kari Paukku
Kari Paukku Accepted Answer Pending Moderation
  1. Friday, 19 May 2023 13:37 PM UTC
  2. PowerBuilder
  3. # 4

Hi,

my hope was to be able to open the file in question without having the need to create a temporary file which I would open. That's the way I do if the document is stored in a BLOB column).

I was hoping that there would be a way to find a pointer to the file and simply open it (as the file is already on the file server).

kp

 

 

Comment
Miguel Leeuwe
  1. Miguel Leeuwe
  2. Friday, 19 May 2023 16:47 PM UTC
I guess it all depends if your blob has been stored in the DB including fileheaders or not. If they're not included you'll have to write to a temp file on disk first.
  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.