37015_en-US

liliconcom

Modal SQL Query Script

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!

Re: Modal SQL Query Script

Perfect!

Thanks,

Victor

Re: Modal SQL Query Script

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

Re: Modal SQL Query Script

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

 

 

 

Re: Modal SQL Query Script

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

Re: Modal SQL Query Script

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

Re: Modal SQL Query Script

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.

Re: Modal SQL Query Script

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