Hi All,

I have set up the SP as follows(it compiles)

SP :-

SET AUTODDL OFF;
set term ^ ;
CREATE PROCEDURE INSERT_BEN AS BEGIN EXIT; END ^


ALTER PROCEDURE INSERT_BEN ( BENEF_ID VARCHAR(15)
,BEN_INITIALS VARCHAR(5)
,BEN_FIRSTNAME VARCHAR(30)
,BEN_SURNAME VARCHAR(30)
,BEN_TITLEID SMALLINT)

RETURNS (BEN_ID INTEGER)
AS

BEGIN
IF (BENEF_ID <> '') THEN
BEGIN

SELECT ID
FROM BENEFICIARY
WHERE (ID = :BENEF_ID)
INTO :BEN_ID;


IF (BEN_ID IS NULL) THEN
BEN_ID = GEN_ID( BENID_GEN, 1 );

INSERT INTO BENEFICIARY ( BENID
, ID
, INITIALS
, SURNAME
, FIRSTNAME
, TITLEID)

VALUES ( :BEN_ID
, :BENEF_ID
, :BEN_INITIALS
, :BEN_SURNAME
, :BEN_FIRSTNAME
, :BEN_TITLEID);



END
END
^

My InsertSQL in the qry is as follows:-

EXECUTE PROCEDURE INSERT_BEN(
:ID
, :INITIALS
, :FIRSTNAME
, :SURNAME
, :TITLEID)


The qry SQL is :-

SELECT
b.MEMBERID
, b.RELATIVETYPEID
, b.OPTIONID
, b.FUNDID
, b.BANKID
, b.BRANCHCODE
, b.ACOUNTNO
, b.ACCOUNTTYPEID
, b.BENID

, e.ID
, e.TITLEID
, e.INITIALS
, e.FIRSTNAME
, e.SURNAME
, e.HOMETEL
, e.WORKTEL
, e.EMAIL
, e.LANGUAGEID
, e.RSTREET1
, e.RSTREET2
, e.RCOUNTRYID
, e.RPROVINCEID
, e.RCITYID
, e.RSUBURBID

,(SELECT TITLE
FROM TITLE t
WHERE t.TITLEID=e.TITLEID) AS BEN_TITLE

FROM MEMBERBEN b

JOIN Beneficiary e
ON (e.BENID = b.BENID)

WHERE (b.MEMBERID = :MEMBERID)
AND (b.OPTIONID = :OPTIONID)
AND (b.FUNDID = :FUNDID)


KeyLinks :-

MEMBERID
BENID
OPTIONID
FUNDID

MasterParamLinks :-

MEMBERID = MEMBERID
OPTIONID = OPTIONID
FUNDID = FUNDID

I get the following error when the SP fires :-

Message length error(encountered 0, expected 8)

Is my column naming correct in the qry? I can only think that the params
sucked into the SP are not the correct ones.

Many thanks,
Dion.





-----Original Message-----
From: Helen Borrie (TeamIBO) [mailto:helebor@...]
Sent: Tuesday, January 15, 2002 1:16 AM
To: IBObjects@yahoogroups.com
Subject: Re: [IBO] Join edit


At 07:00 PM 15-01-02 +1100, TeamIBO wrote:

>A third option is to setup the join as you have it and then setup
>stored procedures to do the edit/insert/delete. Not sure whether this
>works for non-KeyRelation fields or not (I've never tried it).

This is the right option for the scenario where the user wants to edit the
entire dataset on a hunt-and-pick basis, as if it all came from a single
table. In this case, you would not set KeyRelation at all; and you would
have parameterised dml stored procedures for all of the three UpdateSQL
properties. This works beautfiully with IBO.

I strongly advise retaining the column names as parameter names throughout;
and make certain that your output dataset contains only one instance of a
join column name, i.e DON'T use select *, use a column list and output the
join column(s) from only the leftmost table in the join. Use full SQL-92
JOIN syntax and proper identifiers on every column.

Avoid outer joins if possible because they may make your KeyLinks difficult
to determine.

You will need to be especially careful about choosing your KeyLinks for this
dataset. If you get it right, your refreshes will work like a dream.


regards,
Helen Borrie (TeamIBO Support)

** Please don't email your support questions privately **
Ask on the list and everyone benefits
Don't forget the IB Objects online FAQ - link from any page at
www.ibobjects.com



___________________________________________________________________________
IB Objects - direct, complete, custom connectivity to Firebird or InterBase
without the need for BDE, ODBC or any other layer.
___________________________________________________________________________
http://www.ibobjects.com - your IBO community resource for Tech Info papers,
keyword-searchable FAQ, community code contributions and more !

Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/