how to save an office file to database

[dEraCh2000]

i was wondering if someone could help me with this problem. i'm trying to save a .doc, or .ppt to the database, so if someone could provide an example code i would be grateful

 

[ShankarJ]

Hi!

What version of Clarion are you using?

To store an Office document, you need a BLOB field. BLOB's are zero-based string arrays with a maximum size dependent on the database driver.

In C6.x versions, you have FILETOBLOB() & BLOBTOFILE() functions. The Help on BLOB tells you how to store a file in a BLOB. Given below is the help :

label	BLOB [,BINARY] [,NAME( )]

label	The label of the BLOB (PROP:Label).

BLOB	Declares a variable-length string stored on disk per record which may be greater than 64K
BINARY	Declares the BLOB a storage area for binary data (PROP:BINARY).
NAME	Specifies the disk filename for the BLOB field (PROP:NAME).

BLOB (Binary Large OBject) declares a string field which is completely variable-length and may be greater than 64K in size. A BLOB must be declared before the RECORD structure. Generally, up to 255 BLOB fields may be declared in a FILE structure (the exact number and their manner of storage on disk is file driver dependent). 
A BLOB may not be used as a variable--you may not name a BLOB as a control's USE attribute, or directly assign data to or from the BLOB.You can use PROP:Handle to get the Windows handle to the BLOB entity and assign one BLOB to another: get the handle of both BLOB entities and then assign one BLOB's handle to the other BLOB's handle. A BLOB may not be accessed "as a whole;" you must either use Clarion's string slicing syntax to access the data (unlimited in 32-bit), or PROP:ImageBlob. The individual bytes of data in the BLOB are numbered starting with zero (0), not one (1). 

The SIZE procedure returns the number of bytes contained in the BLOB field for the current record in memory. You can also get (and set) the size of a BLOB using PROP:Size. You may set the size of the BLOB before assigning data to a new BLOB using string slicing, but it is not necessary as the size is automatically set by the string slice operation. You can also use PROP:ImageBlob to store and retrieve graphic images without first setting PROP:Size. It is a good idea to first set PROP:Size to zero (0) before assigning data to a BLOB that has not previously contained data, to eliminate any "junk" leftover from any previously accessed BLOB. When assigning from one BLOB to another using PROP:Handle, you may need to use PROP:Size to adjust the size of the destination BLOB to the size of the source BLOB. PROP:Touched can be used to determine if the contents of the BLOB has changed since it was retrieved from disk.

Example:

ArchiveFile PROCEDURE

Names   FILE,DRIVER('TopSpeed')
NaneKey  KEY(Name)
Notes    BLOB        !Can be larger than 64K
Rec      RECORD
Name      STRING(20)
         END
        END

ArcNames FILE,DRIVER('TopSpeed')
Notes    BLOB        
Rec       RECORD
Name       STRING(20)
          END
         END

 CODE
 SET(Names)
 LOOP
  NEXT(Names)
  IF ERRORCODE() THEN BREAK.
  ArcNames.Rec = Names.Rec                               !Assign rec data to Archive 
  ArcNames.Notes{PROP:Handle} = Names.Notes{PROP:Handle} !Assign BLOB to Archive 

  IF ERRORCODE() = 80
   MESSAGE(‘BLOB size is too large’)
   BREAK
  END
  ArcNames.Notes{PROP:Size} = Names.Notes{PROP:Size}     ! and adjust the size
  ADD(ArcNames)
 END

StoreFileInBlob PROCEDURE                                !Stores any disk file into a BLOB
DosFileName STRING(260),STATIC
LastRec     LONG
SavPtr      LONG(1)                                      !Start at 1
FileSize    LONG

DosFile    FILE,DRIVER('DOS'),PRE(DOS),NAME(DosFileName)

Record      RECORD
F1           STRING(2000)
            END
           END

