Hi all,

I've started migrating an Access 2002 only file catalog app to
Access+Firebird (planned to move Access part to Lazarus/FreePascal later).
I wrote a Firebird stored procedure to add all the details about a
file in one shot and call that sp from Access. This doesn't work (see
below) so I decided to collect all my SQL calls and run them in
Flamerobin to see if my "interesting" (aka newbie) way of programming
with Firebird/ODBC was to blame. The Flamerobin run showed a similar
error.
The SP complains when I try to add info for a file for which
TBLVERSIONS.FILEVERSION and TBLVERSIONS.PRODUCTVERSION already exist:
SQL Message : -803
Invalid insert or update value(s): object columns are
constrained - no 2 table rows can have duplicate column values The SP
should just look up the primary key (VERSIONID) and use this. Only if
it doesn't exist yet should it add the version info to TBLVERSIONS. I
think this is what happens but can't figure out a solution. Any
help/suggestions would be welcome.
More background info & the app as it stands now can be found at
http://www.xs4all.nl/~rolislag/firecat/
(see Readme.txt).
If more details are needed (specific error code etc), I'd be happy to
furnish these
Below: stored procedure code copied from Flamerobin:
SET TERM ^ ;CREATE PROCEDURE SPADDALLFILEDETAILS (
CATALOGID Integer,
FILEPATH Varchar(255),
FILENAME Varchar(255),
FILEDATETIME Timestamp,
FILESIZE Integer,
FILEVERSION Varchar(255),
PRODUCTVERSION Varchar(255),
COMPUTERNAME Varchar(255),
FILEDESCRIPTION Varchar(255) )
AS
declare variable localPATHID INTEGER;
declare variable localFILENAMEID INTEGER;
declare variable localCOMPUTERID INTEGER;
declare variable localVERSIONID INTEGER;
begin
/* Stored procedure that adds a file into the database, */
/* as well as all related info such as path etc. */
/* A program can call this stored procedure to add */
/* files without worrying about the table structure. */
/* Requires: */
/* - catalog for which the file should be added */
/* - path where file is found */
/* - file name (without path) */
/* - file creation date/time */
/* - file size in bytes */
/* Optional: */
/* - file version (for executables/dlls) */
/* - product version (for executables/dlls) */
/* - computer name/hostname where file found */
/* - file description */
/* Returns: nothing */
/* 0. Test for valid input */
IF (:CATALOGID IS NULL) THEN
BEGIN
SUSPEND;
END
/* 1. Add path info if necessary */
SELECT PATHID FROM TBLPATHS WHERE FILEPATH=:filepath
INTO localPATHID;
IF (LOCALPATHID IS NULL) THEN
BEGIN
/* Doens't exist yet, so we have to add path data */
INSERT INTO TBLPATHS (FILEPATH) VALUES (:filepath);
SELECT PATHID FROM TBLPATHS WHERE FILEPATH=:filepath
INTO :localPATHID;
IF (localPATHID IS NULL) THEN
BEGIN
/* in any case, when we can't add the info */
/* in the related table, we should just abort */
EXCEPTION SPCOULDNOTFINDPATHINFO;
END
END /*IF LOCALPATHID IS NULL THEN*/
/* 2. Add filename info if necessary */
SELECT FILENAMEID FROM TBLFILENAMES WHERE FILENAME=:filename
INTO localFILENAMEID;
IF (localFILENAMEID IS NULL) THEN
BEGIN
/* Doens't exist yet, so add filename data */
INSERT INTO TBLFILENAMES (FILENAME) VALUES (:filename);
SELECT FILENAMEID FROM TBLFILENAMES WHERE FILENAME=:filename
INTO :localFILENAMEID;
IF (localFILENAMEID IS NULL) THEN
BEGIN
/* in any case, when we can't add the info */
/* in the related table, we should just abort */
EXCEPTION SPCOULDNOTFINDFILENAMEINFO;
END
END /*IF localFILENAMEID IS NULL THEN*/
/* 3. Add computer name if necessary and present */
/* check for empty values and don't add if computername */
/* is empty as it's a NOT NULL constraint */
IF (COMPUTERNAME IS NOT NULL) THEN
BEGIN
SELECT COMPUTERID FROM TBLCOMPUTERS WHERE COMPUTERNAME=:computername
INTO localCOMPUTERID;
IF (localCOMPUTERID IS NULL) THEN
BEGIN
/* Doens't exist yet, so add computer name */
INSERT INTO TBLCOMPUTERS (COMPUTERNAME) VALUES (:computername);
SELECT COMPUTERID FROM TBLCOMPUTERS WHERE
COMPUTERNAME=:computername
INTO :localCOMPUTERID;
IF (localCOMPUTERID IS NULL) THEN
BEGIN
/* in any case, when we can't add the info */
/* in the related table, we should just abort */
EXCEPTION SPCOULDNOTFINDCOMPUTERINFO;
END
END /*IF localFILENAMEID IS NULL THEN*/
END /*IF COMPUTERNAME IS NOT NULL THEN*/
/* 4. Add version info if present and necessary */
/* check for empty values and don't add if fileversion */
/* is empty as it's a NOT NULL constraint */
IF (FILEVERSION IS NOT NULL) THEN
BEGIN
SELECT VERSIONID FROM TBLVERSIONS WHERE FILEVERSION=:fileversion
AND PRODUCTVERSION=:productversion
INTO localVERSIONID;
IF (localVERSIONID IS NULL) THEN
BEGIN
/* Doens't exist yet, so add version info */
INSERT INTO TBLVERSIONS (FILEVERSION, PRODUCTVERSION)
VALUES (:fileversion, :productversion);
SELECT VERSIONID FROM TBLVERSIONS WHERE FILEVERSION=:fileversion
AND PRODUCTVERSION=:productversion
INTO localVERSIONID;
IF (localVERSIONID IS NULL) THEN
BEGIN
/* in any case, when we can't add the info */
/* in the related table, we should just abort */
EXCEPTION SPCOULDNOTFINDVERSIONINFO;
END
END /*IF localVERSIONID IS NULL THEN*/
END /*FILEVERSION IS NOT NULL THEN*/
/* 5. Add file info and references to catalog, path, */
/* filename, version tables */
INSERT INTO TBLFILES (CATALOGID, COMPUTERID, PATHID,
FILENAMEID, VERSIONID, FILEDATETIME, FILESIZE,
FILEDESCRIPTION)
VALUES
(:catalogid, :localcomputerid, :localpathid,
:localfilenameid, :localversionid, :filedatetime,
:filesize, :filedescription);
/* error handling for any error "when any" */
/* we don't want to ignore errors, so I commented */
/* this for now */
/*WHEN ANY DO
BEGIN
END
*/
end^
SET TERM ; ^
GRANT EXECUTE
ON PROCEDURE SPADDALLFILEDETAILS TO SYSDBA;