procedure, if they don't involve the parameters to the function or the values it
returns, will take effect without compiling and linking the C++ program.</P>
<P>There is a downside, however. It can be very difficult to track down problems
when you are unsure whether they are in the C++ or the SQL part of your program.
When one developer is doing both parts, learning two different tools and switching
between them makes the job harder than it would be in a single tool. Also, the tools
available for working with SQL lack many features that Visual C++ has offered C++
programmers.</P>
<P>Now, with the Enterprise Edition of Visual C++, you can have the best of both
worlds. You can separate your C++ and SQL for reuse and maintenance but use the editor,
syntax coloring, and even the debugger from Visual C++ to work on your SQL stored
procedures.</P>
<P>
<H2><A NAME="Heading4"></A>Exploring the Publishing Application</H2>
<P>One sample database that comes with SQL Server is called <I>pubs</I>. It tracks
the sales of books and the royalties paid to their authors. In this chapter you will
write a new stored procedure and display the records returned by it in a simple record
view dialog box. SQL Server should be up and running before you start to build the
application.</P>
<P>
<H3><A NAME="Heading5"></A>Setting Up the Data Source</H3>
<P>Before you create the project, you need to create a data source to which it will
connect. On your real projects, this data source might already exist.</P>
<P>Choose Start, Settings, Control Panel and then double-click ODBC. Select the User
DSN tab, as in Figure 23.1, and click the Add button to add a new data source name
(DSN).</P>
<P><A HREF="javascript:popUp('23uvc01.gif')"><B>FIG. 23.1</B></A><B> </B><I>Add a
user data source name.</I></P>
<P>On the next dialog box, choose SQL Server, as in Figure 23.2, and click Finish.
You're several steps away from finishing, no matter what the button says.</P>
<P><A HREF="javascript:popUp('23uvc02.gif')"><B>FIG. 23.2</B></A><B> </B><I>Connect
to a SQL Server.</I></P>
<P>On the next dialog box, fill in a name and description for the data source. Then
drop down the Server box; choose your server or type its name. Figure 23.3 shows
the completed dialog box for a test system with only the sample databases installed.
Click Next.</P>
<P><A HREF="javascript:popUp('23uvc03.gif')"><B>FIG. 23.3</B></A><B> </B><I>Specify
the server.</I></P>
<P>You can choose to connect to the server by using NT authentication or SQL Server
authentication. If you're not sure, talk to your system administrator. Because this
sample was developed on a test machine, SQL Server authentication--with the default
account of sa and no password--is acceptable. Figure 23.4 shows the completed dialog
box. Click Next.</P>
<P><A HREF="javascript:popUp('23uvc04.gif')"><B>FIG. 23.4</B></A><B> </B><I>Security
can be lax on test machines but not in the real world.</I></P>
<P>At this point, you can choose whether to connect this data source name to a single
database on the server or to the server as a whole. If you want to associate this
DSN with only one database, select the top check box and choose your database. If
not, leave the top check box deselected. In either case, leave the rest of the dialog
at the defaults, shown in Figure 23.5. Click Next.</P>
<P><A HREF="javascript:popUp('23uvc05.gif')"><B>FIG. 23.5</B></A><B> </B><I>This
DSN is connected to the entire server, not just one database.</I></P>
<P>Accept the default on the next dialog box, shown in Figure 23.6, and click Next.</P>
<P>Leave both check boxes deselected on the last dialog, shown in Figure 23.7. Click
Finish, and the process really is over.</P>
<P>Figure 23.8 shows the summary of settings from this connection process. It's a
very good idea to test your connection before moving on.</P>
<P><A HREF="javascript:popUp('23uvc06.gif')"><B>FIG. 23.6</B></A><B> </B><I>Character
translations and regional settings need no special treatment in this example.</I></P>
<P><A HREF="javascript:popUp('23uvc07.gif')"><B>FIG. 23.7</B></A><B> </B><I>There's
no need to log slow queries or driver statistics in this example.</I></P>
<P><A HREF="javascript:popUp('23uvc08.gif')"><B>FIG. 23.8</B></A><B> </B><I>Confirm
your choices for the ODBC SQL connection.</I></P>
<P>Click Test Data Source, and you should see something like Figure 23.9. If you
don't, click Cancel to return to the final step of the process and click Back until
you are back to the step you need to adjust. Then come forward again with Next.</P>
<P><A HREF="javascript:popUp('23uvc09.gif')"><B>FIG. 23.9</B></A><B> </B><I>Make
sure your DSN connects properly.</I></P>
<P>When you have tested the connection successfully, click OK on the summary dialog
and then OK on the ODBC Data Source Administrator. Close Control Panel.</P>
<P>
<H3><A NAME="Heading6"></A>Building the Application Shell</H3>
<P>Open Developer Studio and choose File, New and then click the Projects tab. Select
MFC AppWizard (exe) and name the project Publishing, as shown in Figure 23.10. Click
OK to start the AppWizard process.</P>
<P><A HREF="javascript:popUp('23uvc10.gif')"><B>FIG. 23.10</B></A><B> </B><I>Start
AppWizard in the usual way.</I></P>
<P>In Step 1 of AppWizard, choose an SDI application. Click Next to move to Step
2 of AppWizard. As shown in Figure 23.11, select the Database View Without File Support
option. Click Data Source to connect a data source to your application.</P>
<P>Select the ODBC option and from the drop-down box next to it, select the DSN you
just created, as shown in Figure 23.12. Leave the Recordset Type as Snapshot and
click OK to specify the exact data source.</P>
<P><A HREF="javascript:popUp('23uvc11.gif')"><B>FIG. 23.11</B></A><B> </B><I>This
application needs database support but will not have a document.</I></P>
<P><A HREF="javascript:popUp('23uvc12.gif')"><B>FIG. 23.12</B></A><B> </B><I>Your
data source is an ODBC data source name.</I></P>
<P>The SQL Server login dialog appears. Click the Options button to show the enlarged
dialog of Figure 23.13. Choose pubs from the Database drop-down box and enter your
login ID and password at the top of the dialog. Click OK.</P>
<P><A HREF="javascript:popUp('23uvc13.gif')"><B>FIG. 23.13</B></A><B> </B><I>Connect
to the sample pubs database.</I></P>
<P>The Select Database Tables dialog, shown in Figure 23.14, appears. Click on dbo.authors,
dbo.titleauthor, and dbo.titles. Click OK.</P>
<P><A HREF="javascript:popUp('23uvc14.gif')"><B>FIG. 23.14</B></A><B> </B><I>Choose
the authors, titles, and authortitle tables.</I></P>
<P>You are back to Step 2 of AppWizard. Click Next to move to Step 3. Choose No Support
for Compound Documents or ActiveX Controls and click Next to move to Step 4. Click
Next to accept the Step 4 defaults and then Next again to accept the Step 5 defaults.
On Step 6, click Finish. The New Project Information summary, shown in Figure 23.15,
appears. Click OK to create the project.</P>
<P><A HREF="javascript:popUp('23uvc15.gif')"><B>FIG. 23.15</B></A><B> </B><I>Confirm
that your choices are correct before clicking OK.</I></P>
<P>You have now completed a shell of an application that displays database values
in a record view, much like the one discussed in Chapter 22. Nothing you have done
so far has been specific to the Enterprise Edition. That is about to change.</P>
<P>
<H3><A NAME="Heading7"></A>Making a Data Connection</H3>
<P>The database tables you specified are connected to your record set, but they aren't
available for use with the SQL features of the Enterprise Edition. You need to make
a data connection to connect the database to your application. Follow these steps
to make the connection:</P>

