10 Replies Latest reply: Feb 4, 2010 12:24 AM by 751395 RSS

    BLOB vs BFILE

    141565
      Dear Oracle Group,

      Our applications are basically Client/Server and/or n-tier typical windows applications. The data is stored in Oracle 9i database on Solaris. Now..., I would like to store photographs and pdf documents in the database. I have read many documents on how to store jpegs and pdfs in Oracle database.

      And I understand that I can store it in the database as a BLOB, BFILE or ORDSYS.OrdImage type column. But I have few problems here. My understanding is that, If I use BFILE or ORDSYS.OrdImage datatype, the image will be copied into the database server (Solaris), and we do not want the files to be copied into Solaris as an operating system file.

      Please if you have any thoughts and/or samples share with me.

      Thanks in advance,
      Raja
        • 1. Re: BLOB vs BFILE
          251301
          The BFILE datatype is a reference to a filesystem file. The data does not reside in the Oracle database and it is not managed by the database.

          If you want to store your image data in the database and have it managed by the database then you should use the ORDIMAGE datatype. This type uses an underlying BLOB to store the data in the database. It also adds image specific type attributes (width, height mimetype etc) to the datatype structure. These attributes are all managed with the image itself and are kept synchronized as the image is processed (scaled, rotated, transcoded to a different format). Another advantage of using ORDImage over BLOB is that your column is strongly typed. You know that this column contains an image, as opposed to audio, video or some other binary data.

          As for how you populate the ORDImage column with image data, there are a variety of methods: SQLLdr, Java client, HTTP clients etc. See the interMedia User Guide documentation for more information and examples.
          • 2. Re: BLOB vs BFILE
            141565
            Hi Rob,

            Thanks for your response, and its a bit clearer now.

            From your answer and my reading of few documents, I think I would have to use BLOB or ORDSYS. Because, I am going to store only PDFs I am thinking of using ORDSYS.ORDDoc datatype.

            Now I would have to start worrying about how to get the PDF from the client end to the database end (without copying the file across). As I said earlier, the applications are OLTP, client/server and/or n-tier and non-browser based applications. And this blocks my chances of using SQLLDR (and HTTP clients as well!? not sure).

            Also I am not quite clear on how do I use HTTP clients for a client/server application. So I think I need to look into OCI.

            Dear forum readers, if any of you have any suggestions or samples that would help me in saving my time.

            Thanks in advance,
            Raja
            • 3. Re: BLOB vs BFILE
              251301
              If you are sure that you are storing only PDF files then I would advise you to use BLOB and not ORDDoc. The reason is that ORDDoc does not understand PDF, (but it does understand all the image, audio and video types) .

              So your table design should include a BLOB column to hold the PDF and possibly a varchar column to hold the mimetype (but this will be a constant if all docs are PDFs)

              As for programming API, what language are you using? The language choice will determine your database API.

              C - OCI api
              C++ - OCCI classes
              Java - JDBC classes
              .NET - ODP.NET

              In each case you want to read closely the sections on using BLOBS. Each language will have it's own examples and code samples for reading and writing to BLOBs
              • 4. Re: BLOB vs BFILE
                251301
                Here is a C++ example using ORDImage
                -----------------------------------------


                #include <iostream>
                #include <fstream>
                #include <occi.h>
                #include <unistd.h>
                #include <string>
                using namespace oracle::occi;
                using namespace std;


                /* Buffer Size */
                #define BUFSIZE 16384;

                class occiImage
                {
                public:
                string username;
                string password;
                string url;

                void insertImageRow (Connection *conn, unsigned int id,
                string descrip, string location, string fName)
                throw (SQLException)
                {

                //
                // Open input file to populate blob
                //
                ifstream inFile;
                inFile.open(fName.data(), ios::in | ios::binary );
                if (!inFile)
                {
                cout << fName; cout << " file not found\n";
                return;
                }

                //
                // Insert row into DB returning ID and blob to be populated
                //
                Statement *stmt = conn->createStatement
                ( "Insert into Photos t values \
                (:v1, :v2, :v3, Ordimage.Init(), OrdImage.Init()) returning \
                ROWID, t.image.getContent() into :v4, :v5");
                stmt->setInt(1, id);
                stmt->setString(2, descrip);
                stmt->setString(3, location);
                stmt->registerOutParam(4, OCCISTRING, 50);
                stmt->registerOutParam(5, OCCIBLOB);
                stmt->executeUpdate();
                string rid = stmt->getString(4);
                Blob blob = stmt->getBlob(5);

                //
                // Populate interMedia blob from file using stream interface
                //
                unsigned int bufsize=BUFSIZE;
                char *buffer = new char[bufsize];

                Stream *strm = blob.getStream();
                while(inFile)
                {
                inFile.read((char *)buffer, bufsize);
                strm->writeBuffer(buffer,inFile.gcount());
                }
                strm->writeLastBuffer(buffer, 0);
                blob.closeStream(strm);
                inFile.close();
                delete[] buffer;

                //
                // In the same transaction, Set the image properties and create a thumbnail
                //
                stmt->setSQL
                ("DECLARE \
                img ORDIMAGE; \
                thumbnail ORDIMAGE; \
                BEGIN \
                select image, thumb into img, thumbnail from Photos \
                where rowid = :v1 for update; \
                img.setMimetype(:v2); \
                BEGIN \
                img.setProperties(); \
                img.processCopy('fileFormat=JFIF maxScale=128 128', thumbnail); \
                EXCEPTION WHEN OTHERS THEN thumbnail := NULL; \
                END; \
                update Photos set image=img, thumb=thumbnail where rowid = :v1; \
                END;");
                stmt->setString(1, rid);
                // set default mime type based upon file Extension in
                // case of setProperties failure (image/<FILE_EXTENSION>)
                stmt->setString(2, "image/" + fName.substr(fName.find_last_of(".") + 1));

                stmt->executeUpdate();
                conn->commit();
                conn->terminateStatement (stmt);
                cout << "Populating and initializing the Image - Success" << endl;

                return;
                }

                void writeThumbnailFile (Connection *conn, unsigned int id,
                string fprefix)
                throw (SQLException)
                {
                cout << "createStatement\n";
                Statement *stmt = conn->createStatement
                ( "Select t.thumb.getContent(), t.thumb.getMimetype() from Photos t \
                where t.id= :v1");
                stmt->setInt(1, id);
                ResultSet *rset = stmt->executeQuery ();

                if (rset->next ())
                {
                Blob blob = rset->getBlob (1);
                string mimeType = rset->getString(2);

                string fName = fprefix + "." +
                mimeType.substr(mimeType.find_last_of("/") + 1);
                cout << "Output file name is " << fName << endl;

                ofstream outFile;
                outFile.open(fName.data() , ios::out | ios::binary);
                if (!outFile)
                {
                cout << fName; cout << " file could not be created\n";
                conn->terminateStatement (stmt);
                return;
                }

                unsigned int bufsize=BUFSIZE;
                char *buffer = new char[bufsize];
                Stream *inStream = blob.getStream (1,0);
                while (1)
                {
                int bytesRead = inStream->readBuffer(buffer, bufsize);
                if (bytesRead < 0) break;
                outFile.write(buffer, bytesRead);
                }
                blob.closeStream(inStream);
                outFile.close();
                delete[] buffer;
                }
                conn->commit();
                conn->terminateStatement (stmt);

                return;
                }

                occiImage ()
                {
                /**
                * default values of username & password
                */
                username = "scott";
                password = "tiger";
                url = "";
                }

                void setUsername (string u)
                {
                username = u;
                }

                void setPassword (string p)
                {
                password = p;
                }

                void setUrl (string u)
                {
                url = u;
                }

                void runImageSample ()
                throw (SQLException)
                {
                Environment *env = Environment::createEnvironment (
                Environment::DEFAULT);
                Connection *conn = env->createConnection (username, password, url);
                cout << "Start runImageSample\n";

                insertImageRow (conn, 1, "picture", "somewhere", "image.dat");
                writeThumbnailFile(conn, 1, "thumbnail");

                env->terminateConnection (conn);
                Environment::terminateEnvironment (env);
                }

                };//end of class occiImage

                int main (void)
                {
                try
                {
                occiImage *i = new occiImage ();
                i->setUsername ("scott");
                i->setPassword ("tiger");
                i->runImageSample ();
                delete(i);
                }
                catch (exception &e)
                {
                cout << e.what();
                }
                }
                • 5. Re: BLOB vs BFILE
                  141565
                  Hi Rob,

                  That was very helpful. Thanks a lot for that. If I have any more questions on this topic, I will post to this forum and hope to get a reply from you :) .

                  Thanks again.

                  Regards
                  Raja
                  • 6. Re: BLOB vs BFILE
                    141565
                    Hi,

                    As Rob has suggested I am using BLOB and not ORDDoc to store PDF documents. I was able to store the PDF document into the BLOB successfully, also I was able to read. But once I read the document I am not sure how to display the document. Here is my situation...

                    My application is a client/server application which has typical windows interface. Once I read the PDF(BLOB) from database, it is actually stored in a memory variable. I would like to know a way of displaying this stored value as a PDF.

                    What I can do is, I can write this variable as a PDF file in say C:\TEMP... . And then display this file.

                    But I would like to know if this is right way of doing it. Or is there any other proper way of doing it.

                    Thanks for your valuable suggestions.

                    Regards
                    Raja
                    • 7. Re: BLOB vs BFILE
                      251301
                      What application are you using to render the PDF to the user?? Adobe Acrobat? Acrobat is packaged as a standalone application but I think it also has ActiveX controls (or COM components, sorry my Windows API knowledge is a bit dated).

                      I would look at the programming APIs for Acrobat and decide how to launch the viewer for the PDF. The API's will then give you some choice as to how to pass the data.
                      • 8. Re: BLOB vs BFILE
                        651310
                        Hi,

                        I am exactly in the same situation as you were when you posted in this discussion. The only difference is that I am using Oracle 10g Express Edition and I am using Java and Javascript for my web application.
                        I would like to know, if it were possible, how did you finally display the pdf file that was contained in the blob column.

                        Thank you so much in advance.
                        • 9. Re: BLOB vs BFILE
                          680382
                          just set the content type to "*application/pdf*" and write the whole blob into the servlet output stream...
                          • 10. Re: BLOB vs BFILE
                            751395
                            hi
                            i'm facing a problem
                            i want to upload pdf files in Blob filed in the database from the oracle forms 6i and show it in a later stage from the same oracle forms 6i.
                            can someone help me?

                            if the upload and preview aren't possible from oracle forms,tell me if i can create a procedure to upload the pdf file into the database and another procedure to recreate for example the same pdf file, and i can manage to show it in the oracle forms using the command: HOST