Watch, Follow, &
Connect with Us

Stephen Ball

InterBase XE7 – Update 1 Released

InterBase XE7 Update 1 Overview

InterBase XE7, update 1 went live on Friday and brings with it SQL language enhancements for Change View, and some updates to IBConsole and the availability of InterBase ToGo for XE7

Full details about the update are available on the InterBase DocWiki

Change View Updates

There are 2 major additions to InterBase Change Views in Update 1

DROP SUBSCRIPTION
Change View SQL Extensions

Drop Subscription

Prior to update 1 you had to manually drop a subscription via an SQL statement. This was a temporary solution and has now been replaced with the correct SQL syntax

DROP SUBSCRIPTION <subscription name> [RESTRICT | CASCADE]

If you want to remove a subscription that has subscribers you must use the CASCADE command as RESTRICT is the default.

Change View SQL Extensions

InterBase SQL now includes additional language to support querying about the changed state of specific fields via the <FIELD> IS [NOT] {CHANGED | INSERTED | UPDATED | DELETED} clause.

Take the following example.  Lets imagine we have two employee records that have been modified.

EMP_NO 39 has their DEPT_NO changed
EMP_NO 109 has their SALARY changed.

At run time you want to select only those that have had a SALARY change prior to processing staff payments. To achieve this first, a Change View subscription is set active (making any SQL statement subsequently run only return the delta for that subscriber). Then SQL where clause then filters further where the SALARY IS UPDATED.

SET SUBSCRIPTION sub_employee_changes at ‘MyDeviceID’ ACTIVE;
SELECT EMP_NO, DEPT_NO, SALARY FROM EMPLOYEE WHERE SALARY IS UPDATED;

EMP_NO DEPT_NO SALARY
——– ———- ———-
109 600 75000

Regardless of what you select, the commit will still bring

InterBase IBConsole Updates

InterBase IBConsole has a number of new features, introduced in Update 1 designed to get you started quicker.

Getting Started with InterBase
Recent databases window
Change Views Integration

IBConsole Start HereIBConsole Start Here

Getting started with InterBase

When you open IBConsole now you find the new InterBase – Start Here page as a tab at the top of the screen. The start here page includes containing details about Community and featured posts, as well as training videos and direct links to the documentation. While an offline page ships with IBConsole, If you want to see this page now, please visit http://www.embarcadero.com/starthere/interbase/index.html

Having this page online allows us to improve our communication with our customers about critical information and appropriate news.

Recent Databases

There is also a new feature in the bottom left of the screen (see image above or below) enabling you to quickly connect back to the recent database you have been working on.

This windows shows you the database name, server and also the when you last accessed the database via IBConsole.

Change Views

Change View IBConsole

IBConsole also now includes access to work with subscriptions.

InterBase ToGo XE7

InterBase XE7 ToGo trial edition is now available for download – more details to come soon about ToGo.

The post InterBase XE7 – Update 1 Released appeared first on Stephen Ball's Technical Blog.

Posted by Stephen Ball on March 12th, 2015 under Uncategorized | Comment now »


InterBase Change Views Demo and Video

InterBase Change Views Example Demo

This week I hosted a Skill Sprints session looking at InterBase Change Views with an example set of code working around a Pharmacy needing to keep centralised drug information updated at distributed pharmacies.

The example code will ship with the next major release of the RAD Studio products – which you can get for free with the current RAD offer

This InterBase Change Views Example demo shows off this powerful way to track on the server what data is changing in a very lightweight and scalable way.

This Skill Sprint Video for InterBase Change Views follows on from the recent blog posts I have done on Change Views which are available here:

InterBase Change Views blogs

What is a Change View?
Creating a Change View
Using a Change View

InterBase Change Views in Spanish

Last week CodeRage III Brazil ran with a host of great sessions in Spanish, covering RTTI, App Tethering, DataSnap and FireDAC and also InterBase. Back in December I hosted the product Launch of InterBase XE7 at CodeRage 9, and it has been great working with Dormevilly and Fernando to get an updated Spanish version done, including some of the great capabilities we have today with Change Views. Great job guys :-)

