AnsweredAssumed Answered

Can you dynamically control the 'Arguments' Parameter in ExecuteSQL via variables?

Question asked by jgill@occu-med.com on Nov 21, 2014
Latest reply on Aug 19, 2017 by wimdecorte

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 = "
" ; // We need to swap out carriage returns in your results with a different character, so specify the character here. 
 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 <- What I want to use as my arguments replace

// ------------ 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" = ?

 

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.

Outcomes