10 Replies Latest reply on Sep 30, 2016 7:51 AM by tfreder

    Extracting binary (user uploaded) document directly from the database

      Goal: To programatically extract/download the files users have uploaded as documents in a particular space.

       

      I have inspected the jiveDocument, jiveDocVersion, jivedocbodyversion,and jivedocumentbody, and jiveBinStore tables and have identifed the query we need to use to find the relevant row of jiveBinStore table that has the binary data for a given version of uploaded document.

       

      We are trying to download the content of 'bindata' column of jiveBinStore (which is of type 'bytea' ) as files. I have tried download it in JAVA and in PERL, and in both the cases, the downloaded binary data from 'binData'  columns works fine if it's a PDF file, but MS-Office thinks the file is corrupted if hte downloaded file was a WORD, EXCEL or powerpoint.

       

      I believe i am missing necessary encoding/decoding of this binary data which is why MS-Office is complaining. I am posting the JAVA adn PERL snippets for where I am dowloading the contents from jiveBinStore. I'd appreciate if somebody could shedl lights on proper techniques for downloading DOCuMENTS that have been uploaded by users.

       

       


      Java:

                ps = conn.prepareStatement("select binData from jivebinstore  where binkey = ?");
                  ps.setString(1, "binaryBody-4007");
                   rs = ps.executeQuery();
                  if (rs != null)
                  {
                       while(rs.next())
                      {
                          byte[]  imgBytes = rs.getBytes(1);
                          // use the stream in  some way here

       

                           OutputStream out = new FileOutputStream("/var/tmp/test2132-4007.doc");
                           out.write(imgBytes);
                          out.close();
                       }
                  }

      Perl:

       

         print "Downloading internaldocid =  $internaldocid, versionid = $versionid, bodyid = $bodyid, filename =  $filename\n";

         my $binQuery = "select binData from  jivebinstore where binkey = 'binaryBody-$bodyid'";
         print ("Query:  $binQuery \n");
         my $download=$dbh->prepare ($binQuery);
          $download->execute();
         my @row = $download->fetchrow_array ;
          my $outputfile =  "/usr/local/psdm/apache-tomcat-6.0.16/webapps/PSDM-Test/jivedocuments/$filename";
          open ( OUT, ">$outputfile");
         print OUT $row[0];
         close  (OUT);

       

      Thanks.