How can get the error code from an Firebird Database

Question

We are using an embedded Firebird V3.0.2 for our MVC5 Project

Problem: When we update the software, we want to find out if a table exists before we create the new table. Therefor we simply make a select on the desired table and if an exception with sql error code -204 occurs, then the table does not exist. But how can I resolve the error code -204?

Code:

var lFbCommand = new FbCommand();
lFbCommand.Connection = "MyConnectionstring";
lFbCommand.Connection.Open();

// check if table exists by an select query
lFbCommand.CommandText = string.Format("SELECT FIRST 1 * FROM {0}", "MyTableName");

try
{
    pFbCommand.ExecuteNonQuery();
}
catch (FirebirdSql.Data.FirebirdClient.FbException e)
{
    if(e.? == -204) // -204 seem to be the errorcode for "Table does not exists."
    {
        // table does not exist.
    }
    else
    {
        throw;
    }
}

Note: We want to prevent parsing the message text in the exception. We want to get a clear flag for this error (e.g. the error code via any property).

you can just ask directly if the table exists - see https://stackoverflow.com/a/45455737/976391 - however there can be a 3rd case: the table exists but the user does not have SELECT grants on the table. OR you may try to create the table, and if the create stmt fails, then it already existed. — Arioch 'The, Aug 23 '17 at 10:10
HOWEVER it all is wrong solution to software upgrades. Because the table has more options than binary "exists/missed". There are many possible structures of the table. As your software keeps evolving so would be those tables, adding columns, removing columns, changing columns types, etc. You have to make VERSIONED library of database formats, and the scripts library, converting database from version N to version N+1 for every N. And you have to make the database formats history table, where you would have date of conversion to format N for every N. The MAX(N) would be the current version. — Arioch 'The, Aug 23 '17 at 10:15
@Arioch: You´re wrong. This solution is for completely new Tables. — Simon, Aug 23 '17 at 13:54
"totally new table" is a temporary fleeting state. As the program evolve it would be more and more rare, degenerate case. But if you want to make your own mistakes rather than learning from others' ones - have a nice trip :-D Few methods of "just create them" you were offered both by me and Mark — Arioch 'The, Aug 23 '17 at 14:13

score 2 · Accepted Answer · answered Aug 23 '17 at 11:03

You shouldn't look at the (so-called) SQLcode. SQLcodes are very unspecific, for example SQLcode -204 covers a broad range of errors (maybe dozens if not hundreds of sub-errors). Not just "table unknown", but also "trigger unknown", "Datatype unknown", etc.

You'd need to look at the error code (FbException.ErrorCode) (which is not the same as the SQLcode), although that is still tricky, because sometimes error codes are nested from unspecific (covering a similar range as the associated SQLcode) to more specific. And that is the case for "table unknown" (isc_dsql_relation_err or 335544580), which is nested in the group isc_dsql_error (335544569). The isc_dsql_error - IIRC - maps almost one-to-one to SQLcode -204 (which could be a few dozen to a few hundred errors).

As far as I know - but I don't usually program in C# - you will need to look at FbException.Errors (which returns a FbErrorCollection) and check if it contains the right error(s).

You are right. There is a collection in the FbException.Errors property. An item contained this code. Thank you very much for your meaningful answer :) — Simon, Aug 23 '17 at 14:35

How can get the error code from an Firebird Database

1 Answers1