2

I have a C++ application which uses an embedded Firebird database (e.g. using the fbembed.dll on Windows for embedded use) via the IBPP library. I am currently using version 2.5.3 of Firebird and 2.5.3.1 of IBPP. The problem I am having is that when I attempt to insert data which cannot be inserted due to column size issues or constraint violations (e.g. the insert should fail), I get no error or indication that the insertion failed.

As a (simplified but representative) example, I have a table like:

create table USER_TABLE (
    ID BIGINT not null,
    USER_ID VARCHAR(10) not null,
    DISPLAY_NAME VARCHAR(50) not null,
-- etc...
    primary key (ID),
    unique (USER_ID)
);

The ID column is populated with a generator in a before insert trigger. Now I try to insert from my application with the following code:

const string SQL_STMT = "insert into USER_TABLE "
        "(USER_ID, DISPLAY_NAME, etc) "
        "values (?, ?, ?);";
IBPP::Statement stmt = IBPP::StatementFactory(m_db, m_tr, SQL_STMT);
stmt->Set(1, userDataObject.getUserId());//returns const string&,
stmt->Set(2, userDataObject.getDisplayName());//returns const string&
//etc, etc...
stmt->Execute();

If my userDataObject.getUserId() value is a string too long for the column like 1234567890xxx I will not get an exception like I would expect. I can (apparently successfully) get the ID inside the same transaction with a select ID from user_table where ... after doing the insert, or via a ... returning ID at the end of the SQL_STMT variable above. But once the transaction is committed and other parts of the code try to get the values in the table, it's not there. At no point do I get an error/exception.

Any ideas why I'm not getting an error in these circumstances?

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
DAG
  • 315
  • 3
  • 9
  • The fact that you can select the value should indicate that the insert succeeded. And whether the value is too long depends on the column character set and your connection character set. Say your connection character set is NONE, and the column is UTF8 (by way of the database default character set), then you might be able to get longer values in without error. Could the problem be that other parts of your program use a transaction that started before the transaction that committed the row, and therefor that transaction can't see the row? – Mark Rotteveel Mar 16 '16 at 20:04
  • @MarkRotteveel So the value I select back is the ID field (the primary key) which gets used to establish some foreign key relationships. It's not transaction start time related because I am shutting down the application and looking at the database with an external tool to see (or not see) what got inserted. The records are definitely not being inserted. I can with isql and Flamerobin do the same insert and get an error back right away, though in those cases I am putting the values directly into the SQL string instead of using parameters as with my code. – DAG Mar 16 '16 at 21:25
  • 1
    If you did a successful select, the value was in the table, and the insert should have succeeded. I can't really explain the behavior you're seeing. I do suggest that you try to upgrade to Firebird 2.5.5 (and backup/restore your database) to see if it also occurs in that version. – Mark Rotteveel Mar 17 '16 at 10:18

1 Answers1

2

So a few weeks later a coworker and I stumbled onto the cause of this issue while looking into a slightly different issue. We were doing our work/testing using the 64 bit Microsoft Visual Studio C++ toolchain. The Firebird libraries return status and error information through an array of type ISC_STATUS. The libraries define this as a typedef of intptr_t which is pointer sized and thus on a 64 bit system is 64 bits.

The IBPP library dynamically loads the Firebird client libraries. When it defines its version of ISC_STATUS, it defines it as a long. On Linux/GCC I think this works (e.g. will be 32 bits on a 32 bit architecture and 64 bits on a 64 bit architecture) but in Microsoft's Visual C++ long is always 32 bits. The result was that when we compiled and ran in a 64 bit configuration it was interpreting an array of 64 bit integers as an array of 32 bit integers. The result was that, as all the values that were put in the array were positive integers that fit in 32 bits, it appeared that every other value in the status array was 0. IBPP detects errors by checking for a non-zero value in the second spot in the array which in 64 bit processing would always be zero because of this.

The solution was to update IBPP's definition of ISC_STATUS to properly match what the Firebird client library was using (this is in the 'ibase.h' header file).

DAG
  • 315
  • 3
  • 9