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-schemasystem-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.
Thanks in advance.