Get varbinary from sql server and send to outputstream in xpages

I used this command to store a zip file as varbinary(max) in an sql server:

INSERT INTO Cache(name,zip)
SELECT 'my_zip_file1',* FROM 
    OPENROWSET(BULK N'D:\folder\fol2\my_zip_file.zip', SINGLE_BLOB) AS import;

and it successfully stores the name and binary data in the table named Cache.

In my xpage I have a button that sends to another download page with render=false and 1 line of code to beforeRenderResponse event in order to execute a java managed bean (i.e dl.download()).

I have managed to use jdbc from managed beans so as to get and store data from relational db such as db2 and sql server.

In this particular case I want to know how can I get the binary data from the db? Using rs.getBytes or rs.getBinaryStream ?

How can I send it to http response output? Below I am pasting the code that fails:

public void download(String filepath) {
    Connection con;
    PreparedStatement stm;
    ResultSet rs;
    byte[] buf = new byte[8192];
    //InputStream is = new ByteArrayInputStream(buf);
    byte[] zip;
    try {
        Class.forName(jdbcClass);
        con = DriverManager.getConnection(url);
        String sql = "SELECT TOP 1 * FROM Cache where name = ?;";
        stm = con.prepareStatement(sql);
        stm.setString(1, "D:\\folder\\fol2\\my_zip_file.zip");
        rs = stm.executeQuery();
        while (rs.next()) {
            zip = rs.getBytes("zip");
            ByteArrayInputStream is = new ByteArrayInputStream(zip);

            //System.out.println("AVAIL:"+zip.available());
            FacesContext facesContext = FacesContext.getCurrentInstance();
            ExternalContext externalContext = facesContext.getExternalContext();
            HttpServletResponse response = (HttpServletResponse) externalContext.getResponse();
            response.setContentType("application/zip, application/octet-stream");
            response.setHeader("Content-disposition", "attachment; filename=z.zip");
            response.setHeader("Cache-Control", "no-cache");
            ServletOutputStream sout = response.getOutputStream();

            int c = 0;
            //while ((c = is.read()) > 0) {
            while ((c = is.read(buf, 0, buf.length)) > 0) {
                //sout.write(c);
                sout.write(buf, 0, c);
                //sout.flush();
            }

        }
    }
}

The error is:

cant get writer while outputstream is used.

Trying another version of the code gave me the error:

output stream is closed

Any ideas?

PS: I have managed to make it work with a text file. I got a string from the db and sent a file containing this string to the outputstream. It was downloaded from the browser and everything was ok. The problem is with binary files as zip...

Answers


This is because you have to close response.

facesContext.responseComplete();

Need Your Help

How to remove all non-alpha numeric characters from a string in MySQL?

mysql regex string alphanumeric

I'm working on a routine that compares strings, but for better efficiency I need to remove all characters that are not letters or numbers.