Can't change blob field in Oracle database

questions
,
1

Hi everyone…

I want to use a BLOB field in Oracle.
However, I can only write the record once and then only read it.
When I try to change it, I get the error “Unsupported File Driver Function.”
Unfortunately, the tracing doesn’t reveal any details.
What am I doing wrong?
I’m using the latest C12 and Oracle 11g.

BCTEST FILE, DRIVER('ORACLE', ''), NAME('BCTEST'), OWNER('****'), PRE(BCTEST), CREATE, THREAD
BField  BLOB ! CLOB in oracle
Record  RECORD
ID        LONG
sName     CSTRING(256)
        END
      END

  MAP
    BlobTest()
  END

  CODE
  BlobTest()

      
BlobTest  PROCEDURE()
sText   CSTRING(50001)
  CODE
  
  CREATE(BCTEST)
  IF ( ErrorCode() )
    MESSAGE(Error() & '|' & FileError())
    RETURN
  END
  
  sText = 'Lorem Ipsum is simply dummy text of the printing and typesetting industry. ' & | 
          'Lorem Ipsum has been the industry''s standard dummy text ever since the 1500s, ' & | 
          'when an unknown printer took a galley of type and scrambled it to make a type specimen book. ' & | 
          'It has survived not only five centuries, but also the leap into electronic typesetting,  ' & | 
          'remaining essentially unchanged. It was popularised in the 1960s with the release of  ' & | 
          'Letraset sheets containing Lorem Ipsum passages, and more recently with desktop publishing  ' & | 
          'software like Aldus PageMaker including versions of Lorem Ipsum.'
          
  OPEN(BCTEST)
  IF ( ErrorCode() )
    MESSAGE(Error() & '|' & FileError(), 'OPEN')
    RETURN
  END

  BCTEST:ID = 1
  BCTEST:BField{PROP:SIZE} = LEN(sText)
  BCTEST:BField[0:LEN(sText)-1] = sText ! it starts at index 0
  ADD(BCTEST) ! this works...
  IF ( ErrorCode() )
    MESSAGE(Error() & '|' & FileError(), 'ADD')
  END
  
  SET(BCTEST)
  NEXT(BCTEST)
  Message(BCTEST:BField[0:BCTEST:BField{PROP:SIZE}])

  ! Change...
  sText = 'change all...'
  BCTEST:BField{PROP:SIZE} = 0
  BCTEST:BField{PROP:SIZE} = LEN(sText)
  BCTEST:BField[0:LEN(sText)-1] = sText ! does not work
  !BCTEST:BField{PROP:Value} = sText ! does not work too
  PUT(BCTEST) ! this not! unsupported file driver function...
  IF ( ErrorCode() )
    MESSAGE(Error() & '|' & FileError(), 'PUT')
  END

Here is the output from tracing