The post InterBase Change Views Demo and Video appeared first on Stephen Ball's Technical Blog.

Posted by Stephen Ball on March 7th, 2015 under Uncategorized | Comment now »


What is an embedded database?

A common question for a developer is how to store data. While file formats like INI files. XML, CSV etc provide a simple way to store data, they are often not secure enough or provide enough features for most applications. This is when a data store is required. One such option is an embedded database.

What is an embedded database?

An Embedded database is one that can be deployed inside or along side you application. They are called embedded as they can be silently deployed / installed to provide database support without the need of a separate admin intensive process.  Embedded databases should be fast, have a small foot print, provide admin free capabilities all at the same time as providing full features SQL capabilities. Unfortunately, not all embedded databases provide complete capabilities, InterBase however is very feature rich in all editions. For more about Embedded databases and the different types they come in, watch the short video below.

Follow these links for

More about InterBase, the embedded database
Your free Developer edition or trial of InterBase

The post What is an embedded database? appeared first on Stephen Ball's Technical Blog.

Posted by Stephen Ball on February 27th, 2015 under Uncategorized | Comment now »


Connecting to InterBase from Visual Studio

I was recently asked how to connect to InterBase from Visual Studio, and while its not something I have done before, luckily I know a man who has. – Thanks to Gabe Goldfield for checking the example below with InterBase XE7 and Visual Studio 2013 Ultimate.

Connecting to InterBase from Visual Studio

InterBase currently supports an ADO.Net driver that works with InterBase XE, XE3 and XE7 providing both 32bit and 64bit support. Full details here.

Prerequisites

You will need to have .Net 2.0 SDK with update.
Microsoft Visual Studio 2005 or above.
InterBase XE or above.

Installation Instructions

Run the InterBase ADO.Net 2.0 installer.

Usage Instructions

Start Visual Studio 2005/2008
File new C# Windows application
Project – Add Reference and add the AdoDbxClient.dll, DbxCommonDriver, DBXInterBaseDriver to your project.
Add a DataGridView component to your Windows Form
The sample code below fills a DataGridView component with the contents of the employee table of the employee.gdb sample InterBase database:

Code Example

>>>
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using Borland.Data;
using Borland.Data.Units;
using System.Data.SqlClient;
using System.Data.Common;
namespace IBXEApplication1
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
ReadData(getConnection());
}
public DbConnection getConnection()
{
// DbProviderFactory factory = DbProviderFactories.GetFactory
// ("Borland.Data.AdoDbxClient");
DbConnection c = new TAdoDbxInterBaseConnection();
//DbConnection c = factory.CreateConnection();
c.ConnectionString = "Database=C:\\Embarcadero\\InterBase\\examples\\database\\employee.gdb;User_Name=sysdba;Password=masterkey";
return c;
}
public void ReadData(DbConnection conn)
{
string sql = "select * from employee";
DbCommand cmd = conn.CreateCommand();
cmd.CommandText = sql;
conn.Open();
DbDataReader myreader = cmd.ExecuteReader();
dataGridView1.DataSource = myreader;
DataSet ds = new DataSet();
DataTable dt = new DataTable("employee");
ds.Tables.Add(dt);
ds.Load(myreader, LoadOption.PreserveChanges, ds.Tables[0]);
dataGridView1.DataSource = ds.Tables[0];
myreader.Close();
}
private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
{

}
}
}
<<<

The post Connecting to InterBase from Visual Studio appeared first on Stephen Ball's Technical Blog.

Posted by Stephen Ball on February 24th, 2015 under Uncategorized | Comment now »


InterBase Change Views – Part 1 – What is a change view

This is the first post of a series on InterBase Change Views and is intended to be a high level overview of the new powerful change view technology that is patent pending and part of InterBase. Following this post I plan to show more posts taking the concept of Change Views into the real world with some real application examples.

