Item9678: Using IFDEFINED with DATABASE plugin in to conditional render a DATABASE query
Priority: Normal
Current State: No Action Required
Released In: n/a
Target Release: n/a
Hi
I want to conditionally render a %DATABASE_SQL% query. I want to use this to stop a HTML form from placing values in a database when I save the form after I've edited it. To do this I tried the following:
%IFDEFINED{"%URLPARAM{"name"}%" then="$percntDATABASE_SQL{description=\"projectdatabase\" sql=\"INSERT INTO example (name, age) VALUES('%URLPARAM{"name"}%','%URLPARAM{"age"}%')\"}$percnt"}%
The %IFDEFINED% works, but the %DATABASE_SQL% doesn't render.
--
HenroRitchie - 15 Sep 2010
Try
SqlPlugin.
--
MichaelDaum - 15 Sep 2010
I tried the following:
%IFDEFINED{"%URLPARAM{"name"}%" then="%SQL{database="projectdatabase" query="INSERT INTO example (name, age) VALUES('%URLPARAM{"name"}%','%URLPARAM{"age"}%')"}%" else="<nop>"}%
The the %SQL% renders irrespective of the
%IF%
, and that inserts two blank rows into my table everytime I save the topic.
--
HenroRitchie - 02 Oct 2010
You need to delay the SQL macro until
after the IF is expanded (in Foswiki, macros are expanded 'inside-out, left-to-right'). See
nested search documentation, ie.
then="$percntSQL{database=\"projectdatabase\" ...}$percnt"
--
PaulHarvey - 03 Oct 2010
Hi Paul
Thanks for the explanation. This has messed me around a lot.
I managed to find an alternative solution by breaking the %SQL% code with the %IFDEFINED% plugin to block a submission to the database and then suppress the SQL error.
See below:
%SQL{%IFDEFINED{"%URLPARAM{"taskname"}%" then="" else="<nop>"}%database="projectdatabase" query="INSERT INTO linkprojectandtasks (ProjectEntry, TaskEntry) VALUES ('%PROJECT%',LAST_INSERT_ID())"}%
I now this is not the best solution, but for now it works.
Regards
Ritchie
--
HenroRitchie - 03 Oct 2010
For what it's worth, you should be able to do this with native IF. I don't think
IfDefinedPlugin uses the registerTagHandler (I could be wrong), and this means that it probably doesn't participate in normal 'inside-out, left-to-right' expansion behaviour.
So maybe that's why it wasn't working. Native
IF should work:
%IF{
"defined 'name'"
then="$percntSQL{
database=\"projectdatabase\"
query=\"INSERT INTO example (name, age) VALUES('$percntURLPARAM{\"name\"}$percnt','$percntURLPARAM{\"age\"}$percnt')\"
}$percnt"
}%
But I'm glad you got it working anyway.
--
PaulHarvey - 03 Oct 2010