Version: 12.0.13941
011204H(1) 14:25:47.740 Connecting Return Code: 0 Time Taken:0.08 secs
011204H(1) 14:25:47.813 Parsing T9 Cursor 24283936 : CREATE TABLE BCTEST (ID NUMBER(10,0),SNAME VARCHAR2(255),BFIELD CLOB)  Return Code: 0 Time Taken:0.00 secs
011204H(1) 14:25:47.813 Executing T9 Cursor 24283936  Return Code: -955 Time Taken:0.00 secs
011204H(1) 14:25:47.814 Parsing T9 Cursor 24283936 : DROP TABLE BCTEST  Return Code: 0 Time Taken:0.00 secs
011204H(1) 14:25:47.814 Executing T9 Cursor 24283936  Return Code: 0 Modified 0 row Time Taken:0.00 secs
011204H(1) 14:25:47.818 Parsing T9 Cursor 24283936 : CREATE TABLE BCTEST (ID NUMBER(10,0),SNAME VARCHAR2(255),BFIELD CLOB)  Return Code: 0 Time Taken:0.00 secs
011204H(1) 14:25:47.818 Executing T9 Cursor 24283936  Return Code: 0 Modified 0 row Time Taken:0.00 secs
011204H(1) 14:25:47.820 BCTEST      FILE,DRIVER('Oracle'),OWNER(******),NAME('BCTEST'),CREATE,THREAD
011204H(1) 14:25:47.820 BCTEST:BFIELD BLOB
011204H(1) 14:25:47.820               RECORD
011204H(1) 14:25:47.820 BCTEST:ID       LONG
011204H(1) 14:25:47.820 BCTEST:SNAME    CSTRING(256)
011204H(1) 14:25:47.820               END
011204H(1) 14:25:47.820             END
011204H(1) 14:25:47.820 
011204H(1) 14:25:47.820 CREATE(BCTEST:0A6BFACH)  Time Taken:0.11 secs
011204H(1) 14:25:47.820 Parsing T9 Cursor 24279760 : SELECT * FROM BCTEST WHERE 0 = 1  Return Code: 0 Time Taken:0.00 secs
011204H(1) 14:25:47.820 Getting information for field 1 
011204H(1) 14:25:47.820 Executing T9 Cursor 24279760  Return Code: 0 Modified 0 row Time Taken:0.00 secs
011204H(1) 14:25:47.820 Getting attribute OCI_ATTR_NAME for Parameter 01727F94H Return Code: 0 Time Taken:0.00 secs
011204H(1) 14:25:47.820 Getting attribute OCI_ATTR_DATA_TYPE for Parameter 01727F94H Return Code: 0 Time Taken:0.00 secs
011204H(1) 14:25:47.820 Getting attribute OCI_ATTR_CHARSET_FORM for Parameter 01727F94H Return Code: 0 Time Taken:0.00 secs
011204H(1) 14:25:47.820  Return Code: 0 Time Taken:0.00 secs
011204H(1) 14:25:47.820 Getting information for field 2 Getting attribute OCI_ATTR_NAME for Parameter 01727F94H Return Code: 0 Time Taken:0.00 secs
011204H(1) 14:25:47.820 Getting attribute OCI_ATTR_DATA_TYPE for Parameter 01727F94H Return Code: 0 Time Taken:0.00 secs
011204H(1) 14:25:47.820 Getting attribute OCI_ATTR_CHARSET_FORM for Parameter 01727F94H Return Code: 0 Time Taken:0.00 secs
011204H(1) 14:25:47.820  Return Code: 0 Time Taken:0.00 secs
011204H(1) 14:25:47.820 Getting information for field 3 Getting attribute OCI_ATTR_NAME for Parameter 01727F94H Return Code: 0 Time Taken:0.00 secs
011204H(1) 14:25:47.820 Getting attribute OCI_ATTR_DATA_TYPE for Parameter 01727F94H Return Code: 0 Time Taken:0.00 secs
011204H(1) 14:25:47.820 Getting attribute OCI_ATTR_CHARSET_FORM for Parameter 01727F94H Return Code: 0 Time Taken:0.00 secs
011204H(1) 14:25:47.820  Return Code: 0 Time Taken:0.00 secs
011204H(1) 14:25:47.820 Getting information for field 4  Return Code: -24334 Time Taken:0.00 secs
011204H(1) 14:25:47.822 OPEN(BCTEST:0A6BFACH)  Time Taken:0.00 secs
011204H(1) 14:25:47.822 BLOB_SET_PROP(BCTEST:0A6BFACH)  Time Taken:0.00 secs
011204H(1) 14:25:47.822 BLOB_TAKE(BCTEST:0A6BFACH)  Time Taken:0.00 secs
011204H(1) 14:25:47.822 Parsing T9 Cursor 94971468 : INSERT INTO BCTEST (ID,SNAME,BFIELD) VALUES (:ID_,:SNAME_,:BFIELD_)  Return Code: 0 Time Taken:0.00 secs
011204H(1) 14:25:47.822 Binding :ID_ with value 1 as type INT for T9 Cursor 94971468  Return Code: 0 Time Taken:0.00 secs
011204H(1) 14:25:47.822 Binding :SNAME_ with value  (null)  as type VARCHAR(256) for T9 Cursor 94971468  Return Code: 0 Time Taken:0.00 secs
011204H(1) 14:25:47.822 Allocating LOB :BFIELD_  T9 LOB 24260608  Return Code: 0 Time Taken:0.00 secs
011204H(1) 14:25:47.822 Allocating temporary LOB  for T9 LOB 24260608  Return Code: 0 Time Taken:0.00 secs
011204H(1) 14:25:47.822 Binding :BFIELD_ with value 577 as type CLOB for T9 LOB 24260608  for T9 Cursor 94971468  Return Code: 0 Time Taken:0.00 secs
011204H(1) 14:25:47.822 Trimming Blob data  T9 LOB 24260608 Writing Blob data  T9 LOB 24260608  Return Code: 0 Time Taken:0.00 secs
011204H(1) 14:25:47.822 Executing T9 Cursor 94971468  Return Code: 0 Modified 1 row Time Taken:0.00 secs
011204H(1) 14:25:47.823 ADD(BCTEST:0A6BFACH)  Time Taken:0.00 secs
011204H(1) 14:25:47.823   BCTEST:BFIELD
011204H(1) 14:25:47.823  BCTEST:ID  : 1
011204H(1) 14:25:47.824  BCTEST:SNAME : ''
011204H(1) 14:25:47.824 
011204H(1) 14:25:47.824 SETfile(BCTEST:0A6BFACH)  Time Taken:0.00 secs
011204H(1) 14:25:47.824 Parsing T9 Cursor 24540764 : SELECT ID,SNAME,BFIELD FROM BCTEST   Return Code: 0 Time Taken:0.00 secs
011204H(1) 14:25:47.824 Defining field 1 as type INT T9 Cursor 24540764  Return Code: 0 Time Taken:0.00 secs
011204H(1) 14:25:47.824 Defining field 2 as type VARCHAR(256) T9 Cursor 24540764  Return Code: 0 Time Taken:0.00 secs
011204H(1) 14:25:47.824 Allocating LOB :BFIELD_  T9 LOB 24260416  Return Code: 0 Time Taken:0.00 secs
011204H(1) 14:25:47.824 Allocating temporary LOB  for T9 LOB 24260416  Return Code: 0 Time Taken:0.00 secs
011204H(1) 14:25:47.824 Defining field 3 as type CLOB T9 Cursor 24540764  Return Code: 0 Time Taken:0.00 secs
011204H(1) 14:25:47.824 Executing T9 Cursor 24540764  Return Code: 0 Modified 0 row Time Taken:0.00 secs
011204H(1) 14:25:47.824 Fetching 1 Record for T9 Cursor 24540764  Return Code: 0 Time Taken:0.00 secs
011204H(1) 14:25:47.824 NEXT(BCTEST:0A6BFACH)  Time Taken:0.00 secs
011204H(1) 14:25:47.824   BCTEST:BFIELD
011204H(1) 14:25:47.824  BCTEST:ID  : 1
011204H(1) 14:25:47.824  BCTEST:SNAME : ''
011204H(1) 14:25:47.824 
011204H(1) 14:25:47.824 Getting Blob Length for T9 LOB 24260416  Return Code: 0 Time Taken:0.00 secs
011204H(1) 14:25:47.824 Reading Blob data for T9 LOB 24260416  Return Code: 0 Time Taken:0.00 secs
011204H(1) 14:25:47.824 BLOB_GET_PROP(BCTEST:0A6BFACH)  Time Taken:0.00 secs
011204H(1) 14:25:47.824 BLOB_YIELD(BCTEST:0A6BFACH)  Time Taken:0.00 secs
011204H(1) 14:25:48.868 BLOB_SET_PROP(BCTEST:0A6BFACH)  Time Taken:0.00 secs
011204H(1) 14:25:48.868 BLOB_SET_PROP(BCTEST:0A6BFACH)  Time Taken:0.00 secs
011204H(1) 14:25:48.869 BLOB_TAKE(BCTEST:0A6BFACH)  Time Taken:0.00 secs
011204H(1) 14:25:48.869 PUT(BCTEST:0A6BFACH) Error: Unsupported File Driver Function  Time Taken:0.00 secs
011204H(1) 14:25:48.869   BCTEST:BFIELD
011204H(1) 14:25:48.869  BCTEST:ID  : 1
011204H(1) 14:25:48.869  BCTEST:SNAME : ''
011204H(1) 14:25:48.869 
011204H(1) 14:25:50.047 DisConnecting Time Taken:0.00 secs
2

