Description
Required Argument
sql
The quoted SQL query.
Description
Runs the SQL query in the background or foreground depending on the Query Builder preference “Run queries in the background when possible”.
Returns
Null (if the query runs in the background) or a data table (if the query runs in the foreground).
Optional Named Arguments
"Private"
A quoted keyword that opens the data table that the query produces without displaying it in a data table window. "Private" is available only if OnRunComplete is included in the script.
"Invisible"
A quoted keyword that hides the data table that the query produces. Use this argument to keep the query result hidden but use it in a subsequent query. The data table is displayed in the Home Window’s Window List and the Window > Unhide list.
Update Table
Updates the specified data table. Runs the query in the foreground.
OnRunComplete
Specifies a script to run after the query is complete. To get the resulting data table, include OnRunComplete. The OnRunComplete script needs to be defined in the global namespace, as indicated by the double colons in this example:
Names Default To Here( 1 );
::onComplete = Function( {dt},
{default local},
Write(
"\!NQuery is complete! Result name: \!"",
dt << Get Name,
"\!", Number of rows: ",
N Rows( dt )
)
);
query = Include( "rentals_fam_romcom.jmpquery" );
query << Run Background( On Run Complete( ::onComplete ) );
OnRunCanceled
Specifies a script to run after the user cancels the query.
OnError
Specifies a script to run if an error occurs.
Notes
If you want the data table that results from the background query, use the OnRunComplete optional argument. You can include a script that runs when the query completes and then assigns a data table reference to the resulting data table. Or you might pass the name of a function that accepts a data table as its first argument. That function is called when the query completes.
Examples
The following example opens a query that you previously saved from Query Builder. The query opens privately, that is, without opening Query Builder. The query runs, and the resulting data table opens.
query = Open( "C:\My Data\Movies.jmpquery", "Private");
dt = query << Run();
You can include a .jmpquery file in a script and run the query in the background using the <<Run Background message.
query = Include( "C:\Queries\movies.jmpquery");
query <<Run Background();
The following example queries the database, opens the resulting data table, and prints the number of data table rows to the log.
confirmation = Function( {dtResult},
Write( "\!NNumber of rows in query result: ", N Rows( dtResult ) )
);
query = New SQL Query(
Connection(
"ODBC:DSN=SQL Databases;APP=MYAPP;TrustedConnection=yes;WSID=D79255;DATABASE=SQB;"
),
QueryName( "movies_to_update" ),
Select( Column( "YearMade", "t1" ), Column( "Rating", "t1" ) ),
From( Table( "g6_Movies", Schema( "SQB" ), Alias( "t1" ) ) ),
);
query << Run( OnRunComplete( confirmation ) );
Description
Runs the SQL query in the background. The running query is not displayed.
Returns
Null (or the data table object, if OnRunComplete is included).
Optional Named Arguments
OnRunComplete(script)
Specifies a script to run after the query is complete. To get the resulting data table, include OnRunComplete. The OnRunComplete script needs to be defined in the global namespace, as indicated by the double colons in this example:
Names Default To Here( 1 );
::onComplete = Function( {dt},
{default local},
Write(
"\!NQuery is complete! Result name: \!"",
dt << Get Name,
"\!", Number of rows: ",
N Rows( dt )
)
);
query = Include( "rentals_fam_romcom.jmpquery" );
query << Run Background( On Run Complete( ::onComplete ) );
"Private"
Does not open the resulting data table. Specify only with OnRunComplete. If you include private in a background query, JMP opens the data table as invisible instead.
"Invisible"
Hides the data table. Use this argument to keep the query result hidden but use it in a subsequent query. The data table is displayed in the Home Window’s Window List and the Window > Unhide list.
OnRunCanceled
Specifies a script to run after the user cancels the query.
OnError
Specifies a script to run if an error occurs.
Notes
All queries except for SAS queries run in the background based on the Query Builder preference “Run the queries in the background when possible”, which is selected by default. For SAS queries, Run Background() is ignored.
You can include a .jmpquery file in a script and run the query in the background using the Run Background message.
query = Include( "C:\Queries\movies.jmpquery");
query <<Run Background();
Description
Runs the SQL query in the foreground.
Returns
A data table that opens when the query is finished.
See Also
Run Background(<OnRunComplete(script), <"Private"|"Invisible">>, <OnRunCanceled(script)>, <OnError(script)>)