topic Re: What is wrong in my function call? in Discussions

What is wrong in my function call?

Neo — Sat, 10 Jun 2023 23:37:15 GMT

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.

Re: What is wrong in my function call?

jthi — Tue, 21 Sep 2021 18:03:53 GMT

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

Re: What is wrong in my function call?

Neo — Tue, 21 Sep 2021 19:40:53 GMT

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

Re: What is wrong in my function call?

jthi — Tue, 21 Sep 2021 19:46:35 GMT

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).

Re: What is wrong in my function call?

Neo — Tue, 21 Sep 2021 19:56:41 GMT

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

Re: What is wrong in my function call?

Neo — Tue, 21 Sep 2021 20:03:20 GMT

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.

Re: What is wrong in my function call?

Craige_Hales — Tue, 21 Sep 2021 22:38:54 GMT

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...

might be interesting too.

Re: What is wrong in my function call?

jthi — Wed, 22 Sep 2021 09:42:27 GMT

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

Re: What is wrong in my function call?

Neo — Sat, 25 Sep 2021 23:15:47 GMT

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

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.

Re: What is wrong in my function call?

jthi — Mon, 27 Sep 2021 04:27:25 GMT

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.

Re: What is wrong in my function call?

Neo — Mon, 27 Sep 2021 09:03:32 GMT

Thanks @jthi . All my scripts and functions have the first line as

Names Default To Here(1);

It just happens (as mentioned in my previous post), this particular function did not run without commenting the above line.

In any case, if I use your suggested function, I get a data table with expected/correct data but the data table is called UntitledXX (XX happens to be 24 in my case). Any help to fix this would be very helpful. Thanks.

Re: What is wrong in my function call?

Neo — Mon, 27 Sep 2021 14:47:53 GMT

Adding

include ("getDataTable4partID.jsl");

before the function call seems to have solved all the issues I have ben having.