SQLFILE and Variables

[#1]

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

[#2]

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");

[#3]

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

[#4]

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.

[#5 · Accepted solution]

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"

[#6]

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