2 Replies Latest reply on Feb 23, 2017 7:32 AM by avernon_iastate

    JDBC, setBlob/setBinaryStream for external container field

    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>