8 Replies Latest reply on Jun 19, 2016 7:34 AM by cruiseqe2

    Problem when using parameters when calling ExecuteSQL.

    cruiseqe2

      Hi.

      I am using FMPA 14.

      I have a bit of SQL created by the excellent SQL Explorer from SeedCode. I then transported the output into the database I am creating.

      The SQL has two parameters, 1 a text one, and the other a numeric one.I am enclosing the text of the code here:

      ========================================================

      // Built by SQLExplorer.  Compliments of SeedCode… Cheers!

       

      Let ( [

       

      // Define Carriage Return Substitution Character

      ReturnSub = "\n" ;

       

      // Enable the second line here if you want the header in your results

       

      header = "";

      //header = "a.__ChoiceId";

       

       

      // Define Table variables

      aCHOICES = Quote ( GetValue ( Substitute ( GetFieldName ( Choices::__ChoiceId ) ; "::" ; ¶ ) ; 1 ) ) & " a" ;

       

      // Define Field Variables

      a__ChoiceId = "a." & Quote ( GetValue ( Substitute ( GetFieldName ( Choices::__ChoiceId ) ; "::" ; ¶ ) ; 2 ) ) ;

      aChoiceText = "a." & Quote ( GetValue ( Substitute ( GetFieldName ( Choices::ChoiceText ) ; "::" ; ¶ ) ; 2 ) ) ;

      a_EventId_fk = "a." & Quote ( GetValue ( Substitute ( GetFieldName ( Choices::_EventId_fk ) ; "::" ; ¶ ) ; 2 ) ) ;

       

      // Build SQL Query

      q =

      "SELECT " & a__ChoiceId & "

      FROM " & aCHOICES & "

      WHERE " & aChoiceText & " = ? AND " & a_EventId_fk & " = ? " ;

       

      // Run SQL Query

      result = ExecuteSQL ( q ; Char ( 9 ) ; "|*|" ; $$Test ; $$SelectedEventId ) ] ; 

       

      // Clean up carriage returns

      List ( header ; Substitute ( result ; [ ¶ ; ReturnSub ] ; [ "|*|" ; ¶ ]  ) )  )

      =================================================================

      I have tried to get this working in a calculation field, in a script, but also in the Data Viewer. When the code was copied, the line in RED was actually

      result = ExecuteSQL ( q ; Char ( 9 ) ; "|*|" ; "AMO" ; "1") ] ; 

      In this form, it worked and found the required record from the table CHOICES.

      In order to make the code more useful, I tried to substitute the two parameters as Global Variable.

      It no longer worked.

       

      I have tried switching the order of the variables, to no avail.

      I tried to substitute one parameter at a time.

      When the value "1" was changed to $$SelectedEventId it works.

      When the value "AMO" was changed to $$Test it doesn't work.

       

      Just in case $$Test was a reserved word or something, I have used other Global Variable names, and nothing works.

       

      Instead of an error - "?" being returned, it just doesn't return anything. The record & field I want returned is definitely in the file CHOICES.

       

      I am at a loss. Can anybody please help me!!

       

      Thanks

       

      Mark.