Hi,

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

SUSPEND should only be used if you plan on using this procedure
as a SELECT-able procedure AND you want to return a row of data.
If not, then use EXIT; instead.

> END
> /* 1. Add path info if necessary */
> SELECT PATHID FROM TBLPATHS WHERE FILEPATH=:filepath
> INTO localPATHID;
> IF (LOCALPATHID IS NULL) THEN

If no row is found in the above SELECT query, LOCALPATHID will not
be modified. That is, it will keep its value as it had before the SELECT.
Given that you did not intialize your variables, it is NULL and it will be
NULL afterwards if no rows are found.

Try initializing it on a specific value, eg "-1" and then check for that.

Or use an EXISTS query:

IF (NOT EXISTS ( SELECT PATHID FROM TBLPATHS WHERE FILEPATH=:filepath ) )
...

etc...

> 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

Same here. If there's no row to be found, localVERSIONID will be NULL
if you didn't intialize it before.


Hope this helps.

Martijn Tonies
Database Workbench - development tool for Firebird and more!
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com