SQL

obj<<Custom SQL(sql)

Changes the query to a custom SQL query and sets the SQL.

obj<<Generate SQL

Returns the SQL that the query generates when you run it.

obj<<Modify

Opens the query in Query Builder.

obj<<PostQueryScript(script_as_text)

Sets a JSL script that runs after the query finishes executing.

obj<<Query Name(<newName>)

Gets (without the newName argument) or sets (with the newName argument) the name of the query. The name of the query is used as the name of the data table that results from running the query.

obj<<Run(<private|invisible>, <UpdateTable(table)>, <OnRunComplete(script)>, <OnRunCanceled(script)>, <OnError(script)>

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).

Arguments

private

Optional. Opens the data table that the query produces without displaying it in a data table window.

invisible

Optional. 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.

UpdateTable

Optional. Updates the specified data table. Runs the query in the foreground.

OnRunComplete

Optional. Specifies a script to run after the query is complete.

OnRunCanceled

Optional. Specifies a script to run after the user cancels the query.

OnError

Optional. 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 ) );

Run Background(<OnRunComplete(script), <private|invisible>>, <OnRunCanceled(script)>, <OnError(script)>

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).