.NET Firebird

Status of the Firebird Tutorial for .NET

Tue, 23 Oct 2007 13:22:17 -0700

At the beginning of January I posted a Request for Ideas: Firebird in .NET Tutorial. First of all, thanks to all who contributed their ideas.

I'm currently working hard on the tutorial. The original idea was to write just a quick introduction but it seems there is a lot to tell... The weakest point of Firebird seems to be the lack of freely available documentation - so I decided to invest a little bit more time in the tutorial.

Structure

I have set the structure for the release 1.0 as follows:

Getting started (installation, management, tools, security)
Tables (creating, autoincrement fields, indexes, importing data)
Data types (including BLOB, GUID)
Executing commands (calling commands from .NET, SELECT, embedded SQL vs. stored procedures, DataSet)
Transactions (default transaction, isolation levels)
Stored procedures (executable/selectable, creating, calling, recommendations)
Character sets (Unicode, problems, recommendations)
Administration basics (securing the TCP/IP channel, backup, multiple instances)
Domains (where to use it, useful domains samples)
Typical tasks (basics of strings, basics of date/time, returning last autoincrement ID, affected row count...)
Performance (indexes, page size, stored procedures)
Database refactoring
Views
Triggers

The emphasis should be on practical experience and recommendations. The following chapters will be the key ones:

Executing commands
Stored procedures
Typical tasks

This release will be focused on the core knowledge, leaving some topics for the future releases.

Future releases

These topics will be added later:

Visual Studio .NET integration
Embedded Firebird
Specialities of Firebird and ASP.NET
.NET Framework 2.0 specific issues
Using Firebird in Mono
Arrays
Events
Services API (backup and restore...)

Comment and suggestions are always welcome! Just add comments to this topic.

Plan

I want to publish a draft version by the end of February... Stay tuned!

Calling Stored Procedures from Stored Procedures

Tue, 23 Oct 2007 13:22:17 -0700

Proper handling of the return values when calling a stored procedure from another stored procedure can be tricky. Here is the overview of the syntax.

Syntax Overview

1. No return values:

EXECUTE PROCEDURE procedure_name (parameter, parameter,...);

2. Single row returned (there is no SUSPEND in the called procedure):

EXECUTE PROCEDURE procedure_name (parameter, parameter,...) RETURING_VALUES :variable1, :variable2;

3. Result set returned from a selectable stored procedure (the called procedure uses SUSPEND; no matter how many rows actually returned):

FOR SELECT col1, col2 FROM procedure_name (parameter, parametere, ...) INTO :variable1, :variable2 DO
BEGIN
  -- your code for processing the result set
END

Common Firebird Error: Multiple Rows in Singleton Select

Possible causes:

Your are calling EXECUTE PROCEDURE statement and the stored procedure returns multiple rows (EXECUTE PROCEDURE expects just a single row).
You are calling SELECT ... INTO statement in your stored procedure and the selection returns multiple rows. If multiple rows are expected, you have to use FOR SELECT ... DO statement.

How to Add Firebird .NET Provider 2.0 to Visual Studio 2005

Tue, 23 Oct 2007 13:22:17 -0700

Jiri Cincura has provided a step-by-step guide to VS.NET 2005 support installation. You can read the article on his blog: DDEX and Firebird .NET Data Provider.

Data Application Block for Firebird SQL (The Code Project)

Tue, 23 Oct 2007 13:22:17 -0700

The Firebird SQL data application block is intended to speed up development, and it should be used in conjunction with data layer classes in much the same way as Microsoft’s data block. The sample included with this article uses the embedded Firebird SQL database (included in sample) to demonstrate the use of the application block without having to bother setting up a database.

Firebird SQL data application block was inspired by the Microsoft Application blocks. The block provides overloaded methods to query a Firebird database which return a dataset, data table, or integer in the case of scalar database calls. An additional overload method takes an existing dataset as a parameter and fills the results of a stored procedure send as parameter as well.

Data Application Block for Firebird SQL - The Code Project - C# Database

Updated Firebird Downloads

Tue, 23 Oct 2007 13:22:16 -0700

I've updated the downloads page to show the most recent versions:

Firebird Server for Windows 1.5.3
Firebird Embedded for Windows 1.5.3
Firebird Server for Windows 2 RC1
Firebird Embedded for Windows 2 RC1
Firebird ADO.NET Provider 1.7.1 for .NET Framework 1.1
FirebirdClient 2 RC3 for .NET Framework 2.0