InterBase Change Views

InterBase XE7 introduces a new way to track data changes to the database called Change Views.

Change Views are a new “subscription based” model allowing you to “subscribe” to data; once subscribed you are able to ask the database at a later time, spanning connections, for what has changed.

This is an especially cool, low cost way to reduce network traffic, mobile data costs and development time when tasked with keeping multiple remote database caches up to date. (especially if you have large tables to keep up dated).

Change Views are simple to use and reduce the upfront planning needed for building in data tracking that history has shown to be inherently complex and error prone.

Best of all change views has zero impact on database performance regardless of the number of subscribers!

Subscription & Subscribers

A core concept to change views is that of Subscriptions and Subscribers.  InterBase Change view allows multiple subscribers to a subscription.

A subscription is defined once and then can be connected to by multiple users. Once defined, you can control who can subscribe thanks to the InterBase inbuilt user security.

Subscriptions work at field level and can be defined to track either an entire table or multiple tables. Subscriptions can also be defined to track Inserts, Updates or Deletes (or a mix of)

Multiple devices per subscriber

In addition to allowing specific users to subscribe to data, you can use a single user to have multiple destinations that are subscribing. This allows a user to subscribe for different devices such as their Phone, Tablet, Laptop or for a single user to be used programatically to distinguish multiple sites – e.g. Office 1, Office 2 etc.

This is done using the “at” verb when connecting to the subscription.

What Change Views are not!

Change Views are not an auditing system. While you can track deleted records, Change View are about identifying what has changed without keeping every value that it has been. If you want to do that, then continue to use Triggers and logging tables for the values you need to audit.

Video – Change Views

An overview of Change Views is available in the InterBase product address from CodeRage 9. I will be digging into more code based versions through this series. This covers the syntax used

Documentation – Change View

There is a great introduction to InterBase Change Views in the release notes for InterBase XE7
http://docwiki.embarcadero.com/InterBase/XE7/en/What’s_New_in_InterBase_XE7#Change_Views_Feature

 

The post InterBase Change Views – Part 1 – What is a change view appeared first on Stephen Ball's Technical Blog.

Posted by Stephen Ball on February 7th, 2015 under Uncategorized | Comment now »


InterBase Change Views – Part 2 – Creating a Change View

If you have ever spent time planning how to identify what data has changed, then change views is about to simplify the way you develop for ever!

If you haven’t already read Part 1 of InterBase Change Views this provides a high level overview of the technology.

Steps for creating a Change View

Rather than having to create and manage triggers to add records into log tables or planning additional date fields into your metadata (and hoping date & time changes don’t effect you) Change View are easily added to a project once the simplified architecture is complete.

There are two essential steps in creating a successful change view:

Defining the purpose of the Change View and the data you want to track
Defining who can subscribe to the change view

1) Creating a change view

Lets start with a simple example. We want to keep our sales fulfilment application up-to-date with the latest stock and suppliers information. This data is stored in two tables, INVENTORY and SUPPLIERS.

Example 1 – Multiple Tables

CREATE SUBSCRIPTION
sub_stock
ON
INVENTORY FOR ROW (INSERT, UPDATE, DELETE),
SUPPLIERS FOR ROW (INSERT, UPDATE, DELETE)
DESCRIPTION ‘Track stock and supplier changes’;

Subscriptions are created with the CREATE SUBSCRIPTION statement followed by a subscription name that you will need to use in SQL later on.

The simple example tracks all columns in the tables INVENTORY and SUPPLIERS for Inserts, Updates and Deletes.

The description allows easy identification of the subscriptions purpose later on.

Example 2 – Specifying Columns

We may have a requirement to track product name changes for our application. We can do this also with a change view.

CREATE SUBSCRIPTION
sub_stockname
ON
INVENTORY(ITEM_NAME) FOR
ROW (INSERT, UPDATE, DELETE)
DESCRIPTION ‘Track stock name changes’;

