AnsweredAssumed Answered

Problem with executing SQL, FMP modifying the string?

Question asked by blortblort on Feb 11, 2019
Latest reply on Feb 12, 2019 by beverly

I had, for a brief moment, a working SQL step in a script to handle a problem I was running into with searches, but I modified the script and now cannot get it back to functionality.


Either of these work fine in the sql console (I'm using a postgres database using the actualtech ESS adapter, and datagrip to manipulate the database directly):


select * from the_personnel_records where the_fullname ~* 'thesearchterm'




select * from "UserManagementTool" where the_fullname ~* thesearchterm


but pasting either of these into FMP's executeSQL step directly ends up not working because FMP butchers them and they don't work:


ERROR: syntax error at or near ""~*"" at character 56

STATEMENT: select * from "UserManagementTool" where "the_fullname" "~*" "thesearchterm"


ERROR: syntax error at or near ""~*"" at character 58

STATEMENT: select * from "the_personnel_records" where "the_fullname" "~*" "thesearchterm"


I cannot for the life of me figure out how I made this work before and why it won't work now, but I'm tearing my hair out at this point.  Any help appreciated.


It might be noteworthy to mention that it was a one-line solution in the script step that I accidentally removed and it's possible I didn't do it with executeSQL, but if not, I don't know how the heck I did it.