<DL>
	<DT></DT>
	<DD><B>1. </B>Choose Project, Add to Project, New.
	<P>
	<DT></DT>
	<DD><B>2. </B>Click the Projects tab.
	<P>
	<DT></DT>
	<DD><B>3. </B>As shown in Figure 23.16, select a Database Project, name it <B>PubDB</B>,
	and select the Add to Current Workspace radio button. Click OK.
	<P>
</DL>

<P><A HREF="javascript:popUp('23uvc16.gif')"><B>FIG. 23.16</B></A><B> </B><I>Create
a subproject within this project.</I></P>

<DL>
	<DT><I></I></DT>
	<P>
	<DD><B>4. </B>The Select Data Source dialog appears. Click the Machine Data Source
	tab, choose the DSN you created (shown in Figure 23.17), and click OK.
	<P>
</DL>

<P><A HREF="javascript:popUp('23uvc17.gif')"><B>FIG. 23.17</B></A><B> </B><I>Connect
to the local server.</I></P>

<DL>
	<DT><I></I></DT>
	<P>
	<DD><B>5. </B>The SQL Server Login dialog appears. As before, specify your login
	ID and password and make sure the pubs database is selected. Click OK to complete
	the data connection.
	<P>
</DL>

<P>In the Workspace pane on the left of the screen, a new tab has appeared. Figure
23.18 shows the new DataView. Expand the Tables section and expand authors to show
the columns within the table. Double-click the authors table, and you can see your
data on the right in Figure 23.18.</P>
<P>Also featured in Figure 23.18 is the Query toolbar, with the following buttons:</P>

