Beginning SQL Programming: Pt. 2

Thursday Jul 26th 2001 by DatabaseJournal.com Staff
Share:

This second installment covers procedural versus declarative languages, SQL's place in the data center and obtaining SQL.

Procedural Versus Declarative Languages

We mentioned at the beginning of the chapter, that SQL is not a procedural language. Before we move on to look at how we can actually implement SQL, we'll first see exactly what we mean by a declarative rather than procedural language. Many readers will have experience in languages like Visual Basic or C++ in which they mastered how to write a series of statements in order to achieve a goal. You have probably even used these commands to "walk through" a set of data checking to see if each item is the item of interest. If you wanted to change data you wrote a series of steps that somehow opened up the datastore, then put a pointer in the correct location, modified the data, moved to the next bit of data (all the while checking if you were at the end of the data yet), modified the next record and finished by closing the datastore. In a procedural language we tell the computer each step to perform. The computer performs those steps and, if they are well written, we will end up with our desired result.

SQL is not a procedural language but a declarative language. You write a single SQL declaration and hand it to the DBMS. The DBMS then executes internal code, which is hidden from us. The DBMS returns a set, which is a group of data that is somehow defined. For example, we write a SQL statement that would translate as something like Give me the last names of all the employees. The DBMS will work away for a few milliseconds and then produce a set that contains Adams, Barrett, Cao, etc. In a declarative language, we carefully phrase what we want and then let the DBMS get it for us. If we have written a good SQL statement then the resulting set of data will be correct.

Procedural languages result in many lines of code. Declarative languages result in one statement of the desired result.

The distinction between these classes of languages drives the entire way that we think about database programming. Many of the mistakes that I see are a result of thinking in a procedural way when writing SQL statements. In this book we will show you hundreds of examples of good SQL statements that will help you on your way to thinking declaratively.

Languages in a Taxi Ride

A good analogy exists between these types of languages and different types of taxi rides. When I get in a taxi I can give a direction to the driver in one of two ways. If the driver looks and sounds like he knows what he is doing then I just give him the address. If the driver looks like he just moved to town yesterday, then I talk him through the directions to get to my destination.

If I give the driver an address I am communicating like a SQL statement, it is a clear description of the result and the driver carries it out as he or she sees fit. We may go south then east or we may go east then south. The driver is free to adapt to local conditions like a closed road or a traffic jam. It doesn't really matter to me; a good taxi driver will get me to my stated destination in the best way the driver knows. During the trip I just sit still and wait for the result. If the address is written on a piece of paper then I can show that same card to any good driver.

If I give the driver exact directions then I am communicating in a procedural language. I tell the driver to head south four blocks then turn left at the Luck Hunan Restaurant. Procedural languages work fine when the taxi driver is not too experienced and when the situation is very uniform. But I must constantly interact with the driver. If I get involved in a book we are likely to overshoot our destination. Likewise, if I tell the driver to turn left at the Luck Hunan Restaurant and the name has changed to the Lucky Duck Bar, then the communication will fail.

In this analogy we see a number of important points that apply to SQL. First, a declarative language only works when you have a strong, SQL-enabled DBMS (taxi driver). Second, procedural languages can fail when there are unexpected changes in the situation (changes in the city's landmarks). Third, procedural languages require interaction during the task (like me instructing the driver at every turn) whereas declarative languages only require that the requester wait for the process to finish. And fourth, a well-written SQL statement will work with any SQL-enabled DBMS (like an address on a paper will work with any good taxi driver).

Comparing Procedural and Declarative Languages

Procedural (Basic, C++, Cobol, etc.) Declarative (SQL)
Most work done by interpreter of the languages Most work done by Data Engine within the DBMS
Many lines of code to perform a task One SQL statement to perform task
Programmer must be skilled in translating the objective into lines of procedural code Programmer must be skilled in clearly stating the objective as a SQL statement
Requires minimum of management around the actual data Relies on sophisticated, SQL-enabled DBMS to hold the data and execute the SQL statement against the data
Programmer understands and has access to each step of the code Programmer has no interaction with the execution of the SQL statement
Data exposed to programmer during execution of the code Programmer receives data at end as an entire set
More susceptible to failure due to changes in the data structure More resistant to changes in the data structure
Traditionally faster, but that is changing Originally slower, but now setting speed records
Code of procedure tightly linked to front end Same SQL statements will work with most front ends
Code loosely linked to front end.
Code tightly integrated with structure of the datastore Code loosely linked to structure of data; DBMS handles structural issues
Programmer works with a pointer or cursor Programmer not concerned with positioning
Knowledge of coding tricks applies only to one language Knowledge of SQL tricks applies to any language using SQL

To summarize this table, declarative languages are quite different from the procedural languages you may be using now. Procedural languages like C++ place an emphasis on the programmer writing many lines of code to describe the exact steps of obtaining a result. Declarative languages like SQL place an emphasis on the programmer writing an exact description of the desired result. The DBMS then handles the task of obtaining the result. A good SQL programmer becomes very skilled at carefully describing the result but remains blissfully ignorant of the internal code of the DBMS that executes the result.

SQL's Place in the Data Center

So how does SQL actually fit into the solution to my data management problems? We discussed earlier that SQL did not have a front end (user interface) or a back end (DBMS). So in the next few sections we will discuss some "ends" that we can use as well as the protocols that create a bridge across which the SQL statements flow.

"How Do I Get SQL? "

You need a front end that can connect to a SQL-enabled DBMS and can pass a SQL statement. We will talk more about connections in Chapter 2, but for now consider a connection to be a conduit for carrying SQL statements between the front and back ends. A connection is like the apparatus of the telephone company that connects your handset to your friend's handset – it allows a language to be used to communicate. Third, you need a SQL-enabled DBMS. You probably already have a SQL-enabled front end and a DBMS in one form or another (see the sections below). And if you don't you can get an inexpensive version or a trial version that will allow you to get started learning SQL for free. Keep in mind that since SQL is a standard in the public domain, you do not need software or a license to use the language. You can acquire SQL by simply learning the syntax of how to write the SQL statements. But in order to practice creating and running SQL statements you will need three parts:

  • a front end
  • a connection
  • a back end

We will look in detail at various options for each of these, but let us start with a quick overview.

The front end provides a means to send a SQL statement. Usually the front end includes an interface to users with a form to gather data and buttons to carry out tasks. The front end may contain a fixed SQL statement or it may use code to create a statement on the fly using the data provided by the user. The most common type of front end is the web browser.

The connection provides a conduit to get the SQL statement to the back end and then to return results to the front end. The connection is like a telephone connection over which a spoken language travels, although the language itself is free. A common connection today is ADO (Activex Data Objects) but there are many other options.

The back end is a DBMS that accepts and can act on SQL statements. Usually the back end has two parts, the actual store of data and a database engine, which can carry out the SQL statement. The most common back ends are the major database products such as Oracle and Microsoft SQL Server.

placement of front end, back end, and connection

The included CD contains a front end, connection and back end, but in a less conventional arrangement than the one we have just described. The CD is a 120-day trial version of Microsoft SQL Server 2000. SQL Server provides a very strong back end, but it does not come with front end or connection software. However, there is a utility called Query Analyzer which comes with SQL Server. This tool provides a front end optimized for developers that want to test SQL statements. It is a very simple front end that allows you to type a SQL Statement, run it and then view the results. A normal front end would have tools for creating a polished interface for users, but Query Analyzer is aimed at developers that are testing. Built into Query Analyzer is connection software that automatically creates a connection when the developer selects which database to work with.

Front Ends

In this book we will use different front ends in chapters in order to help you become more versatile. The differences are not very great; the SQL statements remain the same regardless of the front end. In Chapter 2 we will talk you through setting up various combination of front ends and back ends as well as showing samples of code.

The syntax of how each front end sends those SQL statements may vary occasionally. Likewise, each front end has its own syntax or tools for handling the data that is returned when a SQL statement asks for information. We will point such differences out where they may occur.

Access

Microsoft's desktop database uses SQL in many places.  You can write and store queries as SQL statements. You can use SQL to define the source of a report or form. One option puts the actual SQL statement into the report as the value of the Record Source property. A second option stores the statement in a query and uses the query name as the value for the Record Source. SQL statements are also valid sources for list boxes on Access forms. Although Access never requires a user to employ SQL, in many places SQL can be used instead of Access object names.

The query grid contains a very powerful feature for students of SQL with Access experience. You can use the drag and drop interface of the grid to create a query, the same as you have already done many times in the past. But you can then click on View/SQL and see that visual interface expressed as a SQL statement, ready to cut and paste. Similarly a query can be created as a SQL statement and then viewed as a grid. At any time the query can be run to check the results.

You may be familiar with the Access Query Grid view:

Access Query Grid View
(Click picture for full size view)

We can click on Menu:View | SQL View to see the same request written as a SQL statement:

Access Query as SQL
(Click picture for full size view)

ASP

Active Server Pages, Microsoft's technology for server-side building of dynamic web pages, has the ability to use the ADO component (more on ADO in Chapter 2). The ADO Object allows a programmer to connect an ASP page to a datastore and then send a SQL command to that datastore and receive information back. For example, a web page visitor could enter her membership number into an ASP page and then click Submit. The responding ASP page could use that membership number to look up her account in the corporate database and return appropriate information in a table, text, or list box. ASP is not limited to reading data; it can use SQL statements to instruct the data store to add, change or delete data as per the web visitor's instructions.

For further information regarding Active Server Pages, you can visit http://msdn.microsoft.com/library/backgrnd/html/msdn_aspfaq.htm. You will find an introductory tutorial in ASP in Appendix E at the back of this book.

Visual Basic

Visual Basic, from Microsoft, offers an easy-to-learn front end for Windows applications. It comes with a rich, graphical developer's environment, which allows quick prototyping and easy revisions. The finished product can then be compiled and deployed to any PC running Windows. The chief disadvantage of Visual Basic is that the features that speed up development also cause the applications to be slightly slower and less robust when deployed. The disadvantages will be largely eliminated with the .NET release of Visual Basic. Even though Visual Basic clocks a few percent slower than C++, it is still much faster than scripting languages used in ASP.

Visual Basic includes a DataGrid, which can be filled by the results of a SQL statement; other data– linked interfaces, like list boxes and text boxes, can also be filled from a datastore using SQL. Changes made to data on forms can be written to the database, either automatically or through specific SQL statements. Several third parties offer even more sophisticated controls that produce slick data-linked interfaces with minimal coding.

See Chapter 2 for code specifics of working with VB and SQL and www.vbextras.com for listings of third party products.

In addition to using the controls, you can build and use SQL statements in any VB code. For example, a button on a form might fire an event containing code to send a SQL statement that will change data in certain records. Likewise, data returning from a SQL statement can be accepted into VB code and then manipulated.

For further information regarding Visual Basic & Visual Studio visit http://msdn.microsoft.com/vstudio/prodinfo/overview.asp.

C++

Like ASP and VB, C++ can use ADO to connect to a DBMS and pass a SQL statement. The results are returned and can be used in an array, as variables or in expressions. Code that uses ADO, particularly the recent versions, is both efficient and flexible.

Besides ADO, C++ Programmers have an additional option of using connection technologies such as ODBC or OLEDB (covered in Chapter 2) to pass SQL statements to the DBMS. This procedure involves creating and managing Data Classes. Although more intensive, you can exert a finer level of control than using ADO.

Other Front Ends

Almost every other front end can process a SQL statement to a DBMS. For example, using Visual Basic for Applications (VBA) you can have Microsoft Word (including Mail Merge) use SQL to access data. Likewise, Excel VBA can connect your spreadsheet to a datastore and pass SQL statements to it. PowerBuilder and Delphi are tools similar to Visual Basic that can create a user interface. That interface can also communicate with data stores via SQL statements. Visit http://www.sybase.com/products/powerbuilder/ to learn more about PowerBuilder. Cold Fusion can use proprietary tags to send SQL statements to a DBMS. DBMS vendors such as Oracle offer other packages for front-end development. Both Java and JDBC can also create front ends that will both send and receive the results of SQL statements.

Back Ends

The front end will contain or create a SQL statement and have a way in which to connect to a back end, as we discussed in the sections above. We noted that virtually every front end can send a SQL statement. Likewise, virtually every DBMS sold in the last ten years can receive and process SQL statements. The heavy-duty products designed to support enterprises generally have the term "Server" in their product title in some way. Lightweight back ends are typically desktop applications, which also contain code to support receiving SQL Statements. They can all be used as back ends to receive and process a SQL statement.

Microsoft

The flagship database management system from Microsoft is SQL Server (versions 7 and 2000). The product is optimized to run on Windows NT (or Windows 2000) and has done well in speed test against competing products. Microsoft SQL Server is often less expensive to deploy than competing DBMS. Furthermore, an aggressive training and certification program is increasing the number of professionals qualified to program and run Microsoft SQL Server.

For product information on Microsoft SQL Server 7 or 2000, visit http://www.microsoft.com/sql/productinfo/prodover.htm.

Although SQL Server is Microsoft's enterprise scale DBMS product (it falls under the umbrella of their .Net enterprises servers), there are many other Microsoft products that can use SQL.

Two DBMS products have similar names: Microsoft SQL Server and Sybase SQL Server are both specific DBMS products. These are two of many products that can receive SQL statements and thus function as back ends.

Oracle

Oracle offers one of the world's most widely deployed DBMS, currently named Oracle9i. The Oracle product has proven itself in many of the largest datastores supporting entire corporations. Oracle has established itself as a leader for web sites with very high traffic. The explosion of data access requests from the web has pushed Oracle to further increase speed, capacity and reliability.

To check out Oracle DBMS products see http://www.oracle.com/ip/deploy/database/index.html.

Sybase

Sybase currently offers three DBMS products. Adaptive Server Enterprise is optimized for enterprise computing and web site support. SQL Anywhere Studio provides a suite of tools for rapid development and deployment of business solutions. Adaptive Server IQ provides improved support for the analysis of data and ad hoc queries.

Information about Sybase products can be found at http://www.sybase.com/products/databaseservers/.

IBM

IBM offers the DB2 family of products, which run on the hardware and operating systems of both IBM and non-IBM machines, including a developers kit for their products on Linux platforms. The DB2 Universal Version 7 includes features that move more operations to memory and thus improve performance.

Go to http://www-4.ibm.com/software/data/db2/ for information on IBM's DB2.

Desktop DBMS

You do not need to buy and maintain a Data Server in order to have a back end. Several desktop applications can support SQL requests and will run in memory space on your laptop.

Oracle offers an 8i Personal Edition that can be installed on a desktop. This version is fully capable of storing data and then accepting SQL statements against that data. However, it is not intended to scale to many simultaneous users. As of early 2001, a free trial download is available from www.oracle.com.

Microsoft also offers products for the desktop. First is Access, a DBMS that is easy to learn and powerful but was not designed to scale to more than a few users at once. Access has a drag and drop interface which translates to SQL, and displays both translations on the monitor for most points in the design screens. Note that Access can function as both a front end with the forms and reports objects and as a back end with its table and query objects.

Note that we have mentioned Access under both a front and back end. Some of Access's tools such as forms and reports function as a front end. Other tools such as queries and Tables function as back ends. For people using Access as an entire desktop database solution the distinction is not important. But the front and back end tools can be used separately in conjunction with products from other vendors. For example, an Access front end can connect to an IBM DB2 datastore. Or to switch hands, an ASP page can connect to Access queries and Tables offered as backends. In the case of these cross-software connections, SQL is the language for communication.

There are several places within Access where you can use SQL statements instead of using the names of objects like tables or queries. We discuss these in Chapter 2.

Microsoft also offers the MSDE (Microsoft Data Engine) which is essentially the Microsoft SQL Server data engine without the rest of the DBMS features (such as Query Analyzer), and without the ability to scale beyond a few users. You can use MSDE to manage your data and to run SQL statements against that data, but you do not have the tools for performance monitoring, replication, security and other features found in SQL Server. MSDE is, at the time of this writing, a free download from www.microsoft.com. It also ships with editions of Microsoft Office 2000 which include Access (Microsoft Office 2000 Premium or developer editions).

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved