topic Re: How to replace the text in JSL thru User Interface(a dialog window) in Discussions

How to replace the text in JSL thru User Interface(a dialog window)

steven_gong — Fri, 20 Mar 2020 08:21:11 GMT

Hi,

I've been using SQL in my JSL, and declared 4 variables and set required value.(see below script)

What I want is to create a new window as user interface.. when run this JSL, pop up the window first to allow user to input different values for the variables.

For example:

1st run JSL, user input: x,y,z,n for the 4 variables, to achieve the effect like below:

set @spec = 'x'
set @opn = 'y'
set @Loss = 'z'

set @day = n

2nd run JSL, user input: a,b,c,m for the 4 variables, to achieve the effect like below:

set @spec = 'a'
set @opn = 'b'
set @Loss = 'c'

set @day = m

=========================================

Script:

Names Default To Here( 1 );
ODBC = Create Database Connection( "___" );
SQL ="
declare @spec varchar(50)
declare @opn varchar(50)

declare @Loss varchar(50)
declare @day int

set @spec = 'xxx'
set @opn = 'yyy'
set @Loss = 'zzz'

set @day = n

select * from dt

I feel it difficult to me since I looked up the community, no found any clue on this.

So I'm make new discussion to ask for help.

Any help will be appreciated.

Looking forward to your help.

Thanks.

Re: How to replace the text in JSL thru User Interface(a dialog window)

txnelson — Fri, 20 Mar 2020 10:02:30 GMT

There are an unlimited ways to accomplish what you want. Below is one simple interface, using your pseudo code. You really need to read the Scripting Guide's sections on using Display Objects

Names Default To Here( 1 );
nw = New Window( "Set Values",
	Lineup Box( N Col( 2 ),
		Text Box( "Set Spec" ),
		specCB = Combo Box( {"x", "y", "z", "n"}, specVal = specCB << get Selected() ),
		Text Box( "Set opn" ),
		opnCB = Combo Box( {"x", "y", "z", "n"}, opnVal = opnCB << get Selected() ),
		Text Box( "Set Loss" ),
		lossCB = Combo Box( {"x", "y", "z", "n"}, lossVal = lossCB << get Selected() ),
		Text Box( "Set day" ),
		dayCB = Combo Box( {"x", "y", "z", "n"}, dayVal = dayCB << get Selected() )
	),
	theOK = Button Box( "OK", 
		// Build the statements to be executed
		theExpr = "ODBC = Create Database Connection( \!"___\!" );
		SQL =\!"
		declare " || specVal || " varchar(50)
		declare " || opnVal || " varchar(50)
		declare " || lossVal || " varchar(50)
		declare " || dayVal || " int

		set " || specVal || " = 'xxx'
		set " ||  opnVal || " = 'yyy'
		set " || lossVal || " = 'zzz'
		set " || dayVal || " = n
		 
		select * from dt
		";
		show(theExpr);
		// run the expression (uncomment when code is real)
		//Eval( Parse( theExpr ) );
		
		nw << close window;
	)
);

Re: How to replace the text in JSL thru User Interface(a dialog window)

steven_gong — Tue, 24 Mar 2020 11:28:20 GMT

Hi txnelson,

Thanks very much for the guidance with complete script.

I've learned the new window with combo box setting to create the user interface with defined variable.

The tricky thing is how to use JSL variable to replace the SQL variable.

I learned similar thing in below link:

Thanks to @pmroz.

and I make my script work as expected.

Here is my final scrip for information.

==============================

Names Default To Here( 1 );
specVal = "";
NW = New Window( "Input Spec",
	<<Modal, 
	<<Return Result,
	
	Text Box( "Set Spec" ),
	specCB = Combo Box( {"DA", "WB", "MOLD"} ), 
	//specCB = Text Edit Box( "WB" ),
	
	
	Button Box( "OK", specVal = specCB << get selected() )
	//Button Box( "OK", specVal = specCB << get text() )
				
);

If( specVal != "", 

	(ODBC = Create Database Connection( "___" ) ;
	SQL = Eval Insert(
		"
DECLARE @SPEC VARCHAR(50)
SET @SPEC = '^specVal^'
SELECT WL.ContainerName,WL.ProductName, WL.SPECNAME
   FROM A_WIPLOT WL
WHERE WL.SPECNAME LIKE CONCAT('%',@SPEC,'%')
ORDER BY WL.SPECNAME
"
	) ; 
	//show(SQL); to see the log to confirm if SQL statement meet the requirement
	DT = Execute SQL( ODBC, SQL, "SPEC_WIP" ) << Maximize window ; 

	SUMMARY = DT << Summary( Group( :SPECNAME ), Freq( "None" ), Weight( "None" ) ) ;
	Close Database Connection( ODBC ) ; 
	
	)
);

Re: How to replace the text in JSL thru User Interface(a dialog window)

steven_gong — Tue, 24 Mar 2020 07:15:17 GMT

I don't know if the subject name can be re-named.

Just for record: the subject should be - how to pass the JSL variable to SQL variable thru User Interface.