843187_en-US

liliconcom

Database Trawler Crashing

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></MODAL>

Automation and ScriptingRe: Database Trawler Crashing

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

Re: Database Trawler Crashing

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?

Re: Database Trawler Crashing

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

Re: Database Trawler Crashing

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?

Re: Database Trawler Crashing

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.

Re: Database Trawler Crashing

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" );

Re: Database Trawler Crashing

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

 

Re: Database Trawler Crashing

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></MODAL>