Eclipse Community Forums: Eclipse Scout » How to call stored procedure with output parameters

Help

Home

Home » Eclipse Projects » Eclipse Scout » How to call stored procedure with output parameters

Show: Today's Messages :: Show Polls :: Message Navigator

How to call stored procedure with output parameters [message #1790651]

Wed, 13 June 2018 22:57

Miloslav Frajdl

Messages: 48
Registered: June 2018

Member

Hi.
I'm beginner in Scout framework and I have one trouble.
I have stored procedure in Oracle Database with this header:

procedure create_user(
     av_name varchar2
    ,av_surname varchar2
    ,av_mail varchar2 
    ,an_bank_code varchar2
    ,av_password varchar2
    ,an_return_code out number
    ,av_return_code_desc out varchar2)

I need call this procedure in UserDetailService.java:

long ret = 0;
String retDesc = "";
SQL.callStoredProcedure("call app.user_manager.create_user(:name, :surname, :loginName, :bankCode, :password, :ret, :retDesc)",
				new NVPair("name", formData.getName().getValue()),
				new NVPair("surname", formData.getSurname().getValue()),
				new NVPair("loginName", formData.getLoginName().getValue()),
				new NVPair("bankCode", formData.getInstitution().getValue()),
				new NVPair("password", formData.getPassword().getValue()),
				new NVPair("ret", ret),
				new NVPair("retDesc", retDesc));

Procedure work fine, but not return values to "ret" and "retDesc" variables.

Can anyone advise me? Thank you very much.

Report message to a moderator

Re: How to call stored procedure with output parameters [message #1790654 is a reply to message #1790651]

Thu, 14 June 2018 05:13

Patrick Baenziger

Messages: 96
Registered: September 2011

Member

Hi there

TL;DR: Use holders for output values (subclasses of org.eclipse.scout.rt.platform.holders.IHolder<T>)

Longer answer:
You pass in a primitive long and a reference to a String for the two output parameters.
Because Java is call-by-value, the NVPair just receives a reference to an (immutable) String object, and a copy of the long value. With both of them, it can't return the result values to you.
The solution to this is a holder. A Holder is an object that is a mutable container for the result which you can pass to the NVPair to get returned values.

Holders exist in many forms, but these all implement the IHolder interface (org.eclipse.scout.rt.platform.holders):

For most Java Primitives (DateHolder, StringHolder, IntegerHolder, ..) and their respective Array Versions (StringArrayHolder, ...)
Properties in FormData (AbstractPropertyData)
ValueFields in FormData (AbstractValueFieldData)

Pass any of them to your NVPair and that should do the trick.

[Updated on: Thu, 14 June 2018 05:17]

Report message to a moderator