FireBird database is one of best embedded databases. It has excellent performance on concurrent access.
To learn more about Firebird Database, please visit Firebird Website.

Firebird client library comes with API's. Also, There is an C++ library that wraps those API's. You may download Firebird Client Library and its source code from Firebird Website. The Firebird OLE DB Provider is a wrapper of those Firebird Client library Version 1.5 API's. This provider is being implemented with ATL OLE DB Provider templates along with the Firebird C++ library. The privider itself has a relatively small footprint even though the provider does not have many dependencies. It only depends on Firebird Client library along with Windows OS runtime. You may use ADO or ADO.NET 2.0 to access data with Firebird OLE DB Provider.

The following are the features that this Firebird OLE DB Provider supports:

• Transaction
• Record scroll
• Parameters (in and out)
• Bookmarks
• Database Schema - tables and their columns
• Extended error information
• Data arrays*
• Western charset
• Transparency - all scripts are transparent to Firebird database engine

* - All multiple dimension data arrays defined in the database will be retrieved as one dimension safearray. For input data array, multiple dimension arrays are not supported. However, you may use one dimension array as long as the one dimension array has exactly the same number of elements. string array is not supported.

Using Firebird OLE DB Provider is very similiar to using MySQL OLE DB Provider and SQLite OLE DB Provider. There is not much big difference. Of cause, the connection string is different. The example below demostrates how to use Firebird OLE DB Provider using ADO with Visual Basic. The sample code will show you the usage of the following features:

• Transaction
• Parameter input and output
• Database schema
• Data arrays
• Unicode string manipulation

As far as record scroll and bookmarks usage, please refer to MySQL OLE DB Provider or SQLite OLE DB Provider.

'objects used throughout the demostration
Private mConn As Connection
Private mCmd As Command
Private mRs As Recordset

'
'Synposis:  Create connection, and command objects that
'           are used throughout the rest of the demostration
'
Private Sub Init(Optional ByVal bEmbedded As Boolean = False)
    Set mConn = New Connection
    If Not bEmbedded Then
    mConn.Open "Provider=FBS.FirebirdProvider; Data Source=localhost" _
	& "Initial Catalog=c:\TestDB.fdb; User ID=SYSDBA; Password=masterkey"
    Else
    mConn.Open "Provider=FB.FirebirdProvider;" _
	& "Initial Catalog=c:\TestDB.fdb; User ID=SYSDBA; Password=masterkey"
    End If    
    Set mCmd = New Command
    mCmd.ActiveConnection = mConn
End Sub

'
'Synposis:  Delete all parameters, if any, from the command object
'
Private Sub ClearParameters()
    While mCmd.Parameters.Count > 0
        mCmd.Parameters.Delete 0
    Wend
End Sub

'
'Synposis:  run test
'
Private Sub cmdCreate_Click()
    On Error GoTo trap
    Init
    CreateArrayTestTable
    TestArray
    StoreProc
    CreateTable
    TestTransaction
    Exit Sub
trap:
    MsgBox Err.Description
End Sub

'
'Synposis:  If the table product does not exist, create it
'
Private Sub CreateTable()
    Set mRs = mConn.OpenSchema(adSchemaTables)
    While Not mRs.EOF
        If UCase(mRs.Fields("table_name")) = "PRODUCT" Then
            Exit Sub 'if found, do nothing but return
        End If
        mRs.MoveNext
    Wend
    'if not found, create the product table
    mCmd.CommandText = "CREATE TABLE product (id INTEGER NOT NULL " _
		& "PRIMARY KEY, partNumber VARCHAR(20), name VARCHAR(60))"
    Debug.Print mCmd.CommandText
    mCmd.Execute
End Sub

'
'Synposis:      Demostrate data parameter input and output
'               1. Create store procedure
'               2. Call store procedure with in/out parameters
'               3. print output
'
Private Sub StoreProc()
    On Error GoTo trap
    mCmd.CommandText = "Create PROCEDURE MyProc (In0 INTEGER, " _
    	& " In1 SMALLINT, In2 BIGINT, In3 FLOAT, In4 DOUBLE PRECISION) " _
   	& " RETURNS (o0 INTEGER, o1 SMALLINT, o2 BIGINT, o3 FLOAT, " _
   	& "o4 DOUBLE PRECISION)" _
   	& " AS BEGIN  o0=In0*10; o1=In1*2; o2=In2*2; o3=In3*3.0; o4=In4*4; END"
    Debug.Print mCmd.CommandText
    mCmd.Execute
