topic Re: Create UI to help build sql queries in Discussions

Create UI to help build sql queries

Tom_P — Fri, 09 Jun 2023 19:57:51 GMT

I'm looking to create a JMP UI where a user selects a program to run and adds in some data filter options to the end [after where statement] or middle [from statement] of an SQL query. My first problem is that i think i'm assigning the user entered/selected data incorrectly to a new variable (see row 45--->DataParam_1). Then i'm not sure how to assign the filter selection statements ("AND ....") and merge it all back into one larger program SQL statement. I tried to take all of the user entered data and pass it back to a function called SUB_QUERY_1, manipulate it there and finally pass it to a the final query function SQL_Query_1 but this appears to not work.

Clear Log();

SUB_QUERY_1 = Function( {Company_1, Company_2, Product_1, Product_2, Program_1, Program_2}, 
	//create query substatements for sql insertion after the main WHERE statement
	code_1 = If(
		(Company_1 != 0) & (Company_2 != 0), Eval Insert( "AND (COMPANY LIKE '%^Company_1^%' OR COMPANY LIKE '%^Company_2^%))" ),
		(Company_1 != 0), Eval Insert( "AND COMPANY LIKE '%^Company_1^%')" )
	);
		
	code_2 = If(
		(Product_1 != 0) & (Product_2 != 0), Eval Insert( "AND (PRODUCT LIKE '%^Product_1^%' OR PRODUCT LIKE '%^Product_2^%))" ),
		(Product_1 != 0), Eval Insert( "AND PRODUCT LIKE '%^Product_1^%')" )
	);

	Show( code_1, code_2 );	
	//based on selected checkbox, run the query associated and insert code_1,code_2 at end of sql statement
	If(
		Program_1 != 0, SQL_Query_1( code_1, code_2 ),
		Program_2 != 0, SQL_Query_2( code_1, code_2 )
	);
);

//Final query to run based on altered info from user-interface & SUB_QUERY function
SQL_Query_1 = Function( {code_1, code_2},
	sql_statement_1 = Eval Insert( "Select	*
    from TABLE.DATA
    WHERE SCANDATETIME > (SYSDATE - 30)" + code_1 + code_2 );
	Show( sql_statement_1 );
	Open Database( "DSN=xxx;UID=xxx;PWD=xxx;", sql_statement_1, "Table1" );
);

//SQL_Query_2 will be copy of SQL_Query_1 with different sql statement
//ignore that it is missing for the moment

//user-interface
ww = New Window( "Epi JMP Data Query",
	<<Modal,
	<<return result,
	V List Box( Spacer Box( Size( , 10 ) ) ),
	H List Box(
		Border Box( Sides( 15 ),
			V List Box(
				Text Box( "Select Program to Run:" ), 
				//User selects program to run
				DataParam_1 = Check Box( "Option_1", Program_1 = DataParam_1 << Get ), //am i assigning DataParam_1 user input to Program_1 incorrectly
				DataParam_2 = Check Box( "Option_2", Program_2 = DataParam_2 << Get )  //multiplying out mistake?
			)
		),
		Spacer Box( Size( 25, 10 ) ),
		V List Box(
			Align( right ),
			Text Box( "Data Filter" ),
			Spacer Box( Size( , 10 ) ),
			Text Box( "Company:" ),
			Spacer Box( Size( , 10 ) ),
			Text Box( "Customer:" ),
			Spacer Box( Size( , 10 ) ), 

		),
		Panel Box( "",
			V List Box(
				H List Box(
				//User selects sql query filters to apply
					comboObj1 = Combo Box( {"", "AAA", "BBB", "CCC"}, Company_1 = comboObj1 << GetSelected ),
					comboObj2 = Combo Box( {"", "AAA", "BBB", "CCC"}, Company_2 = comboObj2 << GetSelected )
				),
				H List Box(
					comboObj3 = Combo Box( {"", "Prod1", "Prod2"}, Product_1 = comboObj3 << GetSelected ),
					comboObj4 = Combo Box( {"", "Prod1", "Prod2"}, Product_2 = comboObj4 << GetSelected )
				), 

			), 

		), 

	),
	V List Box(
		Spacer Box( Size( , 10 ) ),
		H List Box( Spacer Box( Size( , 1 ) ) ),
		V List Box(
			H List Box(
				Ok_btn = Button Box( "OK", SUB_QUERY_1( Company_1, Company_2, Product_1, Product_2, Program_1, Program_2 ) ),
				cancel_btn = Button Box( "Cancel" )
			)
		)
	)
);

Re: Create UI to help build sql queries

ErraticAttack — Wed, 29 Sep 2021 11:50:48 GMT

@Tom_P , I've created a sample script here that uses explicit scope control to keep track of UI elements and provide a more succinct callback mechanism. This is an example script and has a bunch of boiler-plate needed. If you find yourself following this method and making many interactive UI scripts, I advise to create a library to perform the boiler-plate code automatically (JSL has a wonderful ability to use meta-programming to modify scripts before they're run).

