topic Re: A variable value from user - sql query in Discussions

A variable value from user - sql query

Botzal — Tue, 26 Feb 2019 14:24:01 GMT

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!

Re: A variable value from user - sql query

uday_guntupalli — Tue, 26 Feb 2019 14:34:12 GMT

@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

Re: A variable value from user - sql query

Jeff_Perkinson — Tue, 26 Feb 2019 16:07:06 GMT

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

Re: A variable value from user - sql query

Botzal — Tue, 26 Feb 2019 21:38:43 GMT

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

Re: A variable value from user - sql query

uday_guntupalli — Wed, 27 Feb 2019 00:12:48 GMT

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

Re: A variable value from user - sql query

gzmorgan0 — Wed, 27 Feb 2019 05:11:47 GMT

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

Re: A variable value from user - sql query

gzmorgan0 — Wed, 27 Feb 2019 06:13:16 GMT

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.

Re: A variable value from user - sql query

Botzal — Sat, 02 Mar 2019 10:09:07 GMT

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

Re: A variable value from user - sql query

Botzal — Sat, 02 Mar 2019 10:19:45 GMT

@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[]

Re: A variable value from user - sql query

uday_guntupalli — Sun, 03 Mar 2019 03:02:24 GMT

@Botzal ,
I think the reason you were running into the error was because of the way your string had commas and other separators. I re-wrote your script and I am providing it below:

LB = Lineup Box(1); 
LB << Append(
		PB_Inputs = Panel Box("User Inputs",
				       a = Text Edit Box( "" ), //foo[1]
				       b = Text Edit Box( "" ), // foo[2]
				    );
	   );
MyUI = V List Box(LB, 
		  BB_UserInputUnload = Button Box("Ok",
						  foo = Eval List( {a << get text, b << get text} );
						  Show( foo );
					     q_str = "DRIVER=SQL Server;SERVER=MyServer;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);
				Eval Insert(q_str); 
				Show(q_str); 
												 ),
				 );
NW = New Window("User Input Collection Window",
				VLB = V List Box(); 
			   ); 
VLB << Append(MyUI);

Also, a couple of suggestions:

1. When you are constructing display boxes, separate them out and build them independently
2. When using statements like Eval, always define your string or statement separately, it will help you to isolate the error and see which line of code is introducing the error

Hope this helps.

Re: A variable value from user - sql query

Botzal — Sun, 03 Mar 2019 12:16:42 GMT

@uday_guntupalli

Hi,

Thanks for your solution and the explanation.

I try to run the script and insert the data into the box panel. When I try to press OK button nothing happens.

Please, see log below

LB = Lineup Box(1); 
LB << Append(
		PB_Inputs = Panel Box("User Inputs",
				       a = Text Edit Box( "" ), //foo[1]
				       b = Text Edit Box( "" ), // foo[2]
				    );
	   );
MyUI = V List Box(LB, 
		  BB_UserInputUnload = Button Box("Ok",
						  foo = Eval List( {a << get text, b << get text} );
						  Show( foo );	
						  				    
q_str = "DRIVER=SQL Server;SERVER=My 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);
				Eval Insert(q_str); 
				Show(q_str); 
												 ),
				 );
NW = New Window("User Input Collection Window",
				VLB = V List Box(); 
			   ); 
VLB << Append(MyUI);

Re: A variable value from user - sql query

gzmorgan0 — Sun, 03 Mar 2019 12:39:15 GMT

I think you need to change the code q_str = Eval Insert(q_str) or Eval Insert Into(q_str) . Eval Insert Into() with change the value of its argument and it must be a sttored value like q_str; Eval Insert() just returns a string, it does not change its argument.

				Show(q_str);
				q_str = Eval Insert(q_str); 
				Show(q_str); 
												 ),
				 );
NW = New Window("User Input Collection Window",
				VLB = V List Box(); 
			   ); 
VLB << Append(MyUI);

Re: A variable value from user - sql query

Botzal — Sun, 03 Mar 2019 12:51:49 GMT

It still doesn't work :(

Re: A variable value from user - sql query

uday_guntupalli — Sun, 03 Mar 2019 14:47:55 GMT

@Botzal ,
When you say it didn't work, can you explain what you are seeing. @gzmorgan0 was correct in catching my fault, where I should have used Eval Insert Into instead of Eval Insert :

Also, are you replacing the Server name in the string shown above ?

Additionally, you are only constructing the string so far, you need actually run the string after constructing it to see the results as shown in the example below from the Scripting Index :

ex = "Today is ^As Date( Today())^";
Eval Insert Into( ex );
ex;

Re: A variable value from user - sql query

gzmorgan0 — Sun, 03 Mar 2019 14:56:40 GMT

Please post your log. This script is not running a query, it is just writing the result of the query command to the log.

Which version of JMP are you using? JMP has a Query Builder. You could create that query to prompt for the two fields you need. Then you can save the script, or the query. There are multiple points of failure for a query,

Incorrect query syntax, note that case can be important. So if a user is typing in harry, but the db field is HARRY, then the result will be empty. Also SQL quires quotes around a value with spaces or special characters. So first, get a script to work without the prompt, that is hardcode the values of the prompt and run the portion of code dtScadaData = Open Database(etc.)
Once the hardcoded script works, then try the prompt typing in exactly what was hardcoded above. Also print out the string and compare.mple,
The query login can be a point of failure
Your connection string to the db driver may not be the same as another users. For example, I set up the driver for Excel to be named Excel Files, someone else might have a different name.
Other user privileges might be different than yours, they might not have access to the files/tables.
I am sure there are more,

I see that @uday_guntupalli just responded. So I will stope now.

