Dynamically Replacing ExecuteSQL Arguments

[James Gill]

Due to areas outside of my control, I have a need to dynamically control the otherwise static arguments required for the ExecuteSQL function to work. To make a long story short, I am modifying an installation SeedCode ProMaps so that I can deal with a data type mismatch.

 

During the course of my troubleshooting, I found that ExecuteSQL was returning a data type mismatch when I was attempting to search on the ID field of a table.  The problem that I've encountered is that while I use numerical ID fields, ProMaps assumes the use of text ID fields.  I used SeedCode's SQLExplorer to craft a query that works when using the arguments function of ExecuteSQL, but now I've hit a roadblock in crafting an ExecuteSQL function that can alter both the WHERE clause and the ExecuteSQL arguments so that ExecuteSQL functions correctly.

 

The originally query is set via a Let statement, where the sc_searchFieldString and other variables are set:

sc_searchFieldString = Case (not IsEmpty ($sc_searchFieldString) ; " WHERE " & Substitute ($sc_searchFieldString ; "¶" ; " AND "))

The original query looks like this: 

"SELECT " & amapDataObject & "," & aArea & " 
FROM " & aPROPERTIES &
sc_searchFieldString  
& " ORDER BY " & aArea & " ASC, " & aArea & " ASC "

The problem was that the script was originally stringing together multiple variables that contained each field and value to be queried and then concatenating them together into a text string that contains the WHERE statement, the fields, and the values to be searched.  Attempting to search ANY number field results in a data type mismatch.  Their query is below:

 WHERE a."id_Parentrecord" = '1234' AND a."textfield1" = 'Visible' AND a."textfield2" = 'CA'

After testing and making sure that this was the problem, my solution was to re-craft the query so that it takes advantage of the arguments function of ExecuteSQL. For whatever reason, I was unable to break the query with data type mismatches (or at least mismatches between text and numbers). The new query looks like this (I'll change the other table variables whenever I get the arguments working):

// ------------  BEGIN EXECUTESQL BLOCK ------------  

Let ( [ 

ReturnSub = "n" ; // We need to swap out carriage returns in your results with a different character, so specify the character here.  n is the default.
SQLResult = ExecuteSQL ( 

// ------------  BEGIN QUERY ------------

"SELECT a."gm_MapData", a."gm_Area"
FROM "Properties" a " &
$sc_searchFieldString &"
ORDER BY a."gm_Area" ASC" ; 

// ------------  END QUERY ------ ------ 

// ------------  BEGIN FIELD AND ROW SEPARATORS ------------ 

"    " ; "|*|" ; 

// ------------  END FIELD AND ROW SEPARATORS ------------ 

// ------------  BEGIN ARGUMENTS ------------  
// ------------  These arguments are pulled from the values you entered when running your query.  You can substitute your own values, fields or variables here. One argument per ? in your query, in query order, separated with semicolons. ------------ 

$sc_searchArgumentQueryString

// ------------  END ARGUMENTS ------------  

 ) ] ; 

// ------------  BEGIN CARRIAGE RETURN SUBSTITUTIONS ------------  

 Substitute ( SQLResult ; [ ¶ ; ReturnSub ] ; [ "|*|" ; ¶ ]  )

// ------------  END CARRIAGE RETURN SUBSTITUTIONS ------------  

)

// Compliments of SeedCode… Cheers!

// ------------  END EXECUTESQL BLOCK ------------ 

And $sc_searchFieldString is now replaced with:

WHERE a."id_ParentRecord" = ? AND a."textfield1" = ? AND a."textfield1" = ?

So now all that's left is for me to craft the arguments string.  The problem that I've run into is that I can replace the individual argument parameters with either variables or field contents, but I cannot replace the entire arguments parameter with a variable as I've done with $sc_searchFieldString as ExecuteSQL fails to return a result.

 

As with $sc_seachFieldString, I'm also setting a variable that contains the values to be searched:

$sc_searchFieldArguments = 
//This list must be in the same order as the search arguments string
List (
//value 1
"$sc_id_parentRecord_argument" ;
//value 2
"$sc_textfield1_argument" ;
//value 3
"$sc_textfield2_argument" 
)

When it comes time to parse the information into a value that should work as an argument for ExecuteSQL, I'm simply replacing the pilcrows with a ';'

$sc_searchArgumentQueryString = 
Substitute ($sc_searchFieldArguments ; ¶ ; ";" )

Obviously this isn't working, but I'm wondering what I'm doing wrong.

 

Edit: After a little more research, I think a better title for this post would have been 'Dynamically Controlling ExecuteSQL Argument Parameter'