Updated Firebrid Downloads

Tue, 23 Oct 2007 13:22:16 -0700

New development versions available:

Firebird Server for Windows 2.0 RC 2
Embedded Firebird for Windows 2.0 RC 2
FirebirdClient 2.0 RC5 for .NET Framework 2.0

Available in downloads section.

Firebird Questions .NET

Tue, 23 Oct 2007 13:22:16 -0700

I've just released a new experimental site: http://www.firebirdquestions.net/. It is a searchable and browsable archive of firebird-net-provider mailing list.

Noteworthy features:

Updated automatically every 60 seconds
Search is powered by Lucene.Net and Seekafile Server.
You can browse the history by threads.
You can skip to next/previous thread by hitting 'j'/'k' keys.
All e-mails protected.

Comments and suggestions are welcome!

How to build Firebird Client 2.0 for .NET Compact Framework

Tue, 23 Oct 2007 13:22:16 -0700

From Firebird Questions .NET:

How to compile source code to work with .NET Compact Framework? http://www.firebirdquestions.net/Item.aspx/4302/re_cf

Is it possible to stop/start remote firebird service using Firebird ADO.NET Provider?

Tue, 23 Oct 2007 13:22:16 -0700

No, it's not.

However, you can use this simple code:

ServiceController sc = new ServiceController("FirebirdGuardianDefaultInstance"); // or "FirebirdServerDefaultInstance" if you are not using Guardian

  
Console.WriteLine("Stopping service...");
sc.Stop();
   
while (sc.Status != ServiceControllerStatus.Stopped)
 Thread.Sleep(100);
   
Console.WriteLine("Service stopped.");

Console.WriteLine("Starting service...");
sc.Start();
   
while (sc.Status != ServiceControllerStatus.Running)
 Thread.Sleep(100);
   
Console.WriteLine("Service started.");

You need to reference System.ServiceProcess.dll and add these usings:

using System.ServiceProcess;
using System.Threading;

Weekly builds of Firebird ADO.NET Provider

Tue, 23 Oct 2007 13:22:16 -0700

Weekly builds of Firebird ADO.NET Provider are available at http://netprovider.cincura.net/.

Read more in the announcement by Jiri Cincura.

Common Firebird Errors and Firebird .NET Provider Exceptions

Tue, 23 Oct 2007 13:22:16 -0700

When working with Firebird in .NET I've been collecting Firebird errors and exceptions and their causes. It saved me a lot of time because the exceptions usually came again... I'm sharing my list here - if you have something to add don't hesitate and add a comment.

Unable to complete network request to host "localhost" (Provider exception).

The client is not able to connect to the server because of the network.

Possible causes:

Firebird is not installed on the server.
Bad host name or IP address.
Firewall is blocking the Firebird port (default is TCP 3050).

Unable to load DLL (fbembed) (Provider exception)

The Firebird ADO.NET provider is not able to find the fbembed.dll file (embedded Firebird engine).

Possible causes:

The file "fbembed.dll" is not in the working directory of your application. For console or Windows forms applications it is usually the directory with your .exe file. For ASP.NET applications it's the system folder (e.g. C:\WINDOWS\system32].

Multiple Rows in Singleton Select (Firebird error)

Possible causes:

You are calling SELECT ... INTO statement in your stored procedure are the selection returns multiple rows. If multiple rows are expected, you have to use FOR SELECT ... DO statement.

Internal gds software consistency check (Internal error code (165)) (Firebird error)

The database file is corrupted.

Possible causes:

Your are not closing the connection properly. See also Problems with Creating a New Database using Embedded Firebird

Arithmetic exception, numeric overflow or string truncation (Firebird error)

This error is one of the most frequent ones.

Possible causes:

Too long string assigned to a VARCHAR variable in a stored procedure (usually hidden because of joining string using || operator).
Mismatch of stored procedure variable type and field type (shorter varchar(x) in the stored procedure declaration)

Dynamic SQL Error: parameter mismatch for procedure PROCEDURENAME (Firebird error)

Possible causes:

Wrong number of parameters (too few or too many supplied)
Wrong stored procedure name

Conversion error from string "2004-12-14" (Firebird error)

Possible causes:

Wrong INTO order in a stored procedure

