Solved: A variable value from user - sql query

Get Direct Link · Feb 26, 2019 6:24 AM

I try to get a variable value from the user and use it as a SQL query
How can I add to a query?

Thanks!

gzmorgan0 · Mar 7, 2019 2:31 AM

@Botzal, please search for Open Database() in the JMP Scripting Index >> Functions. This function has 2 required and 2 option arguments:

the connect string
the query string
<invisible | private> property for the output table, you likely do not want this
<output table name>

You have been using one string for the first two arguments. It is difficult to debug and see this when we do not have test files to use. Remove the comment lines for dt_ScadaData to test if it runs. Remove the comment lines for NW if you want the UI to automatically close. You might want it to remain open so you can pull several tables. If that is what you want, then Eval Insert Into(qstr), replaces the place holder variables foo[1] and foo[2]. So just in case you intend to use the UI more than once, I created a variable cq_str which is assigned the variable string. So this should work. I typically do not use Eval Insert Into() but instead something like cq_str = Eval Insert(q_str);

LB = Lineup Box(1); 
LB << Append(
		PB_Inputs = Panel Box("User Inputs",
				       a = Text Edit Box( "" ), //foo[1]
				       b = Text Edit Box( "" ), // foo[2]
				    );
	   );
q_connect = "DRIVER=SQL Server;SERVER=MyServer;Trusted_Connection=Yes";	   
q_str = "DECLARE @StartDateTime  datetime = DATEADD(day, -2, CAST(GETDATE() AS varchar(11))) 
		 DECLARE @EndDateTime   datetime = DATEADD(day,0, GETDATE()) 
	     EXEC ListData @Criteria=
	     '<ScadaPortal>
			  <Data Name=\!"PointData\!" Format=\!"Wide\!" Interval=\!"60m\!">
				<Where>
					<Column Name=\!"Point\!" Project=\!"^foo[1]^\!" Value=\!"^foo[2]^\!"/>
				</Where>
			  </Data>
		 </ScadaPortal>'
		, @StartDateTime=@StartDateTime
		, @EndDateTime=@EndDateTime
		Scada Data";

MyUI = V List Box(LB, 
		  BB_UserInputUnload = Button Box("Ok",
				foo = Eval List( {a << get text, b << get text} );
				Show( foo );
				cq_str = q_str;
				Show(cq_str);
				Eval Insert Into(cq_str); 
				Show(cq_str); 
				//dt_ScadaData = Open Database(q_connect, cq_str); //this is the runit command	
				//NW << Close Window(); //this will close the UI don't use if you want to keep ist open								 
	      ) //end ButtonBox
	    );  //end VListBox
NW = New Window("User Input Collection Window",
				VLB = V List Box(); 
			   ); 
VLB << Append(MyUI);

View solution in original post

uday_guntupalli · Feb 26, 2019 09:34 AM

@Botzal ,
One way to achieve what you want would be by using the Substitiute(). For e.g.

 WhereStatement = " WHERE  [ReadTime] >=  DateTimeStart and [ReadTime] <= DateTimeEnd "; 
WhereStatement = Substitute(WhereStatement, "DateTimeStart", "'" || MyStartTime ||"'", "DateTimeEnd", "'" || MyEndTime||"'");

In the example shown above, DateTimeStart and DateTimeEnd in the query are being replaced using MyStartTime and MyEndTime which are based on user input.

Similarly in your case you could initialize the SqlQuery as a separate string first, use the Substitute() to replace the strings you want. Once you do that and have the query you want, then query from the database. Let me know if that helps. Small advice for future is, please post JSL using the "JSL" block, so people can easily use the script you post to offer examples

Best
Uday

Jeff_Perkinson · Feb 26, 2019 11:07 AM

@uday_guntupalli's suggestion of Substitute() is a good one.

Another function you may want to familiarize yourself with is Eval Insert(). It can insert variable values into text strings.

myvar="a";

response=eval insert("The correct answer is: ^myvar^");

print(response);

Here's the log from that code:

/*:
//:*/
myvar="a";

response=eval insert("The correct answer is: ^myvar^");

print(response);
/*:

"The correct answer is: a"

-Jeff

Botzal · Feb 26, 2019 04:38 PM

@Jeff_Perkinson

@Jeff_Perkinson wrote:
@uday_guntupalli's suggestion of Substitute() is a good one.

Another function you may want to familiarize yourself with is Eval Insert(). It can insert variable values into text strings.
myvar="a";

response=eval insert("The correct answer is: ^myvar^");

print(response);
Here's the log from that code:
/*:
//:*/
myvar="a";

response=eval insert("The correct answer is: ^myvar^");

