Item9678: Using IFDEFINED with DATABASE plugin in to conditional render a DATABASE query

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

Commenting is disabled while not logged in