3 Replies Latest reply on Mar 23, 2017 10:55 PM by philmodjunk

    Using executeSql to search for strings containing periods

    Malcolm

      I'm having trouble handling a search string that contains a period. This is my code, it returns "The column named "Full.Name" does not exist in any table in the column reference's scope." Can someone put me on the right track please?

       

      Let ( [

      ; searchFld = cf_SQL_FieldName ( GetFieldName (  EMP::Account Name ) )

      ; TBL = cf_SQL_TableName ( GetFieldName (  EMP::id ) )

      ; dataFld = cf_SQL_FieldName ( GetFieldName (  EMP::id ) )

       

      ; sqlQuery = "SELECT " & dataFld & " from " & TBL & " WHERE " & searchFld & "=" & Quote( "Full.Name") // get(accountName) )

       

      ];

       

      "" // AccountID = ExecuteSQL ( sqlQuery ; "" ; ¶  )

       

      )

       

       

       

      malcolm

        • 1. Re: Using executeSql to search for strings containing periods
          philmodjunk

          "Full.Name" is evaluating as the name of a column in the table instead of as literal text.

           

          'Full.Name' is the notation for literal text

          • 2. Re: Using executeSql to search for strings containing periods
            user19752

            So use parameter rather than self quoted value.

             

            ... "=?"

             

            ExecuteSQL(... ; "Full.Name")

            • 3. Re: Using executeSql to search for strings containing periods
              philmodjunk

              You might be interested in a different format for an SQL query:

               

              Let ( [

              @dataFld = SQLRef ( EMP::id ; "fld" ) ;

              @DataTBL = SQLRef ( EMP::id ; "TO" ) ;

              @searchFld = SQLRef ( EMP::Account Name ; "fld" ) ;

               

              SQLa = "SELECT @dataFld FROM @DataTBL

                              WHERE @searchFld = 'Full.Name' " ;

               

              SQL = Substitute ( SQLa ;

              [ "@dataFld" ; @dataFld ] ;

              [ "@DataTBL" ; @DataTBL ] ;

              [ "@searchFld" ; @searchFld ] )

              ] ;

               

              ExecuteSQL ( SQL ; "" ; "" )

              ) // Let

               

              My custom function is a bit different, GetFieldName is used inside the custom function and a second parameter allows me to use the same CF for both field and table occurrence names. But note how the SQL expression is much more clearly composed and thus easy to analyze.

               

              I also do not type in anything but the text to the right of SQLa and without the quotes. A system of custom functions then writes the full expanded expression for me such that all I have to add is the explicit field references in the top declaration block and any optional parameter expression in the ExecuteSQL call.

               

              You can find the custom functions, both SQLRef and the ones I use to create such query expressions in the final example  (Button Bars as Value Lists) in:

               

              Adventures in FileMaking #2-enhanced value selection