Tim:

My full routine is:

============
BEGIN CODE
============

....
const
 C_INSERT_DOCUMENT2SITE_SQL = 'INSERT INTO Document2Site (DocumentID,
SiteID) VALUES (:DocumentID, :SiteID)';
 C_SELECT_DOCUMENT2SITE_SQL = 'SELECT * FROM Document2Site WHERE
(DocumentID = :DocumentID) AND (SiteID = :SiteID)';
....

procedure TDocumentsForm.DocumentQueryAfterPost(DataSet: TDataSet);
var
 Count: integer;
 Doc, Site: integer;
begin
 if DM.UtilityQuery.Active then DM.UtilityQuery.Close;
 DM.UtilityQuery.SQL.Text := C_SELECT_DOCUMENT2SITE_SQL;

 //Get the DocumentID of the new record and the SiteID of the current site
 Doc := Dataset.FieldByName('DocumentID').AsInteger;
 Site := DM.SitesTable.FieldByName('SiteID').AsInteger;

 //Check to see if a record link already exists
 DM.UtilityQuery.ParamByName('DocumentID').AsInteger :=
DocumentQuery.FieldByName('DocumentID').AsInteger;
 DM.UtilityQuery.ParamByName('SiteID').AsInteger :=
DM.SitesTable.FieldByname('SiteID').AsInteger;
 DM.UtilityQuery.Open;
 Count := DM.UtilityQuery.RecordCount;
 DM.UtilityQuery.Close;

 //Create a new document link only if it doesn't already exist
 if Count = 0 then
 begin
   DM.UtilityQuery.SQL.Text := C_INSERT_DOCUMENT2SITE_SQL;
   DM.UtilityQuery.ParamByName('DocumentID').AsInteger := Doc;
   DM.UtilityQuery.ParamByName('SiteID').AsInteger := Site;
   DM.UtilityQuery.ExecSQL;
   DM.UtilityQuery.Close;
 end;
end;

============
END CODE
============

Yes, my insert is outside the WHERE constraints.  The last section of the
code (starting with "if Count = 0 then") performs an insert on the
Document2Site table that would put it back in compliance with the
constraint.  But, it relies upon getting the correct DocumentID which I now
realize isn't possible in this scenario.

Hmmmm.  Just brainstorming ... I could rebuild this in a manner so that the
form is not databound (at least not for the insert portion).  Would this
better be handled by a stored procedure on the database end?  What is the
best way to handle this kind of many-to-many relationship?

Thanks.

Greg

Greg,

<< Yes, my insert is outside the WHERE constraints.  The last section of the
code (starting with "if Count = 0 then") performs an insert on the
Document2Site table that would put it back in compliance with the
constraint.  But, it relies upon getting the correct DocumentID which I now
realize isn't possible in this scenario. >>

Yep, that will be an issue.

<< Hmmmm.  Just brainstorming ... I could rebuild this in a manner so that
the form is not databound (at least not for the insert portion).  Would this
better be handled by a stored procedure on the database end? >>

Sure, especially if it is solely a database-only operation that requires
multiple steps.  You could perform the initial insert and then just run
another query to check for the linkage, inserting it if necessary.   If you
need some specific SP code, just post the INSERT SQL along with the linkage
query, and I'll give you the correct SQL/PSM code.

<< What is the best way to handle this kind of many-to-many relationship? >>

It really depends upon the needs of the appliation, so it's hard for me to
comment in specifics without knowing your application design.

--
Tim Young
Elevate Software
www.elevatesoft.com

> << Hmmmm.  Just brainstorming ... I could rebuild this in a manner so that
> the form is not databound (at least not for the insert portion).  Would
> this better be handled by a stored procedure on the database end? >>
>
> Sure, especially if it is solely a database-only operation that requires
> multiple steps.  You could perform the initial insert and then just run
> another query to check for the linkage, inserting it if necessary.   If
> you need some specific SP code, just post the INSERT SQL along with the
> linkage query, and I'll give you the correct SQL/PSM code.

Tim:

I'm just getting back into this after a couple of weeks of being away (my
wife had a baby girl about two weeks ago).

The INSERT SQL is held in the following constant:

 C_INSERT_DOCUMENTS_SQL = 'INSERT INTO Documents (TypeID, Date,
DateReceived, Title, Author, Recipient, StatusID) VALUES (:TypeID, :Date,
:DateReceived, :Title, :Author, :Recipient, :StatusID)';

The linkage query is:

 C_SELECT_DOCUMENT2SITE_SQL = 'SELECT * FROM Document2Site WHERE
(DocumentID = :DocumentID) AND (SiteID = :SiteID)';

I would need the SP to return the DocumentID generated by the insert into
the Documents table.

Thanks for your help in bringing me up to speed on this.

Greg

