Hi all,

I'm looking for some information on how to retrieve PDF files from a MS SQL BLOB field. I am able to use WebFOCUS to query the table and retrieve the 'string' (not sure if that's the right term) from the records but I don't know how to turn that into a hyperlink that will open the document.

Thanks for any help you can provide!

Dan

Dan,

Two things come to mind.

1) A hyperlink could be created to a file. You would then have to figure out how to get the results of your field into a file that a URL could reach.
2) You might be able to dump your field into a variable and then !IBI.FIL.VAR .

I doubt that 2 will work since the length of your field is probably very large.

As for 1, see if you can place the results into a file and then open that file directly first.

Fernando

Prod WF 8.1.04, QA WF 8.2.03, Dev WF 8.2.03

I think you are going to have to use a JSP to do that.

I'd be interested in a technique that does this. We have an Oracle table that we call the File Cabinet. User can store all type of documents in the File Cabinet (.doc, .xls, .pdf, etc.). Sometimes we get a request for what we call a case dump. We generally do this in respone to a supeona for a court case. Right now, someone has to manually retrieve and print these documents. It would be nice to include these in our case dump report.

Maybe I'm totally wrong here, but isn't it true that:
If you're using the report library (which has to be in a rdbms like MS SQL), you can have reportcaster store your reports in it in any format you like. Later, you can retrieve your report from the library.
So I'm inclined to think that this technology should be available. Maybe a close look at way the report library retrieval process works could reveal how it's done.
My two cents...

GamP,

You are correct in your assessment of the Report Library. My challenge is that I am trying to replace an existing .NET interface that uses a tree-view. When a user clicks an item from the tree it directly opens the PDF document. When you click on an item in the Report Library tree what you see is a page that shows the meta-data about the document which includes versioning information. Then, the user has to click on a link from that page to access the document.

One possible way around this is to cast the PDF files into the library and then create a web page that has hyperlinks to the actual URLs for each document. That's a lot more work than I'd rather do since there are quite a few of these documents and each user group has different access to the docs. That would require a separate web page for each group.

The SQL repository I am querying already exists and has a process in place for populating it so I don't want to cause anybody duplicate effort. Plus, it seems like I'm basically one step away from being able to deliver the document to my WebFOCUS user so my hope was to find a way to make this happen without a lot of extra code.

Dan

Example of jsp to retrieve pdf from a Blob. You would have to change the specifics for MS Sql, and add the necessary code for retrieving request parameters, but you would be able to call this as a hyperlink from a report:

<%@ page contentType="application/pdf" %>
<%@ page pageEncoding="iso-8859-1" %>
<%@ page language="java" import="java.sql.*,java.util.*,java.io.*" %>
<%!  
     // connection method for jdbc.
     Connection getConnection() throws Exception {
          String driver = "org.gjt.mm.mysql.Driver";
          String url = "jdbc:mysql://host-name/db-name";
          String username = "userid";
          String password = "password";
          Class.forName(driver);
          Connection conn = DriverManager.getConnection(url, username, password);
          return conn;
     }
  
     byte[] getBLOB(String _rptId) throws Exception {
          Connection conn = null;
          ResultSet rs = null;
          PreparedStatement pstmt = null;
          String query = "SELECT REPORT FROM botldata b where reportid = ?";
          Blob blob = null;

          try {
               conn = getConnection();
               pstmt = conn.prepareStatement(query);
               pstmt.setString(1, _rptId);
               rs = pstmt.executeQuery();
               rs.next();
               blob = rs.getBlob("REPORT");
          } catch (Exception e) {
          } finally {
               rs.close();
               pstmt.close();
               conn.close();
          }
          if (blob != null) 
               return blob.getBytes((long)1, (int)blob.length());
          else
               return null;
     }
%>
<% 
     // scriptlet part.
     ServletOutputStream os = response.getOutputStream();
     byte[] b = getBLOB("R13200fise1_2");
     os.write(b);
     os.close();
     out.clear();
     out = pageContext.pushBody();
