A variable value from user - sql queryI 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 queryHi All,
@gzmorgan0 , Thank you so much for your solution, that works good!
@uday_guntupalli @stan_koprowski @Jeff_Perkinson ,Thank you all for your support!
Re: A variable value from user - sql query@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
- property for the output table, you likely do not want this
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]^\!"/">
</COLUMN>
</WHERE>
</DATA>'
, @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);</SCADAPORTAL>
Re: A variable value from user - sql query@uday_guntupalli ,
What I posted is a log - see below
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(I changed);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]^\!"/">
</COLUMN>
</WHERE>
</DATA>'
, @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);
/*:</SCADAPORTAL> Are you sure that i "defining strings with separators outside the quotes" ? Plese, can you mark the separators and tell me what do you mean.
* The issue is that when I run the script and insert values to Panel Box and than i press to Button Box("Ok"..) , I dont recived any data.
Thx.
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:
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
Re: A variable value from user - sql query@uday_guntupalliThanks for your solution.
I tried to apply your advice but it didn't work. ![:\](https://jmp.vanillacommunities.com/resources/emoji/confused.png ":\")
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\!"/">
</COLUMN>
</WHERE>
</DATA>'
, @StartDateTime=@StartDateTime
, @EndDateTime=@EndDateTime","Scada Data");</SCADAPORTAL> 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]^\!"/">
</COLUMN>
</WHERE>
</DATA>'
, @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);
/*:</SCADAPORTAL>3. Can you see the script in chapter 2 that I apply your advice, If I understood you correctly.
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]^\!"/">
</COLUMN>
</WHERE>
</DATA>'
, @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);</SCADAPORTAL>
Re: A variable value from user - sql queryHi @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]^\!"/">
</COLUMN>
</WHERE>
</DATA>'
, @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);</SCADAPORTAL>
Re: A variable value from user - sql queryHi @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]^\!"/">
</COLUMN>
</WHERE>
</DATA>'
, @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);</SCADAPORTAL>Re: A variable value from user - sql queryHi @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 queryPlease 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@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 queryIt still doesn't work 
Re: A variable value from user - sql queryI 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@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]^\!"/">
</COLUMN>
</WHERE>
</DATA>'
, @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);</SCADAPORTAL>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]^\!"/">
</COLUMN>
</WHERE>
</DATA>'
, @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);</SCADAPORTAL>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@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]^\!"/">
</COLUMN>
</WHERE>
</DATA>'
, @StartDateTime=@StartDateTime
, @EndDateTime=@EndDateTime","Scada Data");
show(q_str);
wait(0);
uservalue << close window();
)
);
/*:
DisplayBox[]</SCADAPORTAL>
Re: A variable value from user - sql query@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]^\!"/">
</COLUMN>
</WHERE>
</DATA>'
, @StartDateTime=@StartDateTime
, @EndDateTime=@EndDateTime","Scada Data");
show(q_str);
wait(0);
uservalue << close window();
)
);</SCADAPORTAL>
Re: A variable value from user - sql queryThe 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></MODAL>
'
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.
'
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, 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@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@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'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 ,
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
