1 2 Previous Next 18 Replies Latest reply on Feb 13, 2012 6:35 PM by listen2earth

    SQL and "_"

    kaostika

      Hi,

       

      I am having trouble using SQL runner and using fields that have and "_". I read you can escape them with a '' but that did not work with Filemaker.

       

      Oreste

        • 1. Re: SQL and "_"
          beverly

          Oreste, this plug-in? http://www.dracoventions.com/products/2empowerFM/family/sql.php

           

          and is the underscore in the field name or in the field contents?

          have you tried to contact dracoventions for help?

           

          Beverly

          • 2. Re: SQL and "_"
            kaostika

            Thanks for your reply Beverly,

             

            The "_" is in the field name.  I am using Draconventions plugin.  I ran the SQL thru and only Validation and it failed.  I assumed it was on the SQL part not the plugin part.

             

            I can ask Dracon.

             

            Oreste

            • 3. Re: SQL and "_"
              beverly

              Did you try to quote the field name?

               

              Sorry, without more details, it's difficult to trouble-shoot.

               

              Beverly

              • 4. Re: SQL and "_"
                kaostika

                I will try that.  What I was trying to do is is get all the field names from a calculation and run it Thru the SQL so I didnt have to manually put them in for any table I was doing.  Basically I was making a PUSH PULL system.  I was pushing Records from a Filemaker Table in to a SQL  (to be show on the web)

                 

                listToCommaSep ( FieldNames (Get( FileName) ; "W" & Table ) )  ( this basically takes a Return delimited list the Filemaker generates and make it into a Comma Seperated string that I can use for the internal part of the SQL statement.

                 

                This CF allowed me to get the fields in this table and run it thru.  I only need to use one Line of SQl that was "soft Coded"

                 

                INSERT INTO   table ( listToCommaSep ( FieldNames (Get( FileName) ; "W" & Table ) ) )

                Rest of SQL

                 

                Instead of

                 

                INSERT INTO table (Field1,field2,field3,field4)   that is Hard Coded.

                 

                The CF works as long as there is no "_"

                 

                 

                Does this make sense?

                 

                Oreste

                • 5. Re: SQL and "_"
                  rhlilienkamp

                  So the problem is the CF not the SQL.  Column names like last_Name work fine with SQLrunnner.

                  • 6. Re: SQL and "_"
                    greglane

                    Hi Oreste,

                     

                    As Beverly suggested, you should use quotes around field and table names, especially when you're building a generalized solution that needs to work on unknown names that might begin with an underscore or contain spaces or SQL reserved words.

                     

                    Also, I've found it useful to query the FileMaker_Tables and FileMaker_Fields metatables to get lists of tables and fields. This makes it easier to appropriately handle certain field types. Give these a try:

                     

                    SELECT TableName, TableID, BaseTableName, BaseFileName, ModCount FROM FileMaker_Tables

                     

                    SELECT TableName, FieldName, FieldType, FieldId, FieldClass, FieldReps, ModCount FROM FileMaker_Fields

                     

                    Greg

                    • 7. Re: SQL and "_"
                      kaostika

                      Greg, Are you saying I can Use SQL to get info from the Database it self.  Like a DDR or something.


                      I will messaround with the CF when I get home.  If I can get this type of system working it will save me hours of time in the furture.


                      Oreste

                      • 8. Re: SQL and "_"
                        greglane

                        Oreste,

                         

                        This query:

                         

                        SELECT TableName, FieldName, FieldType, FieldId, FieldClass, FieldReps, ModCount FROM FileMaker_Fields

                         

                        would return something like the following, with a row for every field in every table occurrence of the file.

                         

                        Catalog ProductsProduct IDdecimal1Normal16
                        Catalog ProductsProduct Namevarchar3Normal11
                        Catalog ProductsCategoryvarchar5Normal11
                        Catalog ProductsCostdecimal7Normal11
                        Catalog ProductsDate Createddate24Normal11
                        Catalog ProductsDate Modifieddate25Normal11
                        Catalog ProductsPart Numbervarchar33Normal11
                        Catalog ProductsLocationvarchar34Normal11
                        Catalog ProductsPicturebinary46Normal11
                        Catalog ProductsCreated Byvarchar49Normal11
                        • 9. Re: SQL and "_"
                          kaostika

                          Wow it works. A few more questions.  How can Make that table (grid) and is there a place or List, where I can see all that metadata.

                           

                          Oreste

                          • 10. Re: SQL and "_"
                            greglane

                            Using one of the many SQL plugins, you can easily wrap the results of a query in <tr> and <td> tags. Here's the query I used:

                             

                            epSQLExecute( "SELECT '<tr><td>' + TableName, FieldName, FieldType, FieldId, FieldClass, FieldReps, STRVAL(ModCount) + '</td></tr>' FROM FileMaker_Fields"; "rowSeparator='¶', columnSeparator='</td><td>'" )

                            • 11. Re: SQL and "_"
                              robmertens

                              hello all,

                               

                              i can see that there are a few people here that know about SQL and Filemaker. Can anyone of you point me into the direction of a tutorial, text, internet page or other place where i can start reading and learning this stuff in combination with what is needed?

                              I'd really like to integrate it into our system as the system we use now is to cumbersome. (Importing several excel tables with over 3500 lines every day)

                               

                              Thanx!

                              • 12. Re: SQL and "_"
                                beverly

                                Hello, robmertens! Start with this page: http://www.filemaker.com/support/technologies/odbc.html

                                 

                                FileMaker and ODBC/JDBC (also known as xDBC) have several ways to interact, as you see on this page. xDBC/FileMaker requires xDBC driver(s). The method you choose to use, requires a different driver (also depending on platform). The page has resources, that you should checkout, such as the guide and questions.

                                 

                                The companion page to the above, if you didn't find the link to it: http://www.filemaker.com/support/technologies/sql.html is mostly about ESS (External SQL Sources). there are many resources on this page, too.

                                 

                                In addition there are plug-ins that allow you to use SQL in FileMaker. Use your favorite search engine to find for "FileMaker SQL plugin".

                                 

                                Beverly

                                • 13. Re: SQL and "_"
                                  rhlilienkamp

                                  Kevin Frank has several blob related to using SQL in FMP see: http://www.fielmakerhacks.com

                                  • 14. Re: SQL and "_"
                                    kaostika

                                    W3schools.com

                                     

                                    Sent from my iPad

                                    1 2 Previous Next