Oracle Forms Web

Upload, edit and download files from/to the database with the

Webutil library

 

Home page

 

 

1.                      Purpose

 

The purpose of this article is to demonstrate how, with the Webutil library,  we can upload local documents into the database, edit those stored documents on the client machine, save modified documents to the database, and use the MSWord CheckSpell function.

 

The goal is to have, with Webutil, the same functionality as an “OLE CONTAINER” C/S component.

 

 

 

2.                      Upload local document to the database

 

 

*   Select a file on the client machine with the WEBUTIL_FILE.FILE_OPEN_DIALOG function

 

  FUNCTION WEBUTIL_FILE.FILE_OPEN_DIALOG

  (

        directory_name,

        file_name,

        file_filter,

        title

  ) return VARCHAR2;

 

directory_name is the name of the starting directory (let empty to start at the root)

file_name is the name of the searching file

file_filter is the list of file types to display (e.g.  '|All files|*.*|' or '|Word Files|*.doc|Excel Files|*.xls|')

title is the title of the dialog box

 

 

 

*   Upload the selected document into the database with the Webutil_File_Transfer.Client_To_DB function

 

FUNCTION Webutil_File_Transfer.Client_To_DB

(

   clientFile       in VARCHAR2,

   tableName        in VARCHAR2,

   columnName       in VARCHAR2,

   whereClause      in VARCHAR2,

   asynchronous     in BOOLEAN default FALSE,

   callbackTrigger  in VARCHAR2 default NULL

) return BOOLEAN;

 

clientFile is the full file name returned by the File_Open_Dialog() function

tableName is the table that contains a BLOB column

columnName is the BLOB column you want to store the file

whereClause is the Where clause to identify a unique raw in the table

asynchronous allows form to re-draw its screen before the end of the upload

callbackTrigger is the name of a form-level trigger which will be called once the upload is completed.

 

Assume you want to store the c:\docs\doc.xls in the DOC column of the DOCUMENTS table where DOC_ID = 10, use the following instruction:

 

Declare

   LB$Result  BOOLEAN ;

Begin

LB$Result := Webutil_File_Transfer.Client_To_DB

(

   ‘c:\docs\doc.xls’,

   ‘DOCUMENTS’,

   ‘DOC’,

   ‘DOC_ID = 10’

    ) ;

End ;

 

 

Attention:

The Client_To_DB() function use an UPDATE statement to store the document in an existing BLOB column, so the corresponding raw must exists in the table before calling the function.

 

 

 

3.                       Download from database, edit on client machine and re-save document to        database

 

 

*           Download the document on the client machine with the Webutil_File_Transfer.DB_To_Client function

 

FUNCTION DB_To_Client

 (

   clientFile       in VARCHAR2,

   tableName        in VARCHAR2,

   columnName       in VARCHAR2,

   whereClause      in VARCHAR2

) return BOOLEAN;

 

clientFile is the local file name

tableName is the table that contains a BLOB column

columnName is the BLOB column you want to store the file

whereClause is the Where clause to identify a unique raw in the table

 

 

 

*           Edit the document with the Webutil_Host.blocking function

 

FUNCTION  Blocking(cmd in VARCHAR2) return PROCESS_ID;

 

Example:

 

Declare

   LC$Cmd     Varchar2(256) ;

   Ret        WEBUTIL_HOST.PROCESS_ID ;

   LN$Result  Pls_Integer ;

   LC$FicName Varchar2(128) := Name_In( ‘BLOB_TABLE.DOC_NAME’ ) ;

Begin

   LC$Cmd := 'cmd /c start "" /WAIT "' || LC$FicName || '"' ;

   Ret := WEBUTIL_HOST.blocking( LC$Cmd ) ;

   LN$Result := WEBUTIL_HOST.Get_return_Code( Ret ) ;

End ;

 

Note the /WAIT parameter that wait the return of calling function before returning to Forms

 

 

