SQL Changes
SQL Changes
Correction to INSERT and UPDATE Compiled in Display Mode
In rare cases, INSERT and UPDATE can be compiled in DISPLAY mode. In previous releases, Caché was not correctly converting values for INSERT and UPDATE compiled in DISPLAY mode. This has been corrected. If you use #sqlcompile select=DISPLAY mode in your embedded SQL for INSERT or UPDATE and your input values are in Logical mode, you should update your application code to change the mode to logical or to supply display values as input.
JDBC Return Value Change for Auto Generated Key Value for Multi-Property IDKEY Index
JDBC can automatically generate a key value if any of the IDKEY values is system assigned. For example, if JDBC inserts into a child table with a childsub counter field, it can auto generate a key value. In previous releases, JDBC returned the auto-generated ChildID, the childsub counter field value. This was not sufficient to identify the row just inserted. In this release, JDBC returns a string consisting of the ParentID followed by “||” and then the ChildID. This is sufficient to identify the newly inserted row. However, if you have code that is processing the return value consisting of just the ChildID, you should modify your code to use the new return value.
Make Child Table Read-only If Some Indexes Are Not Projected
In this release if a child table has any indexes that are not projected, the table is read-only and you cannot perform an insert, update, or delete on the table. In previous releases, it was possible to perform these actions, but that action could corrupt an index. If a table projected from the MVSASSOCIATION includes an indexed property, it will be marked as read-only.
New SQL.JDBC Parser
We have replaced the SQL.JDBC parser with one that has significant performance improvements. We believe that it should produce the same results as the parser in previous releases, but there is the possibility that some unusual SQL statements will produce different results.
SQL JDBC UpdatableResultSet Reports Deletions More Consistently
In previous releases, Caché did not adhere to the JDBC specification on how our metadata reports updateable resultsets behavior when records are deleted. In this release, Caché handles these deletions more consistently and follows the behavior described in the specification.
SQL JDBC SysList and SysListProxy Deprecated
The SysList and SysListProxy classes are deprecated. They are included in this release, but we recommend that you replace them with the CacheList, CacheListBuilder and CacheListReader classes.
Do Not Recalculate Computed Field When Field Is Updated But Value is Unchanged
In previous releases an SqlComputeOnChange field computation would be triggered even if the value for the field updated was the same as the previous value. In this release, the computation is done only if one of the field values changes.
Corrective Action for Frozen Plans Created with Earlier Versions of Caché
If you created a plan with Caché 2016.2 and the plan contains any INSERT ... SELECT statements, you need to take actions to correct an error in the plan data.
If you upgrade from Caché 2016.2 to Caché 2017.1 or a later version, these plans will exhibit the following behavior:
-
If the statement plan is Unfrozen, the plan will not be set to Frozen/Upgrade. It will remain Unfrozen. You should recompile the container code (routine, class) that contains the INSERT ... SELECT statement (or Purge cached queries).
-
If the statement plan is Frozen, the plan will be put into an error state with the following message:
Frozen plan metadata for INSERT/SELECT statement frozen prior to version 2017.1 potentially incorrect. Must unfreeze, recompile (or Purge and re-prepare) and then re-freeze if wanted frozen.
