7 Replies Latest reply on Sep 13, 2010 2:53 AM by RemkoKlein

    jbdc, fm 11 server advanced, blobs

    OnnoTijdgat

      Title

      jbdc, fm 11 server advanced, blobs

      Your post

      I am using FM 11 Server Advanced as a data source in a Java/jsp project. The connection with the database is made thru jdbc.

      In principle this works fine. However, I am having trouble retrieving the contents of FM container fields. In principle I should be able to do something like xyz.getBlob(colIdx) to get a Blob object refering to the FM container. xyz is a ResultSet object containing the result of an SQL query.

      However this blob object is always null. Inspecting the Resultset shows that indeed the container fields are always null. (Yes, the DB has data of various forms in all container fields). The field type returned is -4, LONGVARBINARY, which is correct.

      What am i doing wrong? I tried a simple SELECT *, SELECT GetAs() and i never get any non null data.

      Is there sample code that shows how one can retrieve Container data in Java/jdbc.

      Inserting blobs does work though :)

      Onno

        • 1. Re: jbdc, fm 11 server advanced, blobs
          IwaoAVE!

          Hi,

          For a jpeg data inserted using the 'Insert > File' command, the following statement works with FMSA 11v2.

          SELECT GetAs(the_container, 'JPEG') FROM blob_test

          Note that if the wrong type is specified in the GetAs function, it returns null.

          There are several different ways to retrieve container data depending on how it's inserted.
          Please read p.40-41 of 'ODBC and JDBC Guide' for the details.

          Regards,
          Iwao

          • 2. Re: jbdc, fm 11 server advanced, blobs
            RemkoKlein

            I've been developping along with Onno and we've tried this also.

            Going in debug mode in Eclipse all fields in the result set are filled, except for the binary fields, those are 'null'.

            We can easily insert a byte array, but retrieving it again doesn't work.

            • 3. Re: jbdc, fm 11 server advanced, blobs
              IwaoAVE!

              Here is the code that I used for the test.
              As I mentioned in the previous post, the second parameter of the GetAs() function must match with the actual field content type.

              Class.forName("com.filemaker.jdbc.Driver");
              String url = "jdbc:filemaker://192.168.0.22:2399/BlobTest";
              Connection con = DriverManager.getConnection(url, "admin", "");
              String sql = "select GetAs(the_blob, 'JPEG') from BlobTest";
              Statement stmt = con.createStatement();
              ResultSet rs = stmt.executeQuery(sql);
              while (rs.next()) {
              byte[] bytes = rs.getBytes("the_blob");
              if (bytes == null)
                System.out.println("null is returned.");
              else
                System.out.println(bytes.length + " bytes returned.");
              }
              con.close();
              

              Hope this helps,
              Iwao

              • 4. Re: jbdc, fm 11 server advanced, blobs
                RemkoKlein

                Hmm, according to the ODBC/JDBC guide you don't have to use GetAs to get the bytes. I've tried it, but of course no problem in trying again, but do you know if you can get the bytes without specification? For us it's just binary data and not a JPG / PDF or whatever.

                I've just tested this with inserting some binary data (a JPEG) using JDBC, this works fine, but when retrieving it with "GetAs(b, 'JPEG')" it returns null again.

                 

                • 5. Re: jbdc, fm 11 server advanced, blobs
                  IwaoAVE!

                  Regarding your question, I don't know how to get bytes without specifying its type.
                  According to the guide, as you pointed out, you should be able to get the binary data without using GetAs() function, but I couldn't make it work. I have tried calling several different method of java.sql.ResultSet, but they all returned null.
                  Your best bet would be to file it as a bug in the 'Report an Issue' section of this forum unless some expert shows us how to achieve it.


                  For future reference, here's the code I used to insert the JPEG file. I've confirmed the inserted JPEG file can be retrieved using the above code.

                  public static void main(String[] args) {
                       try {
                            Class.forName("com.filemaker.jdbc.Driver");
                            String url = "jdbc:filemaker://192.168.0.28:2399/BlobTest";
                            Connection con = DriverManager.getConnection(url, "admin", "");
                            String sql = "insert into BlobTest (the_blob) values (PutAs(?, 'JPEG'))";
                            PreparedStatement stmt = con.prepareStatement(sql);
                            File f = new File("/Users/xxx/Documents/default.jpg");
                            InputStream inputStream = new FileInputStream(f);
                            long fileSize = f.length();
                            stmt.setBinaryStream(1, inputStream, (int)fileSize);
                            int res = stmt.executeUpdate();
                            System.out.println(res + " rows inserted.");
                            stmt.close();
                            con.close();
                       } catch (Exception e) {
                            e.printStackTrace();
                       }
                  }
                  
                  

                  Note that if you don't use the PutAs() function, insertion still works, but it's not recognized as a JPEG file and the previous SELECT program returns null.

                  # Edited for correcting the insert statement.

                  • 6. Re: jbdc, fm 11 server advanced, blobs
                    TSGal

                    All:

                    Our Testing department has confirmed the problem.  The workaround is to use ResultSet.getBinaryStream() instead of ResultSet.getBlob().

                    TSGal
                    FileMaker, Inc.

                    • 7. Re: jbdc, fm 11 server advanced, blobs
                      RemkoKlein

                      We've also tried this, but we still get the 'null' as returned. Could you post us a small example?

                       

                      Thanks!