How can I use the text entered on textbox as variables on SQL?

[#1]

I'm a beginner with application builders and I want to achieve the following: I'm currently retrieving data from a database using SQL with a specific time period. However, instead of directly modifying the SQL code to change the period, I want to be able to change it via a GUI. I'm currently executing this with JSL, and I want to replace the [HERE] part of the SQL query.

Example JSL:

// Get a handle to the data table
dt = Current Data Table();

unique = Function({a}, // input: List, output: List
	{aa=Associative Array(),nr=NItems(a),i,aKeys,nUnique,aUnique={}},
	For( i=1, i<=nr, i++, aa << insert( a[i] ) );
	aKeys = aa << GetKeys;
	nUnique = NItems( aKeys );
	For( i = 1, i <= nUnique, i++, InsertInto(aUnique,aKeys[i]) );
	aUnique
);

dofun = Function({},{months,varName,dType},
	months = SampleRateInput << Get;
	varName = VarList << GetSelected;
	dType = Column(dt,varName[1]) << GetDataType( "English");
	If(dType!="Character",
		Dialog(Title("Alert"),"Data Type of Variable should be Character.    ");
		Throw()
	);
	dt << ClearSelect;
	dt << SelectRows( selIdx );
);

OKButtonPress=Function({this},
	// This function is called when the OK button is pressed
	(thisModuleInstance << Get Box) << Close Window;
	dofun;
);


CancelButtonPress=Function({this},
	// This function is called when the Cancel button is pressed
	(thisModuleInstance << Get Box) << Close Window;
);


New SQL Query(
	Version( 130 ),
	Connection(	),
	QueryName(  ),
	Select(	),
	From(
		Table( ),
	),
	Where(
		Custom(
			"t1.msr_dt >= DATEADD(month, -[HERE], CURRENT_DATE)",
			UI( Custom( Base( "Continuous" ) ) )
		)
	)
) << Run Foreground( UpdateTable( Current Data Table() ) );

[#2]

My JMP is version 19.

[#3]

Do you have your own custom GUI which you wish to use to change the value? Are you able to extract that value? I think you can just evaluate variables within Where(Custom()) block (at least with JMP18)

Names Default To Here(1);

myval = 150;

dt = New SQL Query(
	Version(130),
	Connection("JMP"),
	JMP Tables(["Big Class" => "\C:\Program Files\JMP\JMPPRO\18\Samples\Data\Big Class.jmp"]),
	QueryName("SQLQuery2"),
	Select(Column("name", "t1")),
	From(Table("SQLQuery1", Alias("t1"))),
	Where(
		Custom(
			Eval Insert("t1.weight >= ^myval^"),
			UI(Custom(Base("Continuous"))))
		)
) << Run Foreground;

[#4]

jthi-san, Arigatougozaimasu.

The GUI described in the JSL earlier was a slightly trimmed-down version of the sample application's JSL. The GUI I actually want to create is like the one in the attached image.

I understand that I can pass variables within SQL statements by enclosing them with '^'!

What JSL code should I write to execute a New SQL Query when the OK button in the GUI is pressed?

[#5]

Hi @TamedZebra ,

Unrelated to the JSL side, but have you tried to apply the database Query Builder? It provides a GUI that gives filters to change date ranges.

Thanks,

Ben

[#6]

@Ben_BarrIngh , Arigatougozaimasu.

It is possible for us to save a script on a Data-table, and if the script is written 'SQL query', we can update the Data-table from Data-base by One-Click.

But changing the SQL on the script or Query builder takes a little time and a little effort .

So I woud like to edit SQL by GUI popping up by press a script on Data-table.