topic Re: Create a date time panel to extract SQL data in Discussions

Create a date time panel to extract SQL data

bernie426 — Fri, 15 Apr 2016 06:20:00 GMT

Hello,

I used to use a jmp script to extract sql data table. This day I tried to built a window panel box to execute the same sql data table function.

The simple idea is to extract sql data table within a period of time, which would be two input variables in the panel box. The "End_Time" can easily use Today() syntax. However, for "Start_Time", I tried to write a script that allow user to input a certain "hour "before End_time that he want to pull the data table.

Unfortunately, this script is not working as expected.

Can any jmp script guru try to correct my script below? I also attached the jmp script I wrote for modification.

Many Thanks,

nw = new window("SQL Data Table Extraction",
      Vlistbox(
            tb1=textbox("Start_Time: "),
            dteb1 = numbereditbox(1),
  hlistbox(
  text box("Enter the number of hours: "),
  hours_teb = text edit box()),
            tb2=textbox("End_Time: "),
            dteb2 = numbereditbox(1),
                  ),
      ok_button = button box("Get Data", << Open Database("DSN=;Description=;UID=;PWD=;APP=;WSID=", sql_statement, Test Data)),
dteb1 << set format(format("m/d/y h:m:s", 23, 0)),
dteb1 << set width(23),
dteb1 << set(today()-EvalExpr((hours_tab)*60*60)),
dteb2 << set format(format("m/d/y h:m:s", 23, 0)),
dteb2 << set(Today()));
  sql_statement="SELECT * FROM dbo.TESTCOMPLETE WHERE (dCreated >= '"||Start Date/Time||"') AND (dCreated <='"||End_Date/Time||"')");

Re: Create a date time panel to extract SQL data

ian_jmp — Fri, 15 Apr 2016 09:38:35 GMT

In terms of selecting date times you might like to look at this thread too. So using the code kindly written by Vince Feller you might like to try this kind of approach since it gives a lot more functionality:

NamesDefaultToHere(1);

// Written by Vince Faller

// Makes a calendar come up on a numeditbox (defaults to now)

// Note, this only works on JMP 11 or above

