WorkingWithStoredProcedures
The DAS can work with stored procedures in the same way it works with SQL statements. The following example should look familiar except that a stored prcedure call statement replaces the typical SELECT statement:
Panel |
---|
DAS das = |
Wiki Markup |
h1. WorkingWithStoredProcedures
The DAS can work with stored procedures in the same way it works with SQL statements. The following example should look familiar except that a stored prcedure call statement replaces the typical SELECT statement:
{panel}
DAS das = DAS.FACTORY.createDAS(getConnection());
read = das.createCommand("{call GETALLCOMPANIES()}");
root = read.executeQuery(); {panel} |
The
...
predefined
...
stored
...
procedure
...
"GETALLCOMPANIES"
...
returnes
...
a
...
result
...
just
...
like
...
"select
...
*
...
from
...
company"
...
would.
...
There
...
are,
...
however,
...
procedures
...
that
...
do
...
not
...
return
...
results
...
and
...
these
...
calls
...
can
...
also
...
be
...
executed
...
with
...
the
...
same
...
programming
...
model.
...
In
...
fact
...
any
...
arbitrary
...
stored
...
procedure
...
can
...
be
...
invoked
...
in
...
this
...
way.
...
The
...
follwing
...
example
...
calls
...
a
...
proc
...
that
...
deletes
...
an
...
identified
...
company:
Panel |
---|
DAS das = {panel} DAS das =DAS.FACTORY.createDAS(getConnection());
delete = das.createCommand(" DELETECUSTOMER (?)
1234);
|
Stored
...
procedures
...
may
...
also
...
provide
...
IN/OUT
...
and
...
OUT
...
parameters.
...
The
...
following
...
illustrates
...
the
...
use
...
of
...
a
...
stored
...
procedure
...
that
...
returns
...
a
...
result
...
set
...
as
...
well
...
as
...
an
...
OUT
...
parameter:
Panel |
---|
DAS das = {panel} DAS das =DAS.FACTORY.createDAS(getConfig("storedProcTest.xml"), getConnection());
read = das.getCommand("getNamedCustomers");
"Williams");
root = read.executeQuery(); Integer customersRead = (Integer) read.getParameter(2); {panel} |
This
...
example
...
makes
...
use
...
of
...
a
...
configuration
...
file
...
to
...
define
...
the
...
command
...
including
...
the
...
OUT
...
parameter:
...
<Config
...
xsi:noNamespaceSchemaLocation="http:///org.apache.tuscany.das.rdb/config.xsd"
...
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
...
<Command name="getNamedCustomers"
...
SQL="
{call
...
GETNAMEDCUSTOMERS(?,?)}
"
...
kind="procedure">
...
<Parameter direction="IN"
...
columnType="commonj.sdo.String"/>
...
<Parameter direction="OUT"
...
columnType="commonj.sdo.IntObject"/>
...
</Command>
...
</Config>
...
This
...
stored
...
procedure
...
is
...
defined
...
such
...
that
...
the
...
first
...
argument
...
provides
...
a
...
name
...
to
...
match
...
when
...
looking
...
for
...
customers
...
and
...
the
...
second
...
argument
...
is
...
an
...
OUT
...
parameter
...
and
...
providing
...
the
...
number
...
of
...
customers
...
that
...
match
...
the
...
provided
...
name.
...
The
...
proc
...
also
...
returns
...
the
...
list
...
of
...
matching
...
Customers.
...