trap:
    Dim p(5) As New Parameter
    
    p(0).Direction = adParamInputOutput
    p(0).Type = adInteger
    p(0).Value = 2
    p(1).Direction = adParamInputOutput
    p(1).Type = adSmallInt
    p(1).Value = 10000
    p(2).Direction = adParamInputOutput
    p(2).Type = adBigInt
    p(2).Value = 123444444012#
    p(3).Direction = adParamInputOutput
    p(3).Type = adSingle
    p(3).Value = 10000.12
    p(4).Direction = adParamInput
    p(4).Type = adDouble
    p(4).Value = 111122223333.111
    p(5).Direction = adParamOutput
    p(5).Type = adDouble
    p(5).Value = 0#

    'output shall not be in this query string per firebird
    mCmd.CommandText = "EXECUTE PROCEDURE MyProc(?, ?, ?, ?, ?)" 
    Dim n As Integer
    mCmd.Execute n, p
    
    Debug.Print p(0).Value
    Debug.Print p(1).Value
    Debug.Print p(2).Value
    Debug.Print p(3).Value
    Debug.Print p(5).Value  'output
    
End Sub


'
'Synposis:  Create a table that has columns with data array type. If the table does not
'           exist, create it. If it does, do nothing but return
'
'           The table has the following columns:
'           ID - Integer, primary key
'           I2A - small integer, 2 dimension array
'           I4A - Integer, one dimension array with low bound 0, and upper bound 3
'           F4A - float, one dimension array with low bound 1, and upper bound 4
'           F8A - double, one dimension array with low bound 1, and upper bound 4
'           DA  - date, one dimension array with low bound 1, and upper bound 4
'           TA  - Time, one dimension array with low bound 1, and 4 as its upper bound
'           TS  - Timestamp,one dimension array with low bound 1, and 4 as its upper bound
'
'           string array is not supported in this release.
'
Private Sub CreateArrayTestTable()
    Set mRs = mConn.OpenSchema(adSchemaTables)
    While Not mRs.EOF
        If UCase(mRs("table_name").Value) = "ARRAYTEST" Then 
            Exit Sub 'if it exists, do nothing but return
        End If
        mRs.MoveNext
    Wend
    Dim sSQL As String
    sSQL = "CREATE TABLE ARRAYTEST "
    sSQL = sSQL + "(ID INTEGER NOT NULL PRIMARY KEY, "
    sSQL = sSQL + "I2A SMALLINT [2,2], I4A INTEGER [0:3], "
    sSQL = sSQL + "F4A FLOAT [4], F8A DOUBLE PRECISION [4], "
    sSQL = sSQL + "DA DATE [4], TA TIME [4], TS TIMESTAMP [4])"
    mCmd.CommandText = sSQL
    mCmd.Execute
End Sub

'
'Synposis:  Demostrate data array manipulation
'           1. Delete all data from ARRAYTEST table
'           2. Store array data into ARRAYTEST table
'           3. Read data array back
'
Private Sub TestArray()
    mCmd.CommandText = "DELETE FROM ARRAYTEST"
    mCmd.Execute    'delete all records if any

    mCmd.CommandText = "INSERT INTO ARRAYTEST values (?,?,?,?,?,?,?, ?)"
    Dim I As Integer
    Dim i2a(3) As Integer   'you may use a 2 dimension matrix
                            'as long as # of elements match that
                            'declared in the table
    Dim i4a(3) As Long
    Dim f4a(3) As Single
    Dim f8a(3) As Double
    Dim da(3) As Date
    Dim ta(3) As Date
    Dim ts(3) As Date
    Dim v As Variant
    
    For I = 0 To 3
        i2a(I) = (I + 1) * (I + 1)
        i4a(I) = (I + 5) * 5
    Next I
    For I = 0 To 3
        f4a(I) = 3.141569 + I
        f8a(I) = 6.29 + I
    Next I
    For I = 0 To 3
        da(I) = DateAdd("d", I * 2, "10/09/2005")
        ta(I) = DateAdd("n", I * 5, "12:30:00")
        ts(I) = DateAdd("h", I, "10/09/2005 12:30:00")
    Next I
    Dim ps(7) As New Parameter
    ps(0).Direction = adParamInput
    ps(0).Type = adInteger
    ps(0).Value = 2
    
    ps(1).Direction = adParamInput
    ps(1).Type = adVariant
    v = i2a
    ps(1).Value = v   'short
    
    ps(2).Direction = adParamInput
    ps(2).Type = adVariant
    v = i4a
    ps(2).Value = v   'long
    
    ps(3).Direction = adParamInput
    ps(3).Type = adVariant
    v = f4a
    ps(3).Value = v   'float
    
    ps(4).Direction = adParamInput
    ps(4).Type = adVariant
    v = f8a
    ps(4).Value = f8a   'double
    
    ps(5).Direction = adParamInput
    ps(5).Type = adVariant
    v = da              'date
    ps(5).Value = v
    
    ps(6).Direction = adParamInput
    ps(6).Type = adVariant
    v = ta              'time
    ps(6).Value = v
    
    ps(7).Direction = adParamInput
    ps(7).Type = adVariant
    v = ts              'timestamp
    ps(7).Value = v

    
    For I = 0 To 7
        mCmd.Parameters.Append ps(I)
    Next I
    mCmd.Execute
    
    mCmd.CommandText = "select ID, I2A, I4A, F4A, F8A, DA, TA, TS from arraytest"
    ClearParameters
    Set mRs = mCmd.Execute
    Debug.Print mRs(0) 'please watch window to view the result
