Subscribe Bookmark RSS Feed

Modal SQL Query Script

vkessler

Community Trekker

Joined:

Dec 23, 2015

Hello,

i´m wondering if it is possbile to add a "modal behaviour" to a SQL query, when the query is scripted. Here is my example:

 

***** Script Start *****

 

New SQL Query(
    Connection(...),
    Select(...),
    From(...)
) << Modify << Modal;

 

doStuffWithTheTableFromTheQuery();

 

***** Script End *****

 

I know that "<< Modal" doens´t work. I also know that there is a PostQueryScript(). However, i rather want to write my script without it. Is there a way to make the SQL Query modal?

 

Thank you!

1 ACCEPTED SOLUTION

Accepted Solutions
Eric_Hill

Staff

Joined:

Oct 1, 2013

Solution

Hey, vkessler,

 

There is not a way to make the Query Builder window (what comes up when you pass Modify to a query object) modal.  However, if what you want your user to be able to change is restricted to just the filter part of the query, you can make your query have a prompted filter, and then, instead of doing Modify, do Run Foreground.  The filter prompt *is* modal, so the user would be prompted to update the filter, and then the query would run to completion (thanks to "Foreground") before your next line of JSL runs.

If prompting for the filter seems like enough and you don't know how to do it, let me know and I can get started.

 

Thanks,

 

Eric

7 REPLIES
txnelson

Super User

Joined:

Jun 22, 2012

Can you provide the reason that you want the SQL query to be modal?

Jim
vkessler

Community Trekker

Joined:

Dec 23, 2015

The issue is, that the script proceeds before the query was executed. Hence, "doStuffWithTheTableFromTheQuery()" fails, since there is no table. I have a workaround with a while-loop, where the existence of the table is checked before the script continues, but i was wondering if there is another solution like a modal SQL query.

pmroz

Super User

Joined:

Jun 23, 2011

Try putting wait(0); after your query.  Should allow the query to finish before continuing to the next step.

Eric_Hill

Staff

Joined:

Oct 1, 2013

Solution

Hey, vkessler,

 

There is not a way to make the Query Builder window (what comes up when you pass Modify to a query object) modal.  However, if what you want your user to be able to change is restricted to just the filter part of the query, you can make your query have a prompted filter, and then, instead of doing Modify, do Run Foreground.  The filter prompt *is* modal, so the user would be prompted to update the filter, and then the query would run to completion (thanks to "Foreground") before your next line of JSL runs.

If prompting for the filter seems like enough and you don't know how to do it, let me know and I can get started.

 

Thanks,

 

Eric

vkessler

Community Trekker

Joined:

Dec 23, 2015

Hey Eric,

thanks for your advice. With a prompted filter the query does become modal. However, this doesn´t solve my problem unfortunately. This is because the actual download from the database needs time and "doStuffWithTheTableFromTheQuery()" fails again, since the table isn´t scriptable. My solution is now a query with a prompted filter and a while-loop. The "wait(0)" seems to have no effect. Here is my code example:

 

New SQL Query(
    Connection(...),

    Queryname("SQL Table"),
    Select(...),
    From(...),

    Where(... Prompt(...) ...)
) << run;

 

checkForDataTable = Function({min},
    dtSwitch = 0;
    dtCountTry = 0;
    dtCountMax = 120 * min; // 120 * min * 0,5 s = min minutes
    While( dtSwitch == 0 & dtCountTry < dtCountMax,
        dtCountTry++;
        Try(    
            dt = Data Table( "SQL Table" );
            dtSwitch = 1;
            ,
            If( dtCountTry == dtCountMax,
                Throw( "Script stopped. SQL query took "||Char(min)||" min." )
            );
            Wait( 0.5 );
        );
    );
);    

checkForDataTable(10);

 

doStuffWithTheTableFromTheQuery();

 

 

 

Eric_Hill

Staff

Joined:

Oct 1, 2013

Hey, vkessler,

Change 

) << run;

to:

) << run foreground;

run by itself will run the query in the background if that's what your preferences are set up for (and it's the default), so that's why doStuffWithTheTableFromTheQuery() runs too soon.  If you use Run Foreground, the script will wait for the query to finish before proceeding.

 

HTH,

 

Eric

vkessler

Community Trekker

Joined:

Dec 23, 2015

Perfect!

Thanks,

Victor