topic Re: insert string from textbox in SQL statement in JSL script in Discussions

insert string from textbox in SQL statement in JSL script

CSkjodt — Fri, 09 Jun 2023 22:10:06 GMT

Hi, I am taking my first stab at a jmp application.

I would like to insert a string from a textbox in a SQL statement used for a JMP query to a database. This string is my search parameter.

The script I have made does not seem to work and the solution is probably straight forward - but I am missing some basic JSL syntax understanding and was hoping to get help getting past this, for me, crucial point.

The script for calling and getting the string:

parameterScript = Function( {this},
    {text}, 
// This function is called when the Text Edit Box value is committed
    input = this << Get Text
);

The script for querying the database:

importDataPress = Function( {this}, 
// This function is called when the button is pressed
    name = New SQL Query(
        Connection( "ODBC:DSN=DATABASE;PWD=XXX;" ),
        QueryName( "my_table" ),
        CustomSQL( "SELECT * FROM DATABASE WHERE parameter == \!" + input + \!";" )
    ) << Run;
    <<Get Button Name;
);

Thank you !

Re: insert string from textbox in SQL statement in JSL script

jthi — Wed, 31 Mar 2021 09:35:59 GMT

You should start this by building smaller blocks and learning the syntax (how to concatenate strings in JMP, how namespaces/functions work, and so on)

Some material suggestions:

JSL Syntax Reference

JMP15 Scripting Guide

Scriping Index found from JMP Help menu

Very short example (check log when you change text in the window):

Names Default To Here(1);

importDataPress=Function({this},
	retVal = this << get text;
	sqlString = "select * from * where " || retVal;
	Show(sqlString);
);

win = New Window("Example", Text Edit Box("Example Text", 
	<< set function(
		function({this},
			importDataPress(this)
		)
	))
);

Re: insert string from textbox in SQL statement in JSL script

pmroz — Fri, 02 Apr 2021 12:54:22 GMT

Try something like this. Note the use of evalinsert, panelbox, hlistbox, etc. Look them up in the scripting index for more information.

importDataPress = Function( {parameter_value}, 
	sql_statement = evalinsert(
"SELECT * FROM DATABASE WHERE parameter = '^parameter_value^'");

	show(sql_statement);
// This function is called when the button is pressed
    name = New SQL Query(
        Connection( "ODBC:DSN=DATABASE;PWD=XXX;" ),
        QueryName( "my_table" ),
        CustomSQL( sql_statement )
    ) << Run;
);

nw = new window("Example Input for SQL",
	panelbox("Parameter Specification",
		hlistbox(
			text box("Enter value for parameter: "),
			teb = text edit box("", << set width(200)),
		),
	),
	panelbox("Actions",
		hlistbox(
			button box("Cancel", nw << close window),
			button box("OK",
				pvalue = teb << get text;
				if (pvalue == "",
					ew = new window("Error", << modal,
						text box("Nothing specified")
					);
					,
					// else import the data
					importdatapress(pvalue);
					nw << close window;
				);
			),
		),
	),
);

Re: insert string from textbox in SQL statement in JSL script

CSkjodt — Tue, 06 Apr 2021 12:33:20 GMT

Thank you so much, this did the trick :)