Hello,

Any comments on the following will be greatly appreciated!

I am experiencing an intermittent problem using IB_DSQL for batch inserts with Firebird. When I test my code on my development systems, it executes without problem, but on the production system, it intermittently fails. Today I tested the code on 3 other systems: it failed on two of them and ran fine on the third! Two of the three failing systems run Windows 2000 Professional, the other runs Windows XP Home. The two systems that do not exhibit the failure run Win2K Pro and Win XP Pro.

Environment:

Borland C++ Builder 6 Professional, Build 10.166
Firebird 1.5, RC7 SS (Win32)
IBObjects 4.3Aa (also failed using 4.2Ie)

The scenario:

I am doing bulk inserts (hundreds of thousands) of records into several tables. I commit every 10,000 inserts. Before each insert I also use a TIB_Cursor to execute a select SP to generate a record ID number.

Initialization:
1. Prepare each IB_DSQL instance.
TIB_DSQL * ibdsqlOptionPriceInsert; // (from the datamodule's header file)
ibdsqlOptionPriceInsert->Prepare();
The SQL statement I use for the insert is:
insert into OptionPriceTable
( OptionPrice_ID, Option_ID, Bid_Price, etc. )
values (:OptionPrice_ID, :Option_ID, :Bid_Price, etc. );
(Of course, I do not use "etc." in the actual code, but have removed additional parameters for brevity)
2. Using the ParamByName function, retrieve the column pointers for each input parameter.
TIB_Column * ibdsqlOptionPriceInsert_OptionPrice_ID;
TIB_Column * ibdsqlOptionPriceInsert_Option_ID;
TIB_Column * ibdsqlOptionPriceInsert_Bid_Price;
ibdsqlOptionPriceInsert_OptionPrice_ID = ibdsqlOptionPriceInsert->ParamByName( "OptionPrice_ID" );
ibdsqlOptionPriceInsert_Option_ID = ibdsqlOptionPriceInsert->ParamByName( "Option_ID" );
ibdsqlOptionPriceInsert_Bid_Price = ibdsqlOptionPriceInsert->ParamByName( "Bid_Price" );
etc.
3. Prepare the cursor instance
DbGetNewPriceID->Prepare();
4. Retrieve the column pointer for the returned field.
DbGetPriceID_ID_VAL = DbGetNewPriceID->FieldByName( "id_val" );

During the insertion loop, I do the following:
1. If needed, start the transaction:
if ( iPendingInsertions == 0 )
{
DbImportTrans->StartTransaction();
}
2. Get a new record ID
if ( DbGetNewPriceID->Active )
DbGetNewPriceID->Active = false;
DbGetNewPriceID->Active = true;
iPriceID = DbGetPriceID_ID_VAL->AsInt64;
DbGetNewPriceID->Active = false;
3. Load the parameters
ibdsqlOptionPriceInsert_OptionPrice_ID->AsInt64 = iPriceID;
ibdsqlOptionPriceInsert_Option_ID->AsInt64 = pPrice->iOptionID;
ibdsqlOptionPriceInsert_Bid_Price->AsFloat = pPrice->dBidPrice;
etc.
4. Execute the request and bump the insertion counter
ibdsqlOptionPriceInsert->Execute();
iPendingInsertions++;
5. After 10,000 insertions (combined across all tables), commit the transaction.
if ( iPendingInsertions >= 10000 )
{
DbImportTrans->Commit();
iPendingInsertions = 0;
}


The failure:
1. Intermittently (after a hundred thousand to a million inserts), the program will lock up while inside the IBObjects code - but only on some machines, not on all, as mentioned above.
2. Even though all IBObjects requests are issued in a separate data-import thread from the main thread that handles the Windows interface, the entire program is locked up and does not respond to any user interaction.
3. The program has to be terminated from the windows task manager or the Builder IDE. Note that when running inside the C++ Builder IDE, the IDE can't stop the program and bring the debugger up: termination is the only thing that works.
4. While running the import loop, the CPU is running at 100% utilization. When the program locks up, CPU usage drops to idle, so whatever is bad, it isn't an infinite loop.
5. The "SQL hourglass" is gone when the program locks up, making it appear that we aren't down inside a Firebird operation?
6. One time I believe I caught it locked up down inside the TIB_Cursor execution, but every other time it is inside the TIB_DSQL Execute() function.

Since I can't postmortem the failure via the debugger, how do I know it is failing in those places?
I wrote a little function that writes integer values into a shared-memory file, which I also mapped into the memory space of another little app I wrote, which periodically looks at the shared memory and updates a string grid column with the new values. I wrote unique values into the shared memory location at selected points through the data-import loop, and kept adding more as I saw which value was the "last" value before the crash occurred.
The Execute statement listed above actually now looks like this:
vWriteTraceMemEntry( pvTraceMemHandle, 84302 );
ibdsqlOptionPriceInsert->Execute();
vWriteTraceMemEntry( pvTraceMemHandle, 84303 );
In this example, the "last" value I see in the shared memory is 84302, which leads me to believe that I entered the function and never returned.

As this is all brand-new code, I could well believe I have done something to trash something critical inside IBO, but have not yet found any evidence of that other than the crash itself.

Has anyone seen any problems along these lines before and can tell me what they found at the root of it? I will start trying to drill down into the Delphi code to see if I can find the actual crater and learn anything from that, but I am not a Delphi programmer by any stretch of the imagination, so it remains to be seen how far I'll get... In the meantime, any ideas will be very welcome!

Best regards, and TIA,

Dan Wilson.