[Solutions] SQL Builder

[Eden Morris]

File Name: SQL Builder

File Submitter: Eden Morris

File Submitted: 29 Apr 2012

File Category: Solutions

FM Version: 12

This SQL code generator will generate ExecuteSQL() code for FileMaker allowing you to use unsafe field names and make changes to database schema after making the code. If you have feedback of any kind such as bugs or feature requests please visit the support topic for this file.

UPDATE 5/23/2012

• Code window opens at start up
  
• Disabled field name check at start up.
  
• No bug fixes ( please send bug reports in the support topic )
  

UPDATE 5/23/2012

• Added Settings page
  
• Added Loading page
  
• Fixed bug in code window
  

UPDATE 5/16/2012

• Misc bug fixes and UI tweaks
  

UPDATE 5/15/2012

• Table and Field ID's are now tracked allowing for auto repairing of table and field names after schema changes
  
• Misc bug fixes and UI tweaks
  

UPDATE 5/13/2012

• All new UI
  
• Support for more functions
  

UPDATE 5/9/2012 (v1.0?!)

• Support for FROM and Right JOIN Table Aliases
  
• Misc. bug fixes
  
• Tweaked the UI
  

UPDATE 5/5/2012

• You can now group by a field without displaying it in the results
  
• Added JOIN Example
  

UPDATE 5/4/2012

• Added a Statement Creation Wizard ( v07 )
  
• Improved Dynamic Parameters Support. ( v0601 )
  
• Support for Dynamic Parameters.
  

UPDATE 5/2/2012 ( Back From Vacation! )

• Starting Value navigation buttons.
  
• Output code display options can be set at the bottom of the details layout.
  

UPDATE 4/29/2012

• Added warning icons that show up if a field no longer exists so that schema changes can be easily tracked down.
  
• Added starting value and maximum results in the results window.
  

UPDATE 4/25/2012

• Fixed "AND OR" Bug
  
• Insert functions into select field lines
  
• Adding a function to a WHERE line will change it into a HAVING line
  
• Manual entry will populate when entered on SELECT and WHERE/HAVING lines
  

UPDATE 4/24/2012

• Fixed bug in ORDER BY
  
• Table Occurrences are now all that is needed to populate the field list.
  

UPDATE 4/23/2012

• Added "Get at Date or Time" Check box for SELECT lines and an example of how it works.
  
• Statements can be assigned categories
  
• Simplified the code when there is no JOIN statement
  
• Code now has formatting to make reading the code easier
  
• Dropped the use of Quote() and now use escaped quotes
  
• You can attach a sub-query when using "WHERE IN" by clicking on the icon to the right of the value field
  

UPDATE 4/22/2012

• More Unsafe Names
  
• UNION drop down menu and examples showing how to use it.
  
• Documentation
  

Thanks for the feedback so far...

Click here to download this file

[Ocean West]

Here are some more unsafe names:

ABSOLUTE

COMMIT

DOUBLE

ACTION

CONNECT

DROP

ADD

CONNECTION

ELSE

ALL

CONSTRAINT

END

ALLOCATE

CONSTRAINTS

END_EXEC

ALTER

CONTINUE

ESCAPE

AND

CONVERT

EVERY

ANY

CORRESPONDING

EXCEPT

ARE

COUNT

EXCEPTION

AS

CREATE

EXEC

ASC

CROSS

EXECUTE

ASSERTION

CURDATE

EXISTS

AT

CURRENT

EXTERNAL

AUTHORIZATION

CURRENT_DATE

EXTRACT

AVG

CURRENT_TIME

FALSE

BEGIN

CURRENT_TIMESTAMP

FETCH

BETWEEN

CURRENT_USER

FIRST

BINARY

CURSOR

FLOAT

BIT

CURTIME

FOR

BIT_LENGTH

CURTIMESTAMP

FOREIGN

BLOB

DATE

FOUND

BOOLEAN

DATEVAL

FROM

BOTH

DAY

FULL

BY

DAYNAME

GET

CASCADE

DAYOFWEEK

GLOBAL

CASCADED

DEALLOCATE

GO

CASE

DEC

GOTO

CAST

DECIMAL

GRANT

CATALOG

DECLARE

GROUP

CHAR

DEFAULT

HAVING

CHARACTER

DEFERRABLE

HOUR

CHARACTER_LENGTH

DEFERRED

IDENTITY

CHAR_LENGTH

