RE: Use stored procedure for insert questions
Hi again Thanks a lot for your answer but it's still not working The broker refuse to insert the instance because there is no sequence manager. Here is the stored procedure : PROCEDURE ADDINSTANCE ( IID OUT twtinstance.id%NUMBER , ITAG IN twtinstance.tag%CHAR , INAME IN twtinstance.name%CHAR , IOWNER IN twtinstance.UpdatePri%NUMBER , IWID IN twtinstance.WID%CHAR , ISRCID IN twtinstance.SourceId%NUMBER , ILOCID IN twtinstance.LocationId%NUMBER , ICFGID IN twtinstance.ConfigId%NUMBER , IZID IN twtinstance.ZoneId%NUMBER ) IS aInsId NUMBER; BEGIN SELECT twtinstances_id_seq.NEXTVAL INTO aInsId FROM dual; -- Insert the instance itself INSERT INTO twtInstances (Id, State, DataSync, SourceId, WID, LocationId, Name, ZoneId, ConfigId, UpdatePri, Tag) VALUES (aInsId, 0, 1, ISRCID, IWID, ILOCID, INAME, IZID, ICFGID, IOWNER, ITAG) RETURNING Id INTO IID; END ADDINSTANCE; -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Friday, November 07, 2003 5:12 PM To: [EMAIL PROTECTED] Subject: RE: Use stored procedure for insert questions Based on the configuration of the 'insert-procedure' descriptor that you provided earlier, here's the syntax of a procedure that 'should' work. Please not that the compiler on my email editor is not working right now, so I have no way of confirming if the syntax is correct. However, the basic structure is correct. CREATE OR REPLACE PROCEDURE ADDINSTANCE ( aID OUT TWTINSTANCES.ID%TYPE , aDESCRIPTION IN TWTINSTANCES.DESCRIPTION%TYPE , aDATASYNCIN TWTINSTANCES.DATASYNC%TYPE , aCONFIGIDIN TWTINSTANCES.CONFIGID%TYPE) IS BEGIN -- Get next value from the sequence -- This will put the value in the 'out' parameter named aID SELECT .NEXTVAL INTO aID FROM DUAL; -- Insert the record INSERT INTO TWTINSTANCES (ID, DESCRIPTION, DATASYNC, CONFIGID) VALUES (aID, aDESCRIPTION, aDATASYNC, aCONFIGID); END; HTH Ron Gallagher Atlanta, GA [EMAIL PROTECTED] -Original Message- From: Thierry Hanot [mailto:[EMAIL PROTECTED] Sent: Friday, November 07, 2003 10:58 AM To: 'OJB Users List' Subject: RE: Use stored procedure for insert questions Thanks But how to do it , because there is no documentation on it ??? I probably did a wrong descriptor on the insert-procedure . B.R Thierry -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Friday, November 07, 2003 4:31 PM To: [EMAIL PROTECTED] Subject: RE: Use stored procedure for insert questions Thierry -- You shouldn't need to use a sequence manager since the SP is assigning the id. Based on the configuration of the 'insert-procedure' descriptor, the first argument to the sp will be defined as either 'OUT' or 'IN OUT'. In either case, the value that's returned by the sp will be placed in the 'id' attribute on the 'Twtinstances' class. HTH Ron Gallagher Atlanta, GA [EMAIL PROTECTED] -Original Message- From: Thierry Hanot [mailto:[EMAIL PROTECTED] Sent: Friday, November 07, 2003 10:20 AM To: 'OJB Users List' Subject: Use stored procedure for insert questions Hello All I currently trying to use the cvs version of ojb 1.0 with an Oracle 9i Database and I have a few problem with stored procedure and sequences: 1: I want to Stored procedure for insert some rows. The stored procedure return the id ( primary key ) of the row.But i don't know wich sequence manager i need to use for managing this class . Here is the class descriptor: But i don't find any way to use it correctly in ojb . First i didn't define any sequence manager , the PB refuse to insert any instance . Second try : using the SequenceManagerNativeImpl ( not implemented for this pla
Re: Use stored procedure for insert questions
- Original Message - From: <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Saturday, November 08, 2003 5:11 AM Subject: RE: Use stored procedure for insert questions > Based on the configuration of the 'insert-procedure' descriptor that you > provided earlier, here's the syntax of a procedure that 'should' work. > Please not that the compiler on my email editor is not working right now, so > I have no way of confirming if the syntax is correct. However, the basic > structure is correct. > > CREATE OR REPLACE PROCEDURE ADDINSTANCE >( aID OUT TWTINSTANCES.ID%TYPE >, aDESCRIPTION IN TWTINSTANCES.DESCRIPTION%TYPE >, aDATASYNCIN TWTINSTANCES.DATASYNC%TYPE >, aCONFIGIDIN TWTINSTANCES.CONFIGID%TYPE) >IS > > BEGIN > >-- Get next value from the sequence >-- This will put the value in the 'out' parameter named aID >SELECT .NEXTVAL > INTO aID > FROM DUAL; > >-- Insert the record >INSERT INTO TWTINSTANCES > (ID, >DESCRIPTION, >DATASYNC, >CONFIGID) >VALUES > (aID, >aDESCRIPTION, >aDATASYNC, >aCONFIGID); > END; > > HTH > > Ron Gallagher > Atlanta, GA > [EMAIL PROTECTED] > > -Original Message- > From: Thierry Hanot [mailto:[EMAIL PROTECTED] > Sent: Friday, November 07, 2003 10:58 AM > To: 'OJB Users List' > Subject: RE: Use stored procedure for insert questions > > > Thanks But how to do it , because there is no documentation on it ??? > I probably did a wrong descriptor on the insert-procedure . > > B.R > Thierry > > > > > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > Sent: Friday, November 07, 2003 4:31 PM > To: [EMAIL PROTECTED] > Subject: RE: Use stored procedure for insert questions > > > Thierry -- > > You shouldn't need to use a sequence manager since the SP is assigning the > id. Based on the configuration of the 'insert-procedure' descriptor, the > first argument to the sp will be defined as either 'OUT' or 'IN OUT'. In > either case, the value that's returned by the sp will be placed in the 'id' > attribute on the 'Twtinstances' class. > > HTH > > Ron Gallagher > Atlanta, GA > [EMAIL PROTECTED] > > > -Original Message- > From: Thierry Hanot [mailto:[EMAIL PROTECTED] > Sent: Friday, November 07, 2003 10:20 AM > To: 'OJB Users List' > Subject: Use stored procedure for insert questions > > > Hello All > I currently trying to use the cvs version of ojb 1.0 with an Oracle 9i > Database and I have a few problem with stored procedure and sequences: > > > 1: I want to Stored procedure for insert some rows. > The stored procedure return the id ( primary key ) of the row.But i don't > know wich sequence manager i need to use for managing this class . > > Here is the class descriptor: > > table="VMUSER.TWTINSTANCES"> > jdbc-type="TIMESTAMP"/> > jdbc-type="BIGINT"/> > jdbc-type="BIGINT"/> > jdbc-type="VARCHAR"/> > primarykey="true" /> > > > return="false"/> > return="false"/> > return="false"/> > > > > > But i don't find any way to use it correctly in ojb . > First i didn't define any sequence manager , the PB refuse to insert any > instance . Second try : using the SequenceManagerNativeImpl ( not > implemented for this plateform ). > > > > > 2: One other question concern the repository.dtd wich has the following > lines on the stored procedures: > > > constant-argument)?, attribute*)> name CDATA #REQUIRED > return-field-ref CDATA #IMPLIED > include-all-fields (true | false) "false" > > > > > A procedure could have one to many arguments , I just wonder if we should > have > > constant-argument)+, attribute*)> name CDATA #REQUIRED > return-field-ref CDATA #IMPLIED > include-all-fields (true | false) "false" > > > > > > > > 3: Is it possible to use different sequence manager in the same repository > ??? If yes how ? > > > B.R > > > Thierry Hanot > > - &g
RE: Use stored procedure for insert questions
Based on the configuration of the 'insert-procedure' descriptor that you provided earlier, here's the syntax of a procedure that 'should' work. Please not that the compiler on my email editor is not working right now, so I have no way of confirming if the syntax is correct. However, the basic structure is correct. CREATE OR REPLACE PROCEDURE ADDINSTANCE ( aID OUT TWTINSTANCES.ID%TYPE , aDESCRIPTION IN TWTINSTANCES.DESCRIPTION%TYPE , aDATASYNCIN TWTINSTANCES.DATASYNC%TYPE , aCONFIGIDIN TWTINSTANCES.CONFIGID%TYPE) IS BEGIN -- Get next value from the sequence -- This will put the value in the 'out' parameter named aID SELECT .NEXTVAL INTO aID FROM DUAL; -- Insert the record INSERT INTO TWTINSTANCES (ID, DESCRIPTION, DATASYNC, CONFIGID) VALUES (aID, aDESCRIPTION, aDATASYNC, aCONFIGID); END; HTH Ron Gallagher Atlanta, GA [EMAIL PROTECTED] -Original Message- From: Thierry Hanot [mailto:[EMAIL PROTECTED] Sent: Friday, November 07, 2003 10:58 AM To: 'OJB Users List' Subject: RE: Use stored procedure for insert questions Thanks But how to do it , because there is no documentation on it ??? I probably did a wrong descriptor on the insert-procedure . B.R Thierry -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Friday, November 07, 2003 4:31 PM To: [EMAIL PROTECTED] Subject: RE: Use stored procedure for insert questions Thierry -- You shouldn't need to use a sequence manager since the SP is assigning the id. Based on the configuration of the 'insert-procedure' descriptor, the first argument to the sp will be defined as either 'OUT' or 'IN OUT'. In either case, the value that's returned by the sp will be placed in the 'id' attribute on the 'Twtinstances' class. HTH Ron Gallagher Atlanta, GA [EMAIL PROTECTED] -Original Message- From: Thierry Hanot [mailto:[EMAIL PROTECTED] Sent: Friday, November 07, 2003 10:20 AM To: 'OJB Users List' Subject: Use stored procedure for insert questions Hello All I currently trying to use the cvs version of ojb 1.0 with an Oracle 9i Database and I have a few problem with stored procedure and sequences: 1: I want to Stored procedure for insert some rows. The stored procedure return the id ( primary key ) of the row.But i don't know wich sequence manager i need to use for managing this class . Here is the class descriptor: But i don't find any way to use it correctly in ojb . First i didn't define any sequence manager , the PB refuse to insert any instance . Second try : using the SequenceManagerNativeImpl ( not implemented for this plateform ). 2: One other question concern the repository.dtd wich has the following lines on the stored procedures: A procedure could have one to many arguments , I just wonder if we should have 3: Is it possible to use different sequence manager in the same repository ??? If yes how ? B.R Thierry Hanot - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
RE: Use stored procedure for insert questions
Thanks But how to do it , because there is no documentation on it ??? I probably did a wrong descriptor on the insert-procedure . B.R Thierry -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Friday, November 07, 2003 4:31 PM To: [EMAIL PROTECTED] Subject: RE: Use stored procedure for insert questions Thierry -- You shouldn't need to use a sequence manager since the SP is assigning the id. Based on the configuration of the 'insert-procedure' descriptor, the first argument to the sp will be defined as either 'OUT' or 'IN OUT'. In either case, the value that's returned by the sp will be placed in the 'id' attribute on the 'Twtinstances' class. HTH Ron Gallagher Atlanta, GA [EMAIL PROTECTED] -Original Message- From: Thierry Hanot [mailto:[EMAIL PROTECTED] Sent: Friday, November 07, 2003 10:20 AM To: 'OJB Users List' Subject: Use stored procedure for insert questions Hello All I currently trying to use the cvs version of ojb 1.0 with an Oracle 9i Database and I have a few problem with stored procedure and sequences: 1: I want to Stored procedure for insert some rows. The stored procedure return the id ( primary key ) of the row.But i don't know wich sequence manager i need to use for managing this class . Here is the class descriptor: But i don't find any way to use it correctly in ojb . First i didn't define any sequence manager , the PB refuse to insert any instance . Second try : using the SequenceManagerNativeImpl ( not implemented for this plateform ). 2: One other question concern the repository.dtd wich has the following lines on the stored procedures: A procedure could have one to many arguments , I just wonder if we should have 3: Is it possible to use different sequence manager in the same repository ??? If yes how ? B.R Thierry Hanot - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
RE: Use stored procedure for insert questions
Thierry -- You shouldn't need to use a sequence manager since the SP is assigning the id. Based on the configuration of the 'insert-procedure' descriptor, the first argument to the sp will be defined as either 'OUT' or 'IN OUT'. In either case, the value that's returned by the sp will be placed in the 'id' attribute on the 'Twtinstances' class. HTH Ron Gallagher Atlanta, GA [EMAIL PROTECTED] -Original Message- From: Thierry Hanot [mailto:[EMAIL PROTECTED] Sent: Friday, November 07, 2003 10:20 AM To: 'OJB Users List' Subject: Use stored procedure for insert questions Hello All I currently trying to use the cvs version of ojb 1.0 with an Oracle 9i Database and I have a few problem with stored procedure and sequences: 1: I want to Stored procedure for insert some rows. The stored procedure return the id ( primary key ) of the row.But i don't know wich sequence manager i need to use for managing this class . Here is the class descriptor: But i don't find any way to use it correctly in ojb . First i didn't define any sequence manager , the PB refuse to insert any instance . Second try : using the SequenceManagerNativeImpl ( not implemented for this plateform ). 2: One other question concern the repository.dtd wich has the following lines on the stored procedures: A procedure could have one to many arguments , I just wonder if we should have 3: Is it possible to use different sequence manager in the same repository ??? If yes how ? B.R Thierry Hanot - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]