ElevateDB 2.04 is now available for download. If you're an existing customer, then you should be receiving an email shortly with download instructions. You can view the release notes, including any breaking changes, on the download page before downloading.

Breaking Changes Addendum

There is an addendum to the 2.04 breaking changes for the SQL/PSM FETCH, INSERT, and UPDATE statements. The original breaking change is described as follows:

• You must now use string expressions for column references in the SQL/PSM cursor FETCH, INSERT, and UPDATE statements. Existing routines (functions, procedures, triggers, jobs) in existing configurations and database catalogs will still function properly using the old syntax, but will require the new syntax as soon as they are altered. Routines that are created with 2.04 or higher, and non-persistent routines such as scripts, will require the new syntax.
  
  For example, this is an example of the old way of referencing a column:
  
  

  CREATE FUNCTION ColumnValue(IN TableName VARCHAR COLLATE ANSI_CI)
  RETURNS VARCHAR COLLATE ANSI_CI
  BEGIN
     DECLARE ResultCursor SENSITIVE CURSOR FOR Stmt;
     DECLARE Result VARCHAR DEFAULT '';
  
     PREPARE Stmt FROM 'TABLE '+QUOTEDSTR(TableName,'"');
  
     OPEN ResultCursor;
  
     -- Notice that the Company column name is "hard-coded"
     FETCH FIRST FROM ResultCursor (Company) INTO Result;
  
     CLOSE ResultCursor;
  
     RETURN Result;
  
  END

  
  This would be the same function, but converted to using a dynamic column name specified as a string:
  
  

  CREATE FUNCTION ColumnValue(IN TableName VARCHAR COLLATE ANSI_CI)
  RETURNS VARCHAR COLLATE ANSI_CI
  BEGIN
     DECLARE ResultCursor SENSITIVE CURSOR FOR Stmt;
     DECLARE Result VARCHAR DEFAULT '';
  
     PREPARE Stmt FROM 'TABLE '+QUOTEDSTR(TableName,'"');
  
     OPEN ResultCursor;
  
     -- Notice that the Company column name is specified as a string
     FETCH FIRST FROM ResultCursor ('Company') INTO Result;
  
     CLOSE ResultCursor;
  
     RETURN Result;
  
  END

In addition to the above, there is also one other issue that may pose a problem for existing procedures, functions, jobs, or triggers in an ElevateDB configuration or database catalog, and that is the use of reserved function names as column names with these SQL/PSM statements. For example, this procedure will cause an issue with the new way that these statements are parsed:

CREATE FUNCTION "GetMaxOrderID" (IN "LookupID" INTEGER)
RETURNS INTEGER
BEGIN
   DECLARE Result Cursor for Stmt;
   DECLARE ResultID integer;
   PREPARE Stmt from 'SELECT MAX(OrderID) AS Max FROM MyTable WHERE LookupID = ?';
   EXECUTE Stmt using LookupID;
   FETCH FIRST from Result(Max) into ResultID;
   RETURN ResultID;
END

Preparing this function will cause a parse error for the FETCH FIRST statement at the Max column reference. Due to the changes for 2.04, ElevateDB will now think that the Max reference is for the MAX() aggregate function and report an error. To correct this, alter the function so that the Max column name is enclosed in single quotes and follows the new conventions for specifying column names in FETCH statements:

CREATE FUNCTION "GetMaxOrderID" (IN "LookupID" INTEGER)
RETURNS INTEGER
BEGIN
   DECLARE Result Cursor for Stmt;
   DECLARE ResultID integer;
   PREPARE Stmt from 'SELECT MAX(OrderID) AS Max FROM MyTable WHERE LookupID = ?';
   EXECUTE Stmt using LookupID;
   FETCH FIRST from Result('Max') into ResultID;
   RETURN ResultID;
END

If you have any questions regarding this change, please feel free to email or ask on the support forums.

---

Tags: ElevateDB, New Releases

Permanent Link

Comments (0)	You must be logged in to comment