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 , 
     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 

[#3]

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

[#4]

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

[#5]

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

[#6]

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

[#7]

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.

[#8]

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

[#9]

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

[#10]

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