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("{call 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:
No Format |
---|
<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.
...