cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
Botzal
Level III

A variable value from user - sql query

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!

Scada.PNG

23 REPLIES 23
uday_guntupalli
Level VIII

Re: A variable value from user - sql query

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

Best
Uday
Botzal
Level III

Re: A variable value from user - sql query

@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);
gzmorgan0
Super User (Alumni)

Re: A variable value from user - sql query

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);
Botzal
Level III

Re: A variable value from user - sql query

It still doesn't work :(

uday_guntupalli
Level VIII

Re: A variable value from user - sql query

@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 :
image.png

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;
Best
Uday
Botzal
Level III

Re: A variable value from user - sql query

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);
Botzal
Level III

Re: A variable value from user - sql query

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

 

uday_guntupalli
Level VIII

Re: A variable value from user - sql query

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

 

Best
Uday
Botzal
Level III

Re: A variable value from user - sql query

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

 

 

 

uday_guntupalli
Level VIII

Re: A variable value from user - sql query

@Botzal , 
  You still haven't pasted the log. If you hit Ctrl + Shift + L on the JMP home window, a log window will open up. Alternatively on your script editor, right click and select "Show Embedded Log" as shown here before running your script: 

image.png
Additionally, you are again defining strings with separators outside the quotes. Please refer to previous corrections I have offered and paste the log. If you fail to paste the log, I will not be able to offer any further guidance 

Best
Uday