AnsweredAssumed Answered

JDBC, setBlob/setBinaryStream for external container field

Question asked by avernon_iastate on Jan 4, 2017
Latest reply on Feb 23, 2017 by avernon_iastate

I have a project where an online form that submits two items – a text (URL) field and a file (preferably a PDF) -- to be saved in a FileMaker Pro 14 database  (hosted by FileMaker Server) using a JDBC connection.  I can save the text field to the FileMaker database, but the file won't transfer into the FileMaker Pro's (externally stored) Container field.  Unfortunately, a call to setBlob() or setBinaryStream() (for setting up the SQL statement) results in a "not implemented" exception.  Using setBytes()... doesn't throw an exception, but the file doesn't actually show up in the database either.

 

I’d appreciate any insights as to how to successfully upload a file and get it into my FileMaker Pro database through a JDBC connection.  I would not rule out a configuration or permissions issue related to a file upload.


 

My source code looks something like the following:

Servlet:

package com.server.something;

 

import java.io.IOException;

import javax.naming.NamingException;

import java.io.InputStream;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.SQLException;

 

import javax.servlet.ServletException;

import javax.servlet.annotation.MultipartConfig;

import javax.servlet.annotation.WebServlet;

import javax.servlet.http.HttpServlet;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

import javax.servlet.http.Part;

 

 

public class uploadServlet extends HttpServlet {

    

    // database connection settings

    private String dbURL = "jdbc:filemaker://some.server.com/Some_Database";

    private String dbUser = "someuser";

    private String dbPass = "somepass";

    

    protected void doPost(HttpServletRequest request,

            HttpServletResponse response) throws ServletException, IOException {

        // gets values of text fields

        String firstName = request.getParameter("firstName");

        

        InputStream inputStream = null; // input stream of the upload file

        

        // obtains the upload file part in this multipart request

        Part filePart = request.getPart("photo"); // my real application wants/expects a PDF.

        if (filePart != null) {

            // prints out some information for debugging

            System.out.println(filePart.getName());

            System.out.println(filePart.getSize());

            System.out.println(filePart.getContentType());

            

            // obtains input stream of the upload file

            inputStream = filePart.getInputStream();

        }

        

        Connection conn = null; // connection to the database

        String message = null;  // message will be sent back to client

        

        try {

            // connects to the database

            conn = DriverManager.getConnection( dbURL, dbUser, dbPass );

 

            // constructs SQL statement

            String sql = "UPDATE Some_Table SET first_name = ?, photo = PutAs(?, 'JPEG') WHERE ID = 1"; // hard coded ID field

 

            PreparedStatement statement = conn.prepareStatement(sql);

            statement.setString(1, firstName);

            

            if (inputStream != null) {

                // fetches input stream of the upload file for the blob column

                statement.setBlob(2, inputStream);

 

                // TODO: setBlob and setBinaryStream consistently results in an exception

                // TODO: "[FileMaker][FileMaker JDBC] This method is not yet implemented."

 

                // TODO: setBytes doesn't throw an exception, but the file doesn't seem

                // TODO: to transfer either.  Possible configuration or permissions issue?

            }

 

            // sends the statement to the database server

            int row = statement.executeUpdate();

            if (row > 0) {

                message = "File uploaded and saved into database";

            }

        } catch (SQLException ex) {

            message = "ERROR: " + ex.getMessage();

            ex.printStackTrace();

        } catch (NamingException ex) {

            message = "ERROR: " + ex.getMessage();

            ex.printStackTrace();

        } finally {

            if (conn != null) {

                // closes the database connection

                try {

                    conn.close();

                } catch (SQLException ex) {

                    ex.printStackTrace();

                }

            }

            // sets the message in request scope

            request.setAttribute("Message", message);

            

            // forwards to the message page

            getServletContext().getRequestDispatcher("somefile.jsp").forward(request, response);

        }

    }

}

 

 

 

JSP file:

<%@ page language="java" contentType="text/html; charset=ISO-8859-1"

    pageEncoding="ISO-8859-1"%>

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"

    "http://www.w3.org/TR/html4/loose.dtd">

<html>

<head>

<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">

<title>File Upload to Database Demo</title>

</head>

<body>

    <center>

        <h1>File Upload to Database Demo</h1>

        <form method="post" action="uploadServlet" enctype="multipart/form-data">

            <table border="0">

                <tr>

                    <td>First Name: </td>

                    <td><input type="text" name="firstName" size="50"/></td>

                </tr>

                <tr>

                    <td>Portrait Photo: </td>

                    <td><input type="file" name="photo" size="50"/></td>

                </tr>

                <tr>

                    <td colspan="2">

                        <input type="submit" value="Save">

                    </td>

                </tr>

            </table>

        </form>

    </center>

</body>

</html>

Outcomes