print(response);
/*:

"The correct answer is: a"

Thanks for your solution.

Sorry, I'm trying but it doesn't work.

Please, can you explain to me?

uday_guntupalli · Feb 26, 2019 07:12 PM

@Botzal ,
Please post what you have tried using "JSL" block shown here and a snapshot of the log if possible, that way the users in the community can try and understand what is not working for you.

Best
Uday

gzmorgan0 · Feb 27, 2019 12:11 AM

@Botzal, the uservalue window is non-modal. So the JSL below the New Window tries to run before the user gets a chance to select the button.

Do you know how to make a dialog modal, or how to run the query as part of the OK button?

gzmorgan0 · Feb 27, 2019 01:13 AM

The script below has 2 parts: a modal dialog, and a non-modal dialog.

Names default to here(1);

//Modal method
foo = New Window("Insert Data", <<Modal, <<Return Result,
	a = Text Edit Box(""), 
	b = Text Edit Box("") 	
);	
show(foo);

If(foo["Button"]!=1, Throw("User did not respond"));

remove from(foo, nitems(foo)); // remove the last item
EvalList(foo);   // now a = the first value and b = the 2nd value
q_str = EvalInsert("DECLARE @StartDateTime datetime = DATEADD(   etc.
		<Column Name=\!"Point\!" Project=\!"^a^\!" Value=\!"^b^\!"/>
		</Where>
		</Data>
		</ScadaPortal>'
		etc.  ");
Show(q_str);		
//uncomment the line below after you have tested the string is correct 		
//dt_ScadaData = Open Database("DRIVER=SQL Server; SERVER=....", q_str);


//+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
// Non-Modal Method

//--- Show commands are written to teh LOG not the embedded log


uservalue = New Window("Insert Data", 
	a = Text Edit Box(""), //foo[1]
	b = Text Edit Box(""), //foo[2]
	
	Button Box("OK",
	    foo = Eval List({ a << get text, b << get text});
		show(foo);
		q_str = EvalInsert("DECLARE @StartDateTime datetime = DATEADD(   etc.
		<Column Name=\!"Point\!" Project=\!"^foo[1]^\!" Value=\!"^foo[2]^\!"/>
		</Where>
		</Data>
		</ScadaPortal>'
		etc.  ");
		show(q_str);
		wait(0);
        uservalue << close window();

	 )//end ButtonBox
); //end New Window

Here are the results written in the log, I entered cat and chick, then bull and cow.

Botzal · Mar 2, 2019 05:09 AM

@gzmorgan0

@uday_guntupalli

@Jeff_Perkinson

Hi All,

Thanks for your solution!

The script still doesn't work :(

When I try to insert the parameters, it shows me the following message - "Name Unresolved: erver in access or evaluation of 'erver' , erver"

Although I run the script with the following command - Names default to here(1);

Names default to here(1);
uservalue = New Window("Insert Data", 
	a = Text Edit Box(""), //foo[1]
	b = Text Edit Box(""), //foo[2]
	
	Button Box("OK",
	    foo = Eval List({ a << get text, b << get text});
		show(foo);
		
q_str = EvalInsert("DRIVER=SQL Server;SERVER= ----;Trusted_Connection=Yes",
"DECLARE @StartDateTime  datetime = DATEADD(day, -2, CAST(GETDATE() AS varchar(11))) 
DECLARE @EndDateTime   datetime = DATEADD(day,0, GETDATE()) 
EXEC ListData @Criteria='
<ScadaPortal>
  <Data Name=\!"PointData\!" Format=\!"Wide\!" Interval=\!"60m\!">
    <Where>
		<Column Name=\!"Point\!" Project=\!"^foo[1]^\!" Value=\!"^foo[2]^\!"/>
    </Where>
  </Data>
</ScadaPortal>'
, @StartDateTime=@StartDateTime
, @EndDateTime=@EndDateTime","Scada Data");
		show(q_str);
		wait(0);
        uservalue << close window();
        )
        );

stan_koprowski · Mar 3, 2019 10:12 AM

Hi @Botzal,

What version of SQL Server are you running?

It appears that you might have an evaluation copy of SQL Server that has expired.

Can you verify your SQL Server is not running in evaluation mode?

Thanks,

Stan

Botzal · Mar 2, 2019 05:19 AM

@gzmorgan0

@uday_guntupalli

@Jeff_Perkinson

This is the log -

foo = {"DS", "FD"};

Name Unresolved: erver in access or evaluation of 'erver' , erver

//:*/

Names default to here(1);
uservalue = New Window("Insert Data", 
	a = Text Edit Box(""), //foo[1]
	b = Text Edit Box(""), //foo[2]
	
	Button Box("OK",
	    foo = Eval List({ a << get text, b << get text});
		show(foo);
		
q_str = EvalInsert("DRIVER=SQL Server;SERVER=-----;Trusted_Connection=Yes",
"DECLARE @StartDateTime  datetime = DATEADD(day, -2, CAST(GETDATE() AS varchar(11))) 
DECLARE @EndDateTime   datetime = DATEADD(day,0, GETDATE()) 
EXEC ListData @Criteria='
<ScadaPortal>
  <Data Name=\!"PointData\!" Format=\!"Wide\!" Interval=\!"60m\!">
    <Where>
		<Column Name=\!"Point\!" Project=\!"^foo[1]^\!" Value=\!"^foo[2]^\!"/>
    </Where>
  </Data>
</ScadaPortal>'
, @StartDateTime=@StartDateTime
, @EndDateTime=@EndDateTime","Scada Data");
		show(q_str);
		wait(0);
        uservalue << close window();
        )
        );
/*:

DisplayBox[]

A variable value from user - sql query

Re: A variable value from user - sql query

Re: A variable value from user - sql query

Re: A variable value from user - sql query

Re: A variable value from user - sql query

Re: A variable value from user - sql query

Re: A variable value from user - sql query

Re: A variable value from user - sql query

Re: A variable value from user - sql query

Re: A variable value from user - sql query

Re: A variable value from user - sql query

Recommended Articles

Get Going with JMP: Essentials for Using JMP

Getting Started with JMP: On Demand Course

Introduction to the JMP Scripting Language