7.Data Manipulation

7. Data Manipulation
Introduction:-
The SQL language is powerful and can be used to solve complex problems. However, it cannot solve every problem. There are times when you need to use a procedural language. For example, some computations may required several steps. In particular, decisions involving complex conditions can be easier to evaluate with a procedural language than with a SQL.
Similarly, complex transactions might require updating several tables, so you use a procedural language to sequence the changes in the proper order. You will often have to create modules to interact with users or to respond to user requests. Few standards are defined for using procedural languages within a database environment. The primary environment is Microsoft Access Visual Basic for Application (VBA), which has some powerful (and complex) capabilities for user interaction in the Windows environment.

Computer tools:-Dealing with totals and subtotals is a challenging problem in database application design. Consider the basic sales from example, where a subform displays purchases of individual items. The main advantage to storing totals is that it is faster to retrieve them later, and the queries are easier to write. If you do not store the totals, then your queries have to computer the value every time you need them. These calculations are slower than retrieving the totals because the process entails retrieving all of the underlying details rows, instead of just one total line.
The main drawback to storing calculated values is that the totals might become inconsistent.
To be safe, for all stored totals, you need to include a separate program that goes back to the underlying transaction details and computers the correct totals. Many of these calculations required the use of procedural languages, or chains of SQL statements.

Procedural language:- Procedural language is the generic term for a traditional programming language such as BASIC, COBOL, C++, or Java that evaluates commands in sequential order and contains certain basic logic elements. To write programs, you need to learn the basic operations listed in below:-
• Variables
• Computations
• Standard functions
• Debug
• Output
• Input
• Conditions
• Loops
• Arrays
Programming ultimately reduces to two components: logic and syntax. Programming logic represents the structure of the program and the use of the programming elements to solve the underlying problem.
Syntax represents the specific commands and features in a programming language.

Programming environment:-Even beyond basic structure and syntax, DBMS vendors have several choices about how to create a programming language. Perhaps the biggest choice is exactly where the language will be used. Three common methods is used:
1. within the database forms and reports.
2. within the query system as an extension to SQL
3. hosted within separate programs outside the DBMS(Embedded SQL)
The location or environment, of the language affects what it can do, how it is used and what you will have to learn. For example, to embed SQL within an external program, if you already known the host language (e.g., C++ or COBOL), you only have to learn how to transfer data between SQL and the host language. On the other hand, languages inside the DBMS have their own logic and syntax, so you have to learn an entirely new system.

Windows Environment:-
The windows interface has significantly changed way programs operate. In the “old days”, each program had its own style. The programmer had to design every aspect of the user interface. Also, the program was in control form the start of the session to the end. The user and the operating system (Windows) have control of the computer. As a developer, you write procedures that are called by the user or the operating system to perform a specific task. When the task is accomplished, control is returned to the operating system. As the user performs some task, the event triggers your code to run. A key aspect of developing system is to identify which of the many events should trigger your code.
In many systems (e.g., Oracle and SQL Server), triggers can also be define directly on the data tables.

Data on Forms:-
When you need a user to enter data, you will create a form. Forms are also used to display data and results of computations. Each control on the form represents a type of data associated with that form.
You can also create unbound controls that will hold and display data on a form temporarily. When the form closed, the unbound data disappears. You can use unbound controls when you want users to enter raw data. Then your procedure performs computations on the data and stores it in the database.

Using programs to save and retrieve data in the data base:-
Once you can understand the basic operation of cursors, it is straightforward to retrieve or change the data. Just remember that you change data on row at a time. In Microsoft Access you essentially treat the columns as variables. For example, to obtain the value in the BalanceDue column of the current row, you would use rst(“BalanceDue”).
The advantages of the programming arise when the calculations are more complex. In particular, the code within the loop might have several conditions (If/Then/Else) and use data that was entered on the form.

Handling error:-
There are many sources of errors like mistake in code, faulty data entered by users, hardware or network failures, operating system glitches, and so on. You need to create your programs so that they can deal with errors. If you do not, the program might crash and the user will be left with a blank screen and lost data.
The two primary philosophies exit for handling errors:-
a. Check whether every major operation is performed correctly.

b. Write a special error-handling routine that is called only when any error arise.

Eg:-
On Erroe Go to ErrSub1
Program code
:
:
ErrSub:
Program to handle error