SQL syntax error using "INSERT INTO" in FM Pro 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','email@example.com','my name','2014/07/03','XXXXX-XXXXX-XXXXX-XXXXX-XXXXX-XX','Product level')
System.Data.Odbc.OdbcException: ERROR  [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 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.
I would expect the above query to insert a row into DemoDownloads with the proper information.
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.
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.