Try OpenEdge Now
skip to main content
New and Revised Features
What's New in 11.7 : OpenEdge SQL
 

OpenEdge SQL

OpenEdge Release 11.7 contains OpenEdge SQL updates to provide the following features:
*Authorized Data Truncation (ADT) - Output Option – A new option, Output, was added to the settings for ADT. When theSQLTruncateTooLarge parameter is set to OUTPUT, SQL uses an alternate model of data truncation, in which data is allowed internally to exceed its defined size while data is operated upon at its actual, full size. When SQL outputs data to the application, SQL truncates any data that exceeds its size, to its defined size. This model provides consistency between SQL operations and the physical dimensions of database values. The application safely receives data in its logically defined size, after truncation. The default value for the SQLTruncateTooLarge parameter is OFF.
Note: When the SQLTruncateTooLarge parameter is set to OUTPUT, all SQL operations, such as comparison and sorting, use actual data and only the output is truncated.
*CDCOpenEdge SQL supports Change Data Capture(CDC), and implementations of CDC that can involve Extraction/Transformation/Load(ETL) applications that extract individual data changes for each table. Instead of dealing with the entire table, OpenEdge customers can selectively choose fields from an individual table to capture data and populate it to an external data source. This approach avoids replicating the entire database or an entire table, and provides scalability to the process. OpenEdge CDC is a scalable solution as it allows the CDC policy designer to determine the amount of data change detail needed.
For ease of interpretation of meta-data fields like _Change-FieldMap that are of the type VARBINARY and represent whether the corresponding fields in the Change table have changed or not, OpenEdge SQL provides two scalar functions:
*CDC_get_changed_columns
*CDC_is_column_changed
*JVMOpenEdge SQL uses a Java Virtual Machine(JVM) to execute Triggers, Stored Procedures, and User Defined Functions. Users can specify parameters which should be used by the JVM. JVM Parameters are the options that can be specified while starting Java using the command line. Users, with privileges to create or edit the db_name.oesql.properties files, can customize JVM parameters to control the behavior of the JVM. The JVM parameters used by SQL server are logged to the db_name.lg file.
*UDFOpenEdge SQL provides support for User Defined Functions(UDF) that allow users to extend SQL functionality. A User Defined Function, written in JAVA, contains the logic, accepts the input parameters, and returns a scalar value as the result. User Defined Functions can help reduce network traffic. A complex set of logic that has the potential to filter out a large portion of result set rows, like a Regular Expression that cannot be represented as a single scalar expression, can be expressed as a User Defined Function. User Defined Functions allow users the advantage of having modularity in their code, and also provides the flexibility of reusing UDF.
*Enhanced SQL Logging capabilititesOpenEdge SQL now provides enhanced logging capabilities and control over logging to a granular level. SQL server has the provision to record logging information to the file SQL_server_server-id_ddmmmyyyy_hhmmss.log. In this file name, the server-id corresponds to the server ID shown in the database log file (db_name.lg). While establishing a connection to the database, if logging is enabled and is set to a level between 1 and 4, users can find corresponding levels of detailed logging information in the log file. The level of detail in the log entry depends on the level of logging that is chosen. Level 1 logs the most basic and most important information. Increasing the logging level increases the level of detail that is logged to the log file.
For more information, see:
Manuals:
OpenEdge Data Management: SQL Development
OpenEdge Data Management: SQL Reference
OpenEdge Development: Internationalizing Applications