4 Replies Latest reply on Jun 15, 2010 8:37 AM by TSGal

    Syntax Issues Inserting Multiple Date Values in a Single SQL Statement Over The JDBC Driver in FM 11

    urinformatics

      Summary

      Syntax Issues Inserting Multiple Date Values in a Single SQL Statement Over The JDBC Driver in FM 11

      Description of the issue

      FileMaker Product(s) involved:FileMaker Pro 11.0v1 Operating System(s) involved:Windows Server 2003 Service Pack 2 Detailed description of the issue:We are using the fmjdbc.jar JDBC Driver (Class com.filemaker.jdbc.Driver) that came with our FileMaker Pro 11 installation to connect our Cold Fusion web application to our FileMaker database.  We have an issue when we attempt insert SQL statements with more than one date field in the insert statement.   e.g. INSERT INTO "Contact" (  "rec.dob" , "rec.datecon" ) VALUES (  {05/17/2002}, {06/09/2010} ) ...results in the error...   ERROR: [FileMaker][FileMaker JDBC] FQL0001/(2:50): There is an error in the syntax of the query.  If we reduce our insert statements to one date field, then we have no issues.   e.g. INSERT INTO "Contact" (  "rec.datecon" )  VALUES (   {06/09/2010} )   We have been able to reproduce this issue outside of our web application and web server using RazorSQL (http://www.razorsql.com/download_win.html) and the exact same connection information and driver to our database.  For now we are going to "work around" the issue by doing an insert with only one date field.  And then performing updates for each additional date field.  

        • 1. Re: Syntax Issues Inserting Multiple Date Values in a Single SQL Statement Over The JDBC Driver in FM 11
          urinformatics

          It appears doing subsequent UPDATE statements to update the additional DATE variables is causing a different strange error:

           

          [FileMaker][FileMaker JDBC]

           

          ERROR: [FileMaker][FileMaker JDBC]

           

          NOTE: That is it.  Just "[FileMaker][FileMaker JDBC]" with no message.  No details.  If I copy the SQL to another tool (RazorSQL) the SQL works fine and successfully performs the update.  Here is the SQL causing the error over the JDBC driver within the Cold Fusion application:

           

          UPDATE "Contact" set "rec.child01.dob" = {01/01/2000} WHERE 'THIS' || year("rec.datecon") || month("rec.datecon") || day("rec.datecon") = 'THIS' || year(DATE()) || month(DATE()) || day(DATE()) and "rec.namelast" = 'TEST' and "rec.namefirst" = 'TEST'

           

          ALSO I tried to find a page to download the latest version of the JDBC driver, but there does not appear to be a web page for it.  Does anyone know of one?

          • 2. Re: Syntax Issues Inserting Multiple Date Values in a Single SQL Statement Over The JDBC Driver in FM 11
            TSGal

            urinformatics:

             

            Thank you for your posts.

             

            In your first issue, you may have a syntax error.  Your post has a space before and after the comma for the fields, but no space before and a space after the comma in the VALUES section.

             

            Your second error with [FileMaker][FileMaker JDBC] usually occurs when there is no primary key.  As a workaround, set one of the fields for Validation, Check Always, Not Empty and Unique.  Please keep me updated with any progress.

             

            TSGal

            FileMaker, Inc.

            • 3. Re: Syntax Issues Inserting Multiple Date Values in a Single SQL Statement Over The JDBC Driver in FM 11
              urinformatics

              TSGal,

               

              Thank you for your response.  I tried modifying the whitespace in the query, but it had no effect.  HOWEVER I decided to try submitting a different date format and it appears to have resolved the issue.  

               

              This query DOES NOT WORK:

              INSERT INTO "TSFB_Contact" ( "rec.child01.dob" , "rec.datecon" ) VALUES ( {06/01/2010} , {06/01/2010} )

               

              BUT this query DOES WORK with the modified date format:
              INSERT INTO "TSFB_Contact" ( "rec.child01.dob" , "rec.datecon" ) VALUES ( {D '2010-06-01'} , {D '2010-06-01'} )

               

              So my conclusion is there may be issues with the date formatting part of the JDBC drivers.

               

              ALSO as for the other blank error you believe may be caused by the lack of primary key, I did not investigate because the issue appears resolved and it would be inconvenient to add a primary key to the table at this time.  

               

              Again... Thank you for your assistance!

              • 4. Re: Syntax Issues Inserting Multiple Date Values in a Single SQL Statement Over The JDBC Driver in FM 11
                TSGal

                uriinformatics:

                 

                Thanks for the information, and I'm glad you were able to resolve it.  This information should help others.

                 

                TSGal

                FileMaker, Inc.