By the way, I'd like to ask some questions. - Is there any iBATIS example about calling an Oracle stored procedure which returns a reference cursor?
- When I call a stored procedure, can I use a normal bean class instead of a map (e.g. HashMap) for the <parameter> tag? I think I can answer this question by trying both cases, but I'd also like to ask it here in case I fail in both cases. Sincerely, Thai -----Original Message----- From: Albert L. Sapp [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 13, 2005 3:17 PM To: [email protected] Subject: Re: Multiple table insert problem. Larry Meadors wrote: >Why not just simplify your life and make it a stored procedure? > >Larry > > >On 12/13/05, Albert L. Sapp <[EMAIL PROTECTED]> wrote: > > >>Hi, all. >> >>Our project has been going along well with iBatis and we have already >>rolled out 5 of 7 modules. They like us to automate as much as possible >>and not have the users going to multiple screens to do something, so >>that is where we ran into the problem. >> >>This is for a Oracle database and I am learning PL/SQL and SQL as I >>build this application, so I don't know if there is something in PL/SQL >>or SQL that would do this and not in iBatis. I have been doing searches >>to see if anyone has an example of how to do this in PL/SQL or SQL, but >>apparently I am not searching correctly. >> >>When I create a group, I want to create entries in multiple tables. A >>entry in the group table, a entry for the group's leader in a group >>member table, a entry for the group leader in the user roles table >>indicating he has the group administrator role and, finally, another >>entry in the user roles table if a backup group administrator is entered >>at group creation time. >> >>This is the bean that I pass to the insert function. >> >> >> >>>public class GroupCreate extends BaseBean implements TransferObject >>>{ >>> static final long serialVersionUID = 0; >>> >>> private Group group; >>> private UserGroup userGroup; >>> private UserRole userRole1; >>> private UserRole userRole2; >>> >>> public GroupCreate() >>> { >>> group = null; >>> userGroup = null; >>> userRole1 = null; >>> userRole2 = null; >>> } >>> >>> public GroupCreate(Group group, UserGroup userGroup, UserRole >>>userRole1, UserRole userRole2) >>> { >>> this.group = group; >>> this.userGroup = userGroup; >>> this.userRole1 = userRole1; >>> this.userRole2 = userRole2; >>> } >>> >>> public Group getGroup() >>> { >>> return group; >>> } >>> >>> public void setGroup(Group group) >>> { >>> this.group = group; >>> } >>> >>> public UserGroup getUserGroup() >>> { >>> return userGroup; >>> } >>> >>> public void setUserGroup(UserGroup userGroup) >>> { >>> this.userGroup = userGroup; >>> } >>> >>> public UserRole getUserRole1() >>> { >>> return userRole1; >>> } >>> >>> public void setUserRole1(UserRole userRole1) >>> { >>> this.userRole1 = userRole1; >>> } >>> >>> public UserRole getUserRole2() >>> { >>> return userRole2; >>> } >>> >>> public void setUserRole2(UserRole userRole2) >>> { >>> this.userRole2 = userRole2; >>> } >>>} >>> >>> >>In the group.xml, I have the following defined. >> >> >> >>><statement id="GroupInsert" >>>parameterClass="scs.reaction.user.beans.GroupCreate"> >>> BEGIN >>> INSERT INTO REACTION_USERS.GROUPS (GROUP_ID, >>> GROUP_LEADER_ID, >>> GROUP_NAME, >>> DESCRIPTION, >>> CREATED_BY, >>> CREATED_ON, >>> MODIFIED_BY, >>> MODIFIED_ON, >>> CON_CHECK, >>> GROUP_ADMINISTRATOR) >>> VALUES (#group.groupID:BIGINT#, >>> #group.groupLeaderID:BIGINT#, >>> #group.groupName:VARCHAR#, >>> #group.description:VARCHAR#, >>> #group.createdBy:BIGINT#, >>> #group.createdOn:TIMESTAMP#, >>> #group.modifiedBy:BIGINT#, >>> #group.modifiedOn:TIMESTAMP#, >>> #group.conCheck:BIGINT#, >>> #group.groupAdministrator:BIGINT#); >>> INSERT INTO REACTION_USERS.USER_GROUPS (USER_GROUP_ID, >>> USER_ID, >>> GROUP_ID, >>> MEMBERSHIP_STATUS, >>> CON_CHECK) >>> VALUES >>>(#userGroup.userGroupID:BIGINT#, >>> #userGroup.userID:BIGINT#, >>> #userGroup.groupID:BIGINT#, >>> >>>#userGroup.membershipStatus:VARCHAR#, >>> #userGroup.conCheck:BIGINT#); >>> INSERT INTO REACTION_USERS.USER_ROLES (USER_ROLE_ID, >>> USER_ID, >>> ROLE_ID, >>> NETID, >>> ROLE) >>> VALUES (#userRole1.userRoleID:BIGINT#, >>> #userRole1.userID:BIGINT#, >>> #userRole1.roleID:BIGINT#, >>> #userRole1.netID:VARCHAR#, >>> #userRole1.role:VARCHAR#); >>> INSERT INTO REACTION_USERS.USER_ROLES (USER_ROLE_ID, >>> USER_ID, >>> ROLE_ID, >>> NETID, >>> ROLE) >>> VALUES (#userRole2.userRoleID:BIGINT#, >>> #userRole2.userID:BIGINT#, >>> #userRole2.roleID:BIGINT#, >>> #userRole2.netID:VARCHAR#, >>> #userRole2.role:VARCHAR#); >>> END; >>></statement> >>> >>> >>This works if a backup group administrator is defined when the group is >>defined. Otherwise, the last insert will fail because of null values. >>Is there a way I can set this up to not do the last insert if the >>userRole2 is null, but do it otherwise? I don't want to set up a >>seperate id="GroupInsertNoBackup" and another bean that does not have >>userRole2 in it. >> >>Thanks for any help or pointers you can give me. >> >>Al >> >> >> > > > I think that may be the only way to do it. It will take a while to learn to write a Oracle stored procedure to do what I want it to do, then to write a call to the stored procedure. I have never done either before. Always something new to learn. Thanks for the suggestion. Al
