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 blockThe 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 becalled: SPCOULDNOTFINDVERSIONINFO', '/tmp/test.txt'); ... some more debugging stuff...
EXCEPTION SPCOULDNOTFINDVERSIONINFO;
ENDWhen 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,