Jump to content

ExecuteSQL Strange Bug


This topic is 3012 days old. Please don't post here. Open a new topic instead.

Recommended Posts

I am getting a strange result, I am attempting to use the ExecuteSQL command to avoid using multiple Table Occurrences.

However I am stumped when the field I need does not work on ExecuteSQL for an odd reason.

ItemNo and Level are both Indexed Number Fields with the EXACT SAME SETTINGS, however when performing these commands, ItemNo gives me a result, when Level does not. They both have data inside, etc. Any thoughts or ideas why I am receiving a "?" on the second 

ExecuteSQL("SELECT c.ItemNo FROM Test_C_any c WHERE c.TestID = 'AA'";"";"";"")

ExecuteSQL("SELECT c.Level FROM Test_C_any c WHERE c.TestID = 'AA'";"";"";"")

 

 

Link to comment
Share on other sites

I'm not an ExecuteSQL expert but I see a couple of things. You are not using the parameterized form of the query; and you are supplying to many terms at the end of the query. Parameterized form:

ExecuteSQL("SELECT c.Level FROM Test_C_any c WHERE c.TestID = ?";"";"";"AA")

Link to comment
Share on other sites

17 minutes ago, BruceR said:

I'm not an ExecuteSQL expert but I see a couple of things. You are not using the parameterized form of the query; and you are supplying to many terms at the end of the query. Parameterized form:

ExecuteSQL("SELECT c.Level FROM Test_C_any c WHERE c.TestID = ?";"";"";"AA")

I am not the best at this ExecuteSQL syntax either, but it Works for one field but not for another...

also that expression does not work for either field, so something is wrong with that method.

It seems to do with the FieldName being "Level" when I change the field name from Level to Lvl it works. maybe I am breaking a rule in the syntax using a field name Level... seems a bit silly though. I would assume many developers have a field called Level.

Link to comment
Share on other sites

7 hours ago, cgroody said:

maybe I am breaking a rule in the syntax using a field name Level... seems a bit silly though. I would assume many developers have a field called Level.

Nothing silly about it... it is a matter of being aware of what the standards are and as Comment indicated what reserved keywords come with that standard.  FM's documentation is pretty clear.

Another good example for instance is "grant".  Which is a SQL reserved keyword that will bite you if if you have a client that works with grants.

Just always quote your table and field names.  That gets around all these issues without requiring you to change your field or table names.

There are a number of custom functions floating around that will help you with that.  And will also allow you to pass a real field reference to the SQL query that has the added benefit of keeping your query safe for TO or field name changes.

 

 

Link to comment
Share on other sites

This topic is 3012 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.