1 Reply Latest reply on Jun 3, 2014 1:38 PM by TSGal

    SQL syntax error using "INSERT INTO" in FM Pro 13

    AdamRobichaud

      Summary

      SQL syntax error using "INSERT INTO" in FM Pro 13

      Product

      FileMaker Pro

      Version

      13

      Operating system version

      Windows 7 64-bit

      Description of the issue

      I am developing an application attempting to access several FileMaker Pro 13 databases via ODBC. When I attempt to add rows to an existing table, I get the following error:

      INSERT INTO DemoDownloads (Date,EMail,Name,Expiry_Date,Serial,Product)
      VALUES ('2014-06-03 11:22:01','my@email.com','my name','2014/07/03','XXXXX-XXXXX-XXXXX-XXXXX-XXXXX-XX','Product level')
      System.Data.Odbc.OdbcException: ERROR [42000] [FileMaker][FileMaker] FQL0001/(1:28): There is an error in the syntax of the query.
         at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode)
         at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader,
      Object[] methodArguments, SQL_API odbcApiMethod)
         at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader)
         at System.Data.Odbc.OdbcCommand.ExecuteNonQuery()
         at OrderProcessingSystem.Database.AddRecord(String[] Records, Object[] Values) in C:\Proj\OrderProcessingSystem\Database.cs:line 81


      Obviously, DemoDownloads is the table name, and ODBC connections are enabled with the proper user settings. I have other queries (such as SELECT and UPDATE) working fine. Unless I'm missing something simple, I don't think my syntax is wrong.

      I've since also discovered that the ExecuteSQL command from the FileMaker Pro 13 (and 12) scripting interface return the same error, which eliminates the need for a command

      I'm running FileMaker Pro 13.0v3 on Window 7 (x64), and have the FileMaker ODBC Driver (32-bit) v12.3.103 installed (required, because our app is in 32-bit mode).

      Steps to reproduce the problem

      1. Create a database named "DemoDownloads"
      2. Make an ODBC DSN for DemoDownloads which connects to the above database.
      3. Turn on ODBC sharing for above database (and give appropriate permissions for read/write).
      4. Add a column "Date" to DemoDownloads.
      5. Create a script named "Insert Data" from the manage script menu.
      5a) Add the command "ExecuteSQL"
      5b) Add the Query "INSERT INTO 'DemoDownloads' (Date) VALUES ('2014-06-03 12:00:00')"
      6. Save the script.
      7. Execute the script.

      Expected result

      I would expect the above query to insert a row into DemoDownloads with the proper information.

      Actual result

      Error message claiming SQL syntax is incorrect. According to reference SQL documents provided by FileMaker, the syntax appears correct.

      Exact text of any error message(s) that appear

      FQL0001/(1:28): There is an error in the syntax of the query.

      Configuration information

      I am using the current version of the Filemaker ODBC driver in 32-bit mode, and all the DSNs are configured to the point where I can connect to them (and even query them, in limited cases) from my external ODBC application.

      Workaround

      None.

        • 1. Re: SQL syntax error using "INSERT INTO" in FM Pro 13
          TSGal

               Adam Robichaud:

               Thank you for your post.

               "DATE" is a reserved word used to specify a date value.  Therefore, if you change your field name from "Date" to "Start_Date" (or any other name), the query will then work correctly.  That is,

               INSERT INTO DemoDownloads (Start_Date,EMail,Name,Expiry_Date,Serial,Product) VALUES ('2014-06-03 11:22:01','my@email.com','my name','2014/07/03','XXXXX-XXXXX-XXXXX-XXXXX-XXXXX-XX','Product level')

               For more information, see the Reserved Words section on page 34 of the SQL Reference Guide, along with the example on page 18 that uses the DATE reserved word):

          https://fmhelp.filemaker.com/docs/13/en/fm13_sql_reference.pdf

               TSGal
               FileMaker, Inc.