Database Access Methods - Online Article

In order to "talk" to your database you need to use some sort of software. Whether it comes with your server or you have to write the code yourself, this software is essential fordatabase communications.

Although there are innumerable methods of retrieving and storing data, the following are the most common: native, ODBC, and SQL. SQL is probably the most common data access method, ODBC a close second, and, except for driver creators, native methods are rarely used.

The following is a short discussion on each of the three access methods.

Native Drivers

Native drivers give you the raw power of talking directly to your database. When you make the connection and retrieve data, you are talking right to the file system. An example of a native driver is the Oracle Call Interface, or OCI from Oracle Corporation for Oracle databases.

Native drivers are usually statically or dynamically linked into your software at compile time.

Advantages

     
  • Very fast - with the actual database access code linked together with your program, data access is lightning fast.

Disadvantages

     
  • Not portable - applications created in this manner are usually not portable to other platforms without code modifications.
  •  
  • Inflexible - because the driver is linked into your application, changes in the driver software require possible recompilation of your application.

ODBC

Open Database Connectivity, or ODBC, is a standard developed by Microsoft Corporation. ODBC is an application program interface for accessing data in a standard manner from an abundance of data sources regardless of their type. If the data source is ODBC compliant, your program can talk to it.

ODBC drivers are available for almost every major database vendor.

Tip
Check out the ODBC Homepage for some cool ODBC links!
  http://ourworld.compuserve.com/homepages/VBrant/

Advantages

     
  • Fast - Not as fast as native drivers, but pretty fast. You have one additional layer of software to go through to get to your data.
  •  
  • SQL Enabled - You can use SQL to query the database.

Disadvantages

     
  • Not portable - Applications created in this manner are usually not portable to other platforms without code modifications.
  •  
  • Inflexible - Because the driver stub is linked into your application, changes in the driver software API require possible recompilation of your application.

SQL

Although not a "layer" of access to databases like ODBC or native drivers, the Structured Query Language, or SQL, provides a standard method of querying data from different data sources.

SQL, usually pronounced like the word "sequel," was adopted as an industry standard in 1986. SQL was completely overhauled in 1992 and the new language was called SQL92, or SQL2. Work is currently in progress to produce the next generation, SQL3. The following is a short list of SQL commands and their meanings:

     
  • COMMIT - Most RDBMS's work with units called transactions. A transaction can be made up of multiple actions. The COMMIT command instructs the database to record all the actions that you have performed up until this point, and to reset the transaction. When you COMMIT, the data is available to everyone who has access. Before the COMMIT occurs, however, only people with access to your schema can see the changes.
  •  
  • INSERT - Instructs the database to insert rows into a table.
  •  
  • DELETE - Instructs the database to delete rows from a table.
  •  
  • ROLLBACK - The ROLLBACK command is used instead of a COMMIT. This instructs the database to remove any changes you've made all the way back to the last COMMIT. This is very useful for long, multiple-table updates. For example, let's say you need to add 10 rows to a table. After inserting 9 rows, the 10th insert fails. The first 9rows must be removed for the data to retain its integrity. Using the ROLLBACK command, the 9 inserted rows will not be recorded.
  • SELECT - Instructs the database to return rows from a table.
  •  
  • UPDATE - Instructs the database to modify rows in a table.
  •  

We'll go over the syntax of some of the more commonly SQL commands. Just a reminder, though, that this is by no means an exhaustive SQL syntax review. Dozens of books about SQL have been published. The command syntax that follows is general ANSI SQL and might not be correct for your RDBMS. Please check your documentation if there is any doubt.

Note
In the syntax examples that follow, any parameter that is enclosed in square brackets ([]) is an optional parameter and may be left out.

The WHERE Clause

Most SQL commands act on all the rows of a table at one time. These global actions can be restricted to a limited number of rows by the use of a WHERE clause. The WHERE clause allows you to specify criteria that is used to limit the number of rows that an action is performed.

The general syntax for a WHERE clause is as follows:

COMMAND arguments WHERE [[[schema.]table.]column  OPERATOR value] [AND|OR [[[schema.]table.]column OPERATOR value]]