DELETE doesn’t work too…same Error…
I sent an email to support with an example.
But I’m not really confident; I’m still waiting for an invoice for the renewal.
Does anyone know if Robert is still there?

3

try this

  sText = 'change all...'
  M = len(sText)
  BCTEST:BField{PROP:SIZE} = 0      ! clear it
  BCTEST:BField[ 0 : M-1] = sText   ! assign
  BCTEST:BField{PROP:Size} = M      ! define size
  PUT(BCTEST)
  IF ( ErrorCode() )
    MESSAGE(Error() & '|' & FileError(), 'PUT')
  END

I have this in my toolbox and it works on MSSQL, MySQL and Sqlite3:

! CSTRINGtoBLOB
  cLen                 = LEN(<cstring>)
  <blob>{PROP:Size}    = 0  ! clear it first
  <blob>[ 0 : cLen-1 ] = <cstring>
  <blob>{PROP:Size}    = cLen
  add(... 

! BLOBtoCSTRING
  cLen      = <blob>{PROP:Size}
  <cstring> = <blob>[ 0 : cLen-1 ]
4

No, the same behavior with MS SQL.

How looks your file definition?
Which Clarion version do you use?

5

Clarion 9 and 11
here is file declaration

myfile                  FILE,DRIVER('ODBC','/TURBOSQL=TRUE'),OWNER(glo:owner),NAME('myfile'),PRE(myf),BINDABLE,THREAD !                     
idKey                    KEY(myf:id),NOCASE,OPT,PRIMARY    !                     
userIdKey                KEY(myf:user),DUP,NOCASE,OPT    !                     
eIdKey                   KEY(myf:e_id),DUP,NOCASE,OPT !                     
my_json                    BLOB                           !                     
Record                   RECORD,PRE()
id                          CSTRING(20)                    !                     
e_id                        CSTRING(20)                    !                     
...
user                        LONG                           !                     
                         END
                     END

looks like you are missing primary key, unique value?

1 Like
6

You are so right!

That’s the solution…the primary key was missing. :man_facepalming:
I got no glue why I left it :see_no_evil:

Thank you so much for your help!!!

1 Like