I am at my wits end with this:

CREATE PROCEDURE delete_model_orphans(IN mid INTEGER)
BEGIN
 DECLARE stmnt STATEMENT;
 PREPARE stmnt FROM 'DELETE FROM Stakeholders WHERE mid_local = ?';
 START TRANSACTION ON TABLES Stakeholders;
 BEGIN
   EXECUTE stmnt USING mid;
   COMMIT;
 EXCEPTION
   ROLLBACK;
 END;
END

If I take out the parameterisation then the procedure quite happily deletes all the records in the stakeholders table. With the parameter, it doesn't delete anything, not even the records with the value for mid_local supplied via EDB manager.
(I haven't tried this in code yet). Any suggestions?

Peter

Peter,

<< If I take out the parameterisation then the procedure quite happily
deletes all the records in the stakeholders table. With the parameter, it
doesn't delete anything, not even the records with the value for mid_local
supplied via EDB manager. (I haven't tried this in code yet). Any
suggestions? >>

I'm not sure what the issue is just by looking at it.  Could you post the
CREATE TABLE SQL for the Stakeholders table ?  I can then create the table
here and try it.  I just want to make sure that I get all of the column
types correct.

Thanks,

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

Thanks. here it is ...

CREATE TABLE "Stakeholders"
(
"mid_local" INTEGER NOT NULL DESCRIPTION 'The local model id.',
"stakeholder_id" INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 0, INCREMENT BY 1) NOT NULL DESCRIPTION 'The unique stakeholder id.',
"stakeholder_name" VARCHAR(75) COLLATE "ANSI" DEFAULT '< Enter new model name >'  NOT NULL,
"livestat" INTEGER DEFAULT 0 NOT NULL DESCRIPTION 'The livelihood status assigned to the stakeholder group.',
"notes" BLOB,
"include" BOOLEAN,
CONSTRAINT "primary_key" PRIMARY KEY ("mid_local", "stakeholder_id")
)
DESCRIPTION 'Definitions of the model stakeholders.'
VERSION 1.00
INDEX PAGE SIZE 4096
BLOB BLOCK SIZE 512
MAX ROW BUFFER SIZE 32768
MAX INDEX BUFFER SIZE 65536
MAX BLOB BUFFER SIZE 32768

Peter,

<< Thanks. here it is ... >>

Hmm, it works okay here with the 1.03 candidate code, so I'm going to have
to say that it was fixed already.  The 1.03 release should be available
within the next few days.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

I have installed 1.03 but still have the same problem that Peter described.

The first procedure works OK.  The second procedure with parameters does not work.

CREATE PROCEDURE "X" ()
BEGIN
DECLARE CustCursor CURSOR FOR Stmt;
DECLARE State CHAR(3) DEFAULT '';

PREPARE Stmt FROM 'UPDATE Contact SET StateNo = 999 WHERE StateNo = 2';
OPEN CustCursor;
EXECUTE Stmt;

END


CREATE PROCEDURE "Y" (IN "StateParam" INTEGER)
BEGIN
DECLARE CustCursor CURSOR FOR Stmt;
DECLARE State CHAR(3) DEFAULT '';

PREPARE Stmt FROM 'UPDATE Contact SET StateNo = 999 WHERE StateNo = ?';
OPEN CustCursor;
EXECUTE Stmt USING StateParam;
END

I have attached a zip file with the database.

--
Richard Harding
Windella Computer Knowhow
28 Freeman Drive
Lochinvar NSW 2321
Phone:   61 2 4930 7336
Mobile:   0419 016 032
email:   rharding@wck.com.au

Richard,

<< I have installed 1.03 but still have the same problem that Peter
described.

The first procedure works OK.  The second procedure with parameters does
not work. >>

Are you sure that you're compiling with 1.03 ?  I've tried it here twice and
it works just fine.

--
Tim Young
Elevate Software
www.elevatesoft.com

Ooops . . . .

I have just discovered the "Set Parameter Value" button . . . . which does make a lot of
difference. . . . . .

Thank  you.


--
Richard Harding
Windella Computer Knowhow
28 Freeman Drive
Lochinvar NSW 2321
Phone:   61 2 4930 7336
Mobile:   0419 016 032
email:   rharding@wck.com.au

Richard,

<< I have just discovered the "Set Parameter Value" button . . . . which
does make a lot of difference. . . . . . >>

Yes, it does.

Thanks for the update,

--
Tim Young
Elevate Software
www.elevatesoft.com