<UL>
	<LI><I>Show Diagram Pane</I> toggles the Query Designer diagram pane (discussed in
	the next section).
	<P>
	<LI><I>Show Grid Pane</I> toggles the Query Designer grid pane (discussed in the
	next section).
	<P>
	<LI><I>Show SQL Pane</I> toggles the Query Designer SQL pane (discussed in the next
	section).
</UL>

<P><A HREF="javascript:popUp('23uvc18.gif')"><B>FIG. 23.18</B></A><B> </B><I>The
DataView shows you the database structure and can display your data in the working
area.</I></P>

<UL>
	<LI><I>Show Results Pane</I> toggles the Query Designer results pane (discussed in
	the next section).
	<P>
	<LI><I>Change Type</I> creates a SELECT, INSERT, UPDATE, or DELETE query in the four
	panes of Query Designer.
	<P>
	<LI><I>Run</I> executes your SQL.
	<P>
	<LI><I>Verify SQL Syntax</I> checks the syntax of the SQL you have written.
	<P>
	<LI><I>Sort Ascending</I> displays records from the low value of a selected column
	to high.
	<P>
	<LI><I>Sort Descending</I> displays records from the high value of a selected column
	to low.
	<P>
	<LI><I>Remove Filter</I> shows all the records instead of only those that meet the
	filter specifications.
	<P>
	<LI><I>Group By</I> adds a GROUP BY condition to the query being built.
	<P>
	<LI><I>Properties</I> displays information about a column or table.
</UL>

<H3><A NAME="Heading8"></A>Working with Query Designer</H3>
<P>When you double-click a table name, such as authors, in the DataView to display
all the columns and all the records, you are actually executing a simple SQL query,
as follows:</P>
<P>
<PRE>SELECT authors.* FROM authors
</PRE>
<P>The results of this query appear in the results pane, which is the only one of
the four Query Designer panes to be displayed, by default. This query was built for
you by Query Designer and means <I>show all the columns and records of the</I> authors<I>
table</I>. Figure 23.19 shows the four panes of Query Designer as they appear when
you first make the data connection. To see all four panes, use the toolbar buttons
to toggle them on. You can adjust the vertical size of each pane but not the horizontal.</P>
<P>To change your query, deselect * (All Columns) in the diagram pane (at the top
of Figure 23.19) and then select au_lname, au_fname, and phone. The values in the
results pane become gray to remind you that these aren't the results of the query
you are now building. As you make these selections in the diagram pane, the other
panes update automatically, as shown in Figure 23.20.</P>
<P><A HREF="javascript:popUp('23uvc19.gif')"><B>FIG. 23.19</B></A><B> </B><I>The
DataView shows you the database structure and can display your data in the working
area.</I></P>
<P>Highlight phone in the diagram pane and click the Sort Ascending button on the
Query toolbar. This will sort the results by phone number. Click the Run button on
the Query toolbar to execute the SQL that has been built for you. Figure 23.21 shows
what you should see, including the new values in the results pane.</P>
<P>
<H3><A NAME="Heading9"></A>Stored Procedures</H3>
<P>The capability to create simple SQL queries quickly, even if your SQL skills aren't
strong, is an amazing aspect of the Enterprise Edition. However, using stored procedures
is where the real payoff of this software becomes apparent.</P>
<P><A HREF="javascript:popUp('23uvc20.gif')"><B>FIG. 23.20</B></A><B> </B><I>You
can build simple queries even if you don't know any SQL.</I></P>
<P><A HREF="javascript:popUp('23uvc21.gif')"><B>FIG. 23.21</B></A><B> </B><I>Running
your SQL queries is a matter of a single click.</I></P>
<P>Collapse the tables section in the DataView and expand the Stored Procedures section.
This shows all the stored procedures that are kept in the database and are available
for you to use. Double-click reptq2 to display the procedure. One thing you probably
notice immediately is the syntax coloring in the editor window. The colors used are</P>

<UL>