Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 8 of 8 total |
Parameter values not picked up in stored proc |
Wed, May 2 2007 10:02 AM | Permanent Link |
Peter Thorne | 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 |
Wed, May 2 2007 11:33 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Wed, May 2 2007 12:30 PM | Permanent Link |
Peter Thorne | 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 |
Thu, May 3 2007 4:33 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Wed, Jun 6 2007 8:42 PM | Permanent Link |
Richard Harding | 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 Attachments: EDB_test.zip |
Thu, Jun 7 2007 12:51 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Thu, Jun 7 2007 6:52 PM | Permanent Link |
Richard Harding | 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 |
Fri, Jun 8 2007 3:25 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
This web page was last updated on Sunday, May 19, 2024 at 08:46 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |