Prepared or callable statement has more than 2000 parameter markers.

[gadeyne.bram]

Hi,

I'm using Teiid 8.9 with sybase

I'm trying to execute a statement that results in the exception that is stated in the title. In Teiid 8.7 the same query was running fine.

I've attached the debug log.

I already tried changing the vdb.xml file

I changed the translator-name to sybase-override and added the following xml:

<translator name="sybase-override" type="sybase">

         <property name="MaxInCriteriaSize" value="1800"/>

    </translator>

Should I try something additional?

The query that is being produced contains multiple in statements with 1800 fields.Can I somehow change this to maximum 2000 in total?

[gadeyne.bram]

Ok an update on this matter. Now it works. I've also added

<property name="MaxDependentInPredicates" value="8"/>

There was something strange with this. First I added 9 for MaxDependentInPredicates but then Teiid generated 10 in statements. With 8 as a value it did generate 8 in statements.

[shawkins]

The defaults for Sybase are maxincriteriasize=250 and maxdependentinpredicates=7.  That is a nominal number of dependent values = maxincriteriasize*maxdependentinpredicates = 1750.

If you set maxincriteriasize to a higher value without restricting maxdependentinpredicates, then yes you would expect to see an issue.

As for seeing a different number of predicates than the max.  That can occur when there are multiple dependent predicates, which there are in this case.  When we attempt to use up to the max number of values we'll output a partial value set for one but since they are correlated will have to put out a partial value set for the other - but the logic doesn't seem to account for that against the max setting.  We should open an issue there.

One workaround for this case is that the multiple dependent sets are effectively redundant.  You have the join predicates:

wdp.admissionid = dd.PatientID, gd.PatientID = wdp.admissionid from which we infer dd.PatientID = gd.PatientID, dd.PatientID in <dependent values>, gd.PatientID in <dependent values>

If it is proper to have a relationship between dd and gd, then it would be better if the user query were written with that explicitly (using the join predicates dd.PatientID = gd.PatientID, dd.PatientID = wdp.admissionid) so that we would know that only 1 dependent in predicate should be used.

And in general you can always just disable the use of prepared statements.  Or we could add an enhancement to not treat dependent values specifically are bind values - however when they are not bind values with string and other long values that can cause the SQL string to be too long on some databases.

[gadeyne.bram]

Hi Steven,

Thank your for the feedback! I understand the reason and indeed it works if I change the logic by changing gd.patientid = wdp.admissionid to gd.patientid = dd.patientid.

I'll keep that in mind when writing queries.

[shawkins]

[TEIID-3268] Dependent values can exceed source settings - JBoss Issue Tracker was logged to ensure that we don't exceed the defaults when there is more than 1 dependent expression.