13 Replies Latest reply on Oct 26, 2011 8:44 AM by ow008339

    Forms 10G - Saving Blob on Application Server using Java.

    Timothy Morton
      Hello,

      Using forms 10g, I'm looking for a solution to retreive Blob from my Database (10gR2) and save it to the Application Server on directory "Oraclehome\Apache\Apache\htdocs" so users can download/see it via the browser.
      first of all, I used WebUtil to download files on client and it worked perfectly.
      However, for a couple of reasons, my customer doesn't want to download file on his client.

      So, here I am, I created a java class called "saveBlobToFile" wich receives a string as parameter to write it to a file.
      I imported this class into Forms where I used the following code.
      Everything works fine but I have to convert my RAW into HEX to pass it to Java as a string and convert it again to Byte[].
      I order to speed up the process, I would like to avoid these 2 conversions.
      Do you think it's possible to pass the RAW (which is just a stream of byte) to java? Either by converting it to ORA_JAVA.JARRAY of byte? Either by passing it like an object and cast it in java to an array of bytes?

      Thanks in advance,
      Tim.

      PS: I based my development on this article
      https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=70110.1


      /***************
      FORMS
      ***************/
      DECLARE     
      MY_BLOB BLOB;
      BUFFER RAW (32767);
      AMT BINARY_INTEGER := 10000; --32767;
      POS INTEGER := 1;
      MYBLOBFILE ORA_JAVA.JOBJECT;
      BEGIN
      SELECT DOCUMENT
      INTO MY_BLOB
      FROM WRK_LOB_STORE
      WHERE ID = 1;

      LOOP
      DBMS_LOB.READ (MY_BLOB, AMT, POS, BUFFER);

      IF POS = 1 THEN
      MYBLOBFILE := SAVEBLOBTOFILE.NEW ();
      SAVEBLOBTOFILE.WRITETOFILE (MYBLOBFILE, 'C:\test.txt', RAWTOHEX (BUFFER));
      ELSE
      SAVEBLOBTOFILE.APPENDTOFILE (MYBLOBFILE, 'C:\test.txt', RAWTOHEX (BUFFER));
      END IF;

      EXIT WHEN AMT < 10000;
      POS := POS + AMT;
      END LOOP;
           END;

      /***************
      JAVA
      ****************/

      package saveblobtofile;

      import java.io.FileNotFoundException;
      import java.io.FileOutputStream;
      import java.io.IOException;

      public class saveBlobToFile {

      private FileOutputStream fio;

      public saveBlobToFile() {       
      }

      public void writeToFile ( String fileName, String stringBuffer ) throws FileNotFoundException,
      IOException {

      fio = new FileOutputStream(fileName);
      fio.write(hexStringToByteArray(stringBuffer));
      fio.close();

      }

      public void appendToFile ( String fileName, String stringBuffer ) throws FileNotFoundException,
      IOException {

      fio = new FileOutputStream(fileName, true);
      fio.write(hexStringToByteArray(stringBuffer));
      fio.close();

      }

      public static byte[] hexStringToByteArray(String s) {
      int len = s.length();
      byte[] data = new byte[len / 2];
      for (int i = 0; i < len; i += 2) {
      data[i / 2] = (byte) ((Character.digit(s.charAt(i), 16) << 4)
      + Character.digit(s.charAt(i+1), 16));
      }
      return data;
      }


      public static void main(String[] args) {
      saveBlobToFile saveBlobToFile = new saveBlobToFile();
      }
      }
        • 1. Re: Forms 10G - Saving Blob on Application Server using Java.
          Andreas Weiden
          One quite easy solution is to "adjust" the WEBUTIL_FILETRANSFER-package and create a new procedure DB_TO_AS by combining the existing procedure DB_TO_CLIENT and CLIENT_TO_AS (withou the intermediate step of saving the file to the client)
          • 2. Re: Forms 10G - Saving Blob on Application Server using Java.
            Timothy Morton
            Thank you for your response.

            In the solution you suggested, the only downside I see is the fact that the data will be transfert from the DB to AS by passing through the client network.
            It means that a 1 Mb file will create 3 Mb of traffic. 1 Mb from DB to client, 1 Mb from client to AS and finally 1 Mb from AS to client when the browser opens it.
            It's why I try to do everything directly on AS.

            An another solution would be to let Java do all the job.
            I mean connecting to the database, get the blob with "Blob.getBytes" and save them to the AS".

            Do you think it's possible to create a java class which runs on AS and connect to the DB using JDBC?
            If so, what should I be aware of?
            Do you have an exemple?

            Thanks in advance.
            • 3. Re: Forms 10G - Saving Blob on Application Server using Java.
              spilgrim
              "In the solution you suggested, the only downside I see is the fact that the data will be transfert from the DB to AS by passing through the client network"

              - will it ? I don't think so. It'll be read from DB by the AS and written on the AS - it depends how these two are linked together and if on one server no network at all.
              • 4. Re: Forms 10G - Saving Blob on Application Server using Java.
                InoL
                The procedure can be simpler (and put in in the database):
                CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED BLOB_TO_FILE as
                import java.lang.*;
                import java.sql.*;
                import oracle.sql.*;
                import java.io.*;
                
                public class BLOB_TO_FILE {
                
                   public static void BlobToFile
                   ( String myFile
                   , BLOB   myBlob
                   ) throws Exception
                   {
                      // Bind the object to the database object
                      // Open streams for the output file and the blob
                      File binaryFile = new File(myFile);
                      FileOutputStream outStream = new FileOutputStream(binaryFile);
                      InputStream inStream = myBlob.getBinaryStream();
                
                      // Get the optimum buffer size and use this to create the read/write buffer
                      int size = myBlob.getBufferSize();
                      byte[] buffer = new byte[size];
                      int length = -1;
                
                      // Transfer the data
                      while ((length = inStream.read(buffer)) != -1)
                      {
                         outStream.write(buffer, 0, length);
                         outStream.flush();
                      }
                
                      // Close everything down
                      inStream.close();
                      outStream.close();
                   }
                };
                /
                Edited by: InoL on Jan 26, 2011 10:34 AM
                1 person found this helpful
                • 5. Re: Forms 10G - Saving Blob on Application Server using Java.
                  Andreas Weiden
                  As spilgrim said it won't. I didn't suggest to transfer the file to the client and then form there to the AS, but directly from the db to the as. For that you have to dig into the code of the package WEBUTIL_FILETRANSFER and "combine" the code of the two functions to a new one.
                  • 6. Re: Forms 10G - Saving Blob on Application Server using Java.
                    Timothy Morton
                    I though the file would transfert trought client because webutil is base on webutil.jar which is donwloaded on client machine.
                    Furthermore, Webutil is base on javabean wich runs in the java applet.
                    Did I misunderstand something?

                    I will dig into the code of webutil and keep you abreast of my test.
                    Let me some time to test it because I have a lot of work.

                    Inol, thank you for your code, i will keep it for further use.
                    I'm sure it works to write blob on database server but not on the AS (in the case DB and AS stand on different servers)
                    A question comes to my mind, is it better to use a java procedure or plsql inside the database.
                    I read somewhere on the web that java is slower than plsql native.
                    What do you think about that?
                    • 7. Re: Forms 10G - Saving Blob on Application Server using Java.
                      InoL
                      I'm sure it works to write blob on database server but not on the AS (in the case DB and AS stand on different servers)
                      Yes, you can write directly from DB to the AS file system with this Java procedure. Java does not work with an Oracle database DIRECTORY (like utl_file). If the permissions are set correctly (java.io.FilePermission), you can write to a directory on another server.
                      A question comes to my mind, is it better to use a java procedure or plsql inside the database.
                      I read somewhere on the web that java is slower than plsql native.
                      Use what is needed. If you can do it in pl/sql, do it. There is no good pl/sql alternative for this specific Java procedure.
                      • 8. Re: Forms 10G - Saving Blob on Application Server using Java.
                        François Degrelle
                        Webutil has a Client_To_As() method, so the fact the webutil.jar is located on the Client side has no importance.
                        As Andreas said, you can create a new method in the Webutil library that extract code from existing methods then combine them to have a kind of DB_To_As() that excludes the Client step.

                        Francois
                        • 9. Re: Forms 10G - Saving Blob on Application Server using Java.
                          Timothy Morton
                          Ok. I dig into the code and create a new function to download Blob from DB to AS.
                          In its whole, it works fine. I didn't test it completly. (Error checks, ...)
                          I compared the download time with my first procedure above and it's the same.
                          So I don't really gain time but I don't need to deploy a new Jar file to my 20 clients wich is interesting because I don't always have an easy access to their OAS server.
                          For info, I tried to implement the same progress bar but without success. The small window/box does'nt appear on client side. I missed something...
                          If I get time, I will dig further.
                          Thanks everyone for your help.
                          Here is what i added into the webutil.pll.

                          /**
                          * DB_To_AS - downloads the contents of a database BLOB on the application server.
                          * The combination of tableName and columnName should identify a BLOB
                          * Column in the database. The whereClause should narrow the selection
                          * down so that one and only one row is identified.
                          * The file transfer is carried out using the normal Forms connection.
                          *
                          * The currently connected user #must# have execute priviledges on
                          * the WEBUTIL_DB package.
                          *
                          * If you specify withProgress you will get a progress
                          * bar as the download takes place
                          * Note that the title and subtitle cannot contain the '|'
                          * character
                          */
                          FUNCTION DB_to_AS
                          ( serverFile in VARCHAR2,
                          tableName in VARCHAR2,
                          columnName in VARCHAR2,
                          whereClause in VARCHAR2,
                          withProgress in BOOLEAN DEFAULT FALSE,
                          progressTitle in VARCHAR2 DEFAULT NULL,
                          progressSubTitle in VARCHAR2 DEFAULT NULL) return BOOLEAN ;


                          FUNCTION DB_to_AS
                          ( serverFile in VARCHAR2,
                          tableName in VARCHAR2,
                          columnName in VARCHAR2,
                          whereClause in VARCHAR2,
                          withProgress in BOOLEAN DEFAULT FALSE,
                          progressTitle in VARCHAR2 DEFAULT NULL,
                          progressSubTitle in VARCHAR2 DEFAULT NULL) return BOOLEAN is

                          fileSize PLS_INTEGER := 0;
                          fileChunks PLS_INTEGER := 0;
                          buffer VARCHAR2(32767);
                          clientFileInfo VARCHAR2(1000 char);
                          jobj ORA_JAVA.JOBJECT;
                          lastErrorCode PLS_INTEGER;
                          lastErrorArgs VARCHAR2(1000);

                          BEGIN

                          -- AS file cannot be null. AppServerFileReader.java also handles this. Handling here is better.
                          if serverFile is null then
                          raise AS_FILE_NULL;
                          end if;

                          If checkASACL(serverFile, m_writeACL) Then
                          raise TRANSFER_FORBIDDEN;      
                          End if;     

                          -- First check to see if a transfer is not currently happening
                          if In_Progress then
                          raise AGENT_BUSY;
                          end if;

                          -- Open the correct source & get the file size
                          if not m_DBEnabled then
                          raise TRANSFER_FORBIDDEN;
                          end if;

                                    -- Open Blob
                          if not WebUtil_DB_Local.openblob(tableName, columnName, whereClause, 'R', m_maxTransferSize) then
                          lastErrorCode := WebUtil_DB_Local.getLastError;
                          WebUtil_core.Error(Webutil_Core.WUT_PACKAGE,lastErrorCode,
                          'WEBUTIL_FILE_TRANSFER.AS_To_DB',tableName||'.'||columnName||' - Where '||whereClause);
                          raise READWRITE_ERROR;
                          end if;
                          fileSize := WebUtil_DB_Local.getSourceLength;
                          fileChunks := WebUtil_DB_Local.getSourceChunks;

                          /*
                          -- construct and send the fileinfo string to the client
                          clientFileInfo := serverFile||'|'||to_char(fileSize)||'|N'||
                          WebUtil_Util.BoolToStr(withProgress,'|Y|','|N|')||
                          progressTitle||'|'||progressSubTitle;
                          WebUtil_Core.setProperty(WebUtil_Core.WUT_PACKAGE,'WUT_FILE_INFO',clientFileInfo);
                          */
                                    -- Open AS file.
                          if not WebUtil_Core.checkJava then
                          raise NO_JAVA;
                          end if;           
                                    jobj := JAVA_APPSERV_WRITER.NEW;
                          if not JAVA_APPSERV_WRITER.openFile(jobj,serverFile) then
                          lastErrorCode := JAVA_APPSERV_WRITER.getLastError(jobj);
                          lastErrorArgs := JAVA_APPSERV_WRITER.getLastErrorArgs(jobj) ;
                          WebUtil_core.Error(Webutil_Core.WUT_PACKAGE,lastErrorCode,
                          'WEBUTIL_FILE_TRANSFER.DB_to_AS',lastErrorArgs);
                          raise READWRITE_ERROR;
                          end if;

                          -- Now loop through and get each buffer full of data
                          for i in 1..fileChunks LOOP
                          buffer := WebUtil_DB_Local.ReadData;
                          if not JAVA_APPSERV_WRITER.WriteData(jobj,buffer) then
                          lastErrorCode := JAVA_APPSERV_WRITER.getLastError(jobj);
                          lastErrorArgs := JAVA_APPSERV_WRITER.getLastErrorArgs(jobj) ;
                          WebUtil_core.Error(Webutil_Core.WUT_PACKAGE,lastErrorCode,
                          'WEBUTIL_FILE_TRANSFER.DB_to_AS',lastErrorArgs);
                          raise READWRITE_ERROR;
                          End if;     
                          End loop;

                                    -- Close File
                          If Not JAVA_APPSERV_WRITER.CloseFile(jobj,fileSize) Then
                          lastErrorCode := JAVA_APPSERV_WRITER.getLastError(jobj);
                          lastErrorArgs := JAVA_APPSERV_WRITER.getLastErrorArgs(jobj) ;
                          WebUtil_core.Error(Webutil_Core.WUT_PACKAGE,lastErrorCode,
                          'WEBUTIL_FILE_TRANSFER.UploadInt',lastErrorArgs);      
                          raise READWRITE_ERROR;
                          End If;

                          Return true;

                          EXCEPTION
                          when AS_FILE_NULL then
                          WebUtil_Core.Error(Webutil_Core.WUT_PACKAGE,117,'WEBUTIL_FILE_TRANSFER.DB_to_AS');
                          return false;
                          when TRANSFER_FORBIDDEN then
                          WebUtil_Core.Error(Webutil_Core.WUT_PACKAGE,121,'WEBUTIL_FILE_TRANSFER.DB_to_AS');
                          return false;
                          when AGENT_BUSY then
                          WebUtil_Core.Error(Webutil_Core.WUT_PACKAGE,116,'WEBUTIL_FILE_TRANSFER.DB_to_AS');
                          return false;
                          when READWRITE_ERROR then
                          return false;
                          when NO_JAVA then
                          return false;
                          when WebUtil_Core.BEAN_NOT_REGISTERED then
                          WebUtil_Core.ErrorAlert(WebUtil_Core.getImplClass(WebUtil_Core.WUT_PACKAGE)
                          ||' bean not found. WEBUTIL_FILE_TRANSFER.DB_to_AS will not work');
                          RAISE FORM_TRIGGER_FAILURE;
                          when WebUtil_Core.PROPERTY_ERROR then
                          RAISE FORM_TRIGGER_FAILURE;
                          END DB_to_AS;

                          Edited by: Timothy Morton on 3 févr. 2011 00:44
                          • 10. Re: Forms 10G - Saving Blob on Application Server using Java.
                            Timothy Morton
                            Ok guys after testing it deeper I found an error.
                            I was closing the Blob at the end of the procedure which raised an error.
                            I removed it.
                            I also renamed the function DB_To_AS instead of AS_To_DB.
                            See code above.

                            Edited by: Timothy Morton on 3 févr. 2011 00:44
                            • 11. Re: Forms 10G - Saving Blob on Application Server using Java.
                              François Degrelle
                              Thank you for the community ;-)

                              Francois
                              • 12. Re: Forms 10G - Saving Blob on Application Server using Java.
                                InoL
                                Thanks for sharing. Maybe Oracle will pick up this idea and put in in the next webutil.

                                One remark:
                                Here is what i added into the webutil.pll.
                                It may be a good idea to put this in a custom library (including the private package procedures that are used). Otherwise, you have to keep putting your code back in webutil after an Oracle patch.
                                • 13. Re: Forms 10G - Saving Blob on Application Server using Java.
                                  ow008339
                                  Hi I just came across this thread as I need to transfer a file from the db to the as as described. I have got it working but when I try to transfer a lot of files one after the other or one big file I eventually get an error of ora-105101. Looking this up in google it keeps on mentioning to ensure jar files on certain paths etc. I don't know if that is the problem as the function does work.

                                  I know this thread was answered a long time ago but if anyone could help it would be great

                                  Anthony