8.1 - Critical Code Generator Bug: Grid application

Generated code fails
Parse error: syntax error, unexpected ‘N’ (T_STRING), expecting ‘]’ in /www/htdocs/scriptcase-8.1/app/test/grid_TEST/grid_TEST_grid.class.php on line 208

To replicate:

Create grid application based on
SELECT NVL(A.REPORT_DESCR, ‘N/A’) AS REPORT_DESCR
FROM TEST A

Column aliases confuse code generator.

If sort by REPORT_DESCR is enabled generator will create extra code for column itself

$this->Cmps_ord_def[‘report_descr’] = " asc";
$this->Cmps_ord_def[‘NVL(A.REPORT_DESCR,‘N/A’)’] = “”;

To run application one will either have to disable sorting or comment out extra code:

$this->Cmps_ord_def[‘report_descr’] = " asc";
// $this->Cmps_ord_def[‘NVL(A.REPORT_DESCR,‘N/A’)’] = “”;

Yes. My experience: if you try to do more than the most basic SQL (or PHP, or JavaScript) within SC, SC will become confused.

I have created many db methods/functions to work around SC’s SQL interpretations. I even developed a naming convention: “SC_WORKAROUND###” so that they can be easily spotted in a dump. This doesn’t help with some supported databases though e.g., SQLite.

The real problem is that 100s of applications which worked in v7 and converted to v8.0 w/o issues stopped working after 8.1 upgrade.

$this->Cmps_ord_def['NVL(A.REPORT_DESCR,'N/A')']

The 'N/A inside the [’…’] looks like something that will break the string.

Did you try with double quotes (I don’t know if that’s ok with SQL, but worth the try)?
E.g.

SELECT NVL(A.REPORT_DESCR, [B]"[/B]N/A[B]"[/B]) AS REPORT_DESCR FROM TEST A

That would make SC generate this and maybe it would work

$this->Cmps_ord_def['NVL(A.REPORT_DESCR,[B]"[/B]N/A[B]"[/B])']

line
$this->Cmps_ord_def[‘NVL(A.REPORT_DESCR,‘N/A’)’] = “”;

should never be generated as it does not serve any purpose and it also breaks PHP code.
Sort order for actual field defined by
$this->Cmps_ord_def[‘report_descr’] = " asc";

Unfortunately, using “N/A” is not an option. SQL would treat it as column name thus resulting in SQL error.

Dont remember if oracle allows double quotes, but single are the standard.

Until solved a workaround is to use a view, but I understand is not enough due to number of apps involved.
Another option is not to use nvl function an checking value and rewriting on onRecord. Did you tried?

Actually, NVL is just an example, any SQL expression which includes single quote will break grid application.
I understand that view would be a proper way to handle this going forward.
However, there is no way to rewrite existing applications.
I do have perl script to automatically patch generated code and I can survive for a time being, but real fix would be greatly appreciated.

🚀 Scriptcase 9.12 is now available! Check out the new features here 👉🚀 Next Monday (02/24), Scriptcase 9.12 will be released! Check out the new features before anyone else 👉