8.1 - Critical Code Generator Bug: Grid application

[nik]

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’)’] = “”;

[Giblet535]

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.

[nik]

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.

[robydago]

$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])']

[nik]

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.

[Giu]

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?

[nik]

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.