nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

A variable value from user - sql query

Botzal

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!

Accepted answers

gzmorgan0

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

All comments

uday_guntupalli

@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

Jeff_Perkinson

@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"

Botzal

@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

@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.