topic JMP 13 JSL that passes variables to oracle SQL statement in Discussions

JMP 13 JSL that passes variables to oracle SQL statement

steve_reinhart — Tue, 06 Feb 2018 22:00:09 GMT

I am working with a collegue that has a JMP 13 script to is defining variables using Modal windows. User selection then sets variables that we want to pass into a open database section of our script in the SQL statement. Data table is updated for which oven is selected in the Modal window. We then select the oven column to pass this value into our SQL statement.

Example.

OvenList={18,22};
nw=New Window( " Reference Units",<<Modal,
     Text Box(" Define the Oven you want"),
     Text Box( ""),
          Panel Box("Select",
          oven_bx = Radio Box( {"Oven 18", "Oven 22"})),
          Text Box(""),
     V List Box(ok_btn=Button Box("OK",
          ov1=oven_bx << Get;),
          cncl_btn=Button Box ("Cancel")));
 
New Column("Oven",numeric, nominal);
:Oven<< Set Each Value(OvenList[ov1]);
 
oven_id= :Oven << Get Values;
test_part_id = :ExpNo << Get Values;
 
Open Database( -- connection string info -- ,
 
"
Select 
db.table.field1
db.table.field2
db.table.field3
FROM db.table
WHERE
db.table.exprmt = 'blah'
AND db.table.field = " || oven_id || "
AND db.table.field In(" || test_part_id || ")
ORDER BY test_part_id ASC
",  "dataset");

Issue we have is the following...

oven_id: script passes [2,2]

test_part_id: script passes {"partid1","partid2"}

Questions

1) how do we select the first value in the oven column to set as the oven_id that gets passed to SQL statement?

2) Oracle appears to not like the parenthese and double quotes. It works if we hard code single quotes and part ids ex: IN('partid1','partid2')

HELP!!!

Re: JMP 13 JSL that passes variables to oracle SQL statement

pmroz — Mon, 05 Feb 2018 14:00:31 GMT

Get values returns a list, so you need to refer to individual array elements in order to use them. You can use concat items to create an IN list. This will work:

test_part_in_list = "('" || concat items(test_part_id, "', '") || "')";
sql = 
"Select db.table.field1
        db.table.field2
        db.table.field3
   FROM db.table
  WHERE db.table.exprmt = 'blah'
    AND db.table.field = " || char(oven_id[1]) || "
    AND db.table.field In " || test_part_in_list || "ORDER BY test_part_id ASC";

Open Database( -- connection string info -- , sql, "dataset");

Re: JMP 13 JSL that passes variables to oracle SQL statement

steve_reinhart — Tue, 06 Feb 2018 20:26:50 GMT

pmroz,

Thanks for your quick reply and code suggestion. It worked perfect for our application. Thank you very much. We tried all kinds of solutions from various forums using ^var_name^, ^||var_name||^, etc. Your solution by building the list was the solution step we were missing.

Re: JMP 13 JSL that passes variables to oracle SQL statement

pmroz — Tue, 06 Feb 2018 20:36:47 GMT

Glad it worked for you. The ^varname^ construct needs to be used with evalinsert. The use of evalinsert allows somewhat cleaner looking code, and you don't need to convert numbers to strings with char().

test_part_in_list = "('" || concat items(test_part_id, "', '") || "')";
sql = evalinsert(
"Select db.table.field1
        db.table.field2
        db.table.field3
   FROM db.table
  WHERE db.table.exprmt = 'blah'
    AND db.table.field = ^oven_id[1]^
    AND db.table.field In ^test_part_in_list^ ORDER BY test_part_id ASC");

Open Database( -- connection string info -- , sql, "dataset");