DoSQL 101: Querying data

Last week, we welcomed a new version of DoSQL. Starting this week, we are going to discuss the new features of doSQL and compare them with FileMaker’s native function. Today we will start with a simple example.

SQL Statements

Currently, FileMaker's function “executeSQL” supports only the “SELECT” statement. This means that you can only query the data, but not create, alter or delete any record. If you need any of these functions, you will need a plug-in like doSQL.

myFMbutler doSQL supports these statements:
- SELECT
- INSERT INTO
- UPDATE
- DELETE
- CREATE TABLE
- DROP TABLE
- ALTER TABLE
- CREATE INDEX
- DROP INDEX

Check a copy of the “FileMaker 12 ODBC/JDBC guide” for more details about this statements.

Querying data

But let’s explorer first the new native function in FileMaker Pro 12:

ExcuteSQL ( sqlQuery; fieldSeparator; rowSeparator {; arguments} )

You will notice that the ExecuteSQL takes four parameters. The last one is optional.

The first parameter is the query. In this case this will always be a “SELECT” statement.
In the second parameter you fill in one or more characters that will be used to separate the result values of the fields. The third parameter is similar as the second one, but will be used to separate the records (rows). Let’s forget the last parameter for now.

A simple example

Let’s presume we have the following table “cities” in FileMaker Pro 12.

and we execute the function:

ExecuteSQL(“SELECT cityid, city FROM cities WHERE citizens<200000” ; “;” ; “¶”

This will return us all cities with less then 200000 citizens. The result would look like this:

1;Brugge¶3;Mechelen

To achieve the same with myFMbutler DoSQL, you need this syntax:

Let([
rs = mFMb_DoSQL_SetRowSeparator ( ";" );
cs = mFMb_DoSQL_SetColumnSeparator ( "¶" )
];
mFMb_DoSQL ( "SELECT cityid, city FROM cities WHERE citizens<200000" )
)

In DoSQL the syntax seems to be longer. Let’s call it a design choice. But when you execute multiple queries, you don’t have to define the separators again.

FileMaker Pro 12:
Let([
smallCities = ExecuteSQL ( "SELECT cityid, city FROM cities WHERE citizens<200000" ; “;” ; “¶” );
bigCities = ExecuteSQL ( "SELECT cityid, city FROM cities WHERE citizens>1000000" ; “;” ; “¶” );
smallStates = ExecuteSQL ( "SELECT stateid, state FROM states WHERE citizens<2000000" ; “;” ; “¶” );
bigStates = ExecuteSQL ( "SELECT stateid, state FROM states WHERE citizens>10000000" ; “;” ; “¶” );
];
smallCities & bigCities & smallStates & bigStates
)

DoSQL:
Let([
rs = mFMb_DoSQL_SetRowSeparator ( ";" );
cs = mFMb_SQL_SetColumnSeparator ( "¶" );
smallCities = mFMb_DoSQL ( "SELECT cityid, city FROM cities WHERE citizens<200000" );
bigCities = mFMb_DoSQL ( "SELECT cityid, city FROM cities WHERE citizens>1000000" );
smallStates = mFMb_DoSQL ( "SELECT stateid, state FROM states WHERE citizens<2000000" );
bigStates = mFMb_DoSQL ( "SELECT stateid, state FROM states WHERE citizens>10000000" );
];
smallCities & bigCities & smallStates & bigStates
)

Dynamic Parameters

ExecuteSQL enables you to execute SQL statements containing dynamic parameters to safely query FileMaker Pro databases in order to avoid security vulnerabilities through injection attacks. To do so, you can use the arguments. Pretend we have a global field “numberOfCitizens” where you can enter the maximum of citizens for your query

ExecuteSQL(“SELECT cityid, city FROM cities WHERE citizens < ?” ; “;” ; “¶” ; cities::numberOfCitizens )

If the field contains 200000, it will have the same result as our first example:
1;Brugge¶3;Mechelen

In myFMbutler DoSQL 2, we also support dynamic parameters. To achieve the same as above, you need this syntax:

Let([
rs = mFMb_DoSQL_SetRowSeparator ( ";" );
cs = mFMb_SQL_SetColumnSeparator ( "¶" );
params = mFMb_DoSQL_SetParameters( cities::numberOfCitizens );
];
mFMb_DoSQL ( "SELECT cityid, city FROM cities WHERE citizens < ?" )
)

Next time

Next time in "DoSQL 101": FileMaker Data types

Leave a Reply

You must be logged in to post a comment.