where

arguments are the arguments specific to the COMMAND.
schema is the area where the table exists.
table is the table where the column lives.
column is the column name to compare with value.
value is a literal or column name to compare with column.

Multiple operations may be checked in the WHERE clause. These can be linked with either the AND or OR keyword.

The OPERATOR might be many things depending on the RDBMS in use. Table 6.1 shows the OPERATORs that are available in most RDBMSs.

Table - Operators

   
OperatorMeaningExample
<Less thanemp_id < 10
>Greater thansalary > 50000
=Equal to can_be_paged = 'Y'
<=Less than or equal touser_count <= 128
>=Greater than or equal touser_count >= 0
<>Not equal tolost_shovels <> 5
isFor checking NULL valuesname_suffix is NULL
notFor negating an operatorname_suffix is not NULL
likeAllows for the use of wildcardsfirst_name like '%MUNSTER%'

Please note that the WHERE clause cannot be used alone. It must be appended to a DELETE, SELECT, or UPDATE command.

INSERT

The INSERT statement allows you to create a new row in a table.

The syntax for an INSERT statement is as follows:

INSERT INTO [schema.]table [(column[,column…])] VALUES (value[,value])

Where:

schema is where  the table exists
table is the  target table
column is the  column name(s) of the data you wish to insert
value is the  value(s) that you wish to insert

Examples

INSERT INTO EMPLOYEE ( EMP_ID, LAST_NAME  ) values ( 1, 'Munster' )
  INSERT INTO ADDRESS ( EMP_ID, STREET_ADDRESS ) VALUES
¥ ( 1, '1313 Mockingbird Lane' )

DELETE

The DELETE statement allows you to remove a row or rows from a table.

The syntax for a DELETE statement is as follows:

DELETE FROM [schema.]table [WHERE expression]
where
schema is where  the table exists
table is the  target table
expression is an expression  as outlined in the preceding WHERE clause section
Caution
Without a WHERE clause, the DELETE command removes all rows from a table.

Examples

DELETE FROM EMPLOYEE WHERE EMP_ID = 1
  DELETE FROM ADDRESS WHERE CITY LIKE 'chICAG%'

SELECT

The SELECT statement allows you to retrieve a row or rows from a table.

The syntax for a SELECT statement is as follows:

SELECT [[schema.]table.]column [,[[schema.]table.]column]  FROM [schema.]table [WHERE expression]

where

schema is where  the table exists
table is the  target table
column is column  or columns to retrieve. You can use the asterisk ('*') to indicate  that the SELECT statement  should return all columns.
expression is an expression  as outlined in the preceding WHERE clause section

Examples

SELECT EMP_ID FROM EMPLOYEE
  SELECT LAST_NAME, FIRST_NAME, MID_NAME FROM EMPLOYEE WHERE EMP_ID  = 666

UPDATE

The UPDATE statement allows you to modify a column or columns in one or more rows in a table.

The syntax for an UPDATE statement is as follows:

UPDATE [schema.]table SET [[schema.]table.]column  = value [,[[schema.]table.]column = value] [WHERE expression]

where

schema is where  the table exists
table is the  target table
column is column  or columns to modify
expression is an expression  as outlined in the preceding WHERE clause section
value is the new value  that the column should hold

Examples

UPDATE EMPLOYEE SET SALARY = SALARY +  ( SALARY * .05 )
  UPDATE ADDRESS SET ZIP_CODE = 60805 WHERE ZIP_CODE = 60642

Advantages

     
  • Simple English Syntax - You do not need to know how to program to use SQL.
  •  
  • Standardized - Usually, SQL accesses one RDBMS, which can be used to access another RDBMS.

Disadvantages

     
  • Wordy - SQL queries can become quite complex and lengthy. Because SQL syntax uses regular English words, the results can get somewhat monotonous.

SQL uses simple English words to instruct the database to perform certain actions. SQL can be used with almost every major database product available today. In addition, you can even use SQL syntax to interact with a data source using ODBC!

Tip
Here are some useful SQL Web sites: 

About the Author:

No further information.




Comments

No comment yet. Be the first to post a comment.