AnsweredAssumed Answered

Problem when using parameters when calling ExecuteSQL.

Question asked by cruiseqe2 on Jun 18, 2016
Latest reply on Jun 19, 2016 by 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.

Outcomes