A variable value from user - sql query

[#1]

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!

[#2]

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

[#3]

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

[#4]

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

[#5]

It still doesn't work :(

[#6]

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

[#7]

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

[#8]

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

[#9]

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

[#10]

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

[#11]

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

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