Database Trawler Crashing

[#1]

I wrote a script to trawl through available databases to see if a particular lot ID shows up and pull the data.  My approach involves opening a series of files and then closing the empty ones, open to suggestions on how not to need that?  I have one particularly populous database that opens >1000 files and the script appears to crash when attempting to close the empty data tables.  

Names Default To Here(1);
// Defining firstname and lastname string

FirstParameter = "First Parameter";
SecondParameter = "Second Parameter";
LotNumber = "Lot Number";


//changing username variables based on user input

w = New Window("Defining Parameters",
	<<Modal, // opens a window with a title and this content...
	Border Box(top(20), bottom(20), Left(20), Right(20), // window dressing 
		V List Box( // V and H lists nest to organize the display boxes
			H Center Box(Text Box("Nominate Parameters and Lot")), // a second title, centered
			Spacer Box(size(1, 30)), // a little vertical space
			H List Box(Text Box("Parameter 1: "), b1 = Text Edit Box(FirstParameter)), // data entry
			Spacer Box(size(1, 10)), // a little vertical space
			H List Box(Text Box("Parameter 2: "), b2 = Text Edit Box(SecondParameter)), // more data entry
			Spacer Box(size(1, 30)), // a little vertical space
			H List Box(Text Box("Lot: "), b3 = Text Edit Box(LotNumber)), // more data entry
			Spacer Box(size(1, 30)), // a little vertical space
			H Center Box( // center the button
				Button Box("Input", // this script runs when the button is pressed...
					// make a new table with the values...
					b1_val = b1 << get text;
					b2_val = b2 << get text;
					b3_val = b3 << get text;
					ok_btn << Click(1);
				)
			),
			ok_btn = Button Box("OK", <<Visibility("Collapse"))
		)
	)
);

Sites = {"Site1;", "Site2;"};


Data_Bases = {db_A, db_B};

n = N Items( Data_Bases );

db_function = Function( {DataBases},

If(b1_val!="" & b2_val !="",

Try(
Source = Char(Data_Bases[l]);

		sql1 = "select *  from iicolumns
where table_owner='"||Source||"'
and column_name like '%"||b1_val||"%'
order by 1";
		First_History = Open Database( Sites[1], sql1, "First_History" );
		
		sql2 = "select *  from iicolumns
where table_owner='"||Source||"'
and column_name like '%"||b2_val||"%'
order by 1";
		Second_History = Open Database( Sites[1], sql2, "Second_History" );
			
Data Table( "First_History" ) << Join(
	With( Data Table( "Second_History" ) ),
	By Matching Columns( :table_name = :table_name ),
	Drop multiples( 0, 0 ),
	Include Nonmatches( 0, 0 ),
	Preserve main table order( 1 ),
	Output Table( "Paired_History.jmp" )
);		
// Close Data Table: Wafer_History
Close( Data Table( "First_History" ), NoSave );


// Close Data Table: Reticle_History
Close( Data Table( "Second_History" ), NoSave );	
		),
		
		
Try(
	If((b1_val!="" & b2_val =="") | (b1_val=="" & b2_val ==""), (
	Source = Char(Data_Bases[l]);

		sql1 = "select *  from iicolumns
where table_owner='"||Source||"'
and column_name like '%"||b1_val||"%'
order by 1";
		Paired_History = Open Database( Sites[1], sql1, "Paired_History" );)
)););


Data Table( "Paired_History.jmp" ) << New Column("Script Part 1", Character, "Nominal", Formula(If( (As Column(1)) != Lag( As Column(1), 1 ) | As Column(2) != Lag( As Column(2), 1 ),"sql", "")));	

Data Table( "Paired_History.jmp" ) << New Column("Script Part 2a", Numeric, "Nominal", Formula(If( (As Column(1)) != Lag( (As Column(1)), 1 ) | (As Column(2)) != Lag( (As Column(2)), 1 ), Sequence(1, 99999, 1, 1))));

Data Table( "Paired_History.jmp" ) << New Column("Script Part 2", Character, "Nominal", Formula(If(!Is Missing(:Script Part 2a), Char(:Script Part 2a))));
	
Data Table( "Paired_History.jmp" ) << New Column("Script Part 3", Character, "Nominal", Formula(If( (As Column(1)) != Lag( (As Column(1)), 1 ) | (As Column(2)) != Lag( (As Column(2)), 1 ), "=\!"", "")));

Data Table( "Paired_History.jmp" ) << New Column("Script Part 4", Character, "Nominal", Formula(If((As Column(1)) != Lag((As Column(1)), 1) | (As Column(2)) != Lag((As Column(2)), 1), "select *  from " || (As Column(2)) || "." || (As Column(1)) || " where lot = '" || b3_val || "'", "")));
	
Data Table( "Paired_History.jmp" ) << New Column("Script Part 5", Character, "Nominal", Formula(If( (As Column(1)) != Lag( (As Column(1)), 1 ) | (As Column(2)) != Lag( (As Column(2)), 1 ),"\!";", "")));

wait(1);
// Delete column formula: Script Part 1
Data Table( "Paired_History" ):"Script Part 1"n << Delete Formula;


// Delete column formula: Script Part 2
Data Table( "Paired_History" ):"Script Part 2"n << Delete Formula;


// Delete column formula: Script Part 3
Data Table( "Paired_History" ):"Script Part 3"n << Delete Formula;


// Delete column formula: Script Part 4
Data Table( "Paired_History" ):"Script Part 4"n << Delete Formula;


// Delete column formula: Script Part 5
Data Table( "Paired_History" ):"Script Part 5"n << Delete Formula;

Data Table( "Paired_History.jmp" ) << New Column( "Initiation",
		Character,
		"Nominal",
		Formula(
			If( Row() == 1,
				"Names Default To Here(1);Clear Globals();dt=Current Data Table();Sites = {\!"DSN=echits4;Database=echits4;\!", \!"DSN=achits4;Database=achits4;\!"};
	"
			)
		)
	);

Data Table( "Paired_History.jmp" ) << New Column("First Script", Character, "Nominal", Formula(:Script Part 1 || Char(:Script Part 2) || :Script Part 3 || :Script Part 4 || :Script Part 5));
Data Table( "Paired_History.jmp" ) << New Column("Second Script", Character, "Nominal", Formula(If((As Column(1)) != Lag((As Column(1)), 1) | (As Column(2)) != Lag((As Column(2)), 1), "History" || Char(:Script Part 2) || " = Open Database( Sites[1], sql" || Char(:Script Part 2) || ", \!"History " || As Column(2) || "_" || As Column(1) || "\!");", "")));
	
wait(1);

// Delete column formula: Second Script
Data Table( "Paired_History" ):Second Script << Delete Formula;


	Data Table( "Paired_History" ) << Select Columns( :Initiation, :"First Script"n, :"Second Script"n );
	Current Data Table() << Bring Window To Front;
	Wait( 1 );
	Main Menu( "Copy" );
	Close( Data Table( "Paired_History" ) );
	ww = New Window( "Trawl", << Script );
	Wait( 0 );
	Main Menu( "Paste" );
	Main Menu( "Reformat Script" );	
	ed = ww[Script Box(1)];
	Wait(1);
	ed << Set Text(Regex(ed << get text, "[\r\t]+", "\!N", GLOBALREPLACE));
	ed << reformat;
	//ww << Save Text(dirpath||names||".jsl");
	//ww << Close Window();
	Wait(1);	
	ed << Run;
	ed << Close Window();

//Close the Source sheet from the ECN.



// Get a list of all open data tables
dtList = Get Data Table List();

// Loop through each data table
for (i = 1, i <= N Items(dtList), i++,
    dt = dtList[i];
    // Check if the data table is empty
    if (N Rows(dt) == 0,
        // Close the empty data table
        Close(dt, No Save);
    );
)
); 

For( l = 1, l <= n, l++, //Show(l);
	db_function( Data_Bases[l] )
);

[#2]

Is there a specific reason why you are trying to run JSL using formulas and not using separate script?

[#3]

It is actually a separate script (Trawl).  The script is compiled in the columns of the data table, pasted into a script window and executed from there.  If there's a more conventional way that's easier, I'm up for that?

[#4]

Write the script into JSL file and run it from there?

[#5]

The sequence I have involves a JSL script that executes a SQL query to create a data table listing all the available databases.  From there, I auto-generate a follow on JSL script to search the list for my input lot.  I'm sure it's possible but I don't immediately know how to do that with a single JSL?

[#6]

I'm pretty sure if you can do it in JMP columns, you can do it utilizing loops in JSL. It should be much easier to debug and modify as you get the JSL syntax highlighting in script editor.

[#7]

The resulting secondary script has many sequential queries, most of which produce empty tables.  If I were able to close those immediately if empty, I think that would help a lot.  Any tips on how to do that?

Names Default To Here( 1 );
Clear Globals();
dt = Current Data Table();
Sites = {"Site1;", "Sites2;"};
sql1 = "select *  from source.table where lot = 'Lot_ID'";
History1 = Open Database( Sites[1], sql1, "History source.table" );

[#8 · Accepted solution]

If(N Rows(dt) == 0, Close(dt, no save));

Also open your tables as invisible (or private). Open Database does support it even though I would use New SQL Query instead, but Open Database is much simpler

[#9 · Accepted solution]

Jarmo I added that to every query, which immediately closes the empty tables and it appears to be stable now.  Here's the finished result if that helps anyone, not handsome but it works.

Names Default To Here(1);
// Defining firstname and lastname string

FirstParameter = "First Parameter";
SecondParameter = "Second Parameter";
LotNumber = "Lot Number";


//changing username variables based on user input

w = New Window("Defining Parameters",
	<<Modal, // opens a window with a title and this content...
	Border Box(top(20), bottom(20), Left(20), Right(20), // window dressing 
		V List Box( // V and H lists nest to organize the display boxes
			H Center Box(Text Box("Nominate Parameters and Lot")), // a second title, centered
			Spacer Box(size(1, 30)), // a little vertical space
			H List Box(Text Box("Parameter 1: "), b1 = Text Edit Box(FirstParameter)), // data entry
			Spacer Box(size(1, 10)), // a little vertical space
			H List Box(Text Box("Parameter 2: "), b2 = Text Edit Box(SecondParameter)), // more data entry
			Spacer Box(size(1, 30)), // a little vertical space
			H List Box(Text Box("Lot: "), b3 = Text Edit Box(LotNumber)), // more data entry
			Spacer Box(size(1, 30)), // a little vertical space
			H Center Box( // center the button
				Button Box("Input", // this script runs when the button is pressed...
					// make a new table with the values...
					b1_val = b1 << get text;
					b2_val = b2 << get text;
					b3_val = b3 << get text;
					ok_btn << Click(1);
				)
			),
			ok_btn = Button Box("OK", <<Visibility("Collapse"))
		)
	)
);

Sites = {"Site1;", "Site2;"};

//

Data_Bases = {db_A, db_B};

n = N Items( Data_Bases );

db_function = Function( {DataBases},

If(b1_val!="" & b2_val !="",

Try(
Source = Char(Data_Bases[l]);

		sql1 = "select *  from iicolumns
where table_owner='"||Source||"'
and column_name like '%"||b1_val||"%'
order by 1";
		First_History = Open Database( Sites[1], sql1, "First_History" );
		
		sql2 = "select *  from iicolumns
where table_owner='"||Source||"'
and column_name like '%"||b2_val||"%'
order by 1";
		Second_History = Open Database( Sites[1], sql2, "Second_History" );
			
Data Table( "First_History" ) << Join(
	With( Data Table( "Second_History" ) ),
	By Matching Columns( :table_name = :table_name ),
	Drop multiples( 0, 0 ),
	Include Nonmatches( 0, 0 ),
	Preserve main table order( 1 ),
	Output Table( "Paired_History.jmp" )
);		
// Close Data Table: Wafer_History
Close( Data Table( "First_History" ), NoSave );


// Close Data Table: Reticle_History
Close( Data Table( "Second_History" ), NoSave );	
		),
		
		
Try(
	If((b1_val!="" & b2_val =="") | (b1_val=="" & b2_val ==""), (
	Source = Char(Data_Bases[l]);

		sql1 = "select *  from iicolumns
where table_owner='"||Source||"'
and column_name like '%"||b1_val||"%'
order by 1";
		Paired_History = Open Database( Sites[1], sql1, "Paired_History" );)
)););		
		




// Join data tables
// → Data Table( "Untitled 274" )






Data Table( "Paired_History.jmp" ) << New Column("Script Part 1", Character, "Nominal", Formula(If( (As Column(1)) != Lag( As Column(1), 1 ) | As Column(2) != Lag( As Column(2), 1 ),"sql", "")));	

Data Table( "Paired_History.jmp" ) << New Column("Script Part 2a", Numeric, "Nominal", Formula(If( (As Column(1)) != Lag( (As Column(1)), 1 ) | (As Column(2)) != Lag( (As Column(2)), 1 ), Sequence(1, 99999, 1, 1))));

Data Table( "Paired_History.jmp" ) << New Column("Script Part 2", Character, "Nominal", Formula(If(!Is Missing(:Script Part 2a), Char(:Script Part 2a))));
	
Data Table( "Paired_History.jmp" ) << New Column("Script Part 3", Character, "Nominal", Formula(If( (As Column(1)) != Lag( (As Column(1)), 1 ) | (As Column(2)) != Lag( (As Column(2)), 1 ), "=\!"", "")));

Data Table( "Paired_History.jmp" ) << New Column("Script Part 4", Character, "Nominal", Formula(If((As Column(1)) != Lag((As Column(1)), 1) | (As Column(2)) != Lag((As Column(2)), 1), "select *  from " || (As Column(2)) || "." || (As Column(1)) || " where lot = '" || b3_val || "'", "")));
	
Data Table( "Paired_History.jmp" ) << New Column("Script Part 5", Character, "Nominal", Formula(If( (As Column(1)) != Lag( (As Column(1)), 1 ) | (As Column(2)) != Lag( (As Column(2)), 1 ),"\!";", "")));

wait(1);
// Delete column formula: Script Part 1
Data Table( "Paired_History" ):"Script Part 1"n << Delete Formula;


// Delete column formula: Script Part 2
Data Table( "Paired_History" ):"Script Part 2"n << Delete Formula;


// Delete column formula: Script Part 3
Data Table( "Paired_History" ):"Script Part 3"n << Delete Formula;


// Delete column formula: Script Part 4
Data Table( "Paired_History" ):"Script Part 4"n << Delete Formula;


// Delete column formula: Script Part 5
Data Table( "Paired_History" ):"Script Part 5"n << Delete Formula;

Data Table( "Paired_History.jmp" ) << New Column( "Initiation",
		Character,
		"Nominal",
		Formula(
			If( Row() == 1,
				"Names Default To Here(1);Clear Globals();dt=Current Data Table();Sites = {\!"DSN=echits4;Database=echits4;\!", \!"DSN=achits4;Database=achits4;\!"};
	"
			)
		)
	);

Data Table( "Paired_History.jmp" ) << New Column("First Script", Character, "Nominal", Formula(:Script Part 1 || Char(:Script Part 2) || :Script Part 3 || :Script Part 4 || :Script Part 5));
Data Table( "Paired_History.jmp" ) << New Column("Second Script", Character, "Nominal", Formula(If((As Column(1)) != Lag((As Column(1)), 1) | (As Column(2)) != Lag((As Column(2)), 1), "History" || Char(:Script Part 2) || " = Open Database( Sites[1], sql" || Char(:Script Part 2) || ", \!"History " || As Column(2) || "_" || As Column(1) || "\!");", "")));
Data Table( "Paired_History.jmp" ) << New Column("Close Empty", Character, "Nominal", Formula(If((As Column(1)) != Lag((As Column(1)), 1) | (As Column(2)) != Lag((As Column(2)), 1),"Try(if (N Rows(")));
Data Table( "Paired_History.jmp" ) << New Column("Close Empty2", Character, "Nominal", Formula(If((As Column(1)) != Lag((As Column(1)), 1) | (As Column(2)) != Lag((As Column(2)), 1), "History" || Char(:Script Part 2))));
Data Table( "Paired_History.jmp" ) << New Column("Close Empty3", Character, "Nominal", Formula(If((As Column(1)) != Lag((As Column(1)), 1) | (As Column(2)) != Lag((As Column(2)), 1),") == 0, Close(")));
Data Table( "Paired_History.jmp" ) << New Column("Close Empty4", Character, "Nominal", Formula(If((As Column(1)) != Lag((As Column(1)), 1) | (As Column(2)) != Lag((As Column(2)), 1),"History" || Char(:Script Part 2)||", No Save)));")));
Data Table( "Paired_History.jmp" ) << New Column("Close", Character, "Nominal", Formula(:Close Empty||:Close Empty2||:Close Empty3||:Close Empty4));
	
wait(1);

// Delete column formula: Second Script
Data Table( "Paired_History" ):Second Script << Delete Formula;
Data Table( "Paired_History" ):Close << Delete Formula;


	Data Table( "Paired_History" ) << Select Columns( :Initiation, :"First Script"n, :"Second Script"n, :Close );
	Current Data Table() << Bring Window To Front;
	Wait( 1 );
	Main Menu( "Copy" );
	Close( Data Table( "Paired_History" ) );
	ww = New Window( "Trawl", << Script );
	Wait( 0 );
	Main Menu( "Paste" );
	Main Menu( "Reformat Script" );	
	ed = ww[Script Box(1)];
	Wait(1);
	ed << Set Text(Regex(ed << get text, "[\r\t]+", "\!N", GLOBALREPLACE));
	ed << reformat;
	//ww << Save Text(dirpath||names||".jsl");
	//ww << Close Window();
	Wait(1);	
	ed << Run;
	ed << Close Window();


); 

For( l = 1, l <= n, l++, //Show(l);
	db_function( Data_Bases[l] )
);