%>

dhagen,

Thanks for this example code! I will see if I can figure out how to adopt it to our database and table structure. I do have a couple of questions...

In our SQL table, 'PDF' is the name of the BLOB field and 'ProfileID' is the unique ID assigned to that record. In your query string you have "SELECT REPORT FROM botldata b where reportid = ?" Do I change that to read "SELECT PDF FROM [ourtablename] b where ProfileID = [someprofileidvalue]"?

Later in your code you have a statement that uses "R13200fise1_2". Is that a hard-coded value? It looks to me like I should replace that with "PDF" is that right?

Thanks!

Dan

dhagen,

One more question. Assuming this works, how do I put this code inside an HTML page that I can call from a drill-down in my .fex procedure? My thought is to pass a !IBI.AMP.VAR that has the ProfileID in it and use that in the SELECT statement.

Thanks again!

Dan

To do what you are looking for, the following would be what you want:

<%@ page contentType="application/pdf" %>
<%@ page pageEncoding="iso-8859-1" %>
<%@ page language="java" import="java.sql.*,java.util.*,java.io.*" %>
<%!  
     // connection method for jdbc.
     Connection getConnection() throws Exception {
          String driver = "com.microsoft.jdbc.sqlserver.SQLServerDriver";
          String url = "jdbc:microsoft:sqlserver://server-name:1433;DatabaseName=dbname"; // replase dbname with actual name
          String username = "userid";
          String password = "password";
          Class.forName(driver);
          Connection conn = DriverManager.getConnection(url, username, password);
          return conn;
     }
  
     byte[] getBLOB(String _rptId) throws Exception {
          Connection conn = null;
          ResultSet rs = null;
          PreparedStatement pstmt = null;
          String query = "SELECT PDF FROM [ourtablename] b where ProfileID = ?";
          Blob blob = null;

          try {
               conn = getConnection();
               pstmt = conn.prepareStatement(query);
               pstmt.setString(1, _rptId); // assign the user requeested pdf id to the above prepared statement.
               rs = pstmt.executeQuery();
               rs.next();
               blob = rs.getBlob("PDF"); // retrieve the blob stored as column PDF.
          } catch (Exception e) {
          } finally {
               rs.close();
               pstmt.close();
               conn.close();
          }
          if (blob != null) 
               return blob.getBytes((long)1, (int)blob.length());
          else
               return null;
     }
%>
<% 
     // scriptlet part.
     ServletOutputStream os = response.getOutputStream();
     byte[] b = getBLOB(request.getParameter("rptId"));  // this line gets the report uid from the jsp call url.
     os.write(b);
     os.close();
     out.clear();
     out = pageContext.pushBody();
%>  

The the url would be something like (assume the above is called /ibi_apps/custom/pdfView.jsp):

  
http://<host>:<port>/ibi_apps/custom/pdfVie...?rptId=R13200fise1_2

You just pass the pdf report id to the jsp like any wf param to a fex (above). You can do this by creating a URL drilldown in your report. The URL value would be

 http://host:port/ibi_apps/custom/pdfView.jsp 

and then add a parameter called "rptId" with the parameter value the column with the unique pdf id; e.g. rptId=PDF_UID


One last thing: Make sure the sql server jar files are on the classpath of your application server. If you use Tomcat, you can put them in the common/lib directory.

Hope this helps

This message has been edited. Last edited by: dhagen,

dhagen,

This is excellent! I am so close thanks to your help. I seem to be having a problem with my APPPATH because I am not able to launch anything from a URL. I can't even get html files from the ibisamp folder to launch. Apache tells me the requested resource is not available.

Hopefully I can get this resolved and move on to testing the .jsp page.

Thanks,

Dan

The apppath should have nothing to do with it. You have to specify the full URL, including the http, server name etc. Otherwise it will default to localhost. That part kinda blows a little. My url looks like:

