Oops! The attachment...
On Fri, Apr 3, 2015 at 8:43 PM, Brad Lowry <[email protected]> wrote: > 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 <[email protected]> > wrote: > >> On Apr 3, 2015, at 5:07 PM, John Weinshel <[email protected]> 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" <[email protected]> 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 <[email protected]> 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 >> > >
