Alway read (and re-read) the FMI xDBC guide. The reserved words are in there, too.
<http://www.filemaker.com/support/product/docs/fmp/fm11_odbc_jdbc_guide_en.pdf> I work in SQL and FM databases every day. I need to re-read these docs frequently. :) Also, I don't use any of the query builders. Just learn SQL and be done with it! Beverly On 17 Aug 2011, at 2:59 PM, Lipford, Terencia (NIH/CIT) [C] wrote: > Eureaka! Thanks Robert, I'm off and running now! I'll switch over the using > JDBC and go from there. At least one thing was accomplished today. Thanks > again! > > I'll let you know if something else odd occurs. > > Terry > > > On 8/17/11 2:54 PM, "Robert Shubert" <rshub...@tronics.com> wrote: > > 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. > > > > > ---------------------------------------- > > 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.
