cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar

SQLFILE and Variables

Hi guys,

I am trying to tidy my script up. I have embedded in my script a very long sql query which on the top level is:

sqlQuery = "Select x from table where identifier =" || primaryidendtifer ||";"

I have saved this in an external .sql file and thought that I could load the query into the script to evaluated when required.

JMP doesnt recognise that it needs to place the variables into the sql script

Any help please

1 ACCEPTED SOLUTION

Accepted Solutions
pmroz
Super User

Re: SQLFILE and Variables

Here's another approach that uses save text file, load text file, and evalinsert.  With evalinsert you surround your variables with the caret character (^), and it substitutes the currently assigned variable values in to your string.

// Save the query to a file with a placeholder for the identifier

sqlQuery = "Select x from table where identifier = ^primaryidentifier^";

save text file("c:\temp\myquery.sql", sqlquery);

// Load the contents of the text file into a variable

sqlQuery = load text file("c:\temp\myquery.sql");

// Set the identifier using evalinsert

primaryidentifier = "12345";

sql_statement = evalinsert(sqlquery);

print(sql_statement);

"Select x from table where identifier = 12345"

View solution in original post

5 REPLIES 5
msharp
Super User (Alumni)

Re: SQLFILE and Variables

Something like

primaryidendtifer = 0;

sqltext = load text file("path.sql");

sqlQuery = substitute(sqltext, "primaryidendtifer", primaryidendtifer);

Alternatively,

Save ' sqlQuery = "Select x from table where identifier =" || primaryidendtifer ||";" ' into a .jsl file. Then use the include statement.  Including another JSL file will pass variables between the two scripts.  Just be careful not to overwrite common variables like "i" in for loops.

include("path.jsl");

Re: SQLFILE and Variables

I like this approach. so my assumption to the use of substitute is that the syntax 'substitutes' names in expression. So the syntax when called

will substitute the name with the required replacement.

Substitute(pattern,name,replacement)

I hope i have this correct. Please correct me if i have not

The alternative seems a simple approach. Which I like.

Thank you for your time

msharp
Super User (Alumni)

Re: SQLFILE and Variables

Per Scripting Index:
y = Substitue(x , patternExpr1, replacementExpr1, ...)

Returns a copy of string, list or expression x, replacing instances of each pattern expression with the corresponding replacement expression.

Yes you are correct.  With the addition that you can replace multiple patterns at once.

pmroz
Super User

Re: SQLFILE and Variables

Here's another approach that uses save text file, load text file, and evalinsert.  With evalinsert you surround your variables with the caret character (^), and it substitutes the currently assigned variable values in to your string.

// Save the query to a file with a placeholder for the identifier

sqlQuery = "Select x from table where identifier = ^primaryidentifier^";

save text file("c:\temp\myquery.sql", sqlquery);

// Load the contents of the text file into a variable

sqlQuery = load text file("c:\temp\myquery.sql");

// Set the identifier using evalinsert

primaryidentifier = "12345";

sql_statement = evalinsert(sqlquery);

print(sql_statement);

"Select x from table where identifier = 12345"

Re: SQLFILE and Variables

This approach looks very feasable. Thank you for making it look so simple.

Removing long sql scripts from my jsl script will be wonderful.

Thank you for your time