"Greg Bishop" <bishop@porpoisemedia.com> wrote in message
news:14A226FA-4A61-4965-A32B-7EEB7AF25B1F@news.elevatesoft.com...
>
> I'm just getting back into this after a couple of weeks of being away (my
> wife had a baby girl about two weeks ago).
>

ABOUT Oh Greg about 2 weeks ago, I bet you forget your wife's birthday
dont you ?
Well done on the baby tho.
Rita

Greg,

<< I'm just getting back into this after a couple of weeks of being away (my
wife had a baby girl about two weeks ago). >>

Congratulations !!!  Is it your first child ?

<< I would need the SP to return the DocumentID generated by the insert into
the Documents table. >>

Okay, but just another question - what happened to the INSERT into the
Document2Site table if the linkage is not already present ?  Does that also
need to be included in the SP ?

--
Tim Young
Elevate Software
www.elevatesoft.com

> Congratulations !!!  Is it your first child ?

Yes, this is the first one.  She (Jillian) was born March 6 and is healthy,
happy, and beautiful (she takes after her mom).  We're very excited.

> << I would need the SP to return the DocumentID generated by the insert
> into the Documents table. >>
>
> Okay, but just another question - what happened to the INSERT into the
> Document2Site table if the linkage is not already present ?  Does that
> also need to be included in the SP ?

Doh!  ... I'll blame it on the lack of sleep.  Yes, it does need to be
included, too.  That one is:

C_INSERT_DOCUMENT2SITE_SQL = 'INSERT INTO Document2Site (DocumentID, SiteID)
VALUES (:DocumentID, :SiteID)';

This, of course, has to be done after retrieving the DocumentID value.
SiteID would be passed to the SP as a parameter.

Thanks.

Greg

Greg,

<< Yes, this is the first one.  She (Jillian) was born March 6 and is
healthy, happy, and beautiful (she takes after her mom).  We're very
excited. >>

Yes, enjoy every minute of it.  My daughter is leaving us next year for an
entire school year - she won the Congress-Bundestag scholarship to be a
foreign-exchange student in Germany, so she'll be studying abroad for her
senior year of high school.  Needless to say, it isn't going to be easy
letting her go.

Here's the SP, however there's a catch.  1.08 does not properly return the
DocumentID OUT parameter, and instead returns NULL.  This will be corrected
in 1.09 due out tomorrow, though, so then it will work just fine.

CREATE PROCEDURE "InsertDocument" (OUT "DocumentID" INTEGER, IN "SiteID"
INTEGER,
                                  IN "TypeID" INTEGER, IN "DocDate" DATE,
                                  IN "DateReceived" DATE, IN "Title"
VARCHAR COLLATE ANSI,
                                  IN "Author" VARCHAR COLLATE ANSI,
                                  IN "Recipient" VARCHAR COLLATE ANSI, IN
"StatusID" INTEGER)
BEGIN
  DECLARE Stmt STATEMENT;
  DECLARE TempCursor SENSITIVE CURSOR FOR CursorStmt;

  PREPARE Stmt FROM 'INSERT INTO Documents (DocumentID, TypeID, Date,
DateReceived,
Title, Author, Recipient, StatusID) VALUES (?, ?, ?, ?, ?, ?, ?, ?)';

  EXECUTE Stmt USING DocumentID, TypeID, DocDate, DateReceived, Title,
Author,
Recipient, StatusID;

  UNPREPARE Stmt;

  -- Now DocumentID should contain the next document ID

  PREPARE CursorStmt FROM 'SELECT * FROM Document2Site WHERE
(DocumentID = ?) AND (SiteID = ?)';

  OPEN TempCursor USING DocumentID, SiteID;

  IF ROWCOUNT(TempCursor) = 0 THEN

     INSERT INTO TempCursor (DocumentID, SiteID) VALUES (DocumentID,
SiteID);

  END IF;

END

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

> Yes, enjoy every minute of it.  My daughter is leaving us next year
for an
> entire school year - she won the Congress-Bundestag scholarship to be a
> foreign-exchange student in Germany, so she'll be studying abroad for
her
> senior year of high school.  Needless to say, it isn't going to be easy
> letting her go.

If your daughter will need any assistance or help here in Germany please
feel free to ask me. It'll be a pleasure to help if I can.

Regards Uli

Uli,

<< If your daughter will need any assistance or help here in Germany please
feel free to ask me. It'll be a pleasure to help if I can. >>

Thank you, that is much appreciated.  We still don't know what area of
Germany she'll be living in yet, but we should know in a month or two.
Also, she has to go to a "language camp" for a couple of weeks before she
settles in with her actual host family, so she'll hopefully get to see two
different cities or areas initially.

They discourage any parents from coming over with the students so that they
can get acclimated as quickly as possible, so we won't be able to visit
until the latter part of her stay there.

But, when we do visit, we'll be sure to let everyone know so that we can try
to visit as many of our friends there as possible, including you, of course.


--
Tim Young
Elevate Software
www.elevatesoft.com