End Sub

'
'Synposis:  Demostrate firebird transaction.
'           To prepare for the demo, delete all records from the table if any
'           Insert 50 records into product table
'           Update the first record and rollback the transaction
'           Update the second record and commit the transaction
'
Private Sub TestTransaction()
    Dim ps(1) As New Parameter
    Dim I As Integer
    ClearParameters     'remove all parameters if any
    mCmd.CommandText = "delete from product"
    mCmd.Execute        'delete all records from product table
    
    'start transaction
    mConn.BeginTrans
    ps(0).Direction = adParamInput
    ps(0).Type = adInteger
    ps(1).Direction = adParamInput
    ps(1).Type = adBSTR
    mCmd.Parameters.Append ps(0)
    mCmd.Parameters.Append ps(1)
    
    'In this release, all strings saved into database are treated as unicode strings.
    'the only way to save unicode string into firebird database is to insert/update a
    'string value via an input parameter
    mCmd.CommandText = "INSERT INTO PRODUCT (ID, partNumber) VALUES (?, ?)"
    For I = 1 To 50
        ps(0).Value = I
        ps(1).Value = "Part " & I
        mCmd.Execute
    Next I
    mConn.CommitTrans
    mCmd.CommandText = "UPDATE PRODUCT SET name=? WHERE ID = ?"
    ClearParameters
    Set ps(0) = New Parameter
    Set ps(1) = New Parameter
    ps(1).Direction = adParamInput
    ps(1).Type = adInteger
    ps(1).Value = 1
    ps(0).Direction = adParamInput
    ps(0).Type = adBSTR
    ps(0).Value = "Firebird provider"
    
    mConn.BeginTrans
    mCmd.Execute I, ps
    mConn.RollbackTrans
    ps(1).Value = 2
    mConn.BeginTrans
    mCmd.Execute I, ps
    mConn.CommitTrans
    
    ClearParameters
    mCmd.CommandText = "Select * from product WHERE ID<=10"
    Set mRs = mCmd.Execute
    While Not mRs.EOF
        If Not IsNull(mRs(2).Value) Then
            Debug.Print mRs(0).Value & " " & mRs(1).Value & " " & mRs(2).Value
        Else
            Debug.Print mRs(0).Value & " " & mRs(1).Value
        End If
        mRs.MoveNext
    Wend
    Me.MSHFlexGrid1 = 100
    Me.MSHFlexGrid1.Clear
    Set Me.MSHFlexGrid1.Recordset = mRs
End Sub

Firebird OLE DB Provider must be activated before you can use it. To activate it, download and install the provider and unzip it. Then read the readme.txt. Run the Activator included in the package to generate your passcode. Click here to get activation code.

Update History:

Date	Changes
2007-01-20	Fixed an activation defect.
2006-05-07	Removed Visual C++ 7.1/8.0 dependency; Added support of server-client architecture as well as embedded db and support for Firebird 1.5.3 and 2.0 beta release
2006-03-05	Fixed a critical activation bug
2005-11-27	Updated to support ADO.NET 2.0
2005-10-16	Changes were made to support Western charset and unicode
2005-10-12	First Release

Firebird OLE DB Provider
Provider VB Test source

This whole post here including the downloads is provided 'as-is', without any express or implied warranty. In no event will the author be held liable for any damages arising from the use of this software.

 To send me feedback, click here