Watch, Follow, &
Connect with Us

Stephen Ball

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 »


InterBase CodeRage 28th-30th November

Register NOW and join me live at #CodeRage! CodeRage is a fantastic Object Pascal programming and C++ Programming event – highly recommended for any developer, and best of all – REGISTRATION IS FREE!! Missed it or got a meeting? – Don’t worry, register and you can still get to see the replays! My two InterBase CodeRage Sessions! I will be … Continue reading InterBase CodeRage 28th-30th November

The post InterBase CodeRage 28th-30th November appeared first on Stephen Ball’s Technical Blog.

Posted by Stephen Ball on January 28th, 2015 under Uncategorized | Comment now »


IBLite example from CodeRage 9

The Object Pascal programming source code for the IBLite example from CodeRage 9 that I demo’ed is now available from Embarcadero’s Code Central http://cc.embarcadero.com/item/30035 The C++ programming version demo’ed requires the data module from the Object Pascal code, so once I’ve cleaned that up I’ll post that as well. The IBLite example code works on Windows, … Continue reading IBLite example from CodeRage 9

The post IBLite example from CodeRage 9 appeared first on Stephen Ball’s Technical Blog.

Posted by Stephen Ball on January 28th, 2015 under Uncategorized | Comment now »


InterBase CodeRage 9

The CodeRage 9 videos are now available for reply online via YouTube. To save you searching, the InterBase CodeRage 9 links I did are below; For the full list of videos from CodeRage 9 please visit the Embarcadero Community blog post for CodeRage 9 InterBase Product Address InterBase IBLite (Object Pascal) InterBase IBLite (C++)

The post InterBase CodeRage 9 appeared first on Stephen Ball’s Technical Blog.

Posted by Stephen Ball on January 28th, 2015 under Uncategorized | Comment now »




Server Response from: BLOGS1