8 Replies Latest reply on Feb 22, 2010 1:26 PM by Adam Cameron.

    RTF and Oracle ... oh my

    Dinghus Level 1

      Anybody know how to store RTF documents in Oracle and then retrieve them so they are actually readable? Using ColdFusion 8 if possible.

       

      I can get the data into the db but it is in binary format. Then when it comes out it is still in binary and can't be read by any editors. urg.

        • 1. Re: RTF and Oracle ... oh my
          JR "Bob" Dobbs-qSBHQ2 Level 3

          Please post the code you are using to retrieve and display the RTF files.

          • 2. Re: RTF and Oracle ... oh my
            Adam Cameron. Level 5

            You shouldn't need to do anything special: the "T" in "RTF" stands for text.  It's just a text format.

             

            But seeing your code and your DB table structure would certainly take a lot of the guess work as to where you're going wrong ;-)

             

            --

            Adam

            • 3. Re: RTF and Oracle ... oh my
              Dinghus Level 1

              Well, the code is simply a cffile reading the file in binary and then taking that data a putting it into a blob in Oracle. BUT all I get is "garbage" because there is a graphic in the file. The editing was originally done in Word then saved as RTF. So the "text" part (I already knew that) is immaterial.

               

              But if I do a straight cffile read, not binary read, Oracle will not accept it even using cfsqlquerytype

               

              The biggest problem with this whole system is that the files are on a file server now. Before the app and files were on the same server so Word could read, edit, and save the files. Now of course, it can't if it is opening it across the network, unless I open it to everyone, which is not an option.

               

              The client wants it all in the database tho. Even their Oracle guru has no insight into this one.

              • 4. Re: RTF and Oracle ... oh my
                Adam Cameron. Level 5

                OK, as per my previous post... RTF files are not binary data.  It's just text.  Even the embedded image has been serialised into plain text (open the file in notepad and have a look!).

                 

                So stop treating it as if it is binary data.  It's just character data.

                 

                --

                Adam

                • 5. Re: RTF and Oracle ... oh my
                  Adam Cameron. Level 5

                  Oh yeah... if someone asks you to post your code... don't write a narrative describing the code... just post the actual code ;-)

                   

                  --

                  Adam

                  • 6. Re: RTF and Oracle ... oh my
                    Dinghus Level 1

                    Maybe you missed the part about it being for a client? Since I don't live at their site on the weekend I didn't have access to the code.  But since it doesn't work at all it really is moot.

                     

                    So what I'm getting from you is that since it insists on being handled as binary data it is actually mis-typed. Ie RTF when it is not RTF.  Word can open it but nothing else so far. Not even Word Pad.  Even if it is all text, which is what it does look like when trying to open it with other programs, it should go right into Oracle. But Oracle throws an error saying it is the wrong type --- not a blob.

                     

                    I can tell this is going to be another one of THOSE problems.

                    • 7. Re: RTF and Oracle ... oh my
                      Jochem van Dieten Level 4

                      If it is text, it should be CLOB, not BLOB.

                      • 8. Re: RTF and Oracle ... oh my
                        Adam Cameron. Level 5

                        Maybe you missed the part about it being for a client? Since I don't live at their site on the weekend I didn't have access to the code.  But since it doesn't work at all it really is moot.

                         

                         

                        It's not moot at all... because unless we see the code, we don't really know what you're doing wrong, other than just making educated guesses.

                         

                        And you never said you didn't have the code in front of you.  So - yes - I missed that bit.  It does not automatically follow that because you are not onsite at the client that you don't have access to the code.  I have access to my client's source code wherever I happen to be.

                         

                        So what I'm getting from you is that since it insists on being handled as binary data it is actually mis-typed.

                         

                        Hang on.  It's your code that's doing the <cffile action="readbinary"> and then sticking it into a BLOB column, isn't it?  That's what you're doing wrong.  It's not binary.  So doing binary operations on it or trying to store it as a binary is wrong.

                         

                        Again... seeing the code would make like so much easier.

                         

                        I experimented with an RTF (with image embedded) last night, reading it from file (<cffile action="read">) and writing it to a SQL Server ntext field (via a CF_SQL_CLOB param).  Reading it back again, writing it to file, and it all works fine.

                         

                        I will crank up my test Oracle DB this evening and try it on that, but I don't foresee any issues...

                         

                        --

                        Adam