TYPE=DATA,
     COLUMN=N2,
     URL=http://meanmachine:8080/ibi_apps/custom/pdfView.jsp?(rptId=N1),  

dhagen,

It turns out that I needed to structure the URL using approot instead of ibi_apps. So I used http://:/approot/pdfview/pdfview.jsp. That runs the .jsp page.


Now I just have to troubleshoot this...

HTTP Status 500 -

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

type Exception report

message

description The server encountered an internal error () that prevented it from fulfilling this request.

exception

org.apache.jasper.JasperException: Exception in JSP: /pdfview/pdfview.jsp:32

29: blob = rs.getBlob("PDF");
30: } catch (Exception e) {
31: } finally {
32: rs.close();
33: pstmt.close();
34: conn.close();
35: }


Stacktrace:
org.apache.jasper.servlet.JspServletWrapper.handleJspException(JspServletWrapper.java:504)
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:393)
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:314)
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:264)
javax.servlet.http.HttpServlet.service(HttpServlet.java:802)


root cause

java.lang.NullPointerException
org.apache.jsp.pdfview.pdfview_jsp.getBLOB(pdfview_jsp.java:41)
org.apache.jsp.pdfview.pdfview_jsp._jspService(pdfview_jsp.java:91)
org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:97)
javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:332)
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:314)
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:264)
javax.servlet.http.HttpServlet.service(HttpServlet.java:802)


note The full stack trace of the root cause is available in the Apache Tomcat/5.5.17 logs.


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

Apache Tomcat/5.5.17

7.7.05M/7.7.03 HF6 on Windows Server 2003 SP2 output to whatever is required.

Dan, you can't do it that way. You are trying to take advantage of Tomcat's willingness to allow you to run JSPs in non-traditional web apps .... such as approot. The JSP you are working with needs JAR files that are delivered with the webfocus web app, and that do not exist in approot. If you cannot deploy the JSP in ibi_apps, then you are going to have to build a proper web app to deploy it in. Unless you want to build your own, or augment approot to include the appropriate dirctory structures, JARs, deployment descriptors etc, I would suggest that you deploy it in ibi_apps. What I would do is create a directory under the /webapps/webfocus71 called custom. Then put the JSP there. That will allow you to take advantage of all the deployed JAR files that are part of the standard web focus install. It will save you a lot of pain! I change my installations all the time with different utilities - and complete apps in some cases - to help enhance the default install.

Adding your own components to ibi_apps is not going to violate any license agreements etc. The only negative thing with it is that you have to remember to re-apply your changes every time you install a new version. Your IT guys might have a fit at first, but this is the reason we all went to web apps in the first place. They are easy to plug-in custom stuff to.

Trust me on this one. This could be a very long road you are heading down.

Ah! That would explain why I had to use approot in my URL as opposed to ibi_apps. Cool. I'll move it under the webapps directory and see what I get.

OK - I moved the folder and the .jsp page under the webapps folder so now it runs from a url that has /ibi_apps/pdfview/pdfview.jsp but I'm still getting an error. I don't know java at all but I'm wondering if it just isn't getting anything from the database which is why it is throwing the java.lang.NullPointerException.

With regard to the SQL Server jar files, I did copy sqljdbc.jar into the C:\Program Files\Apache Software Foundation\Tomcat 5.5\common\lib directory. Do I also need to copy any of the files from the /help /auth or /xa folders?

Thanks,

Dan

There are 3 jar files that make up the JDBC drivers for SQL Server.

msbase.jar
mssqlserver.jar
msutil.jar

You have to copy all 3 to the common/lib directory, or you can place them in the WebFOCUS76/webapps/webfocus76/WEB-INF/lib directory. If you place them in the common/lib, then they will be available for any deployed web application on Tomcat. If you place them in the webfocus lib, then they will be only available to the /ibi_apps application.

Make sure they are the appropriate JDBC drivers for you version and patch level of SQL Server. You can download them from microsoft.com.