topic Re: SQLFILE and Variables in Discussions

SQLFILE and Variables

Thu, 22 Oct 2015 12:22:43 GMT

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

Re: SQLFILE and Variables

msharp — Thu, 22 Oct 2015 16:02:19 GMT

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

pmroz — Thu, 22 Oct 2015 17:41:34 GMT

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

Fri, 23 Oct 2015 06:57:31 GMT

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

Re: SQLFILE and Variables

Fri, 23 Oct 2015 06:58:58 GMT

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

Re: SQLFILE and Variables

msharp — Fri, 23 Oct 2015 14:31:13 GMT

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.