cayenne-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From conflue...@apache.org
Subject [CONF] Apache Cayenne Documentation > Executing a Stored Procedure
Date Fri, 14 Jan 2011 20:35:00 GMT
<html>
<head>
    <base href="https://cwiki.apache.org/confluence">
            <link rel="stylesheet" href="/confluence/s/1810/9/1/_/styles/combined.css?spaceKey=CAYDOC&amp;forWysiwyg=true"
type="text/css">
    </head>
<body style="background: white;" bgcolor="white" class="email-body">
<div id="pageContent">
<div id="notificationFormat">
<div class="wiki-content">
<div class="email">
    <h2><a href="https://cwiki.apache.org/confluence/display/CAYDOC/Executing+a+Stored+Procedure">Executing
a Stored Procedure</a></h2>
    <h4>Page <b>edited</b> by             <a href="https://cwiki.apache.org/confluence/display/~andrus">Andrus
Adamchik</a>
    </h4>
        <br/>
                         <h4>Changes (7)</h4>
                                 
    
<div id="page-diffs">
            <table class="diff" cellpadding="0" cellspacing="0">
            <tr><td class="diff-snipped" >...<br></td></tr>
            <tr><td class="diff-unchanged" > <br>{code} <br></td></tr>
            <tr><td class="diff-changed-lines" >DataContext <span class="diff-changed-words">c<span
class="diff-added-chars"style="background-color: #dfd;">on</span>t<span class="diff-added-chars"style="background-color:
#dfd;">e</span>xt;</span> <br></td></tr>
            <tr><td class="diff-unchanged" > <br>// &quot;my_procedure&quot;
is a name of a stored procedure, <br></td></tr>
            <tr><td class="diff-snipped" >...<br></td></tr>
            <tr><td class="diff-unchanged" > <br>// run query <br></td></tr>
            <tr><td class="diff-changed-lines" >QueryResponse result = <span
class="diff-changed-words">c<span class="diff-added-chars"style="background-color: #dfd;">on</span>t<span
class="diff-added-chars"style="background-color: #dfd;">e</span>xt.performGenericQuery(query);</span>
<br></td></tr>
            <tr><td class="diff-unchanged" > <br>// check the results <br></td></tr>
            <tr><td class="diff-deleted-lines" style="color:#999;background-color:#fdd;text-decoration:line-through;">Iterator