Example 2 is more granular than example 1 and tracks only a single column ITEM_NAME in the INVENTORY table. This is done by providing a comma separated list of field names for the table that you want to track.

Example 3 – Tracking Deletes

We may also want to track deletions from a specific table, lets say ORDERS. A Change Views just tracking DELETE activity on  a table will provide a way to recover data if it is deleted

CREATE SUBSCRIPTION
sub_orderDeletes
ON
ORDERS FOR
ROW (DELETE)
DESCRIPTION ‘Track Order Deletions’;

Using a Change View to track deletes is a powerful feature as you can completely recover the record at its state when it was deleted.

2) Defining who can use a change view / subscription

Once a subscription is created, you need to grant SUBSCRIBE access to the subscription. To do this use the standard GRANT syntax. e.g. (for user SYSDBA)

GRANT SUBSCRIBE ON SUBSCRIPTION sub_stock TO SYSDBA;

InterBase Documentation

For more on Change Views visit the InterBase DocWiki

In my next blog, I’ll look at using a change view

The post InterBase Change Views – Part 2 – Creating a Change View appeared first on Stephen Ball's Technical Blog.

Posted by Stephen Ball on February 7th, 2015 under Uncategorized | Comment now »


InterBase Change Views – Part 3 – Using a change view

In Part 1 I explored the concept of change views, before how to create a Change View ready for use in Part 2. In Part 3 we are going to explore how to use a Change View to collect data that has changed in your subscription.

Using an InterBase Change View to fetch data deltas

Once a Change View Subscription has been created and access to it has been granted (to either users or roles), then its time to start using your Change View!

The following steps are the high level overview of using a Change View.

Start a Transaction in SnapShot isolation mode.
Set the Subscription active
Run your select statement to fetch you delta
Once you have collected the delta commit your transaction to bring your Change View up to date.

SnapShot Mode

OK a bit of background as we all love to know how things work..

InterBase is powered by a multi-generational architecture which is exceptionally useful when data integrity is important. imagine you need to run multiple financial reports while the system is live;  Imagine a connection drops part way through a big data change how do you know what has changed? Multi-generational architectures and transactions makes this possible.

Using a transaction set to SnapShot mode we have a stable point in time that means even if records are updated at the same time as your transaction, they are not missed. When you start your next transaction you will locate those concurrently made changes! – imagine trying to do that with date time stamp tracking!

This means you can have multiple users updating the data at the same time safely without having to lock the tables :-)

Setting the subscription active

At this point, lets pretend we have a STOCK table and we run the following statement

Select * from STOCK

the data returned would be all the STOCK records.

How do we get from here to just getting changed data deltas? Well, first we need to activate the subscription and provide an ID that we are going to track against.

set subscription sub_stock at ‘DeviceID’ active;

With the subscription “sub_stock” active, running the initial statement will again return all records as its the first time data has been requested in the subscription.

At this point there are two possible next steps: Commit or Rollback.

Rollback of the transaction will ensure the data is provided again, Commit will bring the Subscription up to date for the start of the transaction.

Running the Select statement again in an active transaction with the subscription active will now return only delta’s

Preventing you data changes showing in your Change View delta

So we have covered selecting data, but what about updating data? How do you stop the changes to data you make appearing in your subscription yet still available to others? Well simply, make the changes while your subscription is active.

Start a Transaction in SnapShot isolation mode.
Set the Subscription active (using your ID)
Run your update statement to modify the data
Commit your transaction.

Simples!

Introduction to Change Views Video on Embarcadero website

Introduction to InterBase Change Views

 

To see a Change View code example see the InterBase XE7 FirstLook webinar

The post InterBase Change Views – Part 3 – Using a change view appeared first on Stephen Ball's Technical Blog.

Posted by Stephen Ball on February 7th, 2015 under Uncategorized | Comment now »


InterBase Change Views – Part 1 – What is a change view