DELETE

IMMEDIATE

CHECK

DESC

IN

CHR

DESCRIBE

INDEX

CLOSE

DESCRIPTOR

INDICATOR

COALESCE

DIAGNOSTICS

INITIALLY

COLLATE

DISCONNECT

INNER

COLLATION

DISTINCT

INPUT

COLUMN

DOMAIN

INSENSITIVE

NSERT

OF

INT

ON

INTEGER

ONLY

INTERSECT

OPEN

INTERVAL

OPTION

INTO

OR

IS

ORDER

ISOLATION

OUTER

JOIN

OUTPUT

KEY

OVERLAPS

LANGUAGE

PAD

LAST

PART

LEADING

PARTIAL

LEFT

POSITION

LENGTH

PRECISION

LEVEL

PREPARE

LIKE

PRESERVE

LOCAL

PRIMARY

LONGVARBINARY

PRIOR

LOWER

PRIVILEGES

LTRIM

PROCEDURE

MATCH

PUBLIC

MAX

READ

MIN

REAL

MINUTE

REFERENCES

MODULE

RELATIVE

MONTH

RESTRICT

MONTHNAME

REVOKE

NAMES

RIGHT

NATIONAL

ROLLBACK

NATURAL

ROUND

NCHAR

ROWID

NEXT

ROWS

NO

RTRIM

NOT

SCHEMA

NULL

SCROLL

NULLIF

SECOND

NUMERIC

SECTION

NUMVAL

SELECT

OCTET_LENGTH

SESSION

SESSION_USER

USAGE

SET

USER

SIZE

USERNAME

SMALLINT

USING

SOME

VALUE

SPACE

VALUES

SQL

VARBINARY

SQLCODE

VARCHAR

SQLERROR

VARYING

SQLSTATE

VIEW

STRVAL

WHEN

SUBSTRING

WHENEVER

SUM

WHERE

SYSTEM_USER

WITH

TABLE

WORK

TEMPORARY

WRITE

THEN

YEAR

TIME

ZONE

TIMESTAMP

TIMESTAMPVAL

TIMEVAL

TIMEZONE_HOUR

TIMEZONE_MINUTE

TO

TODAY

TRAILING

TRANSACTION

TRANSLATE

TRANSLATION

TRIM

TRUE

UNION

UNIQUE

UNKNOWN

UPDATE

UPPER

USAGE

USER

USERNAME

USING

[Ocean West]

I had to employ a UNION the yesterday - that could be handy to create a unique list of keys from two different selects.

This is a great file you should upload it here http://fmforums.com/forum/files/ you can then manage versions when you re-release it.

thanks

[Eden Morris]

The file has been updated. Please see above.

Also is there any documentation for FileMaker specific quirks in the ExecuteSQL() command so I don't have to figure them out? :)

[Ocean West]

I found out today that you can use escaped quotes with " instead breaking apart the sql string to insert FMP Quote()