Re: A variable value from user - sql query

stan_koprowski — Sun, 03 Mar 2019 15:12:42 GMT

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

Re: A variable value from user - sql query

Botzal — Mon, 04 Mar 2019 09:15:56 GMT

Hi @uday_guntupalli,

The script that the @gzmorgan0 sent me, doesn't work - see JSL below

Sure, i replaced the server to my server.

Please, any help to fix the script.

Thx.

LB = Lineup Box(1); 
LB << Append(
		PB_Inputs = Panel Box("User Inputs",
				       a = Text Edit Box( "" ), //foo[1]
				       b = Text Edit Box( "" ), // foo[2]
				    );
	   );
MyUI = V List Box(LB, 
		  BB_UserInputUnload = Button Box("Ok",
						  foo = Eval List( {a << get text, b << get text} );
						  Show( foo );	
						  				    
q_str = "DRIVER=SQL Server;SERVER=My 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);
				Eval Insert(q_str); 
				Show(q_str); 
												 ),
				 );
NW = New Window("User Input Collection Window",
				VLB = V List Box(); 
			   ); 
VLB << Append(MyUI);

Re: A variable value from user - sql query

Botzal — Mon, 04 Mar 2019 10:52:33 GMT

Hi @uday_guntupalli,

The script that the @gzmorgan0 sent me, doesn't work - see JSL below

Sure, i replaced the server to my server.

Please, any help to fix the script.

Thx.

LB = Lineup Box(1); 
LB << Append(
		PB_Inputs = Panel Box("User Inputs",
				       a = Text Edit Box( "" ), //foo[1]
				       b = Text Edit Box( "" ), // foo[2]
				    );
	   );
MyUI = V List Box(LB, 
		  BB_UserInputUnload = Button Box("Ok",
						  foo = Eval List( {a << get text, b << get text} );
						  Show( foo );	
						  				    
q_str = "DRIVER=SQL Server;SERVER=My 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);
				Eval Insert(q_str); 
				Show(q_str); 
												 ),
				 );
NW = New Window("User Input Collection Window",
				VLB = V List Box(); 
			   ); 
VLB << Append(MyUI);

Re: A variable value from user - sql query

uday_guntupalli — Mon, 04 Mar 2019 13:57:49 GMT

@Botzal ,
You need to look at the advice we already gave you and try to follow it. Please help us help you. When you say, it is not working, we don't know or can't see what is going on. It will help us troubleshoot if you can provide clear detail of what you are saying and why you think it does not work.

1. Did you validate the query works by itself ?

2. Please paste the log

3. You are not evaluating the string as I have shown previously. Kindly evaluate the string by calling it out after it is constructed.

LB = Lineup Box(1); 
LB << Append(
		PB_Inputs = Panel Box("User Inputs",
				       a = Text Edit Box( "" ), //foo[1]
				       b = Text Edit Box( "" ), // foo[2]
				    );
	   );
MyUI = V List Box(LB, 
		  BB_UserInputUnload = Button Box("Ok",
						  foo = Eval List( {a << get text, b << get text} );
						  Show( foo );	
						  				    
q_str = "DRIVER=SQL Server;SERVER=My 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);
				Eval Insert(q_str); 
				Show(q_str); 
                                q_str; // evaluating the query you constructed 
												 ),
				 );
NW = New Window("User Input Collection Window",
				VLB = V List Box(); 
			   ); 
VLB << Append(MyUI);

Re: A variable value from user - sql query

Botzal — Wed, 06 Mar 2019 21:48:26 GMT

@uday_guntupalliThanks for your solution.

I tried to apply your advice but it didn't work. :\

1. The Query works - When I insert numbers in a script and don't variables from the user. For examples - Instead of foo[1], I insert p123 and Instead of oo[2], I insert v123.

dt_ScadaData = Open Database("DRIVER=SQL Server;SERVER=My Server ;Trusted_Connection=Yes",
"DECLARE @StartDateTime  datetime = DATEADD(day, -1, CAST(GETDATE() AS varchar(11))) -- 17 Days Back 
DECLARE @EndDateTime   datetime = DATEADD(day,1, GETDATE()) 

EXEC ListData @Criteria='
<ScadaPortal>
  <Data Name=\!"PointData\!" Format=\!"Wide\!" Interval=\!"60m\!">
    <Where>
   
<Column Name=\!"Point\!" Project=\!"p123\!" Value=\!"v123\!"/>

    </Where>
  </Data>
</ScadaPortal>'
, @StartDateTime=@StartDateTime
, @EndDateTime=@EndDateTime","Scada Data");

2. The log -

foo = {"P123", "V123"};
//:*/

LB = Lineup Box(1); 
LB << Append(
		PB_Inputs = Panel Box("User Inputs",
				       a = Text Edit Box( "" ), //foo[1]
				       b = Text Edit Box( "" ), // foo[2]
				    );
	   );
MyUI = V List Box(LB, 
		  BB_UserInputUnload = Button Box("Ok",
						  foo = Eval List( {a << get text, b << get text} );
						  Show( foo );	
					  				    

q_str ="DRIVER=SQL Server;SERVER=My Server;Trusted_Connection=Yes",
"DECLARE @StartDateTime  datetime = DATEADD(day, -1, CAST(GETDATE() AS varchar(11))) -- 17 Days Back 
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);
				q_str = Eval Insert INTO(q_str); 
				Show(q_str); 
												 ),
				 );
NW = New Window("User Input Collection Window",
				VLB = V List Box(); 
			   ); 
VLB << Append(MyUI);

/*:

3. Can you see the script in chapter 2 that I apply your advice, If I understood you correctly.