Subscribe Bookmark RSS Feed

Create a date time panel to extract SQL data

bernie426

Community Trekker

Joined:

Feb 9, 2015

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

1 ACCEPTED SOLUTION

Accepted Solutions
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

12 REPLIES
ian_jmp

Staff

Joined:

Jun 23, 2011

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.

bernie426

Community Trekker

Joined:

Feb 9, 2015

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

erichill

Staff

Joined:

Oct 1, 2013

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:

11353_qb_date_range_filter.png

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:

11354_date_filter_run_query.png

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

HTH,

Eric

ian_jmp

Staff

Joined:

Jun 23, 2011

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

vince_faller

Super User

Joined:

Mar 17, 2015

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. 

bernie426

Community Trekker

Joined:

Feb 9, 2015

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


11380_pastedImage_7.png

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

bernie426

Community Trekker

Joined:

Feb 9, 2015

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

vince_faller

Super User

Joined:

Mar 17, 2015

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

);