Hi Again,

Beverly Voth, as per usual, has inspired me -- although she may not want to
take credit :-)

I have written two custom functions:
1. sqlCompose which is fully dependent upon $script_variables *
2. sqlExecute which is fully dependent upon $scrpt_variables as well,
including 2 that are set within the sqlCompose function.

I wrote two separate functions to separate the composition of 'Standard
SQL' from the Non-Standard FileMaker options. Even though the First Row as
Column Names/Aliases feature is executed within sqlExecute it is 'composed'
within sqlCompose since writing the "AS-Aliases" would normally be done
within the Standard SQL Code. I also made no attempt to use the '?' as bind
(or sql injection protection) mechanism (yet). I didn't test the GROUP BY
either.

Here is the text of the Two Functions, and attached is an image of a SCRAP
Script that I used to do some testing -- I call it "preTDD" since these are
First Drafts and well short of full Test-Driven-Development standards. I
tested each as I added expected $script_variables to the script. Also, I
aspire to test the GROUP BY feature soon. Honestly, however, unless I end
up using these in production I doubt I will get around to fully robust TDD.

sqlCompose()
=========================
/* sqlCompose is fully based on $script_variables */
/* $where, $orderby, $order_by, $groupby, $group_by could be unset after
their respective clauses have been set if accommodated in the Case()
statements -- seems like a coin-flip */
/* Also $orderby & $order_by and  $groupby & $group_by are Aliases of
eachother with the first of each pair taking precedence */
/* any $session_variable prefixed with $function_ is not meaningful outside
the function(s) and is named such to add a hint name-spacing to avoid
conflicts */
/* $function_error can be made meaningful if you like */
/* $column_name_alias_row is set HERE since aliases are normally part of
the SQL Code as Composed */
Let(
[
$function_error = False;
$function_i = Case($function_i + 0 < 1; 1; $function_i + 1);
$function_column_name_list = Case($function_i + 0 > 1;
$function_column_name_list);
$column_name_alias_row = Case($function_i + 0 > 1; $column_name_alias_row);
$table_name = Case(IsEmpty($table_name) = False; $table_name;
Get(LayoutTableName));
vComma = Case($function_i + 0 > 1; ",");
vPipe = Case($function_i + 0 > 1; "|");
vNext_i = $function_i + 1;
vDone = IsEmpty($column[vNext_i]);
$function_column_name_list = $function_column_name_list & vComma &
$column[$function_i];
$column_name_alias_row = $column_name_alias_row & vPipe &
Case(IsEmpty($column_name_alias[$function_i]) = False;
$column_name_alias[$function_i]; $column[$function_i]);
$function_where_clause = Case(IsEmpty($where) = False; " WHERE " & $where);
$orderby = Case(IsEmpty($orderby) = False; $orderby; IsEmpty($order_by) =
False; $order_by);
$function_orderby_clause = Case(IsEmpty($orderby) = False; " ORDER BY " &
$orderby);
$groupby = Case(IsEmpty($groupby) = False; $groupby; IsEmpty($group_by) =
False; $group_by);
$function_groupby_clause = Case(IsEmpty($groupby) = False; " GROUP BY " &
$groupby);
vResult = "SELECT " & $function_column_name_list & " FROM " & $table_name &
$function_where_clause & $function_orderby_clause & $function_groupby_clause
];
Case($function_error <> False; ""; vDone = True; vResult; sqlCompose))
=========================

sqlExecute()
=========================
/* executeSQL is fully based on $script_variables */
/* vError can be made meaningful if you like */
/*  OFFSET ROWS here since it is NOT Standard SQL */
/* FETCH FIRST ROWS ONLY here since it is NOT Standard SQL */
/* \_ 'WITH TIES' and 'PERCENT' are NOT Supported at this time */
/* $rowSeparator & $row_separator and $fieldSeparator & $field_separator
are Aliases of each other with the first of each pair taking precedence
since that is how FileMaker wrote the Parameter Hints */
Let(
[
vError = Case($function_error = True; True; False);
vOmit_column_name_row = Case($omit_column_name_row = True; True; False);
$fieldSeparator = Case(Length($fieldSeparator) = 1; $fieldSeparator;
Length($field_separator) = 1; $field_separator; ",");
$rowSeparator = Case(Length($rowSeparator) = 1; $rowSeparator;
Length($row_separator) = 1; $row_separator; "¶");
$offset_rows =Case(Floor(Filter($offset_rows; "-.012345678") + 0) < 1;  0;
Filter($offset_rows; "-.012345678") + 0 <> Floor(Filter($offset_rows;
"-.012345678") + 0); 0; Floor(Filter($offset_rows; "-.012345678") + 0));
$offset_rows = Case($offset_rows > 0; " OFFSET " & $offset_rows & " ROWS");
$fetch_first_rows =Case(Floor(Filter($fetch_first_rows; "-.012345678") + 0)
< 1; 0; Filter($fetch_first_rows; "-.012345678") + 0 <>
Floor(Filter($fetch_first_rows; "-.012345678") + 0); 0;
Floor(Filter($fetch_first_rows; "-.012345678") + 0));
$fetch_first_rows = Case($fetch_first_rows > 0; " FETCH FIRST " &
$fetch_first_rows & " ROWS ONLY");
$sqlQuery = $sqlQuery & $offset_rows & $fetch_first_rows;
$column_name_alias_row = Case(vOmit_column_name_row = False;
Substitute($column_name_alias_row; "|"; $fieldSeparator) & $rowSeparator)
];
Case(vError = False; $column_name_alias_row & ExecuteSQL ( $sqlQuery ;
$fieldSeparator ; $rowSeparator )))
=========================

