ch23.htm

VC使用所有细节的逻列

<pre><font color="#008000">order by pub_id, type</font></pre>

<pre><font color="#008000">COMPUTE count(title_id) BY pub_id, type</font></pre>

<P>This stored procedure takes three parameters: lolimit, hilimit, and type. If you run it, the dialog box shown in Figure 23.16 appears: enter parameter values and click OK to run the procedure and see the results in the Output window.</P>

<A HREF="Xfigs16.gif" tppabs="http://www.mcp.com/814147200/0-7897/0-7897-1145-1/figs/ch23/Xfigs16.gif"><b>Fig. 23.16</b></A>

<P><I>Providing parameters to stored procedures is simple.</I></P>

<P>It might be nice if the <font color="#008000">type</font> parameter was a drop-down box, allowing you to see all the <font color="#008000">type</font> values in the table before submitting the query. That sort of capability is just what you can build 
into a C++ program that uses SQL stored procedures. To see how, in the next section you will write a new stored procedure and call it from your C++ program.</P>

<P><B>Writing a New Stored Procedure</B></P>

<P>To create a new stored procedure, right-click Stored Procedures in DataView and choose Ne<U>w</U> Stored Procedure. This code appears in the editor:</P>

<pre><font color="#008000">Create Procedure /*Procedure_Name*/</font></pre>

<pre><font color="#008000">As</font></pre>

<pre><font color="#008000">     return (0)</font></pre>

<P>Edit this code so that it looks like Listing 23.1. Save the stored procedure and its name appears in the DataView.</P>

<P><I>Listing 23.1&#151;author_ytd, the new stored procedure</I></P>

<pre><font color="#008000">CREATE PROCEDURE author_ytd @sales int</font></pre>

<pre><font color="#008000">AS</font></pre>

<pre><font color="#008000">SELECT authors.au_lname, titles.title, ytd_sales</font></pre>

<pre><font color="#008000">   FROM authors, titles, titleauthor</font></pre>

<pre><font color="#008000">   WHERE ytd_sales &gt; @sales </font></pre>

<pre><font color="#008000">      AND authors.au_id = titleauthor.au_id </font></pre>

<pre><font color="#008000">      AND titleauthor.title_id = titles.title_id</font></pre>

<pre><font color="#008000">ORDER BY ytd_sales DESC</font></pre>

<P>This SQL code gathers information from three tables, using the au_id and title_id columns to connect authors to titles. It takes one parameter, <font color="#008000">sales</font>, which is an integer value. Run the procedure to see the results 
immediately. Listing 23.2 shows the results using 4000 as the value for <font color="#008000">sales</font>:</P>

<P><I>Listing 23.2&#151;author_ytd results (@sales = 4000)</I></P>

<pre><font color="#008000">Running Stored Procedure dbo.author_ytd ( @sales = 4000 ).</font></pre>

<pre><font color="#008000">au_lname        au_fname  title                                        ytd_sales   </font></pre>

<pre><font color="#008000">--------------- --------- -------------------------------------------- --------- </font></pre>

<pre><font color="#008000">DeFrance        Michel    The Gourmet Microwave                        22246</font></pre>

<pre><font color="#008000">Ringer          Anne      The Gourmet Microwave                        22246</font></pre>

<pre><font color="#008000">Green           Marjorie  You Can Combat Computer Stress!              18722</font></pre>

<pre><font color="#008000">Blotchet-Halls  Reginald  Fifty Years in Buckingham Palace Kitchens    15096</font></pre>

<pre><font color="#008000">Carson          Cheryl    But Is It User Friendly?                      8780</font></pre>

<pre><font color="#008000">Green           Marjorie  The Busy Executive's Database Guide           4095</font></pre>

<pre><font color="#008000">Bennet          Abraham   The Busy Executive's Database Guide           4095</font></pre>

<pre><font color="#008000">Straight        Dean      Straight Talk About Computers                 4095</font></pre>

<pre><font color="#008000">Dull            Ann       Secrets of Silicon Valley                     4095</font></pre>

<pre><font color="#008000">Hunter          Sheryl    Secrets of Silicon Valley                     4095</font></pre>

<pre><font color="#008000">O'Leary         Michael   Sushi, Anyone?                                4095</font></pre>