Invalid token. invalid request BLR at offset 300. parameter mismatch for procedure PROCEDURENAME (Firebird error)

See http://www.delphifaq.com/faq/delphi/database/f42.shtml

It's not possible to call EXECUTE PROCEDURE that returns something without RETURNING_VALUES.

Expression evaluation not supported (Firebird error)

Calling EXECUTE STATEMENT and joining DATE variable into the SELECT statement without apostrophes around. E.g. cmd = 'SELECT * FROM table WHERE datefield > ' || date;

Search Firebird Questions .NET:
Unable to complete network request to host localhost
Unable to load DLL
Multiple rows in singleton select
Arithmetic exception, numeric overflow or string truncation
Parameter mismatch for procedure
Conversion error from string
Invalid token. invalid request BLR at offset

Firebird and Multi Version Concurrency Control

Tue, 23 Oct 2007 13:22:16 -0700

A nice white paper by Roman Rokytskyy that describes the multi version concurrency control system used in Firebird in comparison to IBM DB2 and Oracle 9i.

So how does it work? The main idea was already presented when we talked about MS Word opening a file in read-only mode, but there are some important details. As the name implies, each record in the system might have multiple versions visible to different transactions. When a transaction modifies a record, a new version is written to the database, and a previous version, representing only the difference between the version of the record that was read by the transaction and the new value of the record, is written as a back version of that record.

http://www.firebirdsql.org/doc/whitepapers/fb_vs_ibm_vs_oracle.htm

Search Firebird Questions .NET:
Oracle
DB2

Firebird Generators: Everything you wanted to know

Tue, 23 Oct 2007 13:22:16 -0700

This is a very nice article about Firebird generators: Firebird Generator Guide

Show what a Generator is in Firebird and how and why to use it. This is the attempt to collect all relevant information about Generators in a single document - hope it works, too... Who should read it?

Read this article if you:

are not familiar with the concept of Generators
have questions on using them
want to make an Integer column behave like an "AutoInc" field as found in other RDBMSs
are looking for examples on how to use Generators for IDs or other tasks
want to know the Firebird word for a "sequence" in Oracle ;-)

Table of contents:

Chapter 1: Generator basics

Chapter 2: Using Generators (in general)

Chapter 3: Using Generators to create unique row IDs

Chapter 4: Using Generators with IBObjects

