10 Replies Latest reply on Feb 26, 2016 6:13 AM by AdamHorne

    Getting ALL field names from a table

    AdamHorne

      I'm trying to generate a log with field names and values using SQL. I'm trying to figure out a way to get the fieldname : value.

       

      For example:

       

      Let ( [

       

      ~q = "SELECT * from sqlAccounts WHERE id = 'F6DA50DC-D860-462E-8231-F26B7CEFB34F'"

       

      ] ;

       

      ExecuteSQL ( ~q ; ¶ ; ¶ )

      )

       

      Produces:

       

      F6DA50DC-D860-462E-8231-F26B7CEFB34F

      ACME Films

      Active

      8816 Some St.

       

      And I want to produce something like this:

       

      id | F6DA50DC-D860-462E-8231-F26B7CEFB34F

      name | ACME Films

      status | Active

      street | 8816 Some St.

       

      Is there a way to produce the field names from the table so I can then just loop through the sql results and add the fieldName and "|" to the text?

        • 1. Re: Getting ALL field names from a table
          mikebeargie

          Filemaker does allow you to query executesql for table names and fields.

          http://www.databuzz.com.au/using-executesql-to-query-the-virtual-schemasystem-tables/

           

          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" ]

          Loop

             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) ]

          End Loop

          Go To Layout [ log ]

          New Record

          Set Field [ log::contents ; $list ]

          • 2. Re: Getting ALL field names from a table
            user19752

            I had coffee now.

             

            Let ( selects = ExecuteSQL ( "

            SELECT ''''+fieldname+' | ''||\"'+fieldname+'\"'

            FROM FileMaker_Fields

            WHERE tablename = ?

            " ; "" ; "," ; "sqlAccounts"  ) ;

            ExecuteSQL ( "

            SELECT " & selects & "

            FROM sqlAccounts

            WHERE id = ?

            " ; ¶ ; "" ; "F6DA50DC-D860-462E-8231-F26B7CEFB34F" )

            )

            • 3. Re: Getting ALL field names from a table
              AdamHorne

              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....

               

              1. What exactly is +fieldname+ and why is it surrounded by +? I see that it is required, because I removed them and it broke.
              • 4. Re: Getting ALL field names from a table
                beverly

                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:

                'field1 |'

                this is concatenated with the use of ||

                then the field name in double quotes:

                "field1"

                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()!


                HTH,

                beverly

                • 5. Re: Getting ALL field names from a table
                  user19752

                  Thanks beverly.

                   

                  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)

                  • 6. Re: Getting ALL field names from a table
                    beverly

                    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.

                    beverly

                    • 7. Re: Getting ALL field names from a table
                      user19752

                      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.

                      • 8. Re: Getting ALL field names from a table
                        beverly

                        You have to be careful with the + and numbers. It can be the "addition" operator.

                         

                        the prepending the || is from Jason Young (Seedcode):

                        http://www.seedcode.com/filemaker-sql-explorer/

                         

                        beverly

                        • 9. Re: Getting ALL field names from a table

                          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.

                           

                          - m

                          • 10. Re: Getting ALL field names from a table
                            AdamHorne

                            Thanks for all the help!