Limitation of XMII setParam function

[Former Member]

Hi,

I have a question regarding to XMII setParam() function.

For example, there is a PI Query as follows:

SELECT ub.procedure_name, ub.recipe_name, sb.operation_name

FROM piunitbatch ub

INNER JOIN pisubbatch sb

ON ub.unitbatchuid = sb.uid

WHERE ub.unitbatchuid IN ([Param.1]) and sb.uid in ([Param.2]).

Param.1 is unitbatch_uid, Param.2 is operation_uid, and both of them are string list enclosed by single quote and separated by comma, i.e. '213cb601-3da7-490c-83b6-1a9e6f120384','c2e1296b-ba91-4789-90d4-2b9e16663d45'. When these two parameters are very very long, i.e. more than 13000 characters, after setParam() the query will crash and return an error message as "Error occurred while processing data stream, Connection reset".

Orignially, I thought this is PI Query limitation. But when I directly replaced param.1 and param.2 as unit_batch_uid and operation_uid string list, and run the query in XMII Query Template, the query works fine returning all records. However, if I keep param.1 and param.2 in the query statement and assign value to these two parameters, and I get connection reset error.

From this experiment, I think the limitation is not from PI Query but from XMII set parameter function.

So my question is that if XMII setParam() function has some maximum string length restriction for parameters?

Thanks in advance for your help.

Hongfang

[jcgood25 · Accepted solution]

Hi Hongfang,

The "Error occurred while processing data stream, Connection reset" is actually a UDS error and based upon the SELECT statement to PI I assume you are using the OleDB connector.

Temporarily set your General log level to "Informational" and then repeat your select all user activity on the web page. If your client side javascript is working properly and not throwing any errors when the .setParam and updateGrid or executeCommand is called to make the query run, the General log will show you all of the query properties sent to the xMII server, including the full (or abbreviated) Param.x and should even show you something like: Executing Query: SELECT ...... and the value of the param will be substituted in the statement. If the full statement is displayed accurately in the General log then the error is not related to the query or javascript, but would be once the request is sent to the UDS.

If it appears that the information is going to PI through the OleDB UDS then your troubleshooting efforts need to look there for problems (UDS error logs?).

If this does happens then you should enter a support ticket in either your partner portal login or in the customer support portal.

Best Regards,

Jeremy Good

[Former Member]

My first question is I assume you are setting Param.1 and Param.2 through JavaScript and not passing as URL params, right?

What if you split up the ub.unitbatchid "IN" string as two params (Param.1 and Param.2) as well as split the sb.uid into two params (Param.3 and Param.4).

So when either of the params are more than 13K characters, that's about 360 comma separated values. I can't image the query runs very quickly with that many values in an "IN" statement.

[Former Member]

Hi Ryan,

Yes, when users select unit procedures and operations from the webpage grid, I concat the unitbatch_uid and operation_uid as string and set Param.1 and Param.2 through javascript. If there are too many selections, i.e, more than 200 unit procedures and operations are selected, Param.1 and Param.2 string become very huge, and the XMII query crashed returning message as "Error occurred while processing data stream, Connection reset”.

Is the string split the only way to solve the problem? Also, if the string length inside Param.1 and Param.2, Param.3 and Param.4 also exceed 13000 characters, what should I do? The fact is that the application I am developing specifiy that users can maximum select 500 unit procedures and operations. In this extreme case, how to handle parameter setting?

Thanks

Hongfang

[Former Member]

How long does it take for a user to select 500 unit procedures and operations from an iGrid? That sounds a bit extreme, but it is a requirement so be it.

To allow for the maximum of 500, why not use Param.1 - Param.5 (100 per each param) for the procedures and Param.6 - Param.10 for the operations. You should be covered then. And you'll still have 22 more params for something else should you need them.

I would imagine there is a limitation to each Param, afterall, everything has a theoretical maximum based on memory size or bus architecture or whatever. However, I don't know what the length limitation is for Params. Perhaps one of the developers will jump on and answer that question.

[Former Member]

Hi, Ryan,

There is a "Select All" button under the grid which allows users to select all rows in the grid very fast and easily. However, in case users select all rows, i.e. 500 rows, developers will have a big headache to handle this extreme case. Haha.

It seems that string split is a possible solution.

Thanks again

Hongfang

[Former Member]

So,

its not a good idea to make users life easy by giving them the easy option to select all in one click

I too hope some one knows whats the maximum characters for 1 input param.

Looks like only solution is to split into many pieces and pass to many params...

[Former Member]

Interesting!

What happens if you test this from the Query Template itself? I mean instead of replacing the Patams in the query try to preload the parameters using the Parameters tab in the Query template.

Share the result after that.

[Former Member]

Hi,

Actually, the query was tested in both javascript setParam() and XMII QT Parameter tab, the result is the same returning error message as "Error occurred while processing data stream, Connection reset”.

That's why I am thinking the limitation not from PI Query but from xmii parameter set function.

Thanks a lot for your reply.

Regards

Hongfang