BlobStorage  FILE,DRIVER('TopSpeed'),PRE(STO)
File         BLOB,BINARY
Record        RECORD
FileName       STRING(64)
              END
             END

 CODE
 IF NOT FILEDIALOG('Choose File to Store',DosFileName,,0010b) THEN RETURN.
 OPEN(BlobStorage)                     !Open the BLOB file
 STO:FileName = DosFileName            ! and store the filename
 OPEN(DosFile)                         !Open the file

 FileSize = BYTES(DosFile)             !Get size of file
 STO:File{PROP:Size} = FileSize        ! and set the BLOB to store the file
 LastRec = FileSize % SIZE(DOS:Record) !Check for short record at end of file
 LOOP INT(FileSize/SIZE(DOS:Record)) TIMES
  GET(DosFile,SavPtr)                  !Get each record
  ASSERT(NOT ERRORCODE())
  STO:File[SavPtr - 1 : SavPtr + SIZE(DOS:Record) - 2] = DOS:Record
                                       !String slice data into BLOB

  SavPtr += SIZE(DOS:Record)           !Compute next record pointer
 END
 IF LastRec                            !If short record at end of file
  GET(DosFile,SavPtr)                  !Get last record
  ASSERT(BYTES(DosFile) = LastRec)     ! size read should match computed size
  STO:File[SavPtr - 1 : SavPtr + LastRec - 2] = DOS:Record
 END
 ADD(BlobStorage)
 ASSERT(NOT ERRORCODE())
 CLOSE(DosFile);CLOSE(BlobStorage)

Regards

 

[dEraCh2000]

i'm using clarion 6.1 Professional edition
thanks for the help
if you could help with this, i'v just started using clarion
and i'm having problems with this help.
where do i acctualy put this code and where do i specify the path of the file, database table and attributes. are there any that have to be included. i'm probably asking for to much so i'll understand if you don't answer
thanks anyway

 

[ShankarJ]

Hi!

It is very difficult to help without knowing the extent of your knowledge in Clarion. First, did you understand the procedure I posted from the Help? If yes, the first step is to copy-paste and then modify that code to your specifications. After that create a Source Procedure in the application and copy-paste the DATA & CODE portions correctly. Make it a Global procedure and call it from your procedure.

Regards

 

[dEraCh2000]

acctually may knowladge of clarion is poor, or shoul i say almost none. i'v just started working for a new company and i'v never used clarion before. i'm the new useless guy ;-). the guy i work with has a considerable expirience with clarion but he's gone home for the holliday, and i'm interested in finishing this before he comes back.
thing with the procedure i don't understand where do specify the path of a file, and the table collumn. i'm using softmasters template to call word, and i was thinking to call this procedure when the user closes word.
my friend could probably solve this and it's maybe the best thing to wait for him.
if you could help i would be grateful but you dont have to bother yourself
thanks anyway

 

[ShankarJ]

Hi!

In the procedure ::

IF NOT FILEDIALOG('Choose File to Store',DosFileName,,0010b) THEN RETURN.

opens a dialog to choose a Filename and stores that name to the "DosFileName" variable.

BlobStorage  FILE,DRIVER('TopSpeed'),PRE(STO)
File         BLOB,BINARY
Record        RECORD
FileName       STRING(64)
              END
             END

is the Topspeed table used to save the File and STO:File is the BLOB column into which it is stored.

You can convert the above procedure into a function which receives the WordFileName and the BLOBColumn as parameters and saves it as shown below ::

StoreFileInBlob PROCEDURE(STRING  WordFileName, BLOB BlobColumn)                                !Stores any disk file into a BLOB

LastRec     LONG
SavPtr      LONG(1)                         !Start at 1
FileSize    LONG

DosFile    FILE,DRIVER('DOS'),PRE(DOS),NAME(WordFileName)

Record      RECORD
F1           STRING(64000)
            END
           END

 CODE

 OPEN(DosFile, 42H)                         !Open the file
 IF ERRORCODE() THEN RETURN.

 FileSize = BYTES(DosFile)             !Get size of file
 BlobColumn{PROP:Size} = FileSize      ! and set the BLOB to store the file
 LastRec = FileSize % SIZE(DOS:Record) !Check for short record at end of file
 LOOP INT(FileSize/SIZE(DOS:Record)) TIMES
  GET(DosFile,SavPtr)                  !Get each record
  ASSERT(NOT ERRORCODE())
  BlobColumn[SavPtr - 1 : SavPtr + SIZE(DOS:Record) - 2] = DOS:Record
                                       !String slice data into BLOB

  SavPtr += SIZE(DOS:Record)           !Compute next record pointer
 END
 IF LastRec                            !If short record at end of file
  GET(DosFile,SavPtr)                  !Get last record
  ASSERT(BYTES(DosFile) = LastRec)     ! size read should match computed size
  BlobColumn[SavPtr - 1 : SavPtr + LastRec - 2] = DOS:Record
 END
 CLOSE(DosFile)

 RETURN

If you cannot understand the above as well, I think it is best you wait for your colleague.

Regards

 

[dEraCh2000]

hi
sorry i didn't answer sooner
i had some problems with that code
and i'v solved my problem by using FILETOBLOB and BLOBTOFILE functions. i save this files to an image collumn in my database and it works fine. it's possible to save any file type
problem solved
thanks again