AH - you just hit it. Sorry I didn't notice it sooner... "LAST" is a reserved work in ODBC. See here:
http://sqlserver2000.databases.aspfaq.com/what-are-reserved-access-odbc-and-sql-server-keywords.html therefore you need to escape any such words if they are column names. Witango/TeraScript doesn't do this for you (although I agree that it should). It's also notable that the escape sequence is different for each DBMS, which is a complication to automatic escaping. So yes, in your case, for any column names which are reserved words, you must use a custom column to add <quote><colname><quote> or "Last" in this case. This will go for both the select and criteria areas. This is one of the more major issues when dealing with FileMaker in that FM does not enforce column names that avoid syntax issues. Most other DBMS do, which makes this a non-issue on other platforms. Robert -----Original Message----- From: Lipford, Terencia (NIH/CIT) [C] [mailto:lipfo...@mail.nih.gov] Sent: Wednesday, August 17, 2011 2:33 PM To: Witango-Talk@witango.com Subject: Re: Witango-Talk: Filemaker Pro Syntax error Robert here's what my manager discovered, if you request the data first then which column it located in, it works. SELECT * FROM PersonnelFile where 'Gadina' = "Last" We have to double quote the column name or it fails. Is this known in Filemaker? On 8/17/11 1:31 PM, "Robert Shubert" <rshub...@tronics.com> wrote: Terry, Continue to evolve the Custom SQL Query until it matches the one that is giving the syntax error. You should bump into the problem. Also, if you can view the SQL statements in debug if you aren't already. Post any differences. Look for specific things like spaces, quotes and case. Robert -----Original Message----- From: Lipford, Terencia (NIH/CIT) [C] [mailto:lipfo...@mail.nih.gov] Sent: Wednesday, August 17, 2011 1:29 PM To: Witango-Talk@witango.com Subject: Re: Witango-Talk: Filemaker Pro Syntax error OK this is the screwiest thing I've every witness. It worked! I can't understand why this worked and selecting the columns from the very table does not! On 8/17/11 1:05 PM, "Robert Shubert" <rshub...@tronics.com> wrote: Terry, Can you build a TAF with a Custom SQL Query and do some simple tests, like Select 1 Or Select count(*) from PersonnelFile Just try "stepping into" your statement to determine where the syntax error is. One of the biggest points of grief with FM is that their ODBC and JDBC errors are not well defined. Also, could you verify the Witango/TeraScript server version and platform for me. Thanks, Robert -----Original Message----- From: Lipford, Terencia (NIH/CIT) [C] [mailto:lipfo...@mail.nih.gov] Sent: Wednesday, August 17, 2011 12:26 PM To: Witango-Talk@witango.com Subject: Re: Witango-Talk: Filemaker Pro Syntax error Hi Robert, Exactly my point, I am getting a response from the server. We've double checked the drivers and they are the same on both sever and client. Directly from the FM updater_11.0.3.312. I'm querying the correct columns from the PersonnelFile table, which are displayed without a hitch in both Witango and Terascibe, and I've double checked my SQL statement and it is accurate. I have no clue what this error means. On 8/17/11 12:12 PM, "Robert Shubert" <rshub...@tronics.com> wrote: Terry, Are you using FileMaker 11.3? Are the ODBC/JDBC drivers at the same version as the server? (this is critical) Is the SQL statement incorrect? (column and table named correct, etc) The good news is that you are getting a response from the server (or at least the ODBC subsystem). Robert -----Original Message----- From: Lipford, Terencia (NIH/CIT) [C] [mailto:lipfo...@mail.nih.gov] Sent: Wednesday, August 17, 2011 11:55 AM To: Witango-Talk@witango.com Subject: Witango-Talk: Filemaker Pro Syntax error My first time attempting to run queries against Filemaker Pro via Witango 5.5 and Terascibe 6. I have successfully connected from Witango 5.5 and Terascribe to the Filemaker Pro DB using ODBC and JDBC, however when I run my DBMS query: SELECT Last,First FROM PersonnelFile WHERE Last='xxxxx' I receive this error message: [Error][201] 8310 [FileMaker][FileMaker ODBC] FQL0001/(1:8): There is an error in the syntax of the query. 42000 Both Filemaker Network sharing and ODBC/JDBC sharing is ON What am I missing? -- Thank you, Terry Lipford NIH Intramural Database (NIDB) 301-402-8307 lipfo...@mail.nih.gov ---------------------------------------- To unsubscribe from this list, please send an email to lists...@witango.com with "unsubscribe witango-talk" in the body. ---------------------------------------- To unsubscribe from this list, please send an email to lists...@witango.com with "unsubscribe witango-talk" in the body. ---------------------------------------- To unsubscribe from this list, please send an email to lists...@witango.com with "unsubscribe witango-talk" in the body. ---------------------------------------- To unsubscribe from this list, please send an email to lists...@witango.com with "unsubscribe witango-talk" in the body. ---------------------------------------- To unsubscribe from this list, please send an email to lists...@witango.com with "unsubscribe witango-talk" in the body. ---------------------------------------- To unsubscribe from this list, please send an email to lists...@witango.com with "unsubscribe witango-talk" in the body. ---------------------------------------- To unsubscribe from this list, please send an email to lists...@witango.com with "unsubscribe witango-talk" in the body. ---------------------------------------- To unsubscribe from this list, please send an email to lists...@witango.com with "unsubscribe witango-talk" in the body.