What is wrong in my function call?

[#1]

I am learning how to write and call a functions in JMP (coming from Matlab).

Trying to pull data for a tested part from database via a SQL query.

Wrote two separate scripts. One is a function with PartID as the input variable and the test data table as output.

Other script is the function call, but I do not seem to be able to pass the partID to the function. Where I am going wrong?

My function call

partDataTable = getDataTable4partID("PART16.0009")

My function

getDataTable4PartID = Function( {PartID},{partDataTable}, 
Open Database(
		"Driver=SQL Server;
		Server=XAB-SQL02;
		DATABASE=TBSDF;
		Trusted_Connection=Yes;",
		"SELECT * FROM dbo.View_BBX a WHERE a.Part like 'PartID' AND a.TestDateTime > '2021-02-01'",
		"partDataTable" // name of the output data table
    );
    
);

The SQL query itself works when PartID is replaced by the actual part number PART16.0009 and run separately spitting the desired data table, but when I use the function call, the data table still has 'PartID' in the Source/ Update From DB scripts in the empty data table. 

[#2 · Accepted solution]

Check Function from Scripting Index and from Programming Functions - Function . Also check Scripting Guide 

Currently you have PartID as only argument and partDataTable as local variable for getDataTable4PartID which you don't use. Most likely you want to have the function defined with something like this to have to arguments:

getDataTable4PartID = Function( {PartID, partDataTable}, 

After getting that sorted out, you have to figure out how to use the arguments in the expression inside the function. For PartID you want to concatenate it into the SQL-query and partDataTable you want to use directly in the Open Database:

Open Database(
		"Driver=SQL Server;
		Server=XAB-SQL02;
		DATABASE=TBSDF;
		Trusted_Connection=Yes;",
		"SELECT * FROM dbo.View_BBX a WHERE a.Part like '"||PartID||"' AND a.TestDateTime > '2021-02-01'",
	partDataTable // no need for quotes here
    );

[#3]

Thanks. This works. However, I noted that having partDataTable with quotes also works fine. Is is supposed to be like this?

[#4]

If you have it in quotes it will most likely name your datatable to partDataTable and not with the name you wanted (if you wanted to have it as an argument).

[#5]

@jthi . Hmm, but I am getting the name I want the output data table to be called (i.e. the same as my partID).  This is my function call.

partDataTable = getDataTable4partID("PART16.0009","PART16.0009")

[#6]

I just realized something and need to confirm if this is indeed the case. After making a change in the function, it appears that I need to run the function once for the changes to be applied. Just saving does not seem to be enough. Is this correct?

I had applied the quotes to partDataTable and saved my function. The function call worked as expected with the data table name I desire. But after running the function once, when I run the function call again, I get what you said I would get, i.e. partDataTable as the name of the data table file generated. 

[#7]

Yes (if I understand the question), the function "function(...)" must be executed to replace the compiled function in the variable it is assigned to, getDataTable4PartID in your example.

If that was as unclear as it looks, ask again...

Write Your Own Functions  might be interesting too.

[#8 · Accepted solution]

Yes, you will have to run the function once to get it "updated".

If you want the datatable to be named same as the PartID then you can just use one argument in the function. Here is a bit more complete example with quotes and without quotes version:

Names Default To Here(1);

getDataTable4PartID_withQuotes = Function({PartID}, 
	Open Database("connectionstring",
		"
		select '" || PartID || "' as value
		from dual
		",
		"PartID"
	);
);

partDataTable_withQuotes = getDataTable4PartID_withQuotes("PART16.0009");
Show(partDataTable_withQuotes);

getDataTable4PartID = Function({PartID}, 
	Open Database("connectionstring",
		"
		select '" || PartID || "' as value
		from dual
		",
		PartID
	);
);

partDataTable = getDataTable4PartID("PART16.0009");
Show(partDataTable);

For me these are printed to the log:

partDataTable_withQuotes = DataTable("PartID");
partDataTable = DataTable("PART16.0009");

JMP will return the datatable from the function (as it is the "last" line), but I don't feel comfortable trusting it so I would use local reference in function and then return the reference and the final function would be something like:

getDataTable4PartID = Function({PartID}, {dtTemp},
	dtTemp = Open Database("connectionstring",
		"select '" || PartID || "' as value
		from dual",
		PartID
	);
	return(dtTemp);
);

[#9]

Thanks @jthi. I am having two issues with what you have suggested.

If I call the function 

//Names Default To Here(1);
getDataTable4PartID = Function({PartID}, {dtTemp},
	dtTemp = Open Database("connectionstring",
		"select '" || PartID || "' as value
		from dual",
		PartID
	);
	return(dtTemp);
);

as

DataTable4PartID = getDataTable4PartID("PART16.0009");

I get a data table with expected/correct data but the data table is called UntitledXX (XX happens to be 24 in my case).

Also, note that I had to comment 

Names Default To Here(1);

without which the function call did not work. Not sure what is going on....any help would be very useful. 

[#10]

If Names Default To Here(1); breaks your script there is most likely something wrong with the namespaces. You should almost always start JSL scripts with Names Default To Here(1) and it shouldn't break anything.

My guess is that it is defined in wrong place (or called in wrong place), order of execution does matter with JSL.