Filemaker does allow you to query executesql for table names and fields.
There may be a query that you could form to concatenate the field name from there, with the value from a record, but my brain is a bit fuzzy without coffee so I can't think of it off the top of my head.
Otherwise you can query the schema first, then perform a loop using GetValue() on the list of fields, and some context-free ExecuteSQL to get the record values.
Set Variable [ $fields ; ExecuteSQL("SELECT * FROM FileMaker_Fields WHERE TableName = ?";"";""; "sqlAccounts" ]
Set Variable [ $id ; "F6DA50DC-D860-462E-8231-F26B7CEFB34F" ]
Set Variable [ $i ; $i + 1 ]
Set Variable [ $field ; GetValue( $fields ; $i ) ]
Set Variable [ $value ; ExecuteSQL("SELECT " & $field & " FROM sqlAccounts WHERE id = ?";"";"";$id) ]
Set Variable [ $list ; List( $list ; $field & " | " & $value ) ]
Exit Loop If [ $i = ValueCount($fields) ]
Go To Layout [ log ]
Set Field [ log::contents ; $list ]
I had coffee now.
Let ( selects = ExecuteSQL ( "
SELECT ''''+fieldname+' | ''||\"'+fieldname+'\"'
WHERE tablename = ?
" ; "" ; "," ; "sqlAccounts" ) ;
ExecuteSQL ( "
SELECT " & selects & "
WHERE id = ?
" ; ¶ ; "" ; "F6DA50DC-D860-462E-8231-F26B7CEFB34F" )
So this worked great. When you have time, would you mind breaking this down a little bit for me?
Mainly this part: "SELECT ''''+fieldname+' | ''||\"'+fieldname+'\"'
I'm getting a little confused with all the quotes....
- What exactly is +fieldname+ and why is it surrounded by +? I see that it is required, because I removed them and it broke.
Adam, it was a little difficult to see what was what. Let's see if I can break down the bits:
SELECT ''''+fieldname+' | ''||\"'+fieldname+'\"'
'''' is actually 4 single quotes. It escape quotes the single quote needed at the beginning of each set of field names
+ is the concatenation character in SQL
fieldname is the reserved word in ExecuteSQL that returns the name of the field. you see that is is used twice in the SELECT, above.
+ concatenate again
' | (that's a single quote, a space, a pipe and a space)
''||\"' is TWO single quotes (escaped single quote) followed by two pipes (to be used by the second query also as concatenation), followed by \" (backslash & double quote to escape the FM use of literal double quote), and finally the ending single quote
+fieldname+ (again to concatenate the field name)
'\"' single quote, backslash, double quote (to escape the FM quote) and the single quote to end the literal.
The result of this first query gets you something like this (the commas comes from the separator between columns:
'field1 | '||"field1",'created_ts | '||"created_ts",'modified_ts | '||"modified_ts",'created_acct | '||"created_acct",'modified_acct | '||"modified_acct",'created_name | '||"created_name",'modfied_name | '||"modfied_name",'field2 | '||"field2"
This "result" from the first query is pushed into the second query as the SELECT. I'll break down those bits, too.
The literals have the single quote, the field name and a pipe:
this is concatenated with the use of ||
then the field name in double quotes:
followed by the comma.
That is what get's your results:
field1 | field1Value
This is a very clever usage of the schema defaults in ExecuteSQL()!
Only a thing, I think "fieldname" in the sql is simply field(column) name in meta data table, not reserved word.
I wrote it using both concatenate operators || and + , there was no deep thinking about it. I wrote it, worked, end. I can't explain why I used +.
|| is SQL standard, + (and - for moving trailing spaces to tail) may be FM specific or addition of SQL implements.
I noticed after post that I forgot to use STRVAL(), but it is not need.
In this case, second query concatenate 2 things as
text || anytype
and it worked, but if this is reversed,
anytype || text
not work if anytype is not text.
And of cource, + (and -) is not usable concatenating number and number, date etc.
Editted: I mistake - function (I'd never used)
I say "reserved word" only that 'fieldname' is a valid constant, function or whatever that can be used by ExecuteSQL() to get what's needed. In this query it's not a placeholder.
In all my testing, || and + worked equally well for concatenation in ExecuteSQL().
The values should be text (or become text), but since ExecuteSQL() only returns text, I found that didn't matter.
SELECT ''||CURDATE, CURDATE, ''+CURTIMESTAMP, CURTIMESTAMP, ''||CURTIMESTAMP
That is also the reason STRVAL() was not needed, because we get text in the result anyway.
You show good technique appending ''|| before other types than text, as replacement of using STRVAL().
I meant that to concatenate
SELECT 1 || 'a', 1 + 'a', 1 + 1
not works as expeced. This should be
SELECT STRVAL(1) || 'a', STRVAL(1) + 'a', STRVAL(1) + STRVAL(1)
if not use ''||
I wonder finding SELECT 1+'a' results 1 just now... SELECT 1||'a' makes error.
That's pretty cool!
Coming at this from the JDBC side, if you know a little Java....You could also query the DBMetaData object returned from FileMaker using a remote Java program which is running against a real-time connection to your FM database.
Sounds more difficult than it is. It's actually super easy to do!
FMP's JDBC driver is excellent.
Thanks for all the help!