Code Snippet: How to resolve the error about using too many parameters – STARLIMS

Introduction

There are cases where you may get an error when trying to process too many folders or too many runs. The error will look like this:

SQL error: SQL-08003: The incoming request has too many parameters. The server supports a maximum of 2100 parameters. Reduce the number of parameters and resend the request.

SQL stmt: select FOLDERNO from ORDERS where ORDNO in ([more than 2100 ORDNOs here])

How can this be avoided

In the QM Dictionary there is procedure called Utilities.Convert.Build1000Array that allows you to break the array of values passed in the IN clauses to smaller junks and this way avoid the error. Here is an example, the following is the SLL code that fails:

:DECLARE sRasSampNoList;

sRasSampNoList := BuildStringForIn(aRasSampNo);

/*Set last spec category of new test plan when the one in RASORDERS does not exist in new test plan;

bRet := SqlExecute("Update RASORDERS set SPECCATEGORY = (Select SPECCATEGORY
from SPEC_SETS where SPECNO = (Select max(ss2.SPECNO)
from SPEC_SETS ss2 where ss2.SAMP_POINT_STEP = SPEC_SETS.SAMP_POINT_STEP ) and SAMP_POINT_STEP = {fn convert(RASORDERS.SP_CODE, SQL_VARCHAR) )
where SPECCATEGORY not in (Select SPECCATEGORY from SPEC_SETS where SAMP_POINT_STEP = {fn convert(RASORDERS.SP_CODE, SQL_VARCHAR) ) and RASSAMPNO in " + sRasSampNoList);

Here is how the modified code should look like:

:DECLARE sRasSampNoList, aRasSampNoList);

sRasSampNoList := BuildString(aRasSampNo);

sRasSampNoList := StrTran(sRasSampNoList, " ", "");

aRasSampNoList := BuildArray("'" + StrTran(sRasSampNoList, ",", "','") + "'");

/*Set last spec category of new test plan when the one in RASORDERS does not exist in new test plan;

bRet := SqlExecute("Update RASORDERS set SPECCATEGORY = (Select SPECCATEGORY
from SPEC_SETS where SPECNO = (Select max(ss2.SPECNO)
from SPEC_SETS ss2
where ss2.SAMP_POINT_STEP = SPEC_SETS.SAMP_POINT_STEP) and SAMP_POINT_STEP = {fn convert(RASORDERS.SP_CODE, SQL_VARCHAR)}) where SPECCATEGORY not in (Select SPECCATEGORY from SPEC_SETS where SAMP_POINT_STEP = {fn convert(RASORDERS.SP_CODE, SQL_VARCHAR)}) and " + DoProc("Utilities.Convert.Build1000Array",{"RASORDERS.RASSAMPNO", aRasSampNoList}) );