ExecuteSQL ( "SELECT "Random Names"."Last Name", "Random Names"."First Name"

FROM "Random Names"

WHERE "Random Names"."Year" = 2001 AND "Random Names"."First Name" LIKE '%ce'";"";"")

vs

ExecuteSQL ("SELECT " & Quote("Random Names") & "." & Quote("Last Name") & " , " & Quote("Random Names") & "." & Quote("First Name") & "

FROM " & Quote("Random Names") & "

WHERE " & Quote("Random Names") & "." & Quote("Year") & " = 2001 AND" & Quote("Random Names") & "." & Quote("First Name") & " LIKE '%ce'";"";"")

[Eden Morris]

Arrrg! I could have swore I tried that in the beginning. But I just tried it now and it worked

oh well, I have updated the database... much easier to read the code now.

[mr_vodka]

I also find that it doesnt handle subqueries well.

[Eden Morris]

Can you give an example of what you mean?

I also find that it doesnt handle subqueries well.

[Eden Morris]

You can now attach a subquery when using "WHERE IN" by clicking on the icon to the right of the value field

[CNS]

I downloaded your example and after reading your Read Me file about how to add tables with the same name and layouts with the same name, etc, I thought you may be using LayoutNames and FieldNames. So, I looked into your scripts and found your "Populate Field List Table" script which indeed proved you were using LayoutNames and FieldNames. So, I'm going to let you know how to make this *so* much easier. All you'll need to do is add the table occurrences, and they could even be named whatever you want.

Since this whole database is about using this new ExecuteSQL function, then you should use SQL to get the Table Names and Field Names, no? FMSQL has two schema tables named "FileMaker_Tables" and "FileMaker_Fields". The columns for each are:

FileMaker_Tables:

1. "TableName" - the name of the table occurrence

2. "TableID" - the unique id of the table occurrence

3. "BaseTableName" - the name of the base table this table occurrence comes from

4. "BaseFileName" - the name of the file (no extension) containing this table

5. "ModCount" - the number of times this table's schema has been modified

FileMaker_Fields:

1. "TableName" - table occurrence name containing this field

2. "FieldName" - the field name

3. "FieldType" - the field type (sql type names: varchar, numeric, date, time, timestamp, longvarbinary; if global storage, then 'global' will be added)

4. "FieldID" - the field id

5. "FieldClass" - the FileMaker field class ('Normal', 'Summary', or 'Calculated')

6. "FieldReps" - the number of reps defined for the field

7. "ModCount" - the number of times this field's definition has been modified

Using those two tables should make it much easier to populate your field list.

HTH,

Jake

[Eden Morris]

Using those two tables should make it much easier to populate your field list.

THANK YOU.... I love learning new tricks. This is now part of the new version and is updated in the Read Me File.

[mr_vodka]

Is there anyway to keep the attachment to this thread? It would really help to have it attached to this thread especially considering that some members may have dropbox blocked by corporate firewalls.

[Eden Morris]

Is there anyway to keep the attachment to this thread? It would really help to have it attached to this thread especially considering that some members may have dropbox blocked by corporate firewalls.

There you go, I will try to keep that attachment up to date. the newest version will always be the URL though.

[john renfrew]

If you sort by more than one field it fails to add the necessary comma between the fields in the prepared statement

And if the GROUP BY selector is ON it will fail if you then add a ORDER BY, so the prepared statement should ignore any values there

Surely the better way in your Mc example is to make the statement SELECT DISTINCT and ignore the GROUP BY, although some SQL expert type needs to let us know which one evaluates quicker and is more error safe.

What about the ability to add a table alias

FROM "Random Names" AS "A"

WHERE 2012 - A."Year" BETWEEN 3 AND 7

[john renfrew]

Works for columns( fields) too so the example can become:

SELECT "First Name" AS "B", 2012 - "YEAR", "YEAR"

FROM "Random Names" AS "A"

WHERE 2012 - A."Year" BETWEEN 3 AND 7

ORDER BY B

Reduces the "" clutter and makes JOIN statements easier to write and read

Idea 2

A function that parses all field names in the selected file and checks against an unsafe list

One of the problems with this example is using the field name 'year' which means it NEEDS quoting, but more discipline will be necessary if we all start using SQL with the fire I imagine we will

[Ocean West]

i have set up the file in our download area:

http://fmforums.com/forum/files/file/25-sql-builder/

and this is the 'support' topic for it

by using the download section you can subscribe or follow versions of this solution file ...

Cheers Stephen

Edited May 14, 2012 by Fitch
fixed link

[Eden Morris]

So maybe I'm not getting this right....

This will work:

SELECT "Year"

FROM "Random Names"

WHERE "Year" = 2000

This will not though and it looks like it should:

SELECT "Year" AS Y

FROM "Random Names" AS R

WHERE R.Y = 2000

What am I doing wrong?

Works for columns( fields) too so the example can become:

SELECT "First Name" AS "B", 2012 - "YEAR", "YEAR"

FROM "Random Names" AS "A"

WHERE 2012 - A."Year" BETWEEN 3 AND 7

ORDER BY B

Reduces the "" clutter and makes JOIN statements easier to write and read

Idea 2

A function that parses all field names in the selected file and checks against an unsafe list

One of the problems with this example is using the field name 'year' which means it NEEDS quoting, but more discipline will be necessary if we all start using SQL with the fire I imagine we will

[john renfrew]

Quote from T'Internet

>>

In short, column aliases exist to help organizing output. In the previous example, whenever we see total sales, it is listed as SUM(sales). While this is comprehensible, we can envision cases where the column heading can be complicated (especially if it involves several arithmetic operations). Using a column alias would greatly make the output much more readable.

The second type of alias is the table alias. This is accomplished by putting an alias directly after the table name in the FROM clause. This is convenient when you want to obtain information from two separate tables (the technical term is 'perform joins'). The advantage of using a table alias when doing joins is readily apparent when we talk about joins.

>>

So the column alias can not be used later in a maths thing like you have.

Think of it as Call Column X >> "ThisName" then later we can do a sort by Column (ThisName) - like Excel

The table alias is a true Alias so once declared needs to be used so

FROM "Random Names" AS "A"

WHERE 2012 - "Random Names"."Year" BETWEEN will fail too, as it now needs to be referred to as such

IT is however brilliant of simplifying JOINS

[Ocean West]

although we know FM's flavor of SQL doesn't include a "LIMIT" feature - many times we wrap it in a pseudo limit by using GetValue ( results ; 1 ) or MiddleValue ( result ; 1 ; 3 )

Perhaps in the SQL Statement Details this faux limit section could be include in the code generation?

[Eden Morris]

although we know FM's flavor of SQL doesn't include a "LIMIT" feature - many times we wrap it in a pseudo limit by using GetValue ( results ; 1 ) or MiddleValue ( result ; 1 ; 3 )

Perhaps in the SQL Statement Details this faux limit section could be include in the code generation?

I have added the ability to limit the results window by starting value and maximum results

[Ocean West]

note - a technical glitch cause the file in the download to be deleted - i restored it to v04,

I had to recreate the original download and merge the thread again..

Sorry for the inconvenience.

[Eden Morris]

Come on people, I know there have to be bugs or at least features you want. :)

[john renfrew]

Eden

Played around a bit with aliases in the latest version and got this to work perfectly

The trick is to refer to the alias everywhere once it is created, and that can be on lines BEFORE it is created.

SELECT "Last Name" , COUNT(B.Score) , MAX(B.Score)

FROM "Random Names" AS "A"

INNER JOIN "Random Scores"  AS "B" ON A."_id" = B."_id_name"

GROUP BY A."Last Name" , A."First Name"

HAVING MAX(B.Score) < 45 AND COUNT(B.Score) = 2

ORDER BY A."First Name"

[Ocean West]

By way of suggestion - on the list view (Home) It would feel more natural that the fields not allow data entry - and clicking on any row would switch to the detail - creating a new record would also switch layouts to the details.

on a couple of layouts Edit Categories button wraps ( on Mac)

Keep up the fine work!

[Eden Morris]

I added that feature, what do you mean about it wraps?

By way of suggestion - on the list view (Home) It would feel more natural that the fields not allow data entry - and clicking on any row would switch to the detail - creating a new record would also switch layouts to the details.

on a couple of layouts Edit Categories button wraps ( on Mac)

Keep up the fine work!

[beverly]

There is not always a JOIN, so that should be eliminated from the calculation using * not isempty()...

The same with GROUP BY (not all queries use it).

LOOKING GOOD, Eden.

Beverly

[Eden Morris]

Im not sure I'm following you. What calculation are you talking about?

There is not always a JOIN, so that should be eliminated from the calculation using * not isempty()...

The same with GROUP BY (not all queries use it).

LOOKING GOOD, Eden.

Beverly

[Ocean West]

This is what i mean.

[Eden Morris]

This is what i mean.

oh! ok fixed for the next version. TY :)

[Daniel Wood]

This file is frickin awesome, love it!

[Tom R.]

This looks like a great tool. It would be nice if there were a brief documentation for using it, or maybe a brief screencast showing how to use it. I'm one of those folks who learns best by example.

Thanks!

Tom

[beverly]

Im not sure I'm following you. What calculation are you talking about?

You seem to have updated the solution, so that my comment is no longer valid.

Thanks!

Beverly

[GingerNut]

This is great but I don't appear to have any documentation and am struggling with the JOIN as my table names are then not showing in WHERE selection

There may be doc that tells me this is not going to work automatically

Great stuff and learning tool though

[pixi]

hi eden,

just want to say THANKYOU for this lovely tool! it helps me learning so much!

cheers

pixi

[Wim Decorte]

Dude, when you have a new question, don't post it in an old thread where people have to wade through two pages of almost two-year-old messages.  Make a new thread and reference this so it all ties together.

 

To answer you question: yes, before you can select anything from a table, that table (TO really) needs to be on the graph of the file you are in when you make the ExecuteSQL

You don't need to "get $$filename" on the graph", that part does not make sense.  Kindly post the full scenario and the SQL statement that you are executing.

 

BUT DON'T REPLY TO THIS POST, make a a new thread.