Hoi Martijn,

Thanks for your quick reply.
See my comments below...
--- In firebird-support@yahoogroups.com, "Martijn Tonies"
<m.tonies@...> wrote:

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

Thanks very much; changed that...

>
> > /* 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.

Exactly. This is my intention: if no rows are found, the sp should add
the info concerned to e.g. the TBLPATHS table...

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

that.
I explicitly declared the local... variables NULL in the beginning of
the sp..

>
> Or use an EXISTS query:
>
> IF (NOT EXISTS ( SELECT PATHID FROM TBLPATHS WHERE

FILEPATH=:filepath ) )
Thanks, but this means more work if the info already exists - I'd have
to get the primary key again using a select statement...

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

Martijn (& others), I'm an idiot. After much testing and debugging
(sending output to an external text table) I realized that I had a
unique constraint on TBLVERSIONS.FILEVERSION. Whenever I tried to add
a FILEVERSION,PRODUCTVERSION record that had an existing FILEVERSION
(but a new PRODUCTVERSION) the sp bombed. Changing the constraint to a
constraint on the two fields together helped <embarrassed grin>.

>
>
> Hope this helps.

Thanks,
Reinier