Chapter 5: What else to do with Generators ( and why most of it won't work ;-)

Glossary

Search Firebird Questions .NET:
Sequence
Generator

FirebirdClient 2.0 for .NET Framework 2.0 released

Tue, 23 Oct 2007 13:22:15 -0700

The final version of the Firebird ADO.NET client for .NET Framework 2.0 is here. This is a summary of the new features:

Visual Studio 2005 integration of the provider using a DDEX provider
Support for the new INSERT ... RETURNING statement of Firebird v2.0
Support for the new PSQL stack trace of Firebird v2.0
New CAS support FirebirdClientPermission and FirebirdClientPermissionAttribute classes
Added support to the new CREATE SEQUENCE statement to the FbBatchExecution class.
Implemented FbParameterCollection.AddWithValue
New GDS implementation for the Firebird/Fyracle External Proceudre engine implementation.
Added a new namespace FirebirdSql.Data.StoredProcedureEngine with two new classes: FbResultSet, FbTriggetContext
Added initial support for boolean input parameters.
[DDEX] Foreign keys should be now automaticalled added to DataSets at design time when dropping tables from the Server Explorer.
Added correct support for the new .net 2.0 DbParameter.SourceColumnNullMapping property (Alexander V. Leshkin)
Initial implementation of the new extended support for char/varchar fields with OCTETS character set.
Added initial support for EXECURE PROCEDURE commands in FbBatchExecution class.
Added new Connection String parameter "Context Connection" to be used with .NET stored procedure implementations for Fyracle.
Added new UTF8 ( Firebird 2.0 only ) character set.
Added a new Role Provider implementation ( FirebirdSql.Web.Providers assembly )

The new version is available in the downloads section.

Search Firebird Questions .NET:
Server Explorer
FirebirdClient
FirebirdClientPermission
CREATE SEQUENCE
FbParameterCollection
FbBatchExecution
UTF8
GDS

Firebird Tutorial for .NET 2.0

Tue, 23 Oct 2007 13:22:15 -0700

I've been promising the Firebird Tutorial for .NET for a long time.

Now there is a first "beta" version: http://www.firebirdtutorial.net/. I decided to start with the .NET 2.0 topics - a lot of interesting things happen around FirebirdClient 2.0 and there is not much documentation about it (unless you are a fan of mailing list browsing).

The tutorial is far from complete but I would like to point out some interesting topics:

Firebird and Visual Studio .NET 2005 screenshot guides
DbProviderFactory generic database access
SqlDataSource and Firebird
Storing connection string in web.config

I also want to thank you for all the comments at the blog posts and e-mails. Although I'm not able to answer to all of them I'm trying to reply by writing new articles. The same applies to the tutorial: If you have a sample code, an interesting note, or something else to add just add a comment to this post or use the feedback e-mail: fbtutorial [at] annpoint.com.

Firebird and Visual Studio .NET 2005 Data Designer Access (DDEX)

Tue, 23 Oct 2007 13:22:15 -0700

There is a new chapter in the Firebird Tutorial for .NET: Firebird Data Access Designer (DDEX) installation.

Follow these instructions to get Firebird support in the Visual Studio .NET 2005 (Standard and up):

Browse Firebird databases in the Server Explorer
Use "Configure Data Source" Wizard to configure SqlDataSource component
Connection Properties editor
etc.

No need for Visual Studio SDK!

Offtopic: MenuPilot 1.0 - Open-Source Task Menu for ASP.NET 2.0

Tue, 23 Oct 2007 13:22:15 -0700

I've just released my new open-source control: MenuPilot 1.0 - fine DHTML context menu with layout of Action Lists/Smart Tags (known from Visual Studio.NET 2005).

Check the live demo and see what it is and how it can help you.

Features

Customizable hint icon.
Customizable colors.
Supports data binding.
Supports menu item separators.
Full Visual Studio .NET 2005 design-time support.
Compiled for ASP.NET 2.0.
Available for 3 inline ASP.NET controls: HyperLink, Label and Image.
Menu items can have Title and Target specified.
Menu items can execute javascript or go to URL.
Includes fix for Internet Explorer z-index bug.
Includes fix for Internet Explorer windowed controls z-index bug.

Browsers

Tested with Firefox 1.5
Tested with Internet Explorer 6.0 (should support 5.5+)

Links

FirebirdClient 2.0.1 released

Tue, 23 Oct 2007 13:22:15 -0700

Carlos Guzmán Álvarez announced new FirebirdClient release.

There are now four packages:

FirebirdClient 2.0.1 for .NET Framework 2.0
FirebirdClient 2.0.1 for .NET Compact Framework 2.0
FirebirdClient 2.0.1 for Mono 1.1.18+
FirebirdClient DDEX Provider 2.0.1 (Visual Studio 2005 design-time support)

Finally there is an official build for .NET Compact Framework 2.0. The DDEX Provider (that provides Visual Studio 2005 design-time support) is now in a separate package.

Links:

Checking if a record exists in Firebird (EXISTS function)

Tue, 23 Oct 2007 13:22:15 -0700

If you need to check whether certain record exists the usual way is to execute the following command and then check if the :idexists variable is greater than zero (this is an example from a stored procedure:

SELECT count(*) FROM company WHERE companyid = :id INTO :idexists;

But the right way to do this is to use EXISTS() function. If you use COUNT(*) Firebird will be looking for all item that fit the condition (i.e. companyid = :id).

If you use EXISTS() it will stop when it finds the first one - so it's much faster!

idexists = 0;
SELECT 1 FROM rdb$database WHERE EXISTS(SELECT * FROM company WHERE companyid = :id) INTO :idexists;

New forums on DotNetFirebird

Tue, 23 Oct 2007 13:22:15 -0700

Because I'm not able to reply to all the questions about Firebird and .NET I'm getting, I've set up a new place to ask these questions: http://forums.dotnetfirebird.org. I'll try to answer the questions if I know the answer. You might also get an answer from someone else if I'm too busy ;-).

Updated Firebird & FirebirdClient Downloads

Tue, 23 Oct 2007 13:22:15 -0700

I've updated the downloads page on DotNetFirebird:

Firebird Server for Windows 1.5.4
Firebird Server for Windows 2.0.0
Embedded Firebird for Windows 1.5.4
Embedded Firebird for Windows 2.0.0
FirebirdClient 2.1.0 beta 2 for .NET Framework 2.0