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!
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]^\!"/>
</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);
/*:
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.
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,
I see that @uday_guntupalli just responded. So I will stope now.
@Botzal, please search for Open Database() in the JMP Scripting Index >> Functions. This function has 2 required and 2 option arguments:
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]^\!"/>
</Where>
</Data>
</ScadaPortal>'
, @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);
Hi All,
@gzmorgan0 , Thank you so much for your solution, that works good!
@uday_guntupalli @stan_koprowski @Jeff_Perkinson ,Thank you all for your support!