Create a date time panel to extract SQL data

[#1]

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||"')");

[#2]

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.

[#3]

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

[#4]

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

[#5]

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

[#6]

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. 

[#7]

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

[#8 · Accepted solution]

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;

to

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

[#9]

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

[#10]

TWo things

1. 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
2. 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);

);