topic Re: How can I use the text entered on textbox as variables on SQL? in Discussions

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

TamedZebra — Tue, 30 Sep 2025 00:54:10 GMT

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() ) );

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

TamedZebra — Tue, 30 Sep 2025 00:55:04 GMT

My JMP is version 19.

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

jthi — Tue, 30 Sep 2025 06:21:48 GMT

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;

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

TamedZebra — Tue, 30 Sep 2025 06:37:53 GMT

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?

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

Ben_BarrIngh — Tue, 30 Sep 2025 07:26:36 GMT

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

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

TamedZebra — Tue, 30 Sep 2025 07:47:12 GMT

@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.

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

jthi — Fri, 03 Oct 2025 15:51:39 GMT

I don't use application builder but rather write everything myself with JSL. This example application won't perform sql query to database but uses JMP table as the "database" and it is quite simplified version but it can give some general ideas

Names Default To Here(1);

initval = 150;

run_query = Expr(
	months_to_query = neb_months << get;
	dt = New SQL Query(
		Version(130),
		Connection("JMP"),
		JMP Tables(["Big Class" => "\C:\Program Files\JMP\JMPPRO\18\Samples\Data\Big Class.jmp"]),
		QueryName("QueryMonths"),
		Select(Column("name", "t1")),
		From(Table("Big Class", Alias("t1"))),
		Where(
			Custom(
				Eval Insert("t1.weight >= ^months_to_query^"),
				UI(Custom(Base("Continuous"))))
			)
	) << Run Foreground;
	wait(0);
	nw << Close Window;
);

nw = New Window("Query Data",
	V List Box(
		Panel Box("How Long",
			Lineup Box(N Col(2),
				Text Box("Months: "),
				neb_months = Number Edit Box(initval),
			)
		),
		Lineup Box(N Col(2),
			Button Box("OK",
				run_query
			),
			Button box("Cancel",
				nw << close window;
			)
		)
	)
);

Write();

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

TamedZebra — Wed, 08 Oct 2025 05:06:58 GMT

get --> Get Text

I was able to execute the changes mentioned above in JMP 19. I was able to achieve what I wanted. Thank you!!!