<pre><font color="#008000">Gringlesby      Burt      Sushi, Anyone?                                4095</font></pre>

<pre><font color="#008000">Yokomoto        Akiko     Sushi, Anyone?                                4095</font></pre>

<pre><font color="#008000">White           Johnson   Prolonged Data Deprivation: Four Case Studies 4072</font></pre>

<pre><font color="#008000"> (14 row(s) affected)</font></pre>

<pre><font color="#008000">Finished running dbo.author_ytd.</font></pre>

<pre><font color="#008000">RETURN_VALUE = 0</font></pre>

<P><B>Connecting the Stored Procedure to C++ code</B></P>

<P>At the moment, you have an empty C++ application that uses a recordset and would display members of that recordset in a record view if you added fields to the dialog to do so. The recordset contains all the columns from the three tables (<font 
color="#008000">authors</font>, <font color="#008000">titleauthor</font>, and <font color="#008000">titles</font>) that you specified during the AppWizard process. That's arranged by a function called <font 
color="#008000">CPublishingSet::GetDefaultSQL()</font> that AppWizard wrote for you. It's shown in Listing 23.3.</P>

<P><I>Listing 23.3&#151;CPublishingSet::GetDefaultSQL() from AppWizard</I></P>

<pre><font color="#008000">CString CPublishingSet::GetDefaultSQL()</font></pre>

<pre><font color="#008000">{</font></pre>

<pre><font color="#008000">     return _T(&quot;[dbo].[authors],[dbo].[titleauthor],[dbo].[titles]&quot;);</font></pre>

<pre><font color="#008000">}</font></pre>

<P>You are going to change this default SQL so that it calls your stored procedure, which is now part of the <font color="#008000">pubs</font> database. First, choose <U>P</U>roject, Set Acti<U>v</U>e Project and select Publishing. Switch to ClassView in 
the Workspace pane, expand CPublishingSet, and double-click <font color="#008000">GetDefaultSQL()</font> to edit it. Replace the code with that in Listing 23.4.</P>

<P><I>Listing 23.4&#151;CPublishingSet::GetDefaultSQL() to call your stored procedure</I></P>

<pre><font color="#008000">CString CPublishingSet::GetDefaultSQL()</font></pre>

<pre><font color="#008000">{</font></pre>

<pre><font color="#008000">     return _T(&quot;{CALL author_ytd(4000)}&quot;);</font></pre>

<pre><font color="#008000">}</font></pre>

<blockquote><p><img src="note.gif" tppabs="http://www.mcp.com/814147200/0-7897/0-7897-1145-1/note.gif">

<P>Normally you would not hard-code the parameter value like this. Adding member variables to the class to hold parameters and passing them to the SQL is a topic you can explore in the online help once you are more familiar with the Enterprise 
Edition.</P>

<p><img src="bottom.gif" tppabs="http://www.mcp.com/814147200/0-7897/0-7897-1145-1/bottom.gif"></blockquote>

<P>The records returned from this query will go into your recordset. The query returns four columns (<font color="#008000">au_lname</font>, <font color="#008000">au_fname</font>, <font color="#008000">title</font>, and <font 
color="#008000">ytd_sales</font>) but the recordset is expecting far more than that. You can use ClassWizard to edit your recordset definition. Follow these steps:</P>

<ol> 

<li><P> Bring up ClassWizard by choosing <U>V</U>iew, Class<U>W</U>izard.</P>

<li><P> Click the Member Variables tab. You should see something like Figure 23.17, showing all the member variables of the record set connected to table columns.</P>

</ol>

<A HREF="Xfigs17.gif" tppabs="http://www.mcp.com/814147200/0-7897/0-7897-1145-1/figs/ch23/Xfigs17.gif"><b>Fig. 23.17</b></A>

<P><I>ClassWizard manages your record set definition.</I></P>

<ol start=3>

<li><P> Highlight [address] and click <U>D</U>elete Variable.</P>

<li><P> In the same way, delete all the variables except <font color="#008000">au_lname</font>, <font color="#008000">au_fname</font>, <font color="#008000">title</font>, and <font color="#008000">ytd_sales</font>.</P>

<li><P> Click OK to close ClassWizard.</P>

</ol>

