Home Blogs Stephen BallInterBase 2017 – Now available

InterBase 2017 – Now available

Posted by Stephen Ball on Thursday, 9 March 2017 in Tutorial

Embed, Deploy Relax – InterBase 2017

InterBase 2017

Here we are in early 2017 fulfilling the promises we made back in November 2016 to deliver a new version of our award-winning InterBase RDBMS. We referred to InterBase 2017 as Project BigBang during development because of the positive impact InterBase is delivering. We have been listening to you, our customers. This result is created by delivering some of the most sought after features, ISV’s and Fortune 100 customers have been asking for such as SQL Derived Table Support, and Server wide performance monitoring to enhance the ability of existing applications to use InterBase as a back-end RDBMS, and that’s just the beginning.

I want to say a big thank you to those who have been personally involved in the beta program, your input is invaluable and is shaping the future developments and direction, including already planned content for Update 1.

InterBase 2017 New Features

A highlighted summary of InterBase 2017 new features includes.

Interactive SQL (isql) updates including new reconnect command for use in isql and SQL scripts to reconnect to the latest successfully connected database. New –names command line option of the form –names <character set name>, to specify the character set to use for the current database connection. Exclusive Isolation Level which allows transactions to acquire an exclusive lock on a target table, and be the only ones able to execute SELECT, INSERT, UPDATE, and DELETE on a table. SQL Derived Table Support (more detail later in the article) Truncate Table command which allows the users and applications to empty the contents of a database table. This feature is useful for tables where rows require frequent deletion. The Truncate Table command performs faster, requires less I/O, and journals and archives much less information than an equivalent DELETE FROM table command. Transaction Wait Time specifies a period of time transactions wait for acquiring lockable resources. Single Line Comment which allows for adding a comment line of this form – – This is a comment line to an SQL statement.

Beyond these new features, there are two other upgrades including the fixing of known bugs and defects plus making the first sweep of a restored database a fast sweep so that any database that you restore is immediately marked as swept. Thus, the first sweep of that database is a fast sweep. There are some prerequisites to be taken care of to make use of this feature. You must perform a database restore with InterBase 2017 using either on–disk structure (ODS) 15 or ODS 16.

The details for each new feature and upgrades are available at this link.

SQL Derived Table Support

This new capability is a high demand one and warrants some additional discussion. The link above has further detail including an example of SQL code used to execute a search using derived table methodology.

First, derived tables are temporary sets of records that can be used inside other queries. These tables can shorten long queries, or break complex processes into logical steps. The query expression can be embedded directly in the SQL statement together with a correlation name used to identify the derived table.

Alternatively, query expressions named by an identifier can be listed in a clause that precedes the SQL statement and the named queries referenced by their identifiers in the SQL statement. This is informally known as “common table expressions” although the SQL standard does not officially recognize that nomenclature. A Common Table Expression (CTE) returns a temporary result set that can be referenced in SELECT, INSERT, UPDATE, or DELETE statements. CTE’s can help to simplify complex subqueries and joins, making the code more readable and easier to maintain.

So far as usage of derived tables is concerned, they can be seen as a View with a statement-level scope. A derived table allows developers the expressive flexibility to use a View-like structure without defining a database schema view. It also lets users obtain the same benefit in an ad hoc query without requiring database administration to create a view definition.

These qualities are in keeping with InterBase’s distinguishing reputation as an RDBMS with a small footprint, close to zero administration requirements, and a multi-generational architecture.

There will be some new demands and constraints to be aware of as well as migration matters going to InterBase 2017.

Users must have proper access privileges on the underlying base tables and views accessed by a derived table. The derived table syntax can be used in triggers and stored procedures as well as user applications. Dynamic SQL supports derived table syntax Interactive SQL (isql) supports derived table syntax. Embedded SQL (static) doesn’t support derived table syntax SQL statements that use derived table syntax only execute on InterBase servers or mobile editions running InterBase 2017 and subsequent editions. Any existing client can submit a SQL statement with derived table syntax using the InterBase DSQL syntax. Third-party supported DB frameworks, including InterClient, should pass through such statements without issues.

Derived tables and common table expressions has been the number one request from a large number of ISV customers migrating from MSSQL, and this feature will enhancing the ability of their existing applications to use InterBase as a backend RDMBS.

InterBase is the Future for Your Data Layer Needs

Consider these new features along with the current, easily configurable InterBase features like column-level data encryption, field-level change tracking (Change Views), user-specific data visibility, journaling, and point-in-time recovery. InterBase 2017 combines all these features to give you a lightweight, easy-to-manage, commercial-grade embeddable database solution that is ideal for distributed systems.

Tips for choosing the best ISV database for you?

If you are looking to choose an ISV database, visit the ISV database comparison on the InterBase pages. Here you will discover a number of questions to help you choose the right database for you.