How to show modal window which running script after it is open?

[#1]

I spend some time created a jsl script, which provide user interface to specify some criteria and pull data from Amazon Redshift based on user input. The script is working well but having a small issue.

Once user clicks on query button on a modal window, it set a variable isValid = 1, close itself to execute subsequent script, which will then create a progress window that display message like "pulling data, please wait..." and starting the query on open. It should close itself after the query is done.

The problem is, this progress window cannot be see (not showing up) on my screen. It seems like JMP run the query script immediately after the window was created before it is being bring to front, although the query is successful. 

I try to insert wait(0) or bring window to front before query script, still no luck. I can only see the progress window only if I remark the query part.

What I can do to make the window show up and running query script at the same time?

/*scripts before this line are not related to the issue, hence not included*/


If(isValid == 1, 
	nwQuery = New Window( "Query", <<Show Menu( 0 ), <<Show Toolbars( 0 ), << Modal,
		Lineup Box(N Col(2), Busy Light(<< Automatic, size(40,40)), Text Box("Pulling data, please wait...")),
		bbOk = Button Box("Ok", << Visibility("Collapse"), << Set Function(Function({thisBox},
			Wait(0);
            /* some action in preparing query criteria 
               window show up if remark below lines */
			mySQL = Eval Insert(tpSQL);
			dbc = Create Database Connection("DSN=Amazon Redshift; UID=***; PSW=***");
			dt = Execute SQL (dbc, mySQL, "Query");
			close database connection(dbc);
            // add wait(2); if remark above lines
			bbOk << Close Window;			
		))),
        bbOk << Click;
	);
);

[#2 · Accepted solution]

You don't need to use modal window to show that query is running. Use normal window, open it before the query starts and close it when the query finishes.

Names Default To Here(1);
isValid = 1;

If(isValid == 1,
	nw = New Window("Query",
		<<Show Menu(0),
		<<Show Toolbars(0),
		Lineup Box(N Col(2), Busy Light(<<Automatic, size(40, 40)), Text Box("Pulling data, please wait...")),
	);
	wait(0);

	mySQL = Eval Insert(tpSQL);
	dbc = Create Database Connection("DSN=Amazon Redshift; UID=***; PSW=***");
	dt = Execute SQL(dbc, mySQL, "Query");
	Close Database Connection(dbc);
	
	nw << Close Window();
	
);

Also I suggest you move from Create Database connection, Execute SQL, Close database connection to New SQL Query as it is the preferred method in for queries (it is more annoying to use, has lacking documentation but still... it is the way going forward).

Edit:

If you decide to move to New SQL Query() remember to use << Run Foreground with it IF you wish the script wait for the query to finish.

[#3]

The reason to use modal window is it will be display on the center of screen and it suppose to be show on top by default. I tried normal window before and it is not show up too.

Anyway, I'll go and gather more info and explore the new sql query method. Will reply you again after that.

Much appreciated for your valuable opinion and suggestion, thanks!

[#4]

After some try and error, I figure out some code to make the script behavior in the way that I want.

As your suggestion, I make use of normal window to show that query is running + New SQL Query function. Since the normal window will not popup at the center of screen, I create a function to capture monitor screen size (don't want to call external power shell script for that) then calculate where it should be appear. 

However, there is a small shortcoming -- the busy light box does not spin when query is running -- nothing we can do about it, right?

Names Default To Here(1);

GetWinConsoleSize = Function({},
	nW = New Window("Get Win Console Size", << Window View("Invisible"), << Maximize Window(1));
	theSize = nW << Get Window Size();
	nW << Close Window();
	Return(theSize);
);

drawWinProgress = Function({scrSize},
    xSize = 250;
    ySize = 50;
	cpX = (scrSize[1]/2)-(xSize/2);
	cpY = (scrSize[2]/2)-(ySize/2);
	nW = New Window("Query Data", << Show Menu(0), << Show Toolbars(0), << Size(xSize,ySize), << Show Window (0),
		Lineup Box(N Col(2), Busy Light(<< Automatic, size(40,40)), Text Box("  Pulling data, please wait ...")),
	);
	nW << Move Window(cpX, cpY);
	nW << Show Window (1);
	Return(nW);
);

nwMain = New Window("UI for user to input query criteria", << Modal, << Return Result,
  // All display box goes here
	H List Box(
		Spacer Box(),
		bbQuery  = Button Box("Query",  
		isValid = 1;
		bbQuery << Close Window;
		,
		isValid = 0;
		bbCancel = Button Box("Cancel", Stop()),
	),
);

If(isValid == 1, 
    wcSize = GetWinConsoleSize();
	wProg = drawWinProgress(wcSize);
	Wait(0);
    // Generating SQL based on user criteria
    dt = New SQL Query(Connection(strOdbcConnection), CustomSQL(mySQL)) << Run Foreground;
    wProg << Close Window;
);

[#5]

You could use << Run Background but it would come with a lot of different problems you would have to solve.

[#6]

Also I suggest you move from Create Database connection, Execute SQL, Close database connection to New SQL Query as it is the preferred method in for queries (it is more annoying to use, has lacking documentation but still... it is the way going forward).

I use create database connection/execute sql/close database connection because often I have numerous SQL queries to execute.  There is overhead involved to create the database connection.  Calling New SQL Query multiple times could be slower.

I'm curious why you think New SQL Query is the way forward?

[#7]

My suggestion is based on this post Which way to go query database? and the comment by Brian. I don't really like using New SQL Query() due to many many problems it has but still, I'm moving more and more of my scripts to use it to hopefully future-proof my scripts.

[#8]

I use new sql query() all the time, because functionality is the best for most of my scripts, and JMP recommends it as "right way". Perhaps it would be worth to write a list of Pro's and Con's and use this as a hint for future development, e.g. put it to the wish list. Why not to enhance new sql query() for more than one statement.

Perhaps we can talk about in Scripters Club.

Best regards,

[#9]

We have at least one wish list item improve documentation on "new sql query()" but there are definitely few more that would be needed. Collecting the frustrations and issues is a good idea (I think I have made some posts regarding those earlier).