dateBox = Function(

{timething = Today()},

{newbox},

newbox = Eval(

Substitute(

Name Expr(

Number Edit Box(

timething, // Eval(Sub()) because of JMP quirk

10, // Just need this because JMP doesn't default a width

<<Set function(

Function( {self},

{},

If( Is Missing( self << get ),

self << Set( Eval( DUMMY ) ) // Sets the date to the timething argument

)

<< Set Format( Format( "m/d/y h:m:s", 23, 0 ) ); // Makes datetime format for the box (gives calendar)

)

Expr( DUMMY ), timething

)

);

newbox;

);

nw =

New Window( "Date Time Span ",

LineUpBox(NCol(2),

TextBox("Start DateTime"), start = dateBox(Today() - InHours(12)),

TextBox("End DateTime"), end = dateBox(),

ButtonBox("Cancel", nw << closeEindow), ButtonBox("OK", runScript)

)

);

runScript =

Expr(

nw << CloseWindow;

startDT = start << get;

endDT = end << get;

Print(startDT, endDT);

);

I didn't check out your original code in detail, but I did see that you were attempting to have a variable name containing '/', which isn't allowed unless you also use 'Name()'. Look at 'Help > Books > Scripting Guide' for more details on what you can and can't do.

Re: Create a date time panel to extract SQL data

Eric_Hill — Wed, 19 Oct 2016 02:25:35 GMT

Hey, bernie426,

If you are on JMP 12, consider using Query Builder for these sorts of things (and if you're not at JMP 12 yet, try to get there ;-). Query Builder lets you interactively create filtered SQL queries, and you can turn those filters into prompts. A primary reason Query Builder was created was for situations just like yours. Here is what the query looks like while being built:

If you look closely you can see (Prompt) next to the filter. Then, when the query is run, you get a prompt that will automatically present a date picker or date-time picker, depending on the format of the variable:

Something to think about, for the future if not right away.

HTH,

Eric

Re: Create a date time panel to extract SQL data

ian_jmp — Fri, 15 Apr 2016 12:44:53 GMT

Much better, Eric! Avoiding JSL is never a bad strategy . Sorry to overlook this option.

Re: Create a date time panel to extract SQL data

bernie426 — Fri, 15 Apr 2016 18:12:56 GMT

I am using JMP 11, which do not have Query Builder functionality. Hope to get one later if our department agree for a upgrade. Anyway, Thanks for the guidance. I tried the script Ian from Vince posted and the date time window function works. However, as I tried to glue with the button box that I designed to extract data table from sql server, it fail to have any response. I checked the sql statement with the original jsl scipt that I used to extract sql data table and it works fine. I am guessing somewhere wrong with the script that designed for button box to pull sql data table. Can any one take a quick fix?

Many Thanks,

NamesDefaultToHere(1);

// Written by Vince Faller

// Makes a calendar come up on a numeditbox (defaults to now)

// Note, this only works on JMP 11 or above

dateBox = Function(

{timething = Today()},

{newbox},

newbox = Eval(

Substitute(

Name Expr(

Number Edit Box(

timething, // Eval(Sub()) because of JMP quirk

10, // Just need this because JMP doesn't default a width

<<Set function(

Function( {self},

{},

If( Is Missing( self << get ),

self << Set( Eval( DUMMY ) ) // Sets the date to the timething argument

)

<< Set Format( Format( "m/d/y h:m:s", 23, 0 ) ); // Makes datetime format for the box (gives calendar)

)

Expr( DUMMY ), timething

)

);

newbox;

);

nw =

New Window( "SQL Data Table Extraction",<<modal,

LineUpBox(NCol(2),

TextBox("Start DateTime"), start = dateBox(Today() - InHours(12)),

TextBox("End DateTime"), end = dateBox(),

ButtonBox("Cancel", nw << closeEindow), ButtonBox("Get Data", RunScript)

));

sql_statement = evalinsert("SELECT * FROM dbo.TESTCOMPLETEWHERE dCreated >= '^startDT^' AND dCreated <= '^endDT^'ORDER BY dCreated");

RunScript= Expr(nw<<Cloased Window;

startDT = start << get;

endDT = end << get;

Open Database(

"DSN=;Description=;UID=;PWD=;APP=;WSID=1",

sql_statement,

"Test Result"));

Re: Create a date time panel to extract SQL data

vince_faller — Wed, 20 Apr 2016 15:15:24 GMT

nw << Cloased Window

will crash the script.

you need to

nw<<close window

also you have another spelling error in your cancel button.

you should also use MDYHMS(startDT) and endDT, or some other conversion to SQL DateTime because JMP will just report a number and SQL won't take this as a date time.

Re: Create a date time panel to extract SQL data

bernie426 — Wed, 19 Oct 2016 02:27:23 GMT

Hi Vince,

Thanks for the reply. I recognized the spelling error days before, but still cannot get my script working as expected.

In your response, do you mean I cannot use the Number Edit Box() script function that you used to wrote for date box()?

Is this the issue that I am receiving the following warning message (Please see the image below) when I clicked on the "Get Data" button?

However how come if I directly run the script below, jmp is able to pull the request data table from sql server?

//This script is wrote to pull data within a certain time frame
start_time="04/11/2016 7:00:00 AM";
end_time="04/12/2016 7:00:00 PM";
sql_statement=evalinsert("SELECT * FROM dbo.TESTCOMPLETEWHERE dCreated >= '^start_time^' AND dCreated <= '^end_time^' ORDER BY dCreated");
Open Database(
  "DSN=;Description=;UID=;PWD=;APP=;WSID=",sql_statement,
  "Test Data");

Re: Create a date time panel to extract SQL data

guy_yosef — Thu, 21 Apr 2016 13:38:58 GMT

i am using the folwing script to exract SQL data table and limit the time in modal

::dbc = my name and login that i insert in different add in, you can use open data base instead

i added botton for 2 diffrent tables (defect and yield)

another filter for paramter (defect type, yield paramter etc.)

my SQL time column is 'dayxxx'

win = New Window("select data"

<< Modal,

Panel Box("Select",

rbox = Radio Box( {"DEFECT", "YIELD"}

)

,Text Box("PARAMETER"),variablebox1 = Text Edit Box()

,Text Box("Insert days"),variablebox2 = Text Edit Box()

);

area1 = rbox <<get;

PARAM = variablebox1 <<get text ;

Days = variablebox2 <<get text ;

table1 = if(

area1 == 1, "defect tablexxx",

area1 == 2, "yield tablexxx"

)

;

/// SQL data selsction ///

dt = Execute SQL(::dbc,

"SELECT * FROM \!"pathxxx\!".\!""||table1||"\!"

WHERE \!"dayxxx\!" > (CURRENT_DATE - "||Days||" DAYS)

AND \!"parameterxxx\!" = '"||PARAM||"'

);

dt << Set Name(""||PARAM||" "||days||"");

*i did alot of modifcation to hide my company data base name so i might made some script erros

Re: Create a date time panel to extract SQL data

vince_faller — Thu, 21 Apr 2016 16:32:17 GMT

If you run start << Get (or just mouse over startDT). You'll see that the value is actually just some number, 3543202800 for "04/11/2016 7:00:00 AM". So when you try putting it in SQL, you're actually just giving it that number (which is not character so you can't concatenate it in). You need to change

startDT = start << get;

endDT = end << get;

startDT = MDYHMS(start << get);

endDT = MDYHMS(end << get);

I think this should let you run.

As far as your error, it just looks like there's no ; after your line 43

Re: Create a date time panel to extract SQL data

bernie426 — Thu, 21 Apr 2016 20:38:51 GMT

Hi Vince,

Thanks for guiding through my blind points and getting me closer. However, even after changing startDT = MDYHMS(start<< get) format I still cannot get this sql script working. Then, I am still receiving the same jmp warning message "Name Unresolved:Runscript in scces or evaluation of 'RunScirpt, RunScript/*###*/". Is there anything wrong in my script?

I also attached my script for checking.

Thanks for your help!

NamesDefaultToHere(1);
// Written by Vince Faller
// Makes a calendar come up on a numeditbox (defaults to now)
// Note, this only works on JMP 11 or above
dateBox = Function(
{timething = Today()},
{newbox},
newbox = Eval(
Substitute(
Name Expr(
Number Edit Box(
timething,  // Eval(Sub()) because of JMP quirk
10,  // Just need this because JMP doesn't default a width
<<Set function(
Function( {self},
{},
If( Is Missing( self << get ),
self << Set( Eval( DUMMY ) ) // Sets the date to the timething argument
)
)
),
<< Set Format( Format( "m/d/y h:m:s", 23, 0 ) ); // Makes datetime format for the box (gives calendar)
)
),
Expr( DUMMY ), timething
)
);
newbox;
);
nw =
New Window( "SQL Data Table Extraction",
LineUpBox(NCol(2),
TextBox("Start DateTime"), start = dateBox(Today() - InDays(12)),
TextBox("End DateTime"), end = dateBox(),
ButtonBox("Cancel", nw << close window), ButtonBox("Get Data", RunScript)
));
sql_statement=evalinsert("SELECT * FROM dbo.TESTCOMPLETE WHERE dCreated >= '^start_time^' AND dCreated <= '^end_time^' ORDER BY dCreated");
RunScript= Expr(nw<<Close Window;
  startDT=MDYHMS(start << get);
  endDT=MDYHMS(end<<get);
  Open Database(
  "DSN=;Description=;UID=;PWD=;APP=;WSID=",
  sql_statement,"Test Result"));

Re: Create a date time panel to extract SQL data

bernie426 — Thu, 21 Apr 2016 20:39:50 GMT

Thanks for sharing....guy.yosef

Re: Create a date time panel to extract SQL data

vince_faller — Thu, 21 Apr 2016 21:16:03 GMT

TWo things

sql_statement=evalinsert("SELECT * FROM dbo.TESTCOMPLETE WHERE dCreated >= '^start_time^' AND dCreated <= '^end_time^' ORDER BY dCreated");
1. These are not the same variables as startDT and EndDT
sql_statement should be inside of RunScript

if I make the last couple lines look like this it prints fine

nw =

New Window( "SQL Data Table Extraction",

LineUpBox(NCol(2),

TextBox("Start DateTime"), start = dateBox(Today() - InDays(12)),

TextBox("End DateTime"), end = dateBox(),

ButtonBox("Cancel", nw << close Window), ButtonBox("Get Data", RunScript)

));

RunScript= Expr(nw<<Close Window;

startDT=MDYHMS(start << get);

endDT=MDYHMS(end<<get);

sql_statement="SELECT * FROM dbo.TESTCOMPLETE WHERE dCreated >= '"||startDT||"' AND dCreated <= '"||endDT||"' ORDER BY dCreated";

print(sql_statement);

);

Re: Create a date time panel to extract SQL data

bernie426 — Thu, 21 Apr 2016 22:11:43 GMT

Hi Vince,

Thanks for the guidance and patience. Finally I got it work out.

Underneath one is the correct and working version.

NamesDefaultToHere(1);
// Written by Vince Faller
// Makes a calendar come up on a numeditbox (defaults to now)
// Note, this only works on JMP 11 or above
dateBox = Function(
{timething = Today()},
{newbox},
newbox = Eval(
Substitute(
Name Expr(
Number Edit Box(
timething, // Eval(Sub()) because of JMP quirk
10, // Just need this because JMP doesn't default a width
<<Set function(
Function( {self},
{},
If( Is Missing( self << get ),
self << Set( Eval( DUMMY ) ) // Sets the date to the timething argument
)
)
),
<< Set Format( Format( "m/d/y h:m:s", 23, 0 ) ); // Makes datetime format for the box (gives calendar)
)
),
Expr( DUMMY ), timething
)
);
newbox;
);
nw =
New Window( "SQL Data Table Extraction",
LineUpBox(NCol(2),
TextBox("Start DateTime"), start = dateBox(Today() - InDays(12)),
TextBox("End DateTime"), end = dateBox(),
ButtonBox("Cancel", nw << close window), ButtonBox("Get Data", RunScript)
));
RunScript= Expr(nw<<Close Window;
sql_statement=evalinsert("SELECT * FROM dbo.TESTCOMPLETE WHERE dCreated >= '^startDT^' AND dCreated <= '^endDT^' ORDER BY dCreated");
startDT=MDYHMS(start << get);
endDT=MDYHMS(end<<get);
Open Database(
"DSN=;Description=;UID=;PWD=;APP=;WSID=",
sql_statement,"Test Result"));