Save Grid not working (Giving a modal error on 'apply')

[ibatey]

I’ve just come back to this feature, having switched it of (as unusable) a few years ago.

When I add a saved grid to my toolbar and press ‘save’ it works as expected. However, when you try and apply said saved grid I just get a modal window popping up showing a lot of (unrendered) HTML that is effectively giving the helpful error message ‘Error’.

Any idea what I’m missing here?

PS… it also happens if I use the persistent state option, but this time on the save!

[ibatey]

I’ve also noticed that when this error occurs, it effectively screws up the sort buttons at the tops of the columns. Giving the same error.

I can simulate the fault by simply clicking on apply to any save. It gives the error and then the sorts don’t work, with the same error. If I logout and in again then the sorts work OK so it is definately the apply save causing the problem.

It is true on previous V9 versions as well… I’d just switched ‘save’ off on the older version and am testing it in the latest release

[Danilo_Lima]

Dear @ibatey,

When we carried out some tests applying the “Save grid” feature, the grid state went to what had previously been saved normally. Tests were performed on the latest version of Scriptcase (9.10.018(5)).

I noticed that the charset you use in the application is ISO-8859-1. We carried out tests both on it and on UTF-8.

Is there any other detail that you can share with us, so that we can try to simulate the possible error?

Best regards!

[ibatey]

Cant really tell you much, but here is what I can:

• It happens on all grids I’ve tried it on, so not related to a specific grid
• I’m using PHP8.1 with the latest Scriptcase (Installed 2 days ago and upgraded since)
• The fact it also screws up the column sorts must be indicative of something
• I don’t recall this specific problem previously, when I did some testing on an earlier version but I don’t have a workable copy of that version to test it. Though I did replicate the fault on an old version when I upgraded the PHP to 8.1 (Without recompiling/generating)
• It happens on the development environment AND the deployed version.
• The save persistent state gives a similar problem (Save, not apply)
• I can create a fresh grid in the new version (With no WHERE clauses) and the save grid functionality seems to work OK. Its the imported grids (Which all have slightly complex sql) that have a problem

Regards

[ibatey]

Been performing some further digging to try and find the error.
I managed to get an error message by right clicking the Apply button and open in new window. This gives the following message:

It looks like the SQL is truncated.
It should be:
SELECT
Actions_Key,
Ref,
Project,
Workstream,
ActionHeader,
Assigned,
Due,
Status,
Date_Closed,
Cat1,
Comments,
Notes,
dateRaised,
event_color
FROM
Actions
WHERE [SQLPrep] AND ([GridfilterAct])
Order by Due
For the record, the SQL_Prep variable is 'Project=‘TEST1234567890123456’ANDWorkstream IN (‘default’,‘xxxxxx’)’ (taken from the data in session functionality)

Using debug mode, the sql on normal operation shows:

(pdo-mysql): select count(*) from Actions where (Project='TEST1234567890123456' AND Workstream IN ('default','xxxxxx') AND (1=1))

[ibatey]

Final bit of info…
I also get an array error when opening ‘apply’ in a new window… the grid appears with incorrect contents so it might be the array error messing up the SQL:
Warning: Undefined array key 1 in C:\Program Files\NetMake\v9-php81\wwwroot\scriptcase\app\IDMB_Project_System\grid_actions1\grid_actions1_save_grid.php on line 479 Warning: Undefined array key 1 in C:\Program Files\NetMake\v9-php81\wwwroot\scriptcase\app\IDMB_Project_System\grid_actions1\grid_actions1_save_grid.php on line 488 Warning: Undefined array key 2 in C:\Program Files\NetMake\v9-php81\wwwroot\scriptcase\app\IDMB_Project_System\grid_actions1\grid_actions1_save_grid.php on line 488 Warning: Undefined array key 2 in C:\Program Files\NetMake\v9-php81\wwwroot\scriptcase\app\IDMB_Project_System\grid_actions1\grid_actions1_save_grid.php on line 488

[ibatey]

I’ve performed lots of tests on a new grid created using the latest version and using the same sql table with the same SQL select statements. This seems to work fine.
HOWEVER, this is NOT a valid resolution for my problem as I have dozens of grids that would take me weeks to rebuild and test from scratch.

Further to these tests, using a grid that previously failed… if I remove the filter option from the save grid, then I do not get an error any more. However, if I use the refined search, then the error reappears

It looks like the parsing of the filter is causing the problem somewhere.

[ibatey]

@Danilo_Lima Seems like there is nobody interested in Netmake?

While there is the usual radio silence, I’ve been trying to get to the bottom of this… (AND FAILED).

I can now state that the application of the save is doing something incorrectly to the SQL statements

The correct SQL is:

When simply saving the grid… aftr selecting a scenario without applying any other filters, the SQL gets changed to an incorrect version:

As you can see, it has lost a considerable chunk of the SQL. (The AND WORKSTREAM IN bits as well as the bracket, etc) This is the thing that is creating the error popup… (Debug mode adds the error details to the popup)

I REITERATE… I MADE NO CHANGES TO ANY SELECTIONS… I simply clicked on save and then reapplied it.

In addition, the ONLY thing that the Save grid does is to save the columns… all other options are switched off for testing.

Once this error is made, it saves it to several Netmake variables, which is why it propagates into other filters, sorts or searches.

FINAL NOTE: If I make the SQL where clause shorter than around 30 characters, the save grid works as expected… How hard can it be to reproduce this?

[Danilo_Lima]

Thank you for your feedback with all the information above.

I noticed that you mentioned at the beginning of the topic that it only occurs with previously created applications, correct? When creating an application from scratch in version 9.10.023, the error is not returned, correct? Or is this also happening with applications created from scratch using it?

Please confirm this to us so that we can proceed with the case.

Best regards!

[ibatey]

It happens on newly created grids as well as old ones which were migrated. The tests with a new grid didn’t have such a long SQL WHERE clause so they appeared to work. I suggest you create one with a long WHERE and test it. You can see the one I did in the posts above.

Ian

[ibatey]

@Danilo_Lima On further testing, it appears that it isn’t just the length of the WHERE clause that is a problem. It seems to only happen if you have a long set of WHERE conditions in a global variable. I use a global [SQLPrep] to store the where clause and if it gets too long then this causes a failure. If I just key in the full where clause manually, then it appears to work even if long.

[ibatey]

@Danilo_Lima I’m being stubborn and not letting this go as its so important. I’ve done some MORE testing and I think I can narrow the problem down to how the SELECT clause is being stored in the Saved grid file.

If I manually key in the SELECT (rather than as a global variable), then the saved grid works and the saved file looks like this:
pub3 (2.5 KB)

If i replace the SELECT with a global variable (That is long), then the saved file looks like this:
pub1 (2.6 KB)

The difference is that the second (non-working) version has some line feeds to make the select go over several lines. I think the ‘apply’ process is interpreting the line feed as an end of SELECT, this causing the error. Like this:

If I manually edit the incorrect one by removing the Line feeds it then works.

So… although it is fairly common to have CRLF in SQL (To make it easy to read), I have removed it from my function which generates the global variable… hopefully that will fix the problem everywhere, testing will prove one way or the other.

I’d also suggest that you adjust your ‘apply’ functionality to ignore CRLF in the Save file so that it is more resilient in the future!