This is the first post of a series on InterBase Change Views and is intended to be a high level overview of the new powerful change view technology that is patent pending and part of InterBase. Following this post I plan to show more posts taking the concept of Change Views into the real world with some real application examples.

InterBase Change Views

InterBase XE7 introduces a new way to track data changes to the database called Change Views.

Change Views are a new “subscription based” model allowing you to “subscribe” to data; once subscribed you are able to ask the database at a later time, spanning connections, for what has changed.

This is an especially cool, low cost way to reduce network traffic, mobile data costs and development time when tasked with keeping multiple remote database caches up to date. (especially if you have large tables to keep up dated).

Change Views are simple to use and reduce the upfront planning needed for building in data tracking that history has shown to be inherently complex and error prone.

Best of all change views has zero impact on database performance regardless of the number of subscribers!

Subscription & Subscribers

A core concept to change views is that of Subscriptions and Subscribers.  InterBase Change view allows multiple subscribers to a subscription.

A subscription is defined once and then can be connected to by multiple users. Once defined, you can control who can subscribe thanks to the InterBase inbuilt user security.

Subscriptions work at field level and can be defined to track either an entire table or multiple tables. Subscriptions can also be defined to track Inserts, Updates or Deletes (or a mix of)

Multiple devices per subscriber

In addition to allowing specific users to subscribe to data, you can use a single user to have multiple destinations that are subscribing. This allows a user to subscribe for different devices such as their Phone, Tablet, Laptop or for a single user to be used programatically to distinguish multiple sites – e.g. Office 1, Office 2 etc.

This is done using the “at” verb when connecting to the subscription.

What Change Views are not!

Change Views are not an auditing system. While you can track deleted records, Change View are about identifying what has changed without keeping every value that it has been. If you want to do that, then continue to use Triggers and logging tables for the values you need to audit.

Video – Change Views

An overview of Change Views is available in the InterBase product address from CodeRage 9. I will be digging into more code based versions through this series. This covers the syntax used

Documentation – Change View

There is a great introduction to InterBase Change Views in the release notes for InterBase XE7
http://docwiki.embarcadero.com/InterBase/XE7/en/What’s_New_in_InterBase_XE7#Change_Views_Feature

 

The post InterBase Change Views – Part 1 – What is a change view appeared first on Stephen Ball's Technical Blog.

Posted by Stephen Ball on February 7th, 2015 under Uncategorized | Comment now »


InterBase Change Views – Part 2 – Creating a Change View

If you have ever spent time planning how to identify what data has changed, then change views is about to simplify the way you develop for ever!

If you haven’t already read Part 1 of InterBase Change Views this provides a high level overview of the technology.

Steps for creating a Change View

Rather than having to create and manage triggers to add records into log tables or planning additional date fields into your metadata (and hoping date & time changes don’t effect you) Change View are easily added to a project once the simplified architecture is complete.

There are two essential steps in creating a successful change view:

Defining the purpose of the Change View and the data you want to track
Defining who can subscribe to the change view

1) Creating a change view

Lets start with a simple example. We want to keep our sales fulfilment application up-to-date with the latest stock and suppliers information. This data is stored in two tables, INVENTORY and SUPPLIERS.

Example 1 – Multiple Tables

CREATE SUBSCRIPTION
sub_stock
ON
INVENTORY FOR ROW (INSERT, UPDATE, DELETE),
SUPPLIERS FOR ROW (INSERT, UPDATE, DELETE)
DESCRIPTION ‘Track stock and supplier changes’;

Subscriptions are created with the CREATE SUBSCRIPTION statement followed by a subscription name that you will need to use in SQL later on.

The simple example tracks all columns in the tables INVENTORY and SUPPLIERS for Inserts, Updates and Deletes.

The description allows easy identification of the subscriptions purpose later on.

Example 2 – Specifying Columns

We may have a requirement to track product name changes for our application. We can do this also with a change view.

CREATE SUBSCRIPTION
sub_stockname
ON
INVENTORY(ITEM_NAME) FOR
ROW (INSERT, UPDATE, DELETE)
DESCRIPTION ‘Track stock name changes’;

Example 2 is more granular than example 1 and tracks only a single column ITEM_NAME in the INVENTORY table. This is done by providing a comma separated list of field names for the table that you want to track.

Example 3 – Tracking Deletes

We may also want to track deletions from a specific table, lets say ORDERS. A Change Views just tracking DELETE activity on  a table will provide a way to recover data if it is deleted

CREATE SUBSCRIPTION
sub_orderDeletes
ON
ORDERS FOR
ROW (DELETE)
DESCRIPTION ‘Track Order Deletions’;

Using a Change View to track deletes is a powerful feature as you can completely recover the record at its state when it was deleted.

2) Defining who can use a change view / subscription

Once a subscription is created, you need to grant SUBSCRIBE access to the subscription. To do this use the standard GRANT syntax. e.g. (for user SYSDBA)

GRANT SUBSCRIBE ON SUBSCRIPTION sub_stock TO SYSDBA;

InterBase Documentation

For more on Change Views visit the InterBase DocWiki

In my next blog, I’ll look at using a change view

The post InterBase Change Views – Part 2 – Creating a Change View appeared first on Stephen Ball's Technical Blog.

Posted by Stephen Ball on February 7th, 2015 under Uncategorized | Comment now »


InterBase Change Views – Part 3 – Using a change view

In Part 1 I explored the concept of change views, before how to create a Change View ready for use in Part 2. In Part 3 we are going to explore how to use a Change View to collect data that has changed in your subscription.

Using an InterBase Change View to fetch data deltas

Once a Change View Subscription has been created and access to it has been granted (to either users or roles), then its time to start using your Change View!

The following steps are the high level overview of using a Change View.

Start a Transaction in SnapShot isolation mode.
Set the Subscription active
Run your select statement to fetch you delta
Once you have collected the delta commit your transaction to bring your Change View up to date.

SnapShot Mode

OK a bit of background as we all love to know how things work..

InterBase is powered by a multi-generational architecture which is exceptionally useful when data integrity is important. imagine you need to run multiple financial reports while the system is live;  Imagine a connection drops part way through a big data change how do you know what has changed? Multi-generational architectures and transactions makes this possible.

Using a transaction set to SnapShot mode we have a stable point in time that means even if records are updated at the same time as your transaction, they are not missed. When you start your next transaction you will locate those concurrently made changes! – imagine trying to do that with date time stamp tracking!

This means you can have multiple users updating the data at the same time safely without having to lock the tables :-)

Setting the subscription active

At this point, lets pretend we have a STOCK table and we run the following statement

Select * from STOCK

the data returned would be all the STOCK records.

How do we get from here to just getting changed data deltas? Well, first we need to activate the subscription and provide an ID that we are going to track against.

set subscription sub_stock at ‘DeviceID’ active;

With the subscription “sub_stock” active, running the initial statement will again return all records as its the first time data has been requested in the subscription.

At this point there are two possible next steps: Commit or Rollback.

Rollback of the transaction will ensure the data is provided again, Commit will bring the Subscription up to date for the start of the transaction.

Running the Select statement again in an active transaction with the subscription active will now return only delta’s

Preventing you data changes showing in your Change View delta

So we have covered selecting data, but what about updating data? How do you stop the changes to data you make appearing in your subscription yet still available to others? Well simply, make the changes while your subscription is active.

Start a Transaction in SnapShot isolation mode.
Set the Subscription active (using your ID)
Run your update statement to modify the data
Commit your transaction.

Simples!

Introduction to Change Views Video on Embarcadero website

Introduction to InterBase Change Views

 

To see a Change View code example see the InterBase XE7 FirstLook webinar

The post InterBase Change Views – Part 3 – Using a change view appeared first on Stephen Ball's Technical Blog.

Posted by Stephen Ball on February 7th, 2015 under Uncategorized | Comment now »




Server Response from: BLOGS1