14 Replies Latest reply on Jun 24, 2016 9:53 AM by TSGal

    JDBC SQL Query with Two (or more) Container Fields Fails with NullPointerException

    fmpdude

      I am trying to create a SQL (JDBC) query that will retrieve multiple container fields from a table using the nice FMP syntax:

       

      GetAs(<FIELD_NAME>, DEFAULT)

       

      One container field in the query, similar to the SQL guide works fine, but two or more will crash with NullPointerExceptions.

       

      Is there a workaround for this?

       

      Don't know if TSGal is aware of any bugs SELECTing multiple container fields, and getting NPEs, but it's easily reproducible in FMPA 14.

       

      I didn't see anything in the documentation that says I can't SELECT multiple continaer fields using GetAs(<FIELD_NAME>, DEFAULT), but maybe I missed it?

       

      TIA

        • 1. Re: JDBC SQL Query with Two (or more) Container Fields Fails with NullPointerException
          TSGal

          fmpdude:

           

          Thank you for your post.

           

          There is a reported JDBC issue when executing a query with a Container field followed by any other field.  However, if the order is reversed ("any field" followed by the Container field), the query works.  The notes have no mention of more than one Container field following "any field".  There is no mention of NullPointerExceptions for this issue.

           

          Regardless, I have sent your post to our Development and Testing departments for review.  When I receive any feedback, I will let you know.

           

          TSGal

          FileMaker, Inc.

          • 2. Re: JDBC SQL Query with Two (or more) Container Fields Fails with NullPointerException
            fmpdude

            Thank you.

             

            I've tested with two- to  multiple container fields.

             

            In my case, and maybe this is where the FMP problem is, assuming there is one, not all container fields are populated in all records -- so some are actually Null. Not all records have PDFs in container fields.

             

            FMPA (14) seems to handle a single container field with some records' values being NULL with no problems, but if I even add a second container field to the SQL won't work at all.

             

            And, then if I turn around my test and use the second container field I added to the first working one by itself, that will work too, until I add the "first" container field again which worked by itself initially, but now won't after being added again.

             

            My issue is that I'm trying to migrate a table/layout with 37 fields 5 of which are container fields -> using a single ResultSet.

             

            If I have to do each container field separately due to some limitation, that would be painful as I'd need to then write 5 update queries and walk through five separate ResultSets and then update the destination table. Eeek!

             

            I'm hoping this is a simple bug or something I could possibly be overlooking.

             

            I have Java code that correctly does a "getBytes()" and then decides whether to... "rs.setBlob()" based on if  there are any bytes in the "getBytes()" variable - that is, if the BLOB field (Container field) is empty/NULL, or not.  But, of course, this code only works if I can get the SELECT to populate the container field(s) initially.

             

            Hopefully, your folks can test this quickly, but I appreciate your quick reply and will await to hear back.

             

            Thanks again!

            • 3. Re: JDBC SQL Query with Two (or more) Container Fields Fails with NullPointerException
              actualjon

              I can reproduce the problem as @fmpdude describes it, and confirm that it exists in the recently released 15.0.6 driver (in the Extras/xDBC folder of the FMPA 15 download).

               

              It definitely seems to be a result of the combination of multiple container fields and NULL values.  I think querying the fields individually (in separate SELECt statements) is likely the only workaround.

               

              Jonathan

              • 4. Re: JDBC SQL Query with Two (or more) Container Fields Fails with NullPointerException
                beverly

                Thanks for chiming in Jonathan! Might there be a way to test for NULL and ignore? (possibly return NULL with a CASE?)

                 

                A query with UNION wouldn't seem to be the way either.

                beverly

                • 5. Re: JDBC SQL Query with Two (or more) Container Fields Fails with NullPointerException
                  fmpdude

                  Awesome. Thanks for the confirmation!!!

                   

                  Hopefully I won't have to query five container fields individually and then create five UPDATE PreparedStatement queries plus five createStatment()s plus five ResultSet processing loops. With over 20 layouts and maybe 50 container fields total, querying each container field separately is not a realistic workaround.

                   

                  Plus, with FMP, aside from the SQL bug with multiple container fields, there also appears to be a bug with ResultSet operations "setBinaryStream()"/"getBinaryStream()". At least for me. These ResultSet methods don't populate the blob field (neither do getBlob() and setBlob()).

                   

                  I have to process container fields like this:

                   

                  // process container field ResultSet

                  while (rs.next())

                  {

                     imageOfItemBlobExists = true;

                   

                     byte[] imageBytes = rs.getBytes("Image_of_item");

                     if (imageBytes != null)

                    {

                     inStream = new ByteArrayInputStream(imageBytes);

                    }

                     else
                     {

                     imageOfItemBlobExists = false;

                    }

                     if (imageOfItemBlobExists)

                    {

                     updateImageOfItemFields.setBlob(1, inStream, inStream.available());

                    }

                     else
                     {

                     updateImageOfItemFields.setBlob(1, (Blob) null);

                    }

                  .

                  .

                  .

                  A pain.

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

                   

                  Will await word from TSGal and hopefully the FM dev team can fix this problem quickly.

                   

                  Thanks again for your help and for your reply!

                  • 6. Re: JDBC SQL Query with Two (or more) Container Fields Fails with NullPointerException
                    TSGal

                    fmpdude:

                     

                    I have added your comments to the original report.

                     

                    Development has requested a sample file and sample code to test.  I have sent you a private message with instructions where to send the file.

                     

                    TSGal

                    FileMaker, Inc.

                    • 7. Re: JDBC SQL Query with Two (or more) Container Fields Fails with NullPointerException
                      fmpdude

                      To Test...

                       

                      I created a simple database with two container fields, a numeric and a text field.

                       

                      Some container fields had all nulls in a record and other records had one or the other container field filled in with a PDF.

                       

                      I wasn’t able to reproduce the issue either in this simple dataset.

                       

                      ---

                       

                      In my actual data...

                       

                      Here is what I get with two container fields in the SELECT:

                       

                       

                      If I try to do the query again, I get an error I've never seen:

                       

                      ----

                       

                      Note: I was able to "workaround" (not a good solution) by creating multiple Statement, ResultSet objects. Each separate UPDATE statement had just the PK and a single container field. Using that technique, I was able to loop through all the data and update all the fields -- so the data itself is OK.

                       

                      Note: Each SELECT statement using only one of the GetAs container fields with the PRO_PK also works fine (again, separately). But both together as shown above don't work at all for me.

                       

                      ----

                       

                      Perhaps @actualjon can help describe how to reproduce the error since he confirmed it as well?

                       

                      ----

                       

                      Hope this gives your devs enough to go on.

                       

                      Short of sending you a proprietary solution, that's probably the best I can do.

                       

                      Please advise.

                       

                      Thanks

                      • 9. Re: JDBC SQL Query with Two (or more) Container Fields Fails with NullPointerException
                        fmpdude

                        TSGal,

                         

                        Why don't you have your dev staff add some basic logging to the FMP JDBC driver. Maybe the driver could look for a simple text file named "debug.txt" (or whatever) to decide whether to log.

                         

                        I'll be glad to help send back diagnostic NullPointerException (NPE) logging information from that updated driver per the screenshot above from my unwanted USPS mail "prohibitory orders" layout.

                         

                        Let me know how i can help, OK?

                        • 10. Re: JDBC SQL Query with Two (or more) Container Fields Fails with NullPointerException
                          fmpdude

                          Would you please post a simple example of how you were able to reproduce the problem I reported? I tried to create a simple example using non-private data, but wasn't able to duplicate the problem in that 5 record sample file.

                           

                          I would like to try to help the FM dev folks as much as possible.

                           

                          Thanks,

                          • 11. Re: JDBC SQL Query with Two (or more) Container Fields Fails with NullPointerException
                            actualjon

                            I will send them my test case.

                             

                            Thanks,

                             

                            Jonathan

                            • 12. Re: JDBC SQL Query with Two (or more) Container Fields Fails with NullPointerException
                              fmpdude

                              Thanks a bunch.

                               

                              If it's not too much trouble, post it here so I can see it too.

                               

                              Thanks,

                              • 13. Re: JDBC SQL Query with Two (or more) Container Fields Fails with NullPointerException
                                fmpdude

                                @TSGal,

                                Any updates on the bugs with container fields, for example where setBinaryStream doesn't work and I thus have to do JDBC gymnastics per my previously posted Java code to get container fields to save in a remote database?

                                 

                                I'm not sure the other poster (actualjon ) sent you his example showing the problem I reported since I never got a copy of that solution.

                                 

                                It's my hope that you'll be able to fix the actual bug(s) reported in the initial posting, soon (it's now been about a month), too (not "Fixed in 15").

                                 

                                Hope to hear back some news soon.

                                 

                                Thank you.

                                • 14. Re: JDBC SQL Query with Two (or more) Container Fields Fails with NullPointerException
                                  TSGal

                                  fmpdude:

                                   

                                  There is no new information at this time.

                                   

                                  TSGal

                                  FileMaker, Inc.