The below method allows for persistence of the inputs as well as protection on the callbacks.

/* SQL Query */

self = New Namespace();
self:tool name = "SQL Setup";

Eval( Eval Expr(

self:initialize = Function( {},
	self = Namespace( Expr( self << Get Name ) );
	If( File Exists( "$TEMP/SQL_Setup.jsl" ),
		self:settings = Include( "$TEMP/SQL_Setup.jsl" );
	,
		self:settings = [=>];
	);
	self:available companies = {"", "AAA", "BBB", "CCC"};
	self:available products = {"", "Prod1", "Prod2"};
	1
);

self:content = Function( {},
	V List Box(
		H List Box(
			Panel Box( "Select Program to Run:",
				V List Box(
					self:DataParam 1 = self:Check Box( {"Option_1"}, "dataparam 1", "single select(this, 1)" )
				,
					self:DataParam 2 = self:Check Box( {"Option_2"}, "dataparam 2", "single select(this, 2)" )
				)
			)
		,
			Panel Box( "Data Filter",
				Lineup Box( N Col( 3 ), Spacing( 3 ),
					V List Box( Align( "Right" ), Text Box( "Company:" ) )
				,
					self:company 1 = self:Combo Box( self:available companies, "company 1" )
				,
					self:company 2 = self:Combo Box( self:available companies, "company 2" )
				,
					V List Box( Align( "Right" ), Text Box( "Customer:" ) )
				,
					self:product 1 = self:Combo Box( self:available products, "product 1" )
				,
					self:product 2 = self:Combo Box( self:available products, "product 2" )
				)
			)
		)
	,
		H List Box(
			self:Button Box( "OK", "run sql" )
		,
			Spacer Box( <<Set Auto Stretching( 1, 0 ) )
		,
			self:Button Box( "Cancel" )
		)
	)
);

self:finalize = Function( {},
	self = Namespace( Expr( self << Get Name ) );
	self:single select;
	1
);

self:single select = Function( {this = 0, index = 0},
	{Default Local},
	self = Namespace( Expr( self << Get Name ) );
	If( {self:DataParam 1, self:DataParam 2} << Get( 1 ) == {1, 1},
		If( index == 1,
			self:DataParam 2 << Set( 1, 0, Run Script( 1 ) )
		,
			self:DataParam 1 << Set( 1, 0, Run Script( 1 ) )
		)
	,
		{self:DataParam 1, self:DataParam 2} << Get( 1 ) == {0, 0},
		Try( this << Set( 1, 1, Run Script( 1 ) ), self:DataParam 1 << Set( 1, 1, Run Script( 1 ) ) );
	);
);

self:button box = Function( {name, callback = 1},
	{Default Local},
	self = Namespace( Expr( self << Get Name ) );
	Eval( Parse( Eval Insert( "\[
	box = Button Box( name, <<Set Function(
		Function( {this},
			{Default Local},
			self = Namespace( "^self << Get Name^" );
			callback = "^callback^";
			If( callback != "1",
				Try( Eval( Parse( "self:" || callback ) ), Show( exception_msg ) )
			)
		)
	) )
	]\" ) ) );
);

self:combo box = Function( {values, name, callback = 1, default = 1},
	{Default Local},
	self = Namespace( Expr( self << Get Name ) );
	Eval( Parse( Eval Insert( "\[
	box = Combo Box( values, <<Set Function(
		Function( {this, index},
			{Default Local},
			self = Namespace( "^self << Get Name^" );
			name = "^name^";
			callback = "^callback^";
			self:settings[name] = this << Get Selected;
			If( callback != "1",
				Try( Eval( Parse( "self:" || callback ) ), Show( exception_msg ) )
			)
		)
	) )
	]\" ) ) );
	If( self:settings << Contains( name ),
		index = Contains( values, self:settings[name] );
		box << Set( index, 0 )
	,
		box << Set( default, 0 )
	);
	box
);

self:check box = Function( {values, name, callback = 1, default = {}},
	{Default Local},
	self = Namespace( Expr( self << Get Name ) );
	Eval( Parse( Eval Insert( "\[
	box = Check Box( values, <<Set Function(
		Function( {this, index},
			{Default Local},
			self = Namespace( "^self << Get Name^" );
			name = "^name^";
			callback = "^callback^";
			self:settings[name] = this << Get Selected;
			If( callback != "1",
				Try( Eval( Parse( "self:" || callback ) ), Show( exception_msg ) )
			)
		)
	) )
	]\" ) ) );
	If( self:settings << Contains( name ),
		N = N Items( self:settings[name] );
		vals = self:settings[name];
		For( i = 1, i <= N, i++,
			index = Contains( values, vals[i] );
			box << Set( index, 1, 0 )
		)
	,
		N = N Items( default );
		For( i = 1, i <= N, i++,
			box << Set( i, 1, 0 )
		);
	);
	box
);

self:destroy = Function( {},
	self = Namespace( Expr( self << Get Name ) );
	Save Text File( "$TEMP/SQL_Setup.jsl", Char( self:settings ) );
	1
);

self:run sql = Function( {},
	{Default Local},
	self = Namespace( Expr( self << Get Name ) );
	{company_1, company_2, product_1, product_2} = {self:company 1, self:company 2, self:product 1, self:product 2} << Get Selected;
	program_1 = self:DataParam 1 << Get( 1 );
	program_2 = self:DataParam 2 << Get( 1 );
	code_1 = If( (Company_1 != "") & (Company_2 != ""),
		Eval Insert( "AND (COMPANY LIKE '%^Company_1^%' OR COMPANY LIKE '%^Company_2^%))" )
	,
		(Company_1 != ""),
		Eval Insert( "AND COMPANY LIKE '%^Company_1^%')" )
	);
		
	code_2 = If( (Product_1 != "") & (Product_2 != ""),
		Eval Insert( "AND (PRODUCT LIKE '%^Product_1^%' OR PRODUCT LIKE '%^Product_2^%))" )
	,
		(Product_1 != ""),
		Eval Insert( "AND PRODUCT LIKE '%^Product_1^%')" )
	);

	Show( code_1, code_2 );	
	//based on selected checkbox, run the query associated and insert code_1,code_2 at end of sql statement
	If(
		Program_1 != 0, self:SQL_Query_1( code_1, code_2 )
	,
		Program_2 != 0, self:SQL_Query_2( code_1, code_2 )
	);
);

self:SQL_Query_1 = Function( {code_1, code_2},
	{Default Local},
	self = Namespace( Expr( self << Get Name ) );
	sql_statement_1 = Eval Insert( "Select	*
    from TABLE.DATA
    WHERE SCANDATETIME > (SYSDATE - 30)" + code_1 + code_2 );
	Show( sql_statement_1 );
	Open Database( "DSN=xxx;UID=xxx;PWD=xxx;", sql_statement_1, "Table1" );
);

) );

Eval( Eval Expr(

self:initialize();
New Window( self:tool name,
	<<Modal,
	<<On Close(
		self = Namespace( Expr( self << Get Name ) );
		Try( self:destroy );
		self << Delete Namespace();
		1
	)
,
	<<On Open(
		self = Namespace( Expr( self << Get Name ) );
		self:window = Current Window();
		self:finalize();
		Print( self << Get Name );
		1
	)
,
	self:content
);

) );

Re: Create UI to help build sql queries

Tom_P — Wed, 29 Sep 2021 13:13:12 GMT

Thanks a million ErraticAttach

When i substitute back in some of my official information, i get a few error messages:

Upon hitting run and UI pops up, the embedded log displays:

The user-defined function "Button Box" conflicts with the JMP function "Button Box". Use a scoped reference (for example, Global:Button Box or Here:Button Box) to call your user-defined function. Other uses of "Button Box" in your script source will continue to call the JMP function.
The user-defined function "Combo Box" conflicts with the JMP function "Combo Box". Use a scoped reference (for example, Global:Combo Box or Here:Combo Box) to call your user-defined function. Other uses of "Combo Box" in your script source will continue to call the JMP function.
The user-defined function "Check Box" conflicts with the JMP function "Check Box". Use a scoped reference (for example, Global:Check Box or Here:Check Box) to call your user-defined function. Other uses of "Check Box" in your script source will continue to call the JMP function.
"#38"

Then i selected Program 1 and hit OK and i see:

1st pop-up: [Microsoft][ODBC Driver Manager] Invalid string or buffer length

2nd pop-up: Consult the log for the SQL statement that failed

Log reads:

code_1 = "AND COMPANY LIKE '%AAA%')";
code_2 = "AND PRODUCT LIKE '%Prod1%')";
sql_statement_1 = "";

Invalid SQL statement:

{Button( 1 )}

Any ideas on how to overcome this lack of an sql_statement?

Re: Create UI to help build sql queries

pmroz — Wed, 29 Sep 2021 14:22:42 GMT

This approach is a bit simpler from a UI point of view. Functions are useful but for this example I don't think you need them. Here's the dialog box:

Note that the selection of which program to run is now a radio box. So instead of choosing options for program 1 and options for program 2 it serves a more singular purpose.

Here's the code:

program_list = {"Option 1", "Option 2"};
company_list = {"AAA", "BBB", "CCC"};
product_list = {"Product 1", "Product 2"};

nw = new window("Option 1", << modal,
	panel box("Select Program to run:",
		program_rb = radio box(program_list),
	),
	panel box("Select Company",
		hlistbox(
			comp_lb = list box(company_list),
			vlistbox(
				comp_right_bb = button box("", << set icon("Next"),
					<< set function(
						function({this},
							comp_selected_lb << append(comp_lb << get selected);
						),
					),
				),
				comp_left_bb  = button box("", << set icon("Prev"),
					<< set function(
						function({this},
							comp_selected_lb << remove selected;
						),
					),
				),
			),
			comp_selected_lb = list box({}),
		),
	),
	panel box("Select Product",
		hlistbox(
			prod_lb = list box(product_list),
			vlistbox(
				prod_right_bb = button box("", << set icon("Next"),
					<< set function(
						function({this},
							prod_selected_lb << append(prod_lb << get selected);
						),
					),
				),
				prod_left_bb  = button box("", << set icon("Prev"),
					<< set function(
						function({this},
							prod_selected_lb << remove selected;
						),
					),
				),
			),
			prod_selected_lb = list box({}),
		),
	),
	panel box("Actions",
		hlistbox(
			button box("OK", 
				continue_flag = 1;
				selected_company_list = comp_selected_lb << get items;
				selected_product_list = prod_selected_lb << get items;
			),
			button box("Cancel", continue_flag = 0),
		),
	),
);

if (continue_flag,
//	print(selected_company_list);
//	print(selected_product_list);

	company_sql = "";
	for (i = 1, i <= nitems(selected_company_list), i++,
		one_company = selected_company_list[i];
		if (i == 1,
			company_sql = evalinsert(" AND (COMPANY LIKE '%^one_company^%'");
			,
		// else
			company_sql = company_sql || evalinsert(" OR COMPANY LIKE '%^one_company^%'");
		);
	);
	company_sql = company_sql || ")";
//	print(company_sql);

	product_sql = "";
	for (i = 1, i <= nitems(selected_product_list), i++,
		one_product = selected_product_list[i];
		if (i == 1,
			product_sql = evalinsert(" AND (PRODUCT LIKE '%^one_product^%'");
			,
		// else
			product_sql = product_sql || evalinsert(" OR PRODUCT LIKE '%^one_product^%'");
		);
	);
	product_sql = product_sql || ")";
//	print(product_sql);

	sql_statement = Eval Insert( 
"Select *
   from TABLE.DATA
  WHERE SCANDATETIME > (SYSDATE - 30)
    ^company_sql^
    ^product_sql");
	print( sql_statement );
//	Open Database( "DSN=xxx;UID=xxx;PWD=xxx;", sql_statement_1, "Table1" );
);

Re: Create UI to help build sql queries

ErraticAttack — Wed, 29 Sep 2021 15:46:08 GMT

I was using the functions as you'd defined them. It looks like you need to concat the strings together by replacing line 195 with WHERE SCANDATETIME > (SYSDATE - 30) " || code_1 || " " || code_2 );

The popup happens because the database has phony values.

The log about redefining the builtins happens because the function names mirror the builtins. You can change the function names if you don't want the log messages.

Re: Create UI to help build sql queries

Tom_P — Wed, 29 Sep 2021 17:27:39 GMT

Yes it now runs properly with

WHERE SCANDATETIME > (SYSDATE - 365)" || "^code_1^" || "^code_2^"

Re: Create UI to help build sql queries

Tom_P — Wed, 29 Sep 2021 17:49:08 GMT

@pmroz

I really appreciate the help and very much like your idea as well

When i run the script as-is, the embedded log does not pick up on my selections and instead shows emptiness even though i made selections

"Select *
   from TABLE.DATA
  WHERE SCANDATETIME > (SYSDATE - 30)
    )
    )"

How can this be remedied?

Re: Create UI to help build sql queries

Tom_P — Wed, 29 Sep 2021 18:03:25 GMT

@pmroz

My apologies

This worked perfectly and i clicked on the selections but did not move them to the right sections to "choose" their value.

Sorry for the mixed message.

Another great solution!