New issue for Error 78 on Reset in SQL

[Rick_UpperPark]

I have a customer who is receiving a error 78 - Invalid Number Of Parameters. I know this is generally caused by the length of the POSITION(VIEW) value exceeding the template generated limit of 1024 characters. see: What causes an Error 78 and/or sometimes "phantom" records in a browse?
I’m pretty certain this is not the case. I have a their database in my dev environment and do not get the error here. I’ve confirm the position length for this view is only 291 characters.
Our SQL Servers are both SQL 2019 (Theirs is CU18 and mine is CU16).

I have the Clarion database trace results from the client that is showing the error. It is indeed occurring on Reset(View) Previous(View). The error indicates “Size not equal for key INP:INSPECTORKEY”. This key consists of two LONG in Clarion and 2 INT columns in the SQL database.

038CCH(6) 15:11:37.196 RESETfile(VIEW:10474328:dbo.InspectorProject:0114E1280H|DBO.Project:0114D6848H|DBO.ProjectStatus:0114D6ED0H|DBO.Department:0114D76C4H)   Time Taken:0.00 secs
038CCH(6) 15:11:37.196 GET_PROPERTY(VIEW:10474328:dbo.InspectorProject:0114E1280H|DBO.Project:0114D6848H|DBO.ProjectStatus:0114D6ED0H|DBO.Department:0114D76C4H)   Time Taken:0.00 secs
038CCH(6) 15:11:37.196 SET_PROPERTY(VIEW:10474328:dbo.InspectorProject:0114E1280H|DBO.Project:0114D6848H|DBO.ProjectStatus:0114D6ED0H|DBO.Department:0114D76C4H)   Time Taken:0.00 secs
038CCH(6) 15:11:37.196 GET_PROPERTY(VIEW:10474328:dbo.InspectorProject:0114E1280H|DBO.Project:0114D6848H|DBO.ProjectStatus:0114D6ED0H|DBO.Department:0114D76C4H)   Time Taken:0.00 secs
038CCH(6) 15:11:37.196 SET_PROPERTY(VIEW:10474328:dbo.InspectorProject:0114E1280H|DBO.Project:0114D6848H|DBO.ProjectStatus:0114D6ED0H|DBO.Department:0114D76C4H)   Time Taken:0.00 secs
038CCH(6) 15:11:37.196 Size not equal for key INP:INSPECTORKEY
038CCH(6) 15:11:37.196 PREVIOUS(VIEW:10474328:dbo.InspectorProject:0114E1280H|DBO.Project:0114D6848H|DBO.ProjectStatus:0114D6ED0H|DBO.Department:0114D76C4H)  Error: Invalid Number Of Parameters  Time Taken:0.00 secs
038CCH(6) 15:11:37.196  INP:INPUNIQUE : 1405
038CCH(6) 15:11:37.196  INP:INSPECTORUNIQUE : 42
038CCH(6) 15:11:37.196  INP:PROJECTUNIQUE : 307
038CCH(6) 15:11:37.196  PRJ:PROJECTNUMBER : '21-R-5 (8845)'
038CCH(6) 15:11:37.196  PRJ:DESCRIPTION : '2021 Pipe Culvert Repairs/Replacements'
038CCH(6) 15:11:37.196  PRJ:PRJUNIQUE : 307
038CCH(6) 15:11:37.196  PRJ:STATUSUNIQUE : 8
038CCH(6) 15:11:37.196  PRJ:DEPARTMENTUNIQUE : 2
038CCH(6) 15:11:37.196  PBS:DESCRIPTION : 'Partial Shutdown'
038CCH(6) 15:11:37.196  PBS:FORECOLOR : 33023
038CCH(6) 15:11:37.196  PBS:PBSUNIQUE : 8
038CCH(6) 15:11:37.196  DPT:DESCRIPTION : 'Public Works Bureau Of Engineering'
038CCH(6) 15:11:37.196  DPT:DPTUNIQUE : 2

Table definition:

InspectorProject     FILE,DRIVER('MSSQL'),OWNER(GBL:Owner),NAME('dbo.InspectorProject'),PRE(INP),BINDABLE,CREATE,THREAD !Inspector Projects  
UniqueKey                KEY(INP:INPUnique),OPT,PRIMARY    !Primary Unique Key  
ProjectKey               KEY(INP:ProjectUnique,INP:InspectorUnique),DUP,OPT !Link to Project table
InspectorKey             KEY(INP:InspectorUnique,INP:ProjectUnique),OPT !Link to Inspector table
Record                   RECORD,PRE()
INPUnique                   LONG                           !                    
ProjectUnique               LONG                           !Unique Key Field    
InspectorUnique             LONG                           !Unique Key Field    
ChgDateTime                 STRING(8)                      !                    
ChgDateGrp                  GROUP,OVER(ChgDateTime)        !Record Change Date/Time Group
ChgDate                       DATE                         !Date of Record Change
ChgTime                       TIME                         !Time of Record Change
                            END                            !                    
                         END
                     END                       

SQL Table:

CREATE TABLE [dbo].[InspectorProject](
	[INPUnique] [INT] IDENTITY(1,1) NOT NULL,
	[ProjectUnique] [INT] NULL,
	[InspectorUnique] [INT] NULL,
	[ChgDateTime] [DATETIME] NULL
) ON [PRIMARY]

[jslarve]

Do you think the “Size not equal for key INP:INSPECTORKEY” line could be of significance? Do you know what sizes they’re referring to? If a column is NULL, does that get included in the size measurement?

[Rick_UpperPark]

I’m pretty sure it is relevant. That the key with the 2 LONG columns in it.
Not sure how you exceed the size of that since it is fixed.
All rows in the table have values for both columns and none of them are of a large value, all under 1000.

[seanh]

This is also mentioned in this topic:
Relevent bit:
Finally, something that may not be related to the Class but should be monitored in the migration, the use of PROP:WHERE and / or SET(KEY,KEY) in the same thread as a browse or FileDropDowns or FileDropCombos based on te same table was causing
Error Code 78 - Illegal Number of parameters. The last thing I remember was commented on in the Clarion11 group on 2020-11-02 in the topic ‘Re: Size not equal for key’ by Bjørn Øyvind Holm, also on the 6th in the topic ‘PTSS 43119’. You can view the PTSS log for more information and testing programs. They solved using ALIAS there.

Table Manager: A class to read tables with less code while optimizing for SQL backends

Good job Carlos and thanks for sharing. I imagine there is a lot of effort invested there. I wanted to provide some constructive criticism as feedback. Your Class is involved in a sensitive area in any Clarion application, which is datacentric, requiring a lot of care to avoid unforeseen events, and the complexity of the issue makes it very difficult to foresee everything, or that a Clarion version change does not end up affecting something that worked, like for example one that was mentioned …

[Rick_UpperPark]

Thanks, Sean. I’ll check that out in the morning.

Update: removing the set(key,key) logic on the table being used in the browse resolved this problem.

[Rick_UpperPark]

Sean, very interesting. I do indeed have a set(key,key) on the INP:InspectorKey in this browse procedure.
It’s odd that I cannot reproduce the problem in my dev environment, even with the customer’s database.
Not sure how I’m going to test to confirm this, but thank you very much for pointing me in this direction.

[jslarve]

@Rick_UpperPark

Not sure how I’m going to test to confirm this

Could CU18 have introduced this new behavior?

@Rick_UpperPark

(Theirs is CU18 and mine is CU16).

[Rick_UpperPark]

CU18 is a possible source, but doesn’t seem real likely.
I did just reproduce the error, but not using the exact same steps. Trying to pin it down now.
Interestingly, this application is in C9.1 but the post and PTSS reports @seanh referenced all talk about how a C11 release introduced the problem.

[jslarve]

In C11, they did make some changes regarding OPT. https://clarionsharp.com/blog/c11-update-released/