How do I use user defined variables to define a database query?

MedianShark873 — Tue, 13 Jun 2023 01:37:35 GMT

Hello, I am using JMP16. I have a pop up box to ask for user input to define two variables ("Entity", and "Last_X_Days") that I would like to use to query a database. I have no problem getting these variables defined, but the data pull query is in quotations and wont take inputs as variables. It just takes them literally.

If "Last_X_Days" and "Entity" are previously defined as variables (based of the user input), how do I get the query to pull the variables and not just write literally the names of the variables?

Note: I changed some of the irrelevant code to more generic terms in CAPS.

Thank you!!!

script = PROGRAM:openDATABASEscript("\[

<PROGRAMInput>
  <Task>
    <TaskName>PROGRAM</TaskName>
    <AdditionalQueries>
      <Query>
        <TaskName>PARAMETER</TaskName>
        <AutoJoin>1</AutoJoin>
        <Parameters>TOOL</Parameters>
        <PrimaryEntity>1</PrimaryEntity>
      </Query>
    </AdditionalQueries>
    <AutoRun>1</AutoRun>
    <ExtractXML>1</ExtractXML>
    <Site>LOCATION</Site>
    <DATABASEQuery>
      <Query>
        <TaskName>DATABASEByDateRange</TaskName>
        <Parameters>PARAMETER NAME</Parameters>
        <Daterange>Last_X_Days</Daterange>
        <Entity>Entity</Entity>
        <ExcludeInvalid>0</ExcludeInvalid>
        <ExcludeNonstandard>0</ExcludeNonstandard>
        <ExcludePre>1</ExcludePre>
        <IncludeSubs>1</IncludeSubs>
        <MonitorType>ALL</MonitorType>
      </Query>
    </DATABASEQuery>
    <Technology>TECHNOLOGY</Technology>
    <ToolStyle>DATABASE</ToolStyle>
    <WriteJSL>FILE</WriteJSL>
  </Task>
</WijtInput>
]\"

);

Re: How do I use user defined variables to define a database query?

jthi — Tue, 13 Jun 2023 05:47:21 GMT

In this case I would most likely use Eval Insert()

Names Default To Here(1);

Last_X_Days = 1;
Entity = "ABC";

query_template = "\[
<PROGRAMInput>
  <Task>
    <TaskName>PROGRAM</TaskName>
    <AdditionalQueries>
      <Query>
        <TaskName>PARAMETER</TaskName>
        <AutoJoin>1</AutoJoin>
        <Parameters>TOOL</Parameters>
        <PrimaryEntity>1</PrimaryEntity>
      </Query>
    </AdditionalQueries>
    <AutoRun>1</AutoRun>
    <ExtractXML>1</ExtractXML>
    <Site>LOCATION</Site>
    <DATABASEQuery>
      <Query>
        <TaskName>DATABASEByDateRange</TaskName>
        <Parameters>PARAMETER NAME</Parameters>
        <Daterange>¤Last_X_Days¤</Daterange>
        <Entity>Entity</¤Entity¤>
        <ExcludeInvalid>0</ExcludeInvalid>
        <ExcludeNonstandard>0</ExcludeNonstandard>
        <ExcludePre>1</ExcludePre>
        <IncludeSubs>1</IncludeSubs>
        <MonitorType>ALL</MonitorType>
      </Query>
    </DATABASEQuery>
    <Technology>TECHNOLOGY</Technology>
    <ToolStyle>DATABASE</ToolStyle>
    <WriteJSL>FILE</WriteJSL>
  </Task>
</WijtInput>
]\";

query_str = Eval Insert(query_template, "¤");
show(query_str);

script = PROGRAM:openDATABASEscript(query_str);

topic How do I use user defined variables to define a database query? in Discussions

How do I use user defined variables to define a database query?

Re: How do I use user defined variables to define a database query?