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
, aDATASYNC IN TWTINSTANCES.DATASYNC%TYPE
, aCONFIGID IN TWTINSTANCES.CONFIGID%TYPE)
IS
BEGIN
-- Get next value from the sequence
-- This will put the value in the 'out' parameter named aID
SELECT <YourSequenceNameHere>.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:
<class-descriptor class="com.infovista.vm.ojb.Twtinstances"
table="VMUSER.TWTINSTANCES">
<field-descriptor name="cdate" column="CDATE"
jdbc-type="TIMESTAMP"/>
<field-descriptor name="configid" column="CONFIGID"
jdbc-type="BIGINT"/>
<field-descriptor name="datasync" column="DATASYNC"
jdbc-type="BIGINT"/>
<field-descriptor name="description" column="DESCRIPTION"
jdbc-type="VARCHAR"/>
<field-descriptor name="id" column="ID" jdbc-type="BIGINT"
primarykey="true" />
<insert-procedure name="ADDINSTANCE">
<runtime-argument field-ref="id" return="true"/>
<runtime-argument field-ref="description"
return="false"/>
<runtime-argument field-ref="datasync"
return="false"/>
<runtime-argument field-ref="configid"
return="false"/>
</insert-procedure>
</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:
<!ELEMENT insert-procedure (documentation?, (runtime-argument |
constant-argument)?, attribute*)> <!ATTLIST insert-procedure
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
<!ELEMENT insert-procedure (documentation?, (runtime-argument |
constant-argument)+, attribute*)> <!ATTLIST insert-procedure
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
---------------------------------------------------------------------
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]
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
