The Enterprise Edition of Visual C++ was developed for those of you who are integrating SQL databases and C++ programs, especially if you use stored procedures. It is sold as a separate edition of the product: you can buy a copy of the Enterprise Edition instead of the Professional Edition. If you already own a Professional or Subscription Edition, you can upgrade to the Enterprise Edition for a reduced price.
SQL, Structured Query Language, allows you to retrieve, update, or add records to or from a relational database.
Calling a SQL stored procedure from C++ is not a difficult task
Look at and change the structure and contents of your database, build queries on-the-fly, edit and debug stored procedures, all in one powerful new view.
Query Designer, Database Designer, and Database Diagrams are all designed to simplify the job of dealing with database and with SQL.
If your database work involves transactions, you need to learn about MTS and what it can do for you.
This revision control system is important to any developer, even if your applications don't involve databases at all. It comes with the Enterprise Edition.
Structured Query Language, SQL, is a way to access databases, interactively or in a program, that is designed to read as though it was English. Most SQL statements are queries, requests for information from one or more databases, but it is also possible to use SQL to add, delete, and change information. As mentioned in Chapter 22, ìDatabase Access,î SQL is an enormous topic. This section reviews the most important SQL commands, so that even if you havenít used it before you can understand these examples and see how powerful these tools can be.
The Enterprise Edition comes with a Developer Edition of Microsoft SQL Server 6.5. The SQL Server Books Online included with the product contains an excellent SQL Reference.
SQL is used to access a relational database, which contains several tables. A table is made up of rows, and a row is made up of columns. Table 23.1 lists some names used in database research or in some other kinds of databases for tables, rows, and columns.
Table 23.1óDatabase Terminology
| SQL | Also Known As |
| Table | Entity |
| Row | Record, Tuple |
| Column | Field, Attribute |
Here's a sample SQL statement:
SELECT au_fname, au_lname FROM authors
It produces a list of author first and last names from a table called authors. (This table is included in the sample pubs database that comes with SQL Server that you'll be using in this chapter.) Here's a far more complicated SQL statement:
SELECT item, SUM(amount) total, AVG(amount) average FROM ledger
WHERE action = 'PAID'
GROUP BY item
having AVG(amount) > (SELECT avg(amount) FROM ledger
WHERE action = 'PAID')
A SQL statement is put together from keywords, table names, and column names. The keywords include:
Like C++, SQL supports two kinds of comments:
/* This comment has begin and end symbols */
-- This is a from-here-to-end-of-line comment
As you saw in Chapter 22, "Database Access," an ODBC program using CDatabase and CRecordset can already access a SQL Server database, or any database that supports SQL queries. What's more, with the ExecuteSQL function of CDatabase, you can execute any line of SQL at all from within your program. Most of the time, the line of SQL that you execute is a stored procedure, a collection of SQL statements stored with the database and designed to be executed on-the-fly.
There are lots of reasons not to hard-code your SQL into your C++ program. The three most compelling are:
Many programmers accessing a SQL database from a C++ application are building on the work of other developers who have been building the database and its stored procedures for years. Copying those procedures into your code would be foolish indeed. Calling them from within your code lets you build slick user interfaces, simplify Internet access, or take advantage of the speed of C++, while retaining all the power of the stored procedures that have already been written.
Highly skilled professionals are always in demand, and sometimes the demand exceeds the supply. Many companies find it hard to recruit solid C++ programmers and just as hard to recruit experienced database administrators who can learn the structure of a database and write in SQL. Imagine how hard it would be to find a single individual who can do both. Almost as difficult would be to expect two developers to work on the parts of the program that called SQL from C++. A much better approach is to have the C++ programmer call well-documented SQL stored procedures, and the SQL developer build those stored procedures and keep the database running smoothly.
Separating the C++ and SQL parts of your application has another benefit: changes to one may not affect the other. For example, a minor C++ that doesn't involve the SQL will compile and link more quickly since the C++ part of the application is a little bit smaller without the SQL statements in it. And changes to the SQL stored procedure, if they don't involve the parameters to the function or the values it returns, will not take effect without compiling and linking the C++ program.
There is a downside, however. It can be very difficult to track down problems when you are not sure 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. And the tools available for working with SQL have not had many of the features that Visual C++ has offered C++ programmers.
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.
One of the sample databases that comes with SQL Server is called pubs. 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.
Building the Application Shell
Bring up Developer Studio and choose File, New, then click the Projects tab. Select MFC AppWizard (exe), and name the project Publishing, as shown in Figure 23.1. Click OK to start the AppWizard process.
Fig. 23.1 Start AppWizard in the usual way.
In step 1 of AppWizard, choose an SDI application. Click Next to move to step 2 of AppWizard. As shown in Figure 23.2, select the Database view without file support option. Click Data Source to connect a data source to your application.
Fig. 23.2 This application needs database support but will not have a document.
Select the ODBC option, and select Local Server from the drop down box next to it, as shown in Figure 23.3. Leave the Recordset type as Snapshot, and click OK to specify the exact data source.
Fig. 23.3 Your data source is a local ODBC database.
The SQL Server login dialog appears. Click the Options button to show the enlarged dialog of Figure 23.4. Choose pubs from the Database: drop-down box, and enter your login ID and password at the top of the dialog. Click OK.
Fig. 23.4 Connect to the sample pubs database.
The Select Database Tables dialog, shown in Figure 23.5, appears. Click on dbo.authors, dbo.titleauthor and dbo.titles. Click OK.
Fig. 23.5 Choose the authors, titles, and authortitle tables.
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, then Next again to accept the Step 5 defaults. On Step 6, click Finish. The New Project Information summary, shown in Figure 23.6, appears. Click OK to create the project.
Fig. 23.6 Confirm your choices are all correct before clicking OK.
You have now completed a shell of an application that displays daabase values in a record view, much like the one discussed in Chapter 22, "Database Access." Nothing you have done so far has been specific to the Enterprise Edition. That is about to change.
Making a Data Connection
The database tables you specified are connected to your record set, but they are not 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:
Fig. 23.7 Create a subproject within this project.
The Select Data Source dialog appears. Choose LocalServer, as shown in Figure 23.8, and click OK.
Fig. 23.8 Connect to the local server.
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.
In the Workspace pane on the left of the screen, a new tab has appeared. Figure 23.9 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 actually see your data, on the right in Figure 23.9.
Fig. 23.9 The DataView shows you the database structure, and can display your data in the working area.
Also featured in Figure 23.9 is the Query toolbar, with the following buttons:
Working with Query Designer
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:
SELECT authors.* FROM authors
The results of this query appear in the results pane, which by default is the only one of the four Query Designer panes to be displayed. This query was built for you by Query Designer, and means "show all the columns and records of the authors table." Figure 23.10 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.
Fig. 23.10 The DataView shows you the database structure, and can display your data in the working area.
To change your query, deselect * (All Columns) in the diagram pane (at the top of Figure 23.10) and then select au_lname, au_fname, and phone. The values in the results pane go gray to remind you that these are not 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.11.
Fig. 23.11 You can build simple queries even if you know no SQL.
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.12 shows what you should see, including the new values in the Results pane.
Fig. 23.12 Running your SQL queries is a matter of a single click.
After you have been looking at your Results pane for a while, a message like the one in Figure 23.13 will appear. If you don't need the results any more, click No. If you are still looking at them, click Yes. If you click neither (for example if another application has focus and you ignore the alert sound that acompanies the message) the results pane will be cleared after another minute. You can get the results back by running the query again.
Fig. 23.13 Results take up space and are cleared as soon as possible.
Stored Procedures
The capability to create simple SQL queries quickly, even if your SQL skills are not strong, is an amazing aspect of the Enterprise Edition. But using stored procedures is where the real payoff of this software displays itself.
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, and you should see something like Figure 23.14.
Fig. 23.14 Using the Developer Studio editor to work with SQL means no learning curve for you.
One thing you probably noticed immediately was the syntax coloring in the editor window. The colors used are:
To run a stored procedure, choose Tools, Run, or right-click the stored procedure name in DataView and choose Run, or right-click in the editor and choose Run. The results appear in the Results pane of the Output windowódon't confuse this with the Results pane of Query Designer. Figure 23.15 shows the Output window stretched very large to show some of the results of reptq2.
Fig. 23.15 You can see the results of any stored procedure from within Developer Studio.
Some stored procedures take parameters. For example, double-click reptq3; its code looks like this:
CREATE PROCEDURE reptq3 @lolimit money, @hilimit money,
@type char(12)
AS
select pub_id, type, title_id, price
from titles
where price >@lolimit AND price <@hilimit AND type = @type
OR type LIKE '%cook%'
order by pub_id, type
COMPUTE count(title_id) BY pub_id, type
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.
Fig. 23.16 Providing parameters to stored procedures is simple.
It might be nice if the type parameter was a drop-down box, allowing you to see all the type 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.
Writing a New Stored Procedure
To create a new stored procedure, right-click Stored Procedures in DataView and choose New Stored Procedure. This code appears in the editor:
Create Procedure /*Procedure_Name*/
As
return (0)
Edit this code so that it looks like Listing 23.1. Save the stored procedure and its name appears in the DataView.
Listing 23.1óauthor_ytd, the new stored procedure
CREATE PROCEDURE author_ytd @sales int
AS
SELECT authors.au_lname, titles.title, ytd_sales
FROM authors, titles, titleauthor
WHERE ytd_sales > @sales
AND authors.au_id = titleauthor.au_id
AND titleauthor.title_id = titles.title_id
ORDER BY ytd_sales DESC
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, sales, 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 sales:
Listing 23.2óauthor_ytd results (@sales = 4000)
Running Stored Procedure dbo.author_ytd ( @sales = 4000 ).
au_lname au_fname title ytd_sales
--------------- --------- -------------------------------------------- ---------
DeFrance Michel The Gourmet Microwave 22246
Ringer Anne The Gourmet Microwave 22246
Green Marjorie You Can Combat Computer Stress! 18722
Blotchet-Halls Reginald Fifty Years in Buckingham Palace Kitchens 15096
Carson Cheryl But Is It User Friendly? 8780
Green Marjorie The Busy Executive's Database Guide 4095
Bennet Abraham The Busy Executive's Database Guide 4095
Straight Dean Straight Talk About Computers 4095
Dull Ann Secrets of Silicon Valley 4095
Hunter Sheryl Secrets of Silicon Valley 4095
O'Leary Michael Sushi, Anyone? 4095
Gringlesby Burt Sushi, Anyone? 4095
Yokomoto Akiko Sushi, Anyone? 4095
White Johnson Prolonged Data Deprivation: Four Case Studies 4072
(14 row(s) affected)
Finished running dbo.author_ytd.
RETURN_VALUE = 0
Connecting the Stored Procedure to C++ code
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 (authors, titleauthor, and titles) that you specified during the AppWizard process. That's arranged by a function called CPublishingSet::GetDefaultSQL() that AppWizard wrote for you. It's shown in Listing 23.3.
Listing 23.3óCPublishingSet::GetDefaultSQL() from AppWizard
CString CPublishingSet::GetDefaultSQL()
{
return
_T("[dbo].[authors],[dbo].[titleauthor],[dbo].[titles]");
}
You are going to change this default SQL so that it calls your stored procedure, which is now part of the pubs database. First, choose Project, Set Active Project and select Publishing. Switch to ClassView in the Workspace pane, expand CPublishingSet, and double-click GetDefaultSQL() to edit it. Replace the code with that in Listing 23.4.
Listing 23.4óCPublishingSet::GetDefaultSQL() to call your stored procedure
CString CPublishingSet::GetDefaultSQL()
{
return _T("{CALL author_ytd(4000)}");
}
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.
The records returned from this query will go into your recordset. The query returns four columns (au_lname, au_fname, title, and ytd_sales) but the recordset is expecting far more than that. You can use ClassWizard to edit your recordset definition. Follow these steps:
Fig. 23.17 ClassWizard manages your record set definition.
Highlight [address] and click Delete Variable.
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 Chapter 22, "Database Access," and uses skills first demonstrated in Chapter 2, "Dialog Boxes and Controls," so the description here will be brief.
Click on the ResourceView tab, expand the resources, expand Dialogs, and double-click IDD_PUBLISHING_FORM. 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 IDC_QUERY_LNAME, IDC_QUERY_FNAME, IDC_QUERY_TITLE, and IDC_QUERY_YTDSALES.
Fig. 23.18 Edit your record view dialog box.
There is one task left: to connect these fields to member variables. Here's how to make that connection:
Fig. 23.19 Connect the record view controls to member variables of the record set.
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.
Fig. 23.20 Your application displays the results of the query in your stored procedure.
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.
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.
The DataView gives you full control over not just the contents of your SQL database, but its design. A raft of graphical tools makes it easy to see how the database works, or to change any aspect of it.
Database Designer
Return to the DataView, right-click the authors table, and choose Design. With the Database Designer, shown in Figure 23.21, you can change the key column, adjust the width, apply constraints on valid values and more.
Fig. 23.21 The Database Designer lets you change any aspect of the design of your database.
For example, click the Properties button at the rightmost end of the Table toolbar while au_id is selected to bring up the property sheet shown in Figure 23.22. The constraint shown here means that au_id must be a 9 digit number. Clicking the Relationship tab, shown in Figure 23.23, shows that au_id is used to connect the authors table to the titleauthor table.
Fig. 23.22 It's simple to specify column constraints.
Fig. 23.23 The Relationships tab makes it simple to see how tables are related.
If you're a database developer, you probably can't wait to open your own database in the Database Designer and set to work. Be sure to take advantage of the many shortcut menus available for you. For example, Figure 23.4 shows the menu that pops up when you right-click anywhere in the authors grid. The first item, Column Properties, is drawn with a pushed-in button to show it's selected. Select Column Names and the grid becomes much smalleróbring back the shortcut menu and choose Column Properties to return to the large grid of properties. Select Keys to see just those columns that are keys, and Name Only to shrink the grid to a tiny column showing only the name of the table.
Fig. 23.24 Shortcut menus make common tasks easy to select.
Database Diagrams
One of the easiest ways to get a lot of information across to people quickly is with a diagram. Figure 23.25 shows a diagram that explains the relationships between the three tables used throughout this chapter. To create the same diagram yourself, follow these steps:
Fig. 23.25 A picture is worth a thousand words when it's time to explain your database design.
If you wish, you can save this diagram in the database. Just click the Save button on the Standard toolbar and provide a name. The diagrams will be available to any other developers who use the Enterprise Edition to access this database.
To change any design decision about these tables, bring up the shortcut menu and choose Column Properties, then edit these properties just as you could in the Database Designer. How's that for an easy way to design and administer an SQL database?
Microsoft Transaction Server is a completely separate product that comes with the Enterprise Edition of Visual C++ but is not integrated with it. MTS enables you to use a collection of COM objects called components to securely implement distributed transactions within enterprise-scale database applications. Applications that use MTS can be written in any language that can produce ActiveX applications, including Visual C++, Visual J++, and Visual Basic.
To work with MTS, you must be comfortable doing under-the-hood ActiveX and COM programming, working directly with interfaces. If you've always relied on MFC to hide interfaces from you, you should probably read Chapter 21, "The Active Template Library," to gain an introduction to the way that interfaces are used. There's more information in the electronic copy of ìActiveX Programming with Visual C++,î which is included in its entirety on the CD that comes with this book
You can use MTS with almost any kind of database, including ordinary file systems, just as you can use ODBC with almost any kind of database. Certainly SQL databases will work with MTS, but so will a huge variety of other resource managers. This allows you access to the power of MTS without having to change your database system at all.
An MTS component is a COM object. It can do any specific task within your system, and often several components will be involved in a given transaction. Components are gathered together into packages which are installed as a unit onto your system.
A transaction is a unit of work that should succeed or fail as a whole. For example, if a customer is transferring money from one bank account to another, the money should be withdrawn from one account and deposited to the other. It does not make sense for one step in this process to fail and the other to proceed to completion. That would either take money away from customers unfairly, or give money to customers unfairly. Database programmers have long realized this, and developed ways of rolling back transactions that were partially completed when a step failed, or of pre-checking conditions to be sure that all the steps will succeed before starting. But these techniques are much more difficult to implement in a large, distributed systemótoo difficult to implement by hand.
For example, imagine two systems are about to take money (say, $100) from a customer's bank account. The first checks the balance and there is enough money. Both systems are connected through a network to the system that keeps the balance for that account. The first system asks for the balance and gets the reply: $150. Moments later, the second asks and is also told $150. The first confidently sends the request for $100 and succeeds, the second asks just a fraction of a second later for $100 and fails. Any portions of a transaction involving this customer that were already completed by the second system will now have to be rolled back. A transactional system like MTS makes this process much simpler for developers by providing system services to support these tasks.
Sound good? Then install the product and get going in the online help. There are two good sample systems included: one is a simple banking application and the other is a game. You can also check out Microsoft's Transaction Server Web site at http://www.microsoft.com/transaction.
If you work as part of a team of developers, a revision control system isn't just a nicety, it's a necessity. For too many teams the revision control system consists of sticking your head into the hall and telling your fellow programmers that you'll be working on fooble.h and fooble.cpp for a while, and to leave them alone. Or perhaps it is more about demanding to know who saved their changes to fooble.h over your changes because you both had the file open at once, and somebody saved after you did. There is a better way.
Revision control systems are not a new idea. They all implement these concepts:
Microsoft's Visual Source Safe is a good revision control system that many developers use to keep their code in order. What sets Visual SourceSafe apart from other RCS systems? It's project-oriented, it hooks into Visual C++ (through the new SCCI interface, some other RCS systems can also hook in,) and it comes with the Enterprise Edition of Visual C++.
When you install Visual SourceSafe, choose a custom installation and select Enable SourceSafe Integration. Doing this adds a cascading menu to Developer Studio's Project menu, shown in Figure 23.26.
Fig. 23.26 Installing Visual SourceSafe adds a cascading menu to the Project menu.
The items on the menu are as follows:
You must have an account and password set up in Visual SourceSafe before you can put a project under source control and use these features. Run Visual SourceSafe from this menu to perform any administrative tasks that have not already been taken care of for you.
Unless you are the only developer who will work on your project, you simply must use a revision control system. Visual SourceSafe is good, it works from within Developer Studio, and if you have the Enterprise Edition of Visual C++, it's free. What more could you want? Install it, learn it, use it. You won't regret it.
Revision control systems work as well on Web pages, database contents, documentation, bug lists, and spreadsheets as they do on code and program files. Once you get the habit and see the benefits, you just won't stop.
This chapter has introduced you to the Enterprise Edition of Visual C++. You've seen how easy it is to integrate C++ and SQL programming, even if your SQL is weak. The sample application in this chapter calls a simple stored procedure, and you can use this technique to call stored procedures written by SQL developers and wrap them in a friendly interface. You've also briefly met the Microsoft Transaction Server and Visual SourceSafe, two stand-alone products that come with the Enterprise Edition.
For information on related topics, try these chapters:
© 1997, QUE Corporation, an imprint of Macmillan Publishing USA, a Simon and Schuster Company.
