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

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

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)
        {

        }
   }
}
<<<

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.

  1. Start a Transaction in SnapShot isolation mode.
  2. Set the Subscription active
  3. Run your select statement to fetch you delta
  4. 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.

  1. Start a Transaction in SnapShot isolation mode.
  2. Set the Subscription active (using your ID)
  3. Run your update statement to modify the data
  4. 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

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:

  1. Defining the purpose of the Change View and the data you want to track
  2. 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

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