it = rowSets.iterator(); <br>while(result.next()) { <br></td></tr>
            <tr><td class="diff-added-lines" style="background-color: #dfd;">for
(response.reset(); response.next();) { <br></td></tr>
            <tr><td class="diff-changed-lines" >if <span class="diff-deleted-words"style="color:#999;background-color:#fdd;text-decoration:line-through;">(result.isList())</span>
<span class="diff-added-words"style="background-color: #dfd;">(response.isList())</span>
{ <br></td></tr>
            <tr><td class="diff-changed-lines" >List list = <span class="diff-deleted-words"style="color:#999;background-color:#fdd;text-decoration:line-through;">result.currentList();</span>
<span class="diff-added-words"style="background-color: #dfd;">response.currentList();</span>
<br></td></tr>
            <tr><td class="diff-unchanged" >         // ... <br></td></tr>
            <tr><td class="diff-unchanged" >     } <br></td></tr>
            <tr><td class="diff-unchanged" >    else { <br></td></tr>
            <tr><td class="diff-changed-lines" >int[] updateCounts = <span
class="diff-deleted-words"style="color:#999;background-color:#fdd;text-decoration:line-through;">result.currentUpdateCount();</span>
<span class="diff-added-words"style="background-color: #dfd;">response.currentUpdateCount();</span>
<br></td></tr>
            <tr><td class="diff-unchanged" >         // ... <br></td></tr>
            <tr><td class="diff-unchanged" >     } <br>} <br></td></tr>
            <tr><td class="diff-snipped" >...<br></td></tr>
        </table>
</div>                            <h4>Full Content</h4>
                    <div class="notificationGreySide">
        <h3><a name="ExecutingaStoredProcedure-UsingQueryResponsetoProcessComplexResults"></a>Using
QueryResponse to Process Complex Results</h3>

<p>Previous chapter showed how to select a single set of data rows using a ProcedureQuery.
In a more general case stored procedures can return multiple sets of data, either as ResultSets
or via OUT parameters, execute update/delete/insert queries, etc. To collect the results of
execution of such stored procedure, you need to run a query using context's <tt>"performGenericQuery"</tt>
method and inspect returned QueryResponse.</p>


<div class="code panel" style="border-width: 1px;"><div class="codeContent panelContent">
<pre class="code-java">
DataContext context;

<span class="code-comment">// <span class="code-quote">"my_procedure"</span>
is a name of a stored procedure,
</span><span class="code-comment">// that must exist in the DataMap
</span>ProcedureQuery query = <span class="code-keyword">new</span> ProcedureQuery(<span
class="code-quote">"my_procedure"</span>);

<span class="code-comment">// Set <span class="code-quote">"IN"</span> parameter
values
</span>query.addParam(<span class="code-quote">"parameter1"</span>, <span
class="code-quote">"abc"</span>);
query.addParam(<span class="code-quote">"parameter2"</span>, <span class="code-keyword">new</span>
<span class="code-object">Integer</span>(3000));

<span class="code-comment">// run query
</span>QueryResponse result = context.performGenericQuery(query);

<span class="code-comment">// check the results
</span><span class="code-keyword">for</span> (response.reset(); response.next();)
{
     <span class="code-keyword">if</span> (response.isList()) {
         List list = response.currentList();
         <span class="code-comment">// ...
</span>     }
    <span class="code-keyword">else</span> {
         <span class="code-object">int</span>[] updateCounts = response.currentUpdateCount();
         <span class="code-comment">// ...
</span>     }
}
</pre>
</div></div>

<h3><a name="ExecutingaStoredProcedure-UsingQueryResponsetoReadOUTParameters"></a>Using
QueryResponse to Read OUT Parameters</h3>

<p>Stored Procedure can return data back to the application as ResultSets or via OUT
parameters. To simplify the processing of the query output, QueryResponse treats OUT parameters
as if it was a separate ResultSet. If a stored procedure declares any OUT or INOUT parameters,
QueryResponse will contain their returned values in the very first result list:</p>

<div class="code panel" style="border-width: 1px;"><div class="codeContent panelContent">
<pre class="code-java">
DataContext ctxt;

<span class="code-comment">// <span class="code-quote">"my_procedure"</span>
is a name of a stored procedure,
</span><span class="code-comment">// that must exist in the DataMap
</span>ProcedureQuery query = <span class="code-keyword">new</span> ProcedureQuery(<span
class="code-quote">"my_procedure"</span>);

<span class="code-comment">// Set <span class="code-quote">"IN"</span> parameter
values
</span>query.addParam(<span class="code-quote">"paramter1"</span>, <span
class="code-quote">"abc"</span>);
query.addParam(<span class="code-quote">"parameter2"</span>, <span class="code-keyword">new</span>
<span class="code-object">Integer</span>(3000));

<span class="code-comment">// run query
</span>QueryResponse result = ctxt.performGenericQuery(query);

<span class="code-comment">// read OUT parameters
</span>List outList = result.firstList();

<span class="code-keyword">if</span>(outList.size() &gt;  0) {
    Map outParameterValues = (Map) outList.get(0);
}
</pre>
</div></div>
    </div>
        <div id="commentsSection" class="wiki-content pageSection">
        <div style="float: right;">
            <a href="https://cwiki.apache.org/confluence/users/viewnotifications.action"
class="grey">Change Notification Preferences</a>
        </div>
        <a href="https://cwiki.apache.org/confluence/display/CAYDOC/Executing+a+Stored+Procedure">View
Online</a>
        |
        <a href="https://cwiki.apache.org/confluence/pages/diffpagesbyversion.action?pageId=10569&revisedVersion=3&originalVersion=2">View
Changes</a>
                |
        <a href="https://cwiki.apache.org/confluence/display/CAYDOC/Executing+a+Stored+Procedure?showComments=true&amp;showCommentArea=true#addcomment">Add
Comment</a>
            </div>
</div>
</div>
</div>
</div>
</body>
</html>

Mime
View raw message