*           Re-save the modified document into the database with the Webutil_File_Transfer.Client_To_DB function

 

 

*           Delete the temporary local file with the Webutil_File.Delete_File function

 

 

 

4.                      Call the CheckSpell MSWord function with CLIENT_OLE2

 

 

The following code allows to call the MSWord CheckSpell function and pass as an argument the content of a TEXT item

 

PROCEDURE CheckSpell

      (

            PC$Item in Varchar2

      ) IS

  -------------------------------------------

  --  Call the MSWord CheckSpell function  --

  -------------------------------------------

      MyApplication client_ole2.OBJ_TYPE;

      MyDocuments   client_ole2.OBJ_TYPE;

      MyDocument    client_ole2.OBJ_TYPE;

      MySelection   client_ole2.OBJ_TYPE;

      args          client_ole2.LIST_TYPE;

 

Begin

 

      MyApplication:=client_ole2.CREATE_OBJ('Word.Application');

      client_ole2.SET_PROPERTY(MyApplication, 'Visible', 0);

      MyDocuments:=client_ole2.GET_OBJ_PROPERTY(MyApplication, 'Documents');

      MyDocument := CLIENT_OLE2.INVOKE_OBJ(MyDocuments,'add');

 

      Myselection := CLIENT_OLE2.GET_OBJ_PROPERTY(Myapplication,'Selection');

 

      client_ole2.SET_PROPERTY(Myselection,'Text',Name_in(PC$item));

      client_ole2.INVOKE(MyDocument,'CheckSpelling');

 

      MySelection:=client_ole2.GET_OBJ_PROPERTY(MyApplication, 'Selection');

      client_ole2.INVOKE(mYSelection,'WholeStory');

      client_ole2.INVOKE(MYSelection,'Copy');

 

      COPY( CLIENT_OLE2.GET_CHAR_PROPERTY(Myapplication,'Selection'), PC$Item ) ;

 

      args := CLIENT_OLE2.CREATE_ARGLIST;

      client_ole2.ADD_ARG(args,0);

      client_ole2.INVOKE(MyDocument,'Close',args);

      client_ole2.DESTROY_ARGLIST(args);

      client_ole2.INVOKE(MyApplication,'Quit');

 

      client_ole2.RELEASE_OBJ(MySelection);

      client_ole2.RELEASE_OBJ(MyDocument);

      client_ole2.RELEASE_OBJ(MyDocuments);

      client_ole2.RELEASE_OBJ(MyApplication);

 

      copy(Substr(Replace(Name_in(PC$Item),CHR(13),CHR(10)),1,Length(Name_in(PC$Item))-1), PC$item );

     

Exception

      When others then

            Null ;

End;

 

You can call this procedure with the following code:

 

CheckSpell( :system.cursor_item ) ;

 

 

 

5.                      Sample dialog

 

 

The WEBUTIL_DOCS.fmb Forms module allow to test all these manipulations.

 

(Download the WEBUTIL_DOCS dialog)

 

 

 

Click the Select a local file… button to choose a file on your local machine

 

 

 

Click the Store local doc to database button to store the selected file in the database

 

 

The name of the new stored file is now displayed in the list of stored files

 

 

 

Double-click on the file name or click the Edit and save button to edit the document.

 

 

 

 

Run the CheckSpell function...

 

 

Click the Start the Word checkspelling button ...

 

 

 

This dialog is based on the BIN_DOCS table. You can use the following creation script.

 

CREATE TABLE BIN_DOCS

 (

   DOC_ID  NUMBER(5) PRIMARY KEY,

   NAME    VARCHAR2(128 BYTE),

   DOC     BLOB

 )

/

 

 

 

 

This basic sample could be the starting point of a most elaborated documentation application.

You could add some columns to the BIN_DOCS table to implement a check-in/check-out functionality that

allows every user to see a doc but only one could modify at the same time.