* I call Single-Dollar-Sign Globals $script_variables, and I call
Double-Dollar-Sign Globals $$session_variables since it is the Scope of
their use that is critical.

FWIW: $0.02

Regards,
Brad

On Fri, Apr 3, 2015 at 4:34 PM, BEVERLY VOTH <[log in to unmask]> wrote:

> On Apr 3, 2015, at 5:07 PM, John Weinshel <[log in to unmask]> wrote:
>
> > And you can use aliases, as in:
> >
> > SELECT c.fName, c.lName
> > FROM Contacts c
>
> But that does not give you the "column names" in the eSQL SELECT statement.
>
> >
> > The irony is that the first use of SQL in Filemaker, with the interaction
> > in 4.1 of ODBC, we did indeed have a fairly tradition, Excel-like
> > interface for building the queries. That was for the Execute SQL script
> > step, which is still useful.
> >
> The Import script step was (and still is) for the SELECT and it will
> return the field/column names should you need to match or create a new
> table.
>
> The Execute SQL script step (not the ExecuteSQL function) is for INSERT,
> UPDATE & DELETE. and you do specify the column names either manually or
> with the widget/dialog.
>
> beverly
>
> > John Weinshel
> >
> > On 4/3/15, 1:56 PM, "BEVERLY VOTH" <[log in to unmask]> wrote:
> >
> >> you can put 'literals' into your query and UNION the results, or just
> >> concat in your final result:
> >>
> >> Let (
> >> [ _header = "name,phone,email"
> >> ; _query = "SElECT name,phone,email FROM ____ ..."
> >> ; _result = ExecuteSQL ( _query, "", "" )
> >> ]; substitute (_header; "," ; char(9) ) & ¶ & _result
> >> )
> >>
> >> my first row "_header" is comma separated literals or as you say, get
> >> from the function - I just find it easier to hard-code. Because this
> >> "row" is comma separated, I need to exchange the comma with a tab
> >> Character (or whatever delimiter is use in the eSQL. then because the
> row
> >> will appear before the eSQL result, add a return. Final append the
> result.
> >>
> >> beverly
> >>
> >>
> >> On Apr 3, 2015, at 4:11 PM, Brad Lowry <[log in to unmask]> wrote:
> >>
> >>> Hi All,
> >>>
> >>> I haven't posted in a million years.
> >>>
> >>> (apologies ahead of time if I interchange column/field/attribute and or
> >>> record/row an so on)
> >>>
> >>> I'm late to the ExecuteSQL party, but I've been using SQL for years in
> >>> MySQL, Oracle, Solid, through SmartPill and even MSSQL. Of course, SQL
> >>> is a Language (the 'L' in SQL). not a product so I am thrilled to be
> >>> able to use it out of the box in FileMaker.
> >>>
> >>> However, every Query Tool I have ever used -- SQL Developer (oracle),
> >>> SQLpro (mac), phpMyAdmin (MySQL on web), even as far back as TOAD! --
> >>> they all had user interface where the returned data is (can be)
> returned
> >>> with the first row as column names.
> >>>
> >>> I don't see this in FileMaker ExecuteSQL().
> >>>
> >>> Thanks to Andrew Duncan
> >>> (
> http://www.databuzz.com.au/using-executesql-to-query-the-virtual-schemas
> >>> ystem-tables/) I know that if I absolutely had to, I could collect the
> >>> fieldnames for a particular table. I could even cobble together such a
> >>> first row -- but only where the query was SELECT * -- unless there are
> >>> more things to be done with those virtual tables.
> >>>
> >>> Further, even though when using SQL Developer I am able see the first
> >>> row as column names (and export to csv etc), when I access such a query
> >>> within PHP for instance that 'first row' is not returned as such.
> >>> However, and more importantly, each attribute name is available within
> >>> the return set (resource) with a foreach loop or whatever.
> >>>
> >>> The biggest drawback is column name aliasing. I just tried this query
> >>> (and it worked) before I began writing:
> >>> ========
> >>> SELECT
> >>> id as hello
> >>> , name as world
> >>> , title
> >>> FROM option_group
> >>> WHERE 1 = 1
> >>> OFFSET 2 ROWS
> >>> FETCH FIRST 5 ROWS ONLY
> >>> ========
> >>>
> >>> Of course, this is a silly example. But imagine writing a report for
> >>> the Staff Directory:
> >>> SELECT TRIM(last) || ', ' || TRIM(first) as "Name" FROM staff_directory
> >>> ORDER BY last, first
> >>>
> >>> Wouldn't it be much easier to build a report using the result if you
> >>> were able to access the ColumnNames of the result as *part* or the
> >>> result?
> >>>
> >>> There are workarounds -- there are always workarounds -- populate
> >>> $$session variables to store your column names before you run the
> query:
> >>> $$columnname[1] = "Name"
> >>> $$columnname[2] = "Phone"
> >>> $$columnname[3] = "Email"
> >>> And then not use the ' as ' aliasing at all... however, since
> >>> ExecuteSQL *allows* it, it would make sense that it be natively
> >>> accessible.
> >>>
> >>> Is there some 'RETURN_COLUMN_NAMES' argument hidden somewhere?
> >>>
> >>> Thanks in advance.
> >>>
> >>> Regards,
> >>> Brad
>