<P>Your application would compile and run now, but until you edit the record view dialog box you won't be able to see the records and columns that are returned by another query. Editing the dialog box was covered in <A HREF="index22.htm" tppabs="http://www.mcp.com/814147200/0-7897/0-7897-1145-1/index22.htm" 
target="text">Chapter 22</A>, &quot;Database Access,&quot; and uses skills first demonstrated in <A HREF="index02.htm" tppabs="http://www.mcp.com/814147200/0-7897/0-7897-1145-1/index02.htm" target="text">Chapter 2</A>, &quot;Dialog Boxes and Controls,&quot; so the description here will be brief.</P>

<P>Click on the ResourceView tab, expand the resources, expand Dialogs, and double-click <font color="#008000">IDD_PUBLISHING_FORM</font>. This dialog box was created for you by AppWizard but has no controls on it yet. Delete the static text reminding you 
to add controls, and add four edit boxes and their labels so that the dialog resembles Figure 23.18. Use sensible resource IDs for the edit boxes, not the defaults provided by Developer Studio. Name them <font color="#008000">IDC_QUERY_LNAME</font>, <font 
color="#008000">IDC_QUERY_FNAME</font>, <font color="#008000">IDC_QUERY_TITLE</font>, and <font color="#008000">IDC_QUERY_YTDSALES</font>.</P>

<A HREF="Xfigs18.gif" tppabs="http://www.mcp.com/814147200/0-7897/0-7897-1145-1/figs/ch23/Xfigs18.gif"><b>Fig. 23.18</b></A>

<P><I>Edit your record view dialog box.</I></P>

<P>There is one task left: to connect these fields to member variables. Here's how to make that connection:</P>

<ol>

<li><P> Bring up ClassWizard while this dialog box has focus.</P>

<li><P> Click the Member Variables tab. </P>

<li><P> Select <font color="#008000">IDC_QUERY_FNAME</font> and click <U>A</U>dd Variable to bring up the Add Member Variable dialog box. </P>

<li><P> From the drop-down box labelled Member Variable name, choose <font color="#008000">m_pSet-&gt;m_au_fname</font> and click OK. </P>

<li><P> In the same way, connect <font color="#008000">IDC_QUERY_LNAME to m_pSet-&gt;m_au_lname</font>, <font color="#008000">IDC_QUERY_TITLE to m_pSet-&gt;m_title</font>, and <font color="#008000">IDC_QUERY_YTDSALES</font> to <font 
color="#008000">m_pSet-&gt;m_ytd_sales</font>. </P>

<li><P> Figure 23.19 shows the ClassWizard dialog box when all four controls have been connected. Click OK to close ClassWizard.</P>

</ol>

<A HREF="Xfigs19.gif" tppabs="http://www.mcp.com/814147200/0-7897/0-7897-1145-1/figs/ch23/Xfigs19.gif"><b>Fig. 23.19</b></A>

<P><I>Connect the record view controls to member variables of the record set.</I></P>

<P>Build your project and run it. You should see a record view like Figure 23.20 (you may have to go through the SQL login procedure again first) and if you scroll through the record view with the arrow buttons, you should see every author from the report 
in Listing 23.2.</P>

<A HREF="Xfigs20.gif" tppabs="http://www.mcp.com/814147200/0-7897/0-7897-1145-1/figs/ch23/Xfigs20.gif"><b>Fig. 23.20</b></A>

<P><I>Your application displays the results of the query in your stored </I><I>procedure.</I></P>

<blockquote><p><img src="tip.gif" tppabs="http://www.mcp.com/814147200/0-7897/0-7897-1145-1/tip.gif">

<P>Make sure you have saved the SQL stored procedure before you build. Because the stored procedures are in a subproject of Publishing, building Publishing will not trigger any saves in the subproject.</P>

<p><img src="bottom.gif" tppabs="http://www.mcp.com/814147200/0-7897/0-7897-1145-1/bottom.gif"></blockquote>

<P>This application doesn't do much at the moment: it just calls a stored procedure and presents the results neatly. With a little imagination, you can probably see how your SQL-based C++ programs can wrap stored procedures in user-friendly interfaces, 
and just how easy it is to develop and maintain those stored procedures using Developer Studio. You can even debug your SQL using the Developer Studio debugger.</P>