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

[Michael Van Canneyt]

On Sat, 5 Jun 2010, Jim wrote:

> 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)

It might be worth putting all parameters in a record definition and pass the
record to your function. That makes the procedure declaration somewhat more
manageable and understandable. (and definitely more efficent as well).

Then, there is an access violation in your code:
Try to get a stack trace, we can then tell where the access violation
occured, maybe it will tell us a little more.

Michael.