[fpc-pascal] Firebird stored procedure exceptions generate access violation

[Jim]

Hi all,

Still working on my locate clone at
http://bitbucket.org/jb/flocate/
- download at
http://bitbucket.org/jb/flocate/downloads

Running Free Pascal Compiler version 2.4.1 [2010/06/01] for x86_64 on
Windows.

I've cobbled together some Firebird database code.
If you want to have a more complete overview, I've posted the entire
database unit to
http://pastebin.com/rgpAK91Q

I have connection/transaction/query objects open. The query calls a
stored procedure on the database and has parameters defined... like this
EXECUTE PROCEDURE SPADDFILEDEFAULT ( ' + ':COMPUTERNAME, ' +    .... and
a lot of others....

In a loop going through the files I've found on the computer (in
TDirectoryEntryList = class(TFPObjectList) ), I call a procedure
procedure TflocateDB.SaveDirectoryEntry(COMPUTERNAME: string; .... and a
lot of others....
that use the open query, first clear (for luck, I guess ;) then assign
parameters (e.g.
    FInsertQuery.Params.ParamByName('COMPUTERNAME').AsString :=
COMPUTERNAME;
    FInsertQuery.Params.ParamByName('DATEACCESSED').AsDateTime :=
DATEACCESSED;
Finally I execute the SQL within an try...except block
    try
      FInsertQuery.ExecSQL;
      FTransaction.CommitRetaining;
      //todo: this is for testing so we can see what didn't get inserted
      //in production, we'd just have one big happy transaction I suppose
    except
      on E: EIBDatabaseError do
      begin
        // We would havee committed, so we should be rolling back.
{$IFDEF DEBUG}
        Writeln(stderr, 'Debug: ', DateTimeToStr(Now),
          ': Database error running insert query (filename: ', FILENAME,
          '). Database error message: ',
          E.Message, '; Database error code: ', E.GDSErrorCode
          );
        Writeln(stderr, 'Debug: ', DateTimeToStr(Now),
          ': Parameters were: ');
        for DebugCounter := 0 to FInsertQuery.Params.Count - 1 do
        begin
          Writeln(stderr, FInsertQuery.Params[DebugCounter].Name, ': *',
            FInsertQuery.Params[DebugCounter].Value , '*');
        end;
        Writeln(stderr, 'Debug: ', DateTimeToStr(Now),
          ': Rolling back transaction.');
{$ENDIF DEBUG}
        FTransaction.RollBack;
      end;
      on E: Exception do
      begin
{$IFDEF DEBUG}
        Writeln(stderr, 'Debug: ', DateTimeToStr(Now),
          ': Error running insert query (filename: ',
          FILENAME, '). Technical details: ',
          E.Message
          );
        Writeln(stderr, 'Debug: ', DateTimeToStr(Now),
          ': Parameters were: ');
        for DebugCounter := 0 to FInsertQuery.Params.Count - 1 do
        begin
          Writeln(stderr, FInsertQuery.Params[DebugCounter].Name, ': *',
            FInsertQuery.Params[DebugCounter].Value, '*');
        end;
{$ENDIF DEBUG}
      end; //E: Exception
    end; //end of actual execsql block 

The stored procedure in Firebird takes all the parameters, looks them up
in the respective tables (e.g. TBLCOMPUTERS), inserts new records if
required, gets the resulting primary keys for dependent tables and
finally inserts all these keys and some more data in a TBLFILES table.
This stored procedure is not faultless yet; sometimes it errors out.
I've implemented multiple custom Firebird exceptions in the stored
procedures like this:
CREATE EXCEPTION SPCOULDNOTFINDVERSIONINFO
'Could not add records: unable to add version information to database';
The SP calls these exceptions when something seriously goes wrong e.g.
        SELECT EXEID FROM TBLEXES WHERE EXEFILEVERSION=:exefileversion
            AND EXEPRODUCTVERSION=:exeproductversion
            INTO :localEXEID;
        IF (:localEXEID IS NULL) THEN
        BEGIN
            /* when we can't add the info                       */
            /* in the related table, we should just abort       */
            localDUMMY = FBDEBUG('Exception for TBLEXES problems will be
called: SPCOULDNOTFINDVERSIONINFO', '/tmp/test.txt');
... some more debugging stuff...
            EXCEPTION SPCOULDNOTFINDVERSIONINFO;
        END


When I hit the stored procedure exception mentioned above, my code
doesn't complain, but on the next file and all subsequent files, the
error code in my code indicates I got an access violation.
E.g. my Firebird logging gives:
5-6-10 10:02:07:Exception for TBLEXES problems will be called:
SPCOULDNOTFINDVERSIONINFO
5-6-10 10:02:07:FILENAME                       :*nmwcdclsx64.dll*
5-6-10 10:02:07:FILEPATH                       :*C:\windows\system32\*
5-6-10 10:02:07:localEXEID                     :*<NULL>*
5-6-10 10:02:07:EXECOMPANY                     :*Nokia*
5-6-10 10:02:07:EXECOPYRIGHT                   :*Copyright (c)
2002,2003,2004,2005. Nokia. All rights reserved.*
5-6-10 10:02:07:EXEDESCRIPTION                 :*Wireless Communication
Device Class Installer*
5-6-10 10:02:07:EXEFILEVERSION may not be NULL :*7.1.18.34*
5-6-10 10:02:07:EXEINTERNALNAME                :*NMWCDCLSX64*
5-6-10 10:02:07:EXEORIGINALFILENAME            :*nmwcdclsx64.dll*
5-6-10 10:02:07:EXEPRODUCTNAME                 :*<NULL>*
5-6-10 10:02:07:EXEPRODUCTVERSION              :*<NULL>*
While the debug output my code generates shows:
Debug: 5-6-2010 10:02:05: SaveDirectoryEntry: going to add parameters
for insert query (nmwcdclsx64.dll)
Debug: 5-6-2010 10:02:05: SaveDirectoryEntry: insert query code done.
(nmwcdclsx64.dll)
Debug: 5-6-2010 10:02:05: SaveDirectoryEntry: going to add parameters
for insert query (<the next file>)
Debug: 5-6-2010 10:02:05: Error running insert query (filename: <the
next file>). Technical details: Access violation
Debug: 5-6-2010 10:02:05: Parameters were:
... and it shows the parameters I've set
In the mean time, I think I know why the stored proc errors out, but I
still think there's something wrong with my FreePascal code ;)

Finally, the question:
What I really want is to get a Firebird SQLCODE error code or some other
details that allow me to figure out whether one of my custom exceptions
get called. Also, it seems something is wrong after the error; although
the objects still seem to exist, the database obviously won't insert
anymore.

How do I get proper error codes and/or fix this code? (Of course,
suggestions on code improvement etc are also welcome